RETURNING, changes(), total_changes(), and Observable Writes

Learning outcomes

Applications need evidence about writes: Which ID did SQLite choose? What values were stored after defaults and affinity? Did an optimistic update match exactly one row? SQLite provides several tools, but they answer different questions.

RETURNING turns a write into a result-producing command

SQLite supports RETURNING since 3.35.0. It can appear on top-level INSERT, UPDATE, and DELETE statements and returns one result row for each row directly changed by that statement.

DROP TABLE IF EXISTS command_note;CREATE TABLE command_note(  note_id INTEGER PRIMARY KEY,  device_code TEXT NOT NULL,  status TEXT NOT NULL DEFAULT 'open',  version INTEGER NOT NULL DEFAULT 1,  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);INSERT INTO command_note(device_code)VALUES ('PUMP-007')RETURNING note_id, device_code, status, version, created_at;

The caller receives the actual ID and defaults SQLite stored. This often expresses intent more directly than INSERT followed by a separate SELECT.

UPDATE and DELETE can return the affected rows

UPDATE command_noteSET status='closed', version=version+1WHERE note_id=1 AND version=1RETURNING note_id, status, version;DELETE FROM command_noteWHERE note_id=1RETURNING note_id, device_code, status;

For UPDATE, column references in RETURNING reflect the post-update values. For DELETE, they reflect the row before deletion. If the optimistic UPDATE predicate matches zero rows, it returns zero rows—an application-visible concurrency signal.

SQLite RETURNING is not “PostgreSQL behavior everywhere”

The syntax was inspired by PostgreSQL, but current SQLite has important limits. Do not infer capabilities from another database product.

Self-referential subqueries inside RETURNING can also be indeterminate because SQLite does not guarantee the relative ordering of internal row changes and those subquery evaluations.

changes(): scope of the most recent direct write

The SQL function changes() reports the number of rows inserted, updated, or deleted by the most recently completed DML statement on the current connection. It excludes auxiliary changes from triggers, foreign-key actions, and REPLACE conflict resolution.

DROP TABLE IF EXISTS count_demo;CREATE TABLE count_demo(id INTEGER PRIMARY KEY, value TEXT);INSERT INTO count_demo(value) VALUES ('a'),('b'),('c');SELECT changes() AS last_statement_rows; -- 3UPDATE count_demo SET value=upper(value) WHERE id IN (1,2);SELECT changes() AS direct_updated;       -- 2-- An UPDATE can count a matched row even when the assigned value is equal.UPDATE count_demo SET value=value WHERE id=1;SELECT changes() AS matched_update;       -- 1

That last result is a reason not to interpret “1 row changed” as “the business value definitely became different.” It tells you the UPDATE affected one row according to SQLite's DML accounting.

total_changes(): connection-lifetime cumulative activity

total_changes() accumulates INSERT, UPDATE, and DELETE changes performed by the current connection since it was opened. Unlike changes(), the total includes rows changed by foreign-key actions and triggers, though REPLACE deletions have special exclusions.

SELECT total_changes() AS connection_total_before;INSERT INTO count_demo(value) VALUES ('d');UPDATE count_demo SET value='B2' WHERE id=2;DELETE FROM count_demo WHERE id=3;SELECT total_changes() AS connection_total_after;

Because the starting value depends on what that connection has already done, total_changes() is not a database-global activity counter. Other connections' writes are excluded.

Driver rowcount and generated-key APIs are adapter semantics

Language drivers expose conveniences such as cursor rowcount or last inserted ID, but exact behavior can differ by API, statement type, batching method, and when the property is read. Treat the driver's documentation as part of your application contract.

Application-like command workflow

The strongest pattern combines identity, concurrency, and returned state in one statement. Here a caller closes a note only if it still has the version previously read.

DROP TABLE IF EXISTS app_note;CREATE TABLE app_note(  note_id INTEGER PRIMARY KEY,  device_code TEXT NOT NULL,  status TEXT NOT NULL CHECK(status IN ('open','closed')),  version INTEGER NOT NULL DEFAULT 1,  closed_at TEXT);INSERT INTO app_note(device_code,status) VALUES ('PUMP-007','open');BEGIN;UPDATE app_noteSET status='closed',    closed_at='2026-08-12T07:15:00Z',    version=version+1WHERE note_id=1  AND version=1  AND status='open'RETURNING note_id, status, version, closed_at;-- Application expects exactly one returned row.COMMIT;

If zero rows return, do not “fix” that by broadening the WHERE clause. Re-read the row: it may already be closed, deleted, or at another version. If more rows could ever return for a command intended to affect one entity, the predicate/key design is wrong.

RETURNING plus UPSERT

RETURNING also reports both inserted and updated rows from an UPSERT, making synchronization APIs easier to observe.

DROP TABLE IF EXISTS api_device;CREATE TABLE api_device(  device_id INTEGER PRIMARY KEY,  external_key TEXT UNIQUE NOT NULL,  label TEXT NOT NULL,  version INTEGER NOT NULL);INSERT INTO api_device(external_key,label,version)VALUES ('asset-100','Pump',1)ON CONFLICT(external_key) DO UPDATE SET  label=excluded.label,  version=excluded.versionRETURNING device_id, external_key, label, version;

The result confirms final top-level row values, but it does not tell you solely from the row whether the branch was INSERT or UPDATE unless your schema/workflow returns additional evidence that distinguishes the path.

Failure cases and production judgment

Chapter 6 final lab

Exercise the complete write lifecycle: insert with generated identity, optimistic update, idempotent UPSERT, and delete with returned evidence.

DROP TABLE IF EXISTS command_device;CREATE TABLE command_device(  device_id INTEGER PRIMARY KEY,  external_key TEXT UNIQUE NOT NULL,  label TEXT NOT NULL,  version INTEGER NOT NULL DEFAULT 1);INSERT INTO command_device(external_key,label)VALUES ('asset-901','Pump 901')RETURNING device_id, external_key, label, version;UPDATE command_deviceSET label='Pump 901A', version=version+1WHERE external_key='asset-901' AND version=1RETURNING device_id, label, version;INSERT INTO command_device(external_key,label,version)VALUES ('asset-901','Pump 901A',2)ON CONFLICT(external_key) DO UPDATE SET label=excluded.label, version=excluded.versionWHERE excluded.version>command_device.versionRETURNING device_id, external_key, label, version;-- This third command returns zero rows because equal version makes DO UPDATE a no-op.DELETE FROM command_deviceWHERE external_key='asset-901'RETURNING device_id, external_key, label, version;SELECT count(*) AS final_rows FROM command_device; -- 0

Notice the third statement: an UPSERT can encounter a uniqueness conflict yet return no row if its conditional DO UPDATE becomes a no-op. “No returned row” is meaningful application evidence, not automatically an error.

Summary and bridge to Chapter 7

Chapter 6 made writes predictable: INSERT delegates identity/defaults to SQLite, UPDATE verifies scope and concurrency, DELETE models lifecycle and cascade reach, UPSERT reconciles logical entities by uniqueness, and RETURNING/change counters make direct outcomes visible. Chapter 7 now deepens the expression language used inside all of those statements—operators, functions, CTEs, recursive queries, and window functions.

Authoritative references

• SQLite — RETURNING.
• SQLite — changes().
• SQLite — total_changes().
• SQLite — last_insert_rowid().
• SQLite — UPSERT.