Upsert in KnexJS

Question

I have an upsert query in PostgreSQL like:

INSERT INTO table
  (id, name)
values
  (1, 'Gabbar')
ON CONFLICT (id) DO UPDATE SET
  name = 'Gabbar'
WHERE
  table.id = 1

I need to use knex to this upsert query. How to go about this?

Answer 1

So I solved this using the following suggestion from Dotnil's answer on Knex Issues Page:

var data = {id: 1, name: 'Gabbar'};
var insert = knex('table').insert(data);
var dataClone = {id: 1, name: 'Gabbar'};

delete dataClone.id;

var update = knex('table').update(dataClone).whereRaw('table.id = ' + data.id);
var query = `${ insert.toString() } ON CONFLICT (id) DO UPDATE SET ${ update.toString().replace(/^update\s.*\sset\s/i, '') }`;

return knex.raw(query)
.then(function(dbRes){
  // stuff
});

Hope this helps someone.

Answer 2

As of [email protected]+ a new method onConflict was introduced.

Official documentation says:

Implemented for the PostgreSQL, MySQL, and SQLite databases. A modifier for insert queries that specifies alternative behaviour in the case of a conflict. A conflict occurs when a table has a PRIMARY KEY or a UNIQUE index on a column (or a composite index on a set of columns) and a row being inserted has the same value as a row which already exists in the table in those column(s). The default behaviour in case of conflict is to raise an error and abort the query. Using this method you can change this behaviour to either silently ignore the error by using .onConflict().ignore() or to update the existing row with new data (perform an "UPSERT") by using .onConflict().merge().

So in your case, the implementation would be:

knex('table')
  .insert({
    id: id,
    name: name
  })
  .onConflict('id')
  .merge()