SQLite Syntax Error Near ON

If you get a weird error like

sqlite prepare error -> SqliteFailure(Error { code: Unknown, extended_code: 1 }, Some("near \"ON\": syntax error"))

or

syntax error near ON

While trying to use UPSERT ... ON CONFLICT(yyyy)…

It could because you’re running Ubuntu Bionic or another similarly old version - your SQLite version’s before 3.24.0

From the SQLite documentation for ON CONFLICT:

The phrase “ON CONFLICT” is also part of UPSERT, which is an extension to INSERT added in version 3.24.0 (2018-06-04)

I got this while testing using Kanidm’s PAM module and kanidm_unixd which uses SQLite under the hood.