RETURNING Clause

RETURNING lets write operations return row data right away.

This removes extra round-trips where you would otherwise run a second SELECT.

Core Concepts

flowchart LR
  A[INSERT/UPDATE/DELETE] --> B[Apply data change]
  B --> C[RETURNING columns]
  C --> D[Immediate result rows]

Operation	Example
Insert + returning	INSERT ... RETURNING id;
Update + returning	UPDATE ... RETURNING *;
Delete + returning	DELETE ... RETURNING name;

INSERT RETURNING

CREATE TABLE tickets (
  ticket_id   INTEGER PRIMARY KEY,
  title       TEXT NOT NULL,
  status      TEXT DEFAULT 'open'
);

INSERT INTO tickets (title)
VALUES ('Fix login bug')
RETURNING ticket_id, title, status;

ticket_id	title	status
1	Fix login bug	open

UPDATE / DELETE RETURNING

UPDATE tickets
SET status = 'done'
WHERE ticket_id = 1
RETURNING ticket_id, status;

ticket_id	status
1	done

DELETE FROM tickets
WHERE ticket_id = 1
RETURNING ticket_id, title;

ticket_id	title
1	Fix login bug

Code Examples

-- Insert and return generated key in one step.
INSERT INTO tickets (title)
VALUES ('Write docs')
RETURNING ticket_id AS new_id;

new_id
2

SQLite-Specific Nuances

SQLite Nuance

RETURNING is available in SQLite 3.35.0 and newer.

If you are on an older version, you must query again after write operations.

Common Pitfalls / Best Practices

Pitfall

Using RETURNING in environments with older SQLite versions and assuming it always works.

Best Practice

Check sqlite3 --version and keep write statements + returned columns minimal and intentional.

Quick Challenge

Insert a row into tickets with title Review PR and return ticket_id plus status.

View Solution

INSERT INTO tickets (title)
VALUES ('Review PR')
RETURNING ticket_id, status;

ticket_id	status
e.g. 3	open