monkey patch knex.js for PostgreSQL 9.5 upsert

upsert.js

const BaseQueryCompiler = require("knex/lib/query/compiler");

/*
db = knex({
  client: "pg",
  connection: config.postgresURL,
  debug: true,
});

upsertMonkeyPatch(db);
*/

export default function upsertMonkeyPatch(db) {
  // https://github.com/tgriesser/knex/issues/54#issuecomment-183838939

  const QueryBuilder = db.client.QueryBuilder;
  const QueryCompiler = db.client.QueryCompiler;
  // monkey patch to add onConflict

  QueryBuilder.prototype.onConflict = function (columns: string[], updates: any) {
    // throw error if method is not insert
    this._single["onConflictUpdate"] = {
      columns,
      updates,
    }

    return this;
  }

  const __baseInsert = BaseQueryCompiler.prototype.insert;

  QueryCompiler.prototype.insert = function insert() {
    let sql /*:string */ = __baseInsert.call(this);

    if(this.single.onConflictUpdate) {
      const {columns, updates} = this.single.onConflictUpdate;
      sql += ` on conflict (${this.formatter.columnize(columns)}) `

      const doUpdate = Object.keys(updates).sort().map(key => {
        const val = updates[key];
        return this.formatter.wrap(key) + ' = ' + this.formatter.parameter(val)
      }).join(", ");

      sql += ` do update set ${doUpdate}`
    }

    var returning = this.single.returning;
    if(returning) {
      sql += ' returning ' + this.formatter.columnize(returning);
    }

    return {
      sql,
      returning,
    };
  }
}

Thanks @hayeah. As an alternative to monkey-patching, I wrote a relatively simple function which works for my use cases:

upsertItem = function(tableName, conflictTarget, itemData)

https://gist.github.com/plurch/118721c2216f77640232