Knex postgres upsert

test.js

// Run with jest

import knex from 'knex'

import knexPgUpsert from '../knexPgUpsert'

const db = knex({ client: 'pg' })

const taps = [
    {
        deleted_at: null,
        created_at: '2017-08-17T18:43:55.010Z',
        updated_at: '2017-08-17T18:43:55.010Z',
        estimate_item_id: 'estimate_item_id-1',
        hole_id: 0,
        tap_option_id: 'foo3',
    },
    {
        deleted_at: null,
        created_at: '2017-08-17T18:43:55.010Z',
        updated_at: '2017-08-17T18:43:55.010Z',
        estimate_item_id: 'estimate_item_id-1',
        hole_id: 1,
        tap_option_id: 'fo5o',
    },
]
const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/
const dateRegex = /\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}.\d{3}Z/

describe('knexPgUpsert', () => {
    test('with no conflict target', () => {
        const query = knexPgUpsert({
            db,
            tableName: 'tap_annotations',
            itemData: taps,
        })
        expect(query.toSQL()).toEqual({
            method: 'raw',
            sql: `INSERT INTO ? (?,?,?,?,?,?)
        VALUES (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)
        ON CONFLICT  DO UPDATE SET
        "deleted_at" = EXCLUDED."deleted_at",
        "created_at" = EXCLUDED."created_at",
        "updated_at" = EXCLUDED."updated_at",
        "estimate_item_id" = EXCLUDED."estimate_item_id",
        "hole_id" = EXCLUDED."hole_id",
        "tap_option_id" = EXCLUDED."tap_option_id"
        RETURNING *;`,
            bindings:[
                'tap_annotations',
                'deleted_at',
                'created_at',
                'updated_at',
                'estimate_item_id',
                'hole_id',
                'tap_option_id',
                null,
                expect.stringMatching(dateRegex),
                expect.stringMatching(dateRegex),
                'estimate_item_id-1',
                0,
                'foo3',
                null,
                expect.stringMatching(dateRegex),
                expect.stringMatching(dateRegex),
                'estimate_item_id-1',
                1,
                'fo5o',
            ],
            options: {},
            __knexQueryUid: expect.stringMatching(uuidRegex),
        })
    })

    test('with conflict target', () => {
        const query = knexPgUpsert({
            db,
            tableName: 'tap_annotations',
            itemData: taps,
            conflictTarget: ['hole_id', 'estimate_item_id'],
        })
        const result = query.toSQL()
        expect(result).toEqual({
            method: 'raw',
            sql: `INSERT INTO ? (?,?,?,?,?,?)
        VALUES (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)
        ON CONFLICT ("hole_id", "estimate_item_id") DO UPDATE SET
        "deleted_at" = EXCLUDED."deleted_at",
        "created_at" = EXCLUDED."created_at",
        "updated_at" = EXCLUDED."updated_at",
        "estimate_item_id" = EXCLUDED."estimate_item_id",
        "hole_id" = EXCLUDED."hole_id",
        "tap_option_id" = EXCLUDED."tap_option_id"
        RETURNING *;`,
            bindings:[
                'tap_annotations',
                'deleted_at',
                'created_at',
                'updated_at',
                'estimate_item_id',
                'hole_id',
                'tap_option_id',
                null,
                expect.stringMatching(dateRegex),
                expect.stringMatching(dateRegex),
                'estimate_item_id-1',
                0,
                'foo3',
                null,
                expect.stringMatching(dateRegex),
                expect.stringMatching(dateRegex),
                'estimate_item_id-1',
                1,
                'fo5o',
            ],
            options: {},
            __knexQueryUid: expect.stringMatching(uuidRegex),
        })
    })
})

upsert.js

/**
 * 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/adnanoner
 * @source https://gist.github.com/adnanoner/b6c53482243b9d5d5da4e29e109af9bd
 * inspired by: https://gist.github.com/plurch/118721c2216f77640232
 * @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 {knexQuery} - A knexQuery
 */
export default function knexPgUpsert({
    db,
    tableName,
    itemData,
    conflictTarget,
}) {
    let itemsArray = []
    if (Array.isArray(itemData)) {
        itemsArray = itemData
    } else {
        itemsArray[0] = itemData
    }
    const itemKeys = Object.keys(itemsArray[0])

    const exclusions = itemKeys
       .filter((c) => c !== conflictTarget)
       .map((c) => db.raw('?? = EXCLUDED.??', [c, c]).toString())
       .join(',\n        ')

    let valuesPreparedString = ''
    let preparedValues = []
    itemsArray.forEach((item) => {
        valuesPreparedString += '('
        for (let i = 0; i < itemKeys.length - 1; i += 1) {
            valuesPreparedString += '?, '
        }
        valuesPreparedString += '?), '
        preparedValues = preparedValues.concat(Object.values(item))
    })
    // Remove last trailing comma
    valuesPreparedString = valuesPreparedString.replace(/,\s*$/, '')

    // if we have an array of conflicting targets to ignore process it
    let conflictPlaceholder = ''
    if (conflictTarget) {
        conflictPlaceholder += '('
        if (Array.isArray(conflictTarget)) {
            for (let i = 0; i < conflictTarget.length - 1; i += 1) {
                conflictPlaceholder += '??, '
            }
            preparedValues = preparedValues.concat(conflictTarget)
        } else {
            preparedValues.push(conflictTarget)
        }
        conflictPlaceholder += '??)'
    }

    const itemKeysPlaceholders = itemKeys.map(() => '?').join(',')
    return db.raw(`
        INSERT INTO ? (${itemKeysPlaceholders})
        VALUES ${valuesPreparedString}
        ON CONFLICT ${conflictPlaceholder} DO UPDATE SET
        ${exclusions}
        RETURNING *;
    `.trim(), [tableName, ...itemKeys, ...preparedValues])
}