Cannot set a ON CONFLICT clause on INSERT INTO (SQLite)

[lnchan]

It seems currently impossible to write the following SQL query using the current HoneySQL helpers:

INSERT OR IGNORE INTO table (column) VALUES ("value");

as defined per SQLite documentation.

SQLite's ON CONFLICT clause is defined here.

[seancorfield]

Thanks. There currently isn't a SQLite dialect but I'll probably create one for things that are non-ANSI SQL.

I'll have to look at some of the other databases to see whether these SQLite extensions would be useful in other dialects (and therefore could become core to HoneySQL) or whether they'll need to be part of a new :sqlite dialect.

It will be a while before I can get to this, however.

[seancorfield]

Does SQLite support the (more standard) ON CONFLICT syntax?

INSERT INTO table (column) VALUES ("value") ON CONFLICT (column) DO NOTHING

That syntax is already supported by HoneySQL (because it is what PostgreSQL and others use) so if SQLite supports that, I'd rather not add SQLite-specific extensions for an alternative syntax for the same thing.

[lnchan]

That syntax is indeed supported, though it does not seem documented(?) in SQLite documentation. SQLite's own syntax does not require specifying the constraints, but PostgreSQL's ON CONFLICT syntax seems supported and working.

[seancorfield]

If you want to provide an example for SQLite, I can add it to the insert into docs, with a note that is how you do insert or ignore...

[seancorfield]

@lnchan Any thoughts on wording for such an example? I'll close this issue out in another week.

[lnchan]

Apologies, I was flying across the ocean, and this slipped my mind.

This is defined as supported per the SQLite documentation on the UPSERT page, meaning that the examples found on the HoneySQL documentation for UPSERTs, such as:

(-> (insert-into :user)
    (values [{:phone "5555555" :name "John"}])
    (on-conflict :phone (where [:<> :phone nil]))
    (do-update-set :phone :name (where [:= :user.active false]))
    (sql/format {:pretty true}))

are properly supported by SQLite since version 3.24.0 (2018-06-04).

However, only a limited subset of this syntax seems to be supported by SQLite. There does not seem to be any support on syntax such as ON CONFLICT ON CONSTRAINT (due to how constraint violations are handled in SQLite); only ON CONFLICT ... DO NOTHING and ON CONFLICT ... DO UPDATE SET seem to be supported by SQLite.

This still means that SQLite's custom ON CONFLICT syntax is not supported, however there are two workarounds (that I know of):

• for upserts, using MySQL's REPLACE INTO, which is supported by SQLite as an alias to INSERT OR REPLACE INTO;
• for more precise upserts or insert-or-ignore, PostgreSQL's ON CONFLICT ... DO syntax is supported by SQLite.

Thank you for your time and help (and for your patience! I am really sorry for the delay.)

[seancorfield]

Thanks for the detailed updates!

:replace-into is supported in HoneySQL's core as of 2.4.969 (because it's for more than one database and causes no conflicts).

I'll make sure to update the docs in my next "HoneySQL session".