In terms of a simple user registration, how can I do a find or create by username with best performance in mind? Guessing I need to use raw queries but I'm not so sql savvy as of yet.

Or at least some kind rejection if the username already exist.

In terms of a simple user registration, how can I do a find or create by username with best performance in mind? Guessing I need to use raw queries but I'm not so sql savvy as of yet.

Or at least some kind rejection if the username already exist.

• javascript
• mysql
• node.js
• knex.js

2 Answers 2

With mysql you probably need like 3 queries, since it doesn't support well mon table expressions (maybe some really new mysql actually supports them) nor returning inserted data:

Get user, if not found insert, get user again (Mysql fetch the row just inserted).

Something like:

  knex.transaction(trx => {
    trx('users').where('username', name).then(res => {
      if (res.length === 0) {
        return trx('users').insert({ username: name }).then(() => {
          return trx('users').where('username', name);
        });
      } else {
        return res;
      }
    });
  }).then(res => console.log(`User is: ${res[0]}`));

I was dealing with this for a whole day. Not sure if this solution works with all databases supported by Knex. I'm using PostgreSQL.

knex('users')
  .insert(
    db('users')
      .select(knex.raw('? ? ?', [
        username,
        first_name,
        last_name
      ]))
      .whereNotExists(
        knex('users').select(0).where({username})
      )
      .limit(1)
  );