6

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))
  )
renathy
  • 4,345
  • 13
  • 73
  • 127

1 Answers1

3

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.

Max Vollmer
  • 8,102
  • 9
  • 27
  • 43
GaryL
  • 1,007
  • 6
  • 10
  • I tried rawStr variant and it often gives me Deadlock found. Not sure why. – renathy Jan 15 '18 at 14:23
  • Bummer. To troubleshoot that, I would first try using the SQL shown by the `.on('query'`, and running it via a SQL command window to see if gives you the same problem. In the end, the deadlock is a DB processing issue, so the root of this issue is that some sequence of SQLs you are using are conflicting with each other. So you need to determine which SQLs (and/or DB commitment control settings) are causing the conflict. Knex's purpose is just to translate code into SQL for you. – GaryL Jan 15 '18 at 16:51
  • When I run the code for one query (once), then everyting works. The same as if running all sqls directy in mysql. However, when run using knex with many products (probably, because of asyncrhonous thing). SQL is correct: INSERT INTO product_ingredients (ingredient_id, product_id) SELECT 450, 28 WHERE NOT EXISTS(SELECT 1 FROM product_ingredients WHERE ingredient_id = 450 and product_id = 28); Not sure, why deadlock can happen here. – renathy Jan 19 '18 at 14:48
  • Btw, If I choose second query, then I do not get any errors (yet, while I am not running on the whole data). – renathy Jan 19 '18 at 14:58
  • Unfortunately the correctness of the raw SQL (or possibly not - due to the deadlock issue) is getting beyond my expertise. I do see a MySQL discussion around your goal here: https://stackoverflow.com/questions/17991479/insert-values-where-not-exists ... which suggests different SQLs to accomplish your goal. – GaryL Jan 20 '18 at 04:32