PostgreSQL 9.5 Upsert using Knex.js

upsert.js

exports.knex = require('knex')({
  client: 'pg',
  connection: {
    host     : '127.0.0.1',
    user     : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  }
});

/**
 * Perform an "Upsert" using the "INSERT ... ON CONFLICT ... " syntax in PostgreSQL 9.5
 * @link http://www.postgresql.org/docs/9.5/static/sql-insert.html
 * @author https://github.com/plurch
 *
 * @param {string} tableName - The name of the database table
 * @param {string} conflictTarget - The column in the table which has a unique index constraint
 * @param {Object} itemData - a hash of properties to be inserted/updated into the row
 * @returns {Promise} - A Promise which resolves to the inserted/updated row
 */
 exports.upsertItem = function(tableName, conflictTarget, itemData) {
   let exclusions = Object.keys(itemData)
       .filter(c => c !== conflictTarget)
       .map(c => exports.knex.raw('?? = EXCLUDED.??', [c, c]).toString())
       .join(",\n");

   let insertString = exports.knex(tableName).insert(itemData).toString();
   let conflictString = exports.knex.raw(` ON CONFLICT (??) DO UPDATE SET ${exclusions} RETURNING *;`, conflictTarget).toString();
   let query = (insertString + conflictString).replace(/\?/g, '\\?');

   return exports.knex.raw(query)
       .on('query', data => console.log('Knex: ' + data.sql))
       .then(result => result.rows[0]);
 };

usage.js

let tableName = 'user_meta';
let conflictTarget = 'login';
let itemData = {
  login: 'plurch',
  user_id: 3332519
};

let resultPromise = upsertItem(tableName, conflictTarget, itemData);

resultPromise.then(userMeta => console.log('Processed this user: ' + userMeta.login));

// 
// This query will be generated:
// 
// insert into "user_meta" ("login", "user_id") values ('plurch', '3332519') ON CONFLICT ("login") DO UPDATE SET "user_id" = EXCLUDED."user_id" RETURNING *;
// 

Thank you!!!

Hello,

This code works, but have my doubts on whether it will work correctly when "itemData" is an array of objects. knex has the ability to deal with multiple insertions.

Object.keys is applied in itemData to figure out the values to keep when updating values on conflicting insertions. But if the argument is an array, the Object.keys will return the keys of the array, and not the keys of the objects of the array. I made a small modification that seems to be working for me.

/**
 * Perform an "Upsert" using the "INSERT ... ON CONFLICT ... " syntax in PostgreSQL 9.5
 * @link http://www.postgresql.org/docs/9.5/static/sql-insert.html
 * @author https://github.com/plurch
 *
 * @param {string} tableName - The name of the database table
 * @param {string} conflictTarget - The column in the table which has a unique index constraint
 * @param {Object} itemData - a hash of properties to be inserted/updated into the row
 * @returns {Promise} - A Promise which resolves to the inserted/updated row
 */
function upsertItems(tableName : string, conflictTarget : string, itemData : any) {
    let firstObjectIfArray = Array.isArray(itemData) ? itemData[0] : itemData;
    let exclusions = Object.keys(firstObjectIfArray)
        .filter(c => c !== conflictTarget)
        .map(c => knex.raw('?? = EXCLUDED.??', [c, c]).toString())
        .join(",\n");

    let insertString = knex(tableName).insert(itemData).toString();
    let conflictString = knex.raw(` ON CONFLICT (??) DO UPDATE SET ${exclusions} RETURNING *;`, conflictTarget).toString();
    let query = (insertString + conflictString).replace(/\?/g, '\\?');

    return knex.raw(query)
        .on('query', data => console.log('Knex: ' + data.sql))
        .then(result => result.rows);
};

In short, I am checking if the argument is actually an array, and if it is, I am checking the first object to get its properties. If not, the code will work as before.

Two things to note about this.

1. My item data has a jsonb object and it failed had to pass it in as

let itemData = {
  data: JSON.stringify(data)
}

2. The auto-incrementing id is updating on each upsert. :(

Seems the primary key isn't being updated. 🤔