Enhancement: support for upserts

[JeffML]

As you probably know, and upsert is a combination of insert/update. The operation will either update and existing row or insert a new one in a table. Most databases support SQL syntax necessary to do this in one operation. Unfortunately, the syntax varies depending on DBMS.

MySQL:

SET @id = 1,
    @title = 'In Search of Lost Time',
    @author = 'Marcel Proust',
    @year_published = 1913;
INSERT INTO books
    (id, title, author, year_published)
VALUES
    (@id, @title, @author, @year_published)
ON DUPLICATE KEY UPDATE
    title = @title,
    author = @author,
    year_published = @year_published;

Where ON DUPLICATE KEY triggers an UPDATE instead of an INSERT.

Postgres (9.5+):

INSERT INTO tablename (a, b, c) values (1, 2, 10)
ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;

where a is (usually) the primary key.

SQLite: Appears to be the same (or close to) the syntax Postgres uses.

Here's a link on how to implement ON CONFICT in knex.js: https://jaketrent.com/post/upsert-knexjs/

[danielrearden]

Great suggestion! Would be great if this finally got merged, which would add an onConflict method to the Knex builder but it looks like progress on that PR has stalled.