A database write often creates information the application immediately needs.

An INSERT may generate an ID and timestamp. An UPDATE may calculate a new counter. A DELETE may need to return enough data for an audit event. The familiar approach is to write first and query afterward, but SQLite has a cleaner option for many of these cases: RETURNING.

Here’s the idea:

INSERT INTO jobs (name)
VALUES ('resize-images')
RETURNING id, name, created_at;

The write and the values I care about stay in one SQL statement. That is convenient, but the more interesting part is understanding exactly what RETURNING promises—and what it does not.

SQLite has supported RETURNING since version 3.35.0, released in 2021. It can be attached to top-level INSERT, UPDATE, and DELETE statements.

Start with generated values

Suppose I have this table:

CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'queued',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Without RETURNING, application code might insert a row and then perform another operation to discover its generated values.

With RETURNING, SQLite can send them back directly:

INSERT INTO jobs (name)
VALUES ('resize-images')
RETURNING id, name, status, created_at;

A result might look like:

id | name          | status | created_at
---+---------------+--------+-------------------
42 | resize-images | queued | 2026-09-08 09:30:00

I prefer this when the generated database values are part of the write result. The SQL says what is created and what the caller needs back in one place.

RETURNING * is valid too:

INSERT INTO jobs (name)
VALUES ('resize-images')
RETURNING *;

For application code, though, I usually list the columns explicitly. That keeps the result shape stable when unrelated columns are added later.

UPDATE can return the new values

RETURNING is not only about inserted IDs.

Imagine a retry counter:

UPDATE jobs
SET retry_count = retry_count + 1
WHERE id = 42
RETURNING id, retry_count;

For UPDATE, references to columns in the modified table represent their values after the change. This makes read-modify-write operations much nicer when the calculation belongs in SQL.

For example:

UPDATE accounts
SET credits = credits - 10
WHERE id = ?
  AND credits >= 10
RETURNING id, credits;

If the statement returns a row, the deduction happened and I immediately have the resulting balance. If it returns no rows, the WHERE condition matched nothing.

That can remove a separate pre-read whose result might already be stale by the time the update runs.

Of course, RETURNING does not replace transaction design. If this update is one step in a larger invariant involving several tables, I still need the appropriate transaction around the whole operation.

DELETE can return the old row

For DELETE, returned column values describe the row before it disappears.

DELETE FROM jobs
WHERE id = 42
RETURNING id, name, status;

This is useful when the application needs to know what it actually deleted:

DELETE
  |
  +-- no returned row -> nothing matched
  |
  +-- returned row    -> deletion happened; use old values

I find this especially handy for application-level logging or cache invalidation. Instead of selecting a row, deleting it, and hoping those two operations still describe the same state, the delete itself produces the relevant values.

Be careful with the word “audit,” though. If audit records must be durable and atomic with the database mutation, an application log emitted after the query is not automatically enough. A crash can happen between the database commit and the external log write.

RETURNING is better than guessing from last_insert_rowid()

SQLite also exposes last_insert_rowid(), and it is useful in the right context. But it answers a narrower question: the ROWID of the most recent successful insert on a database connection.

That has consequences.

It does not describe arbitrary generated columns. It does not work for inserts into WITHOUT ROWID tables. Its meaning also depends on connection state, so sharing a connection across concurrent work requires care.

RETURNING ties the result to the statement that produced it:

INSERT INTO users (email)
VALUES (?)
RETURNING id, email, created_at;

For code that needs the inserted row’s values, I find this easier to reason about than “perform an insert, then inspect mutable connection state.”

There are still cases where a driver’s insert-ID API is perfectly adequate. The point is not that last_insert_rowid() is wrong; it is that RETURNING expresses a richer relationship between a particular write and its result.

Multi-row writes return multiple rows

A multi-row insert can return one result row for every row it directly inserts:

INSERT INTO tags (name)
VALUES ('sqlite'), ('database'), ('sql')
RETURNING id, name;

Likewise, an update affecting many rows can return many rows:

UPDATE jobs
SET status = 'expired'
WHERE status = 'queued'
  AND created_at < ?
RETURNING id, status;

This is useful, but there is an important catch: SQLite does not guarantee the order of rows emitted by RETURNING.

So I do not write application logic like this:

first returned row  -> first input item
second returned row -> second input item

unless I have some separate, explicit identifier that lets me correlate the data safely.

Even an ORDER BY available on certain UPDATE or DELETE builds does not define the order of RETURNING output.

If ordering matters after a batch write, I treat the returned rows as unordered data and sort or index them in application code using an explicit key.

Trigger side effects are a boundary

One subtle limitation matters when triggers are involved.

RETURNING reports rows directly modified by the top-level statement. It does not report additional rows changed by triggers or foreign-key actions.

Also, values returned by the top-level statement do not reflect later changes made by AFTER triggers.

Suppose an AFTER UPDATE trigger normalizes another value. I should not assume this:

UPDATE profiles
SET display_name = ?
WHERE id = ?
RETURNING *;

is necessarily a snapshot of every trigger-induced value that exists after the complete statement finishes.

That distinction is easy to miss because RETURNING feels like “give me the final row.” A better mental model is:

RETURNING -> values observed by the top-level DML statement

If later trigger effects are important to the application, I design for them explicitly rather than assuming they are folded into the returned row.

SQLite RETURNING is not a composable subquery

PostgreSQL users can run into another surprise.

In SQLite, a DML statement with RETURNING cannot currently be used as a subquery or as a data-producing CTE that feeds another query.

This kind of PostgreSQL-style idea is therefore not portable to SQLite:

WITH inserted AS (
    INSERT INTO jobs (name)
    VALUES ('resize-images')
    RETURNING id
)
SELECT id FROM inserted;

SQLite’s RETURNING output goes back to the application. It is not a temporary relation that can be freely composed inside another SQL statement.

When I need another database operation to consume those values atomically, I step back and reconsider the transaction or schema design rather than trying to force PostgreSQL query patterns into SQLite.

Large RETURNING results can cost memory

There is another implementation detail worth knowing for bulk operations.

SQLite performs the database changes and accumulates RETURNING output before handing the result rows back through its stepping interface. A statement returning many rows—or large text and BLOB values—can therefore consume substantial temporary memory.

This makes the following unnecessarily expensive if the caller only needs IDs:

UPDATE documents
SET archived = 1
WHERE project_id = ?
RETURNING *;

If documents contains a large body column, return only what the caller needs:

UPDATE documents
SET archived = 1
WHERE project_id = ?
RETURNING id;

To be fair, explicit result columns are a good API habit anyway. The memory behavior simply gives me another reason to avoid RETURNING * in bulk paths.

Consume every result your driver expects you to consume

At the SQL level, RETURNING turns a write into a statement that also produces rows. Application libraries differ in how they expose those rows, so I check the driver’s API instead of assuming an exec()-style method is enough.

Conceptually, the code should behave like:

prepare write with RETURNING
bind parameters
execute
read returned row(s)
finish statement
commit transaction if appropriate

For a single-row mutation, I also validate the cardinality I expect. If an update identified by a supposedly unique business key returns two rows, silently taking the first one can hide a data-model bug.

The SQL feature is simple; the surrounding application contract still deserves explicit checks.

Test the cases where no row comes back

The happy path is rarely the interesting test.

For a write such as:

UPDATE api_keys
SET revoked_at = CURRENT_TIMESTAMP
WHERE id = ?
  AND revoked_at IS NULL
RETURNING id, revoked_at;

I would test at least these behaviors:

  • an active key returns exactly one row;
  • an already revoked key returns no row;
  • an unknown ID returns no row;
  • the transaction rolls back correctly when a later operation fails;
  • application code does not confuse “no returned row” with a database execution error.

That last distinction is important. Zero affected rows can be valid control flow. A constraint error, busy database, I/O error, or malformed statement is a different category entirely.

Keep the mental model small

I think of SQLite RETURNING as a result channel attached to a write:

INSERT / UPDATE / DELETE
          |
          +---- database mutation
          |
          +---- RETURNING rows -> application

It is especially useful for generated IDs, defaults, counters, conditional updates, and deleted-row data. It can remove follow-up queries and make the relationship between a mutation and its result much clearer.

But it has boundaries: output order is arbitrary, trigger side effects are not fully reflected, the result cannot be composed as a SQLite subquery, and large returned payloads can use significant memory.

In the end, RETURNING is not about saving one query at all costs. The real benefit is that the write itself can tell the application what it directly changed. When that matches the operation I am building, the resulting code is usually smaller and easier to reason about.

Database SQLite SQL Query Design