CODEWITHSUNDEEP
javascriptmysqlnode.jsknex.js
tribe
tribe

Reputation: 321

How to find or create with Knex?

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.

Upvotes: 3

Views: 6194

Answers (2)

saeta
saeta

Reputation: 4238

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)
  );

Upvotes: 1

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

With mysql you probably need like 3 queries, since it doesn't support well common 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]}`));

Upvotes: 5

Related Questions