Исполняемые доки SQLite: RETURNING

Q: Поддерживает ли SQLite конструкцию RETURNING?

Да, начиная с версии 3.35.0 (вышла в марте 2021). RETURNING можно дописать к INSERT, UPDATE и DELETE, чтобы получить обратно затронутые строки. На более старых версиях парсер просто не поймёт такой запрос — проверить версию можно через SELECT sqlite_version();.

Q: Как получить id только что вставленной строки в SQLite?

Достаточно написать INSERT ... RETURNING id (или RETURNING rowid, если у таблицы нет явного первичного ключа). Сгенерированное значение возвращается прямо из самого запроса, так что отдельный вызов lastinsertrowid() уже не нужен.

Q: Можно ли вернуть через RETURNING сразу несколько колонок?

Да. Перечисляете нужные поля через запятую, как в обычном SELECT: RETURNING id, name, created_at. Можно использовать RETURNING , чтобы получить все колонки, или писать выражения — например, RETURNING id, price quantity AS total.

Q: Работает ли RETURNING вместе с UPSERT и ON CONFLICT?

Да. Конструкция INSERT ... ON CONFLICT ... DO UPDATE ... RETURNING ... вернёт строку независимо от того, была ли она вставлена с нуля или обновлена при разрешении конфликта. Это самый чистый способ сделать upsert и сразу прочитать итоговое состояние за один запрос.

Способ увидеть, что только что произошло

Когда вы выполняете INSERT, UPDATE или DELETE, SQLite сообщает только количество затронутых строк — но не какие именно строки изменились и не их итоговые значения. Классический обходной путь — следом запустить SELECT. Получаем два запроса, два обращения к базе и небольшое окно гонки, в котором кто-то другой успеет поменять строку между ними.

Решает это RETURNING. Дописываете его к запросу на изменение данных, перечисляете нужные колонки — и SQLite возвращает затронутые строки так, будто вы выполнили по ним SELECT:

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

INSERT INTO notes (title) VALUES ('Первая заметка')
RETURNING id, title, created_at;

Один запрос, одна поездка в базу — и вы получаете обратно сгенерированный id вместе со значением created_at, которое СУБД подставила по умолчанию.

RETURNING появился в SQLite 3.35.0 (март 2021). Если запрос валится с синтаксической ошибкой, проверьте версию через SELECT sqlite_version(); — в старых сборках этого ключевого слова просто нет.

Как получить ID после INSERT в SQLite

Чаще всего к RETURNING обращаются именно для того, чтобы сразу после вставки забрать сгенерированный первичный ключ:

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

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

До появления RETURNING приходилось делать INSERT, а потом дёргать last_insert_rowid() (или аналог из драйвера) на том же соединении. Способ рабочий, но он завязан на состояние соединения — а это лёгкий способ выстрелить себе в ногу при использовании пулов соединений или потоков. RETURNING id гораздо честнее: он привязан к самому запросу и ведёт себя одинаково, независимо от того, что управляет соединением.

Если в таблице не объявлен явный INTEGER PRIMARY KEY, неявный идентификатор строки тоже можно получить:

CREATE TABLE logs (event TEXT);

INSERT INTO logs (event) VALUES ('запуск')
RETURNING rowid, event;

У каждой обычной таблицы в SQLite есть rowid, и RETURNING спокойно его вернёт.

Несколько колонок и выражения

В RETURNING можно писать то же самое, что и в списке колонок SELECT. Перечисляйте поля, ставьте *, считайте выражения, задавайте псевдонимы:

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 ('Блокнот', 4.50, 3)
RETURNING id, product, price * quantity AS subtotal;

RETURNING * пригодится, когда нужно получить всё разом — в том числе значения по умолчанию, которые подставила сама база, — и при этом не перечислять каждую колонку вручную:

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

INSERT INTO events (name) VALUES ('вход')
RETURNING *;

Вы получаете свежий id, переданное name и timestamp, который посчитал сам SQLite.

RETURNING вместе с UPDATE

В случае с UPDATE конструкция RETURNING возвращает значения уже после обновления — то есть строку в том виде, в котором она оказалась после применения изменений:

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;

Возвращается новый баланс Ada, равный 125, а не старый 100. Именно поэтому RETURNING отлично подходит для атомарных счётчиков и операций списания/пополнения — не нужно читать, считать, записывать и снова перечитывать.

Если под WHERE попадает несколько строк, вы получите по одной строке на каждую затронутую запись:

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;

Три строки на входе — три на выходе. Порядок не гарантируется: если нужен конкретный порядок, сортируйте результат уже на стороне клиента.

RETURNING с DELETE

При DELETE конструкция RETURNING возвращает строки в том виде, в каком они были непосредственно перед удалением. Удобно для архивации, журналов аудита или просто чтобы убедиться, что именно удалилось:

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;

Вы получаете обратно обе просроченные сессии со всеми полями, хотя в таблице их уже нет. Если нужно перенести их куда-то ещё — это идеальный сценарий для таблицы-архива: читаете результат и вставляете в другую таблицу в рамках той же транзакции.

RETURNING при UPSERT в SQLite

RETURNING отлично работает и с INSERT ... ON CONFLICT ... DO 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;

Запустите этот запрос дважды. В первый раз он вставит строку и вернёт ('visits', 1). Во второй раз сработает конфликт, значение увеличится, и вы получите ('visits', 2). В обоих случаях — один запрос и одна строка на выходе. Не нужно гадать «вставилось или обновилось?», прежде чем двигаться дальше.

Это самый чистый паттерн в SQLite для сценария «дай мне текущее значение, а если его нет — создай», причём без лишних обращений к базе.

Несколько нюансов, о которых стоит знать

Вот пара деталей, которые часто застают врасплох:

RETURNING для INSERT и UPDATE всегда видит строку после изменения, а для DELETE — до удаления. Синтаксиса, чтобы получить «другую сторону», просто нет.
Порядок возвращаемых строк не гарантирован. Если он важен — добавляйте ORDER BY на стороне клиента.
RETURNING нельзя засунуть внутрь подзапроса. Это конструкция верхнего уровня для пишущего запроса, а не выражение.
RETURNING не показывает данные, изменённые BEFORE-триггерами как промежуточные — он возвращает то, что реально записалось в таблицу. AFTER-триггеры срабатывают между записью и возвратом строки.
Сгенерированные колонки и значения DEFAULT тоже попадают в результат. Именно поэтому RETURNING * — быстрый способ посмотреть, чем база заполнила строку за вас.

Дальше: импорт данных из CSV

RETURNING хорош, когда вы пишете одну строку или горстку строк и хотите сразу увидеть результат. А вот когда нужно залить тысячи строк из файла — в дело вступают встроенные средства импорта CSV в SQLite. Об этом — на следующей странице.

Часто задаваемые вопросы

Поддерживает ли SQLite конструкцию RETURNING?

Да, начиная с версии 3.35.0 (вышла в марте 2021). RETURNING можно дописать к INSERT, UPDATE и DELETE, чтобы получить обратно затронутые строки. На более старых версиях парсер просто не поймёт такой запрос — проверить версию можно через SELECT sqlite_version();.

Как получить id только что вставленной строки в SQLite?

Достаточно написать INSERT ... RETURNING id (или RETURNING rowid, если у таблицы нет явного первичного ключа). Сгенерированное значение возвращается прямо из самого запроса, так что отдельный вызов last_insert_rowid() уже не нужен.

Можно ли вернуть через RETURNING сразу несколько колонок?

Да. Перечисляете нужные поля через запятую, как в обычном SELECT: RETURNING id, name, created_at. Можно использовать RETURNING *, чтобы получить все колонки, или писать выражения — например, RETURNING id, price * quantity AS total.

Работает ли RETURNING вместе с UPSERT и ON CONFLICT?

Да. Конструкция INSERT ... ON CONFLICT ... DO UPDATE ... RETURNING ... вернёт строку независимо от того, была ли она вставлена с нуля или обновлена при разрешении конфликта. Это самый чистый способ сделать upsert и сразу прочитать итоговое состояние за один запрос.

Связанные концепции

RETURNING в SQLite: возврат строк из INSERT, UPDATE, DELETE