SQLite Basics: Read Changed Rows with RETURNING

RETURNING lets an INSERT, UPDATE, or DELETE produce result rows. It is useful when the application needs an assigned ID or the value produced by a write, without a separate lookup. SQLite added the clause in version 3.35.0; these examples were tested on the newer runtime listed below.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation uses a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. The toy database disappears when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line. This is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Read an assigned ID and a default

Save as insert-returning.sql:

CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'queued'
);
INSERT INTO jobs (title) VALUES ('Read guide')
RETURNING id, title, status;

Run:

python3 run-sql.py insert-returning.sql

Output:

1|Read guide|queued

The insert omits id and status. SQLite supplies both, and RETURNING reports the inserted values. Listing the returned columns explicitly makes the result contract easier to maintain than RETURNING * if the schema later gains more fields.

The ID is one because this is a fresh toy table. Do not infer that production IDs are consecutive, start at one, or can safely be predicted by the caller. The point is to consume the value actually assigned by this insert.

2. See whether a guarded update matched

Save as update-returning.sql:

CREATE TABLE stock (id INTEGER PRIMARY KEY, quantity INTEGER NOT NULL);
INSERT INTO stock VALUES (1, 5);
SELECT 'first attempt';
UPDATE stock SET quantity = quantity - 3
WHERE id = 1 AND quantity >= 3
RETURNING id, quantity;
SELECT 'second attempt';
UPDATE stock SET quantity = quantity - 3
WHERE id = 1 AND quantity >= 3
RETURNING id, quantity;
SELECT 'remaining', quantity FROM stock WHERE id = 1;

Run:

python3 run-sql.py update-returning.sql

Output:

first attempt
1|2
second attempt
remaining|2

The first update returns the new quantity, two. The second matches no row because the remaining stock is below three, so there is no RETURNING row between its label and the final query. No match is different from a returned row containing NULL.

In an application, handle the empty result explicitly. It could mean insufficient stock or a missing ID under this condition. The predicate and write occur together, avoiding a separate read-then-subtract sequence, but a complete ordering workflow still needs its own validation and transaction design.

3. Read a deleted row before rolling back

Save as delete-returning.sql:

CREATE TABLE drafts (id INTEGER PRIMARY KEY, title TEXT);
INSERT INTO drafts VALUES (1, 'Old outline'), (2, 'Current outline');
BEGIN;
DELETE FROM drafts WHERE id = 1 RETURNING id, title;
SELECT 'inside transaction', COUNT(*) FROM drafts;
ROLLBACK;
SELECT 'after rollback', COUNT(*) FROM drafts;

Run:

python3 run-sql.py delete-returning.sql

Output:

1|Old outline
inside transaction|1
after rollback|2

DELETE returns values from the removed row, while INSERT and UPDATE return their new values. The deletion is visible inside the transaction, but ROLLBACK restores the row. Receiving a result is not proof that a surrounding transaction has committed.

This matters when an application publishes an event or sends a confirmation after a write. Arrange those actions around a successful commit rather than around the arrival of the first returned row. The runner consumes every result row before executing the next statement; production code must also finish or close its result cursor appropriately.

The examples return at most one changed row per write to keep output deterministic. Multirow RETURNING output has no guaranteed order; sort collected results in the application when needed. It also does not reflect later AFTER-trigger changes or report extra rows changed by triggers.

SQLite cannot use a write with RETURNING as a CTE result like some other databases can. Large returned datasets also consume temporary memory, so return only what the caller needs. See SQLite’s RETURNING reference for supported operations and limitations.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py insert-returning.sql update-returning.sql delete-returning.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.