knex only add username if it doesn't already exist

Question

I am using knex. Would like an easy way to check if the username exists in db. If it does, return error message ("username already exists"). If not then insert.

router.post('/register', (req, res) => {

const username = req.body.username;
const email = req.body.email;
const password = req.body.password;

  knex('users')
  .insert([{
    username: req.body.username,
    email: req.body.email,
    password: bcrypt.hashSync(req.body.password, 10)
   }]).then(()=>{})
 })

UPDATED Ok so i have the program working as expected. It inserts only if username and email don't exist in table. However I have two issues when I wrote this code.

Please note that I am fairly new with knex library and promises.

1. Performance, my whole app has slowed down

2. My if/else statement ends up always running both commands however, it never inserts into DB if email or username exist. However I always see the console.log even when it doesn't insert.

    const insertUser = knex('users')
   .returning('id')
   .insert([{
    username: req.body.username,
    email: req.body.email,
    password: bcrypt.hashSync(req.body.password, 10)
   }]).then(userNameValid => {console.log(userNameValid)})
   
   const validityCheck = knex.select("username")
   .from("users")
   .where("username", username)
   .andWhere("email", email)
   .then(userNametList => {
   console.log(userNametList)
   })
   
   if (!validityCheck){
   return insertUser;
   } else if (validityCheck !== null){
    return console.log('Username or email is already in use')
   }
   

Answer 1

There's not a one shot knex function for that. If you have a unique constraint on the username (as you should) then you can do an insert and catch if it fails. The failure message would go in the catch handler.

Better practice would be to do a select first and only attempt the insert if it doesn't already exist otherwise return the error message before attempting the insert.

Updated answer for updated question: The insertUser promise is executed when it gets created so you are always inserting a user (and failing if it violates the unique constraint).

Your variable validityCheck is a promise, so !validityCheck will always be false.

What you want to do is create the insert user promise in the .then( ...) block of the validity check...

knex.select("username")
    .from("users")
    .where("username", username)
    .andWhere("email", email)
    .then(userNametList => {
        if (userNameList.length === 0) {
            return knex('users')
              .returning('id')
              .insert([{
                username: req.body.username,
                email: req.body.email,
                password: bcrypt.hashSync(req.body.password, 10);
              }])
              .then((newUserId) => {
                  console.log('inserted user', newUserId);
              });
        }
    console.log('not inserting user');
    return;
});