knex issue whereNotExists

Question

I am having some issues with a Knex route for PostgreSQL. I am trying to insert into a database but only when the item is not in the database already. I am trying to use where not exists but it doesn't seem to be doing what I want it to. I appreciate any help you can give me.

Thank you!

app.post('/addcart', (req,res)=>{
  const{customer_id, product_id,item_quantity}=req.body;
  db('shopping_carts')
  .insert({
    customer_id:customer_id,
    product_id:product_id,
    item_quantity:item_quantity
  })
  .whereNotExists(db.select('*').from('shopping_carts').where('product_id',product_id))
  .then(item=>{
    console.log(item)
    res.json(item)
  })
  .catch((err)=>{
    if(err.column === 'customer_id'){
      res.status(400).json({message:err})
      console.log('test')
    }else{
      res.status(500).json({message:err})
      // console.log(err.name);
    }
  })
})

Answer 1

You can't combine a whereNotExists query with an insert query, they don't support this due to it's complexity (and per @mikael, most db's don't support this). So knex ignores the whereNotExists call after the insert in your method chain.

You need to check for existence first, and then do the insert, via separate calls.

You could also write a raw query. Here's an example, it's not pretty: https://github.com/tgriesser/knex/commit/e74f43cfe57ab27b02250948f8706d16c5d821b8

However, you will run into concurrency/lock issues when trying to do this. You're much better off making use of a unique key and letting the DB reject the insert. Then you can catch it:

.catch((err) => {
  if (err.code === 23505) { res.status(500).json({message: 'duplicate'});
}

Edit, more info if you're curious. There's a very long thread on the topic here: https://github.com/tgriesser/knex/issues/871

Edit: thread from @mikael regarding DB's and insert-where: https://github.com/tgriesser/knex/issues/871