upsert.js

const tableFactory = () => {
  const columns = [];
  const compositeUniqueColumns = [];
  
  const dataTypes = [
    'uuid',
    'string',
    'timestamp',
    'float',
    'integer',
    'boolean',
    'datetime',
    'text',
    'tinyint',
    'json',
  ];
  
  const ignoredColumnAttrs = [
    'notNullable',
    'primary',
    'nullable',
    'references',
  ];
  
  class Column {
    constructor(name) {
      this.name = name;
      this.isUnique = false;
      ignoredColumnAttrs.forEach(x => this[x] = () => this);
    }
    unique() {
      this.isUnique = true;
      return this;
    }
  }
  
  return {
    uniqueConstraints: () => [].concat(
      columns.filter(x => x.isUnique).map(x => x.name),
      compositeUniqueColumns
    ),
    
    unique: (columns) => {
      compositeUniqueColumns.push(columns);
    },
    
    ...dataTypes.reduce((acc, type) => {
      acc[type] = (name) => {
        const column = new Column(name);
        columns.push(column);
        return column;
      };
      return acc;
    }, {}),
  }
}

function upsert(tenant, tableName, data) {
  const table = tableFactory();
  const { build } = schemas.tenantSchema.filter(x => x.tableName === tableName)[0];
  build(table);
  const constraints = table.uniqueConstraints();
  const where = constraints.reduce((acc, column) => {
    acc[column] = data[column];
    return acc;
  }, {});
  const values = {};

  const insert = knex(tableName)
    .insert(data)
    .toString()
    .replace(/\svalues\s(/, ' select ')
    .slice(0, -1);

  const update = knex(tableName)    
    .update(values)
    .where(where)
    .toString();

  const query = `
    BEGIN;

    SET search_path = '${tenant}';

    LOCK TABLE ${tableName} IN SHARE ROW EXCLUSIVE MODE;
    WITH upsert AS (${updateQuery} RETURNING *)
    ${insertQuery} WHERE NOT EXISTS (SELECT * FROM upsert);

    COMMIT;`;

  const now = new Date();
  knex.raw();
}