how to insert if value not exists in knex (nodejs)

Question

I am very new to knex (and node js). I need to write Insert if exists and i am unable to do this.

Below is not working, but is there a way to do this?

var val = "water";
knex('ingredients').insert({'name': val})
      .select(val)
      .whereNotExists(knex('ingredients').where('name', val))
  )

Answer 1

When I tried to do this, .select and .whereNotExists are ignored by KNEX when they come after the .insert function. So here are 2 other ways to get there...

1. Embed the SQL via KNEX.
2. Use KNEX to first attempt to retrieve a match, and then do the insert
   or not, depending on the result.
3. Another method, which I'm assuming from your question is not a direction you want to go, but which I usually use, is to create a database constraint which will reject duplicates.

Embedding SQL in KNEX:

The SQL you want looks like:

INSERT INTO ingredients (name)
    SELECT "name_val"
    WHERE NOT EXISTS 
         (SELECT 1 FROM ingredients WHERE name = "name_val");

So putting that into KNEX looks like:

var val = "water";
var rawStr = "INSERT INTO ingredients (name) SELECT ? WHERE NOT EXISTS(SELECT 1 FROM ingredients WHERE name = ?)";
return knex.raw(rawStr, [val, val])
    .on('query', function(data) {
        console.log("ON-QUERY data:", data);
    });

The optional .on('query' is a very handy troubleshooting tool to see if you're getting the SQL that you are expecting. (see also: query-error and query-results in the knexjs.org site) A downside of this method is that a duplicate fails silently, so no-one is informed of the failure.

Native KNEX:

var val = "water";
return knex('ingredients')
  .select()
  .where('name', val)
  .then(function(rows) {
    if (rows.length===0) {
      // no matching records found
      return knex('ingredients').insert({'name': val})
    } else {
      // return or throw - duplicate name found
    }
  })
  .catch(function(ex) {
    // you can find errors here.
  })

With this second method, I suspect that you may be more subject to concurrency issues, where 2 users both attempt to do 'simultaneous' updates with unexpected results if you have high application usage volumes. On the up side, this method makes it easy to capture and respond to your users that there is a duplicate name error.