SQLite RETURNING句の使い方｜INSERT/UPDATE/DELETEの結果を取得

直前の操作結果をその場で取得する方法

INSERT、UPDATE、DELETE を実行すると、SQLite は影響を受けた行数は教えてくれますが、どの行 が対象になったのか、最終的にどんな値になったのかまでは返してくれません。よくある回避策は、後続で SELECT を投げる方法ですが、これだとクエリが 2 回に分かれてしまいますし、その間に別の処理が同じ行を書き換えてしまうレースコンディションのリスクもわずかに残ります。

そこで登場するのが sqlite returning 句です。書き込み系のステートメントの末尾に 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;

1回のステートメント、1往復で、生成された id とデータベースが埋めてくれた created_at のデフォルト値を取得できます。

RETURNING は SQLite 3.35.0(2021年3月)で追加されました。構文エラーで弾かれる場合は、まず SELECT sqlite_version(); でバージョンを確認してください。古いビルドではこのキーワードが認識されません。

INSERT したレコードの ID を取得する

RETURNING を使う一番よくある場面は、INSERT 直後に自動採番された主キーを取得したいケースです。

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を定義していない場合でも、暗黙の行識別子(rowid)を取得できます:

CREATE TABLE logs (event TEXT);

INSERT INTO logs (event) VALUES ('startup')
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 ('Notebook', 4.50, 3)
RETURNING id, product, price * quantity AS subtotal;

RETURNING * は、デフォルト値などDB側で埋められた値も含めて全カラムをまとめて取得したいときに便利です。カラム名を一つずつ書かずに済みます。

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、そして SQLite が計算したタイムスタンプが返ってきます。

UPDATE で RETURNING を使う

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 が複数行にマッチした場合は、影響を受けた行ごとに 1 行ずつ返ってきます。

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;

3行入れたら、3行返ってきます。ただし返却順は保証されていないので、順序が重要ならクライアント側でソートしてください。

DELETE での RETURNING

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;

期限切れのセッション2件が、テーブルからすでに消えているにもかかわらず、フィールドの値をすべて保ったまま返ってきます。これを別の場所に移したい場合は、アーカイブ用テーブルにそのまま流し込むのにうってつけの構成です。同じトランザクション内で結果を読み取って別テーブルに INSERT すれば完結します。

UPSERT で RETURNING を使う

RETURNING は INSERT ... ON CONFLICT ... DO UPDATE(いわゆる UPSERT)と組み合わせても動きます。返ってくる行は、実際に走った側 — 新規挿入された行か、コンフリクトで更新された行か — をそのまま反映します。

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;

同じ文を 2 回実行してみてください。1 回目は INSERT が走って ('visits', 1) が返ります。2 回目は競合 (conflict) が発生して値がインクリメントされ、('visits', 2) が返ってきます。どちらにしても 1 文で 1 行返るので、「これは INSERT だった? それとも UPDATE だった?」と確認してから次に進む必要はありません。

「現在の値が欲しい、なければ作ってほしい」をラウンドトリップなしで処理するなら、SQLite ではこれが一番きれいなパターンです。

知っておきたい注意点

ハマりやすいポイントをいくつか挙げておきます。

• RETURNING で見えるのは、INSERT と UPDATE の場合は変更_後_の行、DELETE の場合は変更_前_の行です。逆側を取得する構文は用意されていません。
• 返ってくる行の順序は保証されません。順序が重要ならクライアント側で ORDER BY を付けましょう。
• RETURNING をサブクエリの中に書くことはできません。式ではなく、書き込み文のトップレベルに付ける句です。
• RETURNING は BEFORE トリガーで加工された値ではなく、実際に書き込まれた値を返します。AFTER トリガーは書き込みと行の返却の間に実行されます。
• 生成カラム (generated column) や DEFAULT 値も結果に含まれます。だからこそ RETURNING * は、データベースが何を埋めたかをサッと確認するのに便利なんです。

次は: CSV データのインポート

RETURNING は 1 行〜数行の書き込みで結果をすぐ確認したいときに重宝します。一方、ファイルから何千行もロードするような場面では、SQLite の CSV インポート機能の出番です。次のページではその話をします。