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...
- Embed the SQL via KNEX.
- Use KNEX to first attempt to retrieve a match, and then do the insert
or not, depending on the result.
- 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.