Pg-promise: proper way of returning result of query

Question

I want to check whether a username is already in use using pg-promise.

I use the following query:

this.db.one('SELECT EXISTS(SELECT 1 FROM users WHERE username = $1)', username);

I am trying to encapsulate this query inside a function that simply returns true if the username exists, false if not.

Something like:

existsUsername(username){
  this.db.one('SELECT EXISTS(SELECT 1 FROM users WHERE username = $1)', username)
         .then(data => {
           if(data.exists == true){
             return true;
           } else {
             return false;
           }
         });
}

That I can simply use like so:

if(db.users.existsUsername(username)){
  // this username is already taken
}

However the if condition is assessed before the query finishes, resulting in an undefined variable.

What is the proper way of returning the result of a query?

EDIT: the outer caller performs multiple async checks and returns whether the user is valid or not:

function signUp(username, email, ...){
  // perform username existence check existsUser(username)

  // perform email existence check existsEmail(username)

  // ...

  // if everything OK, put user in DB
}

U seems to forgot return in existsUsername – Alejandro May 03 '18 at 16:56 — Alejandro, May 03 '18 at 16:56

vitaly-t · Accepted Answer · 2018-05-06T23:10:56.440

Simplest way of doing it:

existsUsername(username) {
  return this.db.oneOrNone('SELECT * FROM users WHERE username = $1 LIMIT 1', username, a => !!a);
}

And then use it:

db.users.existsUsername(username)
   .then(exists => {
      // exists - boolean
   })
   .catch(error => {
   });

You cannot do things like if(db.users.existsUsername(username)), that's mixing up synchronous code with asynchronous. But you can do if(await db.users.existsUsername(username)), if ES7 syntax is available to you.

And if you have three independent functions like that (checkUserName, checkEmail, checkWhateverElse), and want to execute them all, here's the best way to do it:

db.task(t => {
   return t.batch([checkUserName(t), checkEmail(t), checkWhateverElse(t)]);
})
.then(data => {
    // data = result of the batch operation;
})
.catch(error => {
   // error
});

The same with ES7 syntax:

db.task(async t => {
   const a = await checkUserName(t);
   const b = await checkEmail(t);
   const c = await checkWhateverElse(t);
   return {a, b, c};
})
.then(data => {
    // data = {a, b, c} object;
})
.catch(error => {
   // error
});

Note: Each of those functions is expected to execute queries against t - task context, in order to share the connection.

Alejandro · Answer 2 · 2018-05-04T07:37:44.480

1

You can't use async operation in sync way, you need to rewrite the code that checks if user exists in async way as well. I.e.:

// returns a promise 
function existsUsername(username){
   return this.db.one('SELECT EXISTS(SELECT 1 FROM users WHERE username = $1)', username);
}

And next use it in app in a way like

db.users.existsUsername(username)
   .then( data => {
      data.exists ? handleUserExistsAsync() : handleUserNotExistsAsync();
   })
   .catch( err => {
      // some err occurs, db fail or something
      // however, you can catch it in an upper level
   });

EDIT: Using Promise.all for multiple tasks, may have performance and connection issues (as Vitaly mentioned).

It's better to use db.batch inside db.task

edited May 04 '18 at 07:37

answered May 03 '18 at 17:06

Alejandro

5,834
1
19
36

1

Thanks for your answer. But then as I have multiple such queries (existsUsername, existsEmail etc), how do I know that all the async operations are finished and that I can move on to the return statement of the caller? – Kathandrax May 03 '18 at 17:20
They finished in callback you passed to `.then( data => { ...query done with data... })` – Alejandro May 03 '18 at 17:23
I edited my post to better reflect what I had in mind. I meant to say I need some sort of barrier that guarantees all callbacks have finished before the outer caller can finish. – Kathandrax May 03 '18 at 17:47
1

Yeah, use Promise.all([checkUserName(), checkEmail(), checkWhateverElse()]).then( arrayOfChecksResults => // check that arrayIs ok, and save user ) – Alejandro May 03 '18 at 18:02
1

Beside Promise.all there are other methods to manage bunch of promises, you can check which one is preferable for you – Alejandro May 03 '18 at 18:06
1

That's exactly what I was looking for! Thanks! – Kathandrax May 03 '18 at 18:17
1

Please note that http://bluebirdjs.com/docs/getting-started.html buebird promise have better perfarmance – Alejandro May 03 '18 at 19:08
2

You should never use `Promise.all` with the database methods, as they all end up executing against separate connections, and that's not good for performance. See [Chaining Queries](https://github.com/vitaly-t/pg-promise/wiki/Chaining-Queries). Instead, you should use a task with a `batch`. – vitaly-t May 03 '18 at 19:39

Pg-promise: proper way of returning result of query

2 Answers2