SQLite RETURNING Clause: Get Rows Back from INSERT, UPDATE, DELETE

A Way to See What Just Happened

When you run an INSERT, UPDATE, or DELETE, SQLite tells you how many rows were touched — but not which rows, or what their final values look like. The classic workaround is a follow-up SELECT. That's two round trips, two statements, and a small race window where someone else could change the row between them.

RETURNING fixes that. You append it to a write statement, list the columns you want back, and SQLite hands you the affected rows as if you'd just run a SELECT over them:

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO notes (title) VALUES ('First note')
RETURNING id, title, created_at;

One statement, one trip, and you get back the generated id and the default created_at value the database filled in for you.

RETURNING was added in SQLite 3.35.0 (March 2021). If your statement gets rejected as a syntax error, check SELECT sqlite_version(); — older builds don't know the keyword.

Getting the Generated ID Back

The most common reason to reach for RETURNING is to grab the auto-generated primary key right after inserting:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

INSERT INTO users (email) VALUES ('rosa@example.com')
RETURNING id;

Before RETURNING, you'd insert and then call last_insert_rowid() (or your driver's equivalent) on the same connection. That still works, but it's connection-state magic — easy to get wrong with connection pools or threads. RETURNING id is explicit, local to the statement, and works the same way no matter what's hosting the connection.

If your table doesn't declare an explicit INTEGER PRIMARY KEY, you can still get the implicit row identifier:

CREATE TABLE logs (event TEXT);

INSERT INTO logs (event) VALUES ('startup')
RETURNING rowid, event;

Every regular SQLite table has a rowid, and RETURNING will hand it over.

Multiple Columns and Expressions

RETURNING accepts the same shape as the column list of a SELECT. List columns, use *, build expressions, give them aliases:

CREATE TABLE line_items (
    id INTEGER PRIMARY KEY,
    product TEXT NOT NULL,
    price REAL NOT NULL,
    quantity INTEGER NOT NULL
);

INSERT INTO line_items (product, price, quantity)
VALUES ('Notebook', 4.50, 3)
RETURNING id, product, price * quantity AS subtotal;

RETURNING * is handy when you want everything — including any defaults the database filled in — without naming each column:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO events (name) VALUES ('login')
RETURNING *;

You see the new id, the name you passed, and the timestamp SQLite computed.

RETURNING with UPDATE

On an UPDATE, RETURNING gives you the post-update values — the row as it looks after your changes have been applied:

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance INTEGER NOT NULL
);
INSERT INTO accounts (name, balance) VALUES ('Ada', 100), ('Boris', 50);

UPDATE accounts
SET balance = balance + 25
WHERE name = 'Ada'
RETURNING id, name, balance;

You get back Ada's new balance of 125, not the old 100. That makes RETURNING perfect for atomic counters and credit/debit operations — you don't have to read, compute, write, and re-read.

If the WHERE matches multiple rows, you get one row back per affected row:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products (name, price) VALUES ('A', 10), ('B', 20), ('C', 30);

UPDATE products
SET price = price * 1.1
RETURNING id, name, price;

Three rows in, three rows out. The order isn't guaranteed — if you need a specific order, sort the result on the client side.

RETURNING with DELETE

On DELETE, RETURNING gives you the rows as they were just before deletion. Useful for archiving, audit trails, or simply confirming what got removed:

CREATE TABLE sessions (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    expires_at TEXT NOT NULL
);
INSERT INTO sessions (user_id, expires_at) VALUES
    (1, '2024-01-01'), (2, '2024-01-02'), (3, '2026-01-01');

DELETE FROM sessions
WHERE expires_at < '2025-01-01'
RETURNING id, user_id, expires_at;

You get the two expired sessions back, with all their fields intact, even though they no longer exist in the table. If you want to move them somewhere else, that's a perfect setup for an archive table — read the result and insert it elsewhere in the same transaction.

RETURNING with UPSERT

RETURNING also works with INSERT ... ON CONFLICT ... DO UPDATE. The returned row reflects whichever branch ran — the new insert or the conflict update:

CREATE TABLE counters (
    name TEXT PRIMARY KEY,
    value INTEGER NOT NULL DEFAULT 0
);

INSERT INTO counters (name, value) VALUES ('visits', 1)
ON CONFLICT(name) DO UPDATE SET value = value + 1
RETURNING name, value;

Run that statement twice. The first time it inserts and returns ('visits', 1). The second time the conflict fires, the value is incremented, and you get back ('visits', 2). Either way, one statement and one row out — no need to ask "did it insert or update?" before continuing.

This is the cleanest pattern in SQLite for "give me the current value, creating it if necessary" without round-tripping.

A Few Things to Know

A handful of details that catch people off guard:

• RETURNING always sees the row after the change for INSERT and UPDATE, and before the change for DELETE. There's no syntax to ask for the other side.
• The order of returned rows isn't guaranteed. Add an ORDER BY on the client if it matters.
• You can't put RETURNING inside a subquery. It's a top-level clause on the write statement, not an expression.
• RETURNING doesn't fire BEFORE triggers' modified data — it returns the values that were actually written. AFTER triggers run between the write and the row being returned.
• Generated columns and DEFAULT values are visible in the result. That's exactly why RETURNING * is a quick way to inspect what the database filled in for you.

Next: Importing CSV Data

RETURNING is great when you're writing one row or a handful at a time and want to see the result immediately. When you're loading thousands of rows from a file, you'll reach for SQLite's CSV import tools instead — that's the next page.