Acceleration at KnexJS

Question

I have an upsert query in PostgreSQL, for example:

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 for this upsert request. How to do it?

Answer 1

So I solved this using the following Dotnil answer sentence on the Knex problems page :

 var data = {id: 1, name: 'Gabbar'}; var insert = knex('table').insert(data); var dataClone = _.cloneDeep(data); delete dataClone.id; var update = knex('table').update(dataClone).whereRaw('table.id = ' + data.id); var query = util.format('%s ON CONFLICT (id) DO UPDATE SET %s', insert.toString(), update.toString().replace(/^update\s.*\sset\s/i, '') ); return knex.raw(query) .then(function(dbRes){ // stuff }); 

Hope this helps someone.

Answer 2

I created a function for this, and described it on the knex github problems page (along with some of gotchas for solving with composite unique indices).