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

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