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
}

Answer 1

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.