I’ve been doing the two-step dance for years:

INSERT INTO users (email) VALUES ('x@y.com');
SELECT last_insert_rowid();

Then for anything more than the rowid, another SELECT. Ugly, races with concurrent writers on anything other than rowid.

Turns out SQLite 3.35 (2021) added RETURNING:

INSERT INTO users (email) VALUES ('x@y.com') RETURNING id, email, created_at;
UPDATE users SET active = 0 WHERE last_login < date('now', '-365 days') RETURNING id;
DELETE FROM sessions WHERE expired_at < datetime('now') RETURNING token;

Works on INSERT, UPDATE, DELETE. You get the affected rows back. Single round trip, no race.

This was the main thing I missed from Postgres in SQLite. It’s been there for years and I didn’t notice.

Check your SQLite version:

SELECT sqlite_version();

Anything 3.35+ has it. The bundled version in common Python sqlite3 module follows Python releases — Python 3.11+ typically ships with 3.35+, but your deployment might not. Don’t assume; check.

Now to grep my codebase and replace every “INSERT then SELECT last_insert_rowid” pattern.