Use UPSERT (aka INSERT ... ON CONFLICT SET) in .upsert() and .upsert_all() when available

[simonw]

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.

[simonw]

Relevant code:

sqlite-utils/sqlite_utils/db.py

Lines 3003 to 3046 in 0aefbb6

	if upsert:
	if isinstance(pk, str):
	pks = [pk]
	else:
	pks = pk
	self.last_pk = None
	for record_values in values:
	record = dict(zip(all_columns, record_values))
	placeholders = list(pks)
	# Need to populate not-null columns too, or INSERT OR IGNORE ignores
	# them since it ignores the resulting integrity errors
	if not_null:
	placeholders.extend(not_null)
	sql = "INSERT OR IGNORE INTO [{table}]({cols}) VALUES({placeholders});".format(
	table=self.name,
	cols=", ".join(["[{}]".format(p) for p in placeholders]),
	placeholders=", ".join(["?" for p in placeholders]),
	)
	queries_and_params.append(
	(sql, [record[col] for col in pks] + ["" for _ in (not_null or [])])
	)
	# UPDATE [book] SET [name] = 'Programming' WHERE [id] = 1001;
	set_cols = [col for col in all_columns if col not in pks]
	if set_cols:
	sql2 = "UPDATE [{table}] SET {pairs} WHERE {wheres}".format(
	table=self.name,
	pairs=", ".join(
	"[{}] = {}".format(col, conversions.get(col, "?"))
	for col in set_cols
	),
	wheres=" AND ".join("[{}] = ?".format(pk) for pk in pks),
	)
	queries_and_params.append(
	(
	sql2,
	[record[col] for col in set_cols]
	+ [record[pk] for pk in pks],
	)
	)
	# We can populate .last_pk right here
	if num_records_processed == 1:
	self.last_pk = tuple(record[pk] for pk in pks)
	if len(self.last_pk) == 1:
	self.last_pk = self.last_pk[0]

Also might be able to clear up this weird hack that fires on INSERT OR IGNORE:

sqlite-utils/sqlite_utils/db.py

Lines 3153 to 3164 in 0aefbb6

	if num_records_processed == 1 and not upsert:
	self.last_rowid = result.lastrowid
	self.last_pk = self.last_rowid
	# self.last_rowid will be 0 if a "INSERT OR IGNORE" happened
	if (hash_id or pk) and self.last_rowid:
	row = list(self.rows_where("rowid = ?", [self.last_rowid]))[0]
	if hash_id:
	self.last_pk = row[hash_id]
	elif isinstance(pk, str):
	self.last_pk = row[pk]
	else:
	self.last_pk = tuple(row[p] for p in pk)

[simonw]

I'm going to ship this as 4.0 alpha, because it does represent a very slight backwards incompatible change. It shouldn't affect anyone but it could break code that e.g. captures executed SQL in tests and runs assertions on that.

[simonw]

New documentation: https://sqlite-utils--653.org.readthedocs.build/en/653/python-api.html#alternative-upserts-using-insert-or-ignore

Upserts use INSERT INTO ... ON CONFLICT SET. Prior to sqlite-utils 3.x (TODO: fill in version) these used a sequence of INSERT OR IGNORE followed by an UPDATE. This older method is still used for SQLite 3.23.1 and earlier. You can force the older implementation by passing use_old_upsert=True to the Database() constructor.