UPSERT and RETURNING

Insert-or-update in one statement

Before 3.24 you had to do INSERT OR REPLACE (which deletes + reinserts, breaking foreign keys) or write your own select-then-insert-or-update. Modern SQLite has proper UPSERT:

INSERT INTO t(...) VALUES (...)
ON CONFLICT(unique_col) DO UPDATE SET other_col = excluded.other_col;

The pseudo-table excluded refers to the row that would have been inserted. You can SET any subset of columns and use any expression.

RETURNING (3.35+) lets you get back data from the affected rows in the same round-trip:

INSERT INTO t(...) VALUES (...) RETURNING id, created_at;

Combined, INSERT...ON CONFLICT...DO UPDATE...RETURNING handles 'create or update and tell me what happened' in one statement — the bread-and-butter of REST API write paths.

Self-reference: Pippa's add_message uses INSERT...RETURNING to get the new message id back without a second SELECT. Combined with conn.row_factory = sqlite3.Row it returns the freshly-inserted row to the API in a single round-trip.

Code

UPSERT — register-or-update settings·sql

CREATE TABLE settings (
  k TEXT PRIMARY KEY, v TEXT NOT NULL,
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

INSERT INTO settings(k, v) VALUES ('theme', 'dark')
ON CONFLICT(k) DO UPDATE
  SET v = excluded.v,
      updated_at = datetime('now');

RETURNING — get back the id without a second SELECT·sql

INSERT INTO messages(conversation_id, role, content)
VALUES (?, ?, ?)
RETURNING id, created_at;

Python idiom — UPSERT + RETURNING in one round-trip·python

import sqlite3

conn = sqlite3.connect('demo.db')
conn.row_factory = sqlite3.Row

row = conn.execute(
    'INSERT INTO settings(k, v) VALUES (?, ?) '
    'ON CONFLICT(k) DO UPDATE SET v = excluded.v, updated_at = datetime(\'now\') '
    'RETURNING k, v, updated_at',
    ('theme', 'dark'),
).fetchone()
conn.commit()
print(dict(row))
# {'k': 'theme', 'v': 'dark', 'updated_at': '2026-05-03 12:00:00'}

Exercise

Build a page_views(url TEXT PRIMARY KEY, count INTEGER NOT NULL DEFAULT 0) table. Write an UPSERT that inserts the URL with count 1 if new, or increments count by 1 if existing — in a single statement. Then add RETURNING to give back the new count. Test with 10,000 random URL hits and confirm the totals match.

Insert-or-update in one statement

Code

External links

Exercise

Progress

Comments 0