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 *;
// 

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. 🤔