SQLite added UPSERT support (in the shape of INSERT ... ON CONFLICT SET) in version 3.24.0, released 2018-06-04.

https://sqlite.org/lang_upsert.html

sqlite-utils has .upsert() and .upsert_all() which were updated to have the correct semantics in 2019 in:

• The ".upsert()" method is misnamed #66

As noted in this comment:

If I do implement the correct definition of .upsert() I think I'll use this pattern, since it works in versions of SQLite prior to 3.24:

INSERT OR IGNORE INTO book(id) VALUES(1001);
UPDATE book SET name = 'Programming' WHERE id = 1001;

That was more than five years ago. I think UPSERT is probably a whole lot more available now than it was then.

Meanwhile... my work on SQLite Chronicle (tracking version numbers for SQLite rows automatically using triggers, to help support syncing, subscribing to tables and implementing logic that only looks at rows that are new/updated since the last check) has been stymied by the fact that INSERT OR REPLACE can't be correctly detected by triggers - it's indistinguishable from a delete followed by an insert, so I can't reliably maintain the same version number for a row if an INSERT OR REPLACE does a no-op:

• added_ms and updated_ms columns are not reliable in face of operations like INSERT OR REPLACE INTO sqlite-chronicle#7 (comment)

As such, I'd like to start using real upserts instead in a bunch of places in the Datasette ecosystem.

So... I think it's time sqlite-utils learned to use upsert. I'm not sure how many pre-3.24.0 installs are still around, so it could implement feature detection and switch to the older mechanism if upsert isn't supported.