RETURNING w SQLite: wiersze z INSERT, UPDATE i DELETE

Sposób, by zobaczyć, co się właśnie stało

Gdy uruchamiasz INSERT, UPDATE lub DELETE, SQLite mówi, ilu wierszy to dotyczyło, ale nie mówi, których, ani jakie mają końcowe wartości. Klasyczne obejście to dodatkowy SELECT. To dwa przejścia, dwie instrukcje i małe okno wyścigu, w którym ktoś inny może zmienić wiersz pomiędzy nimi.

RETURNING to naprawia. Dopisujesz go do instrukcji zapisu, podajesz kolumny, które chcesz dostać, a SQLite oddaje ci zmienione wiersze, tak jakby na nich właśnie wykonano SELECT:

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

INSERT INTO notes (title) VALUES ('Pierwsza notatka')
RETURNING id, title, created_at;

Jedna instrukcja, jedno przejście, a dostajesz wygenerowane id i domyślną wartość created_at, którą baza wypełniła za ciebie.

RETURNING dodano w SQLite 3.35.0 (marzec 2021). Jeśli instrukcja zostaje odrzucona jako błąd składni, sprawdź SELECT sqlite_version();: starsze wersje nie znają tego słowa kluczowego.

Odbieranie wygenerowanego ID

Najczęstszy powód, by sięgnąć po RETURNING, to pobranie automatycznie wygenerowanego klucza głównego zaraz po wstawieniu:

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

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

Przed RETURNING trzeba było wstawić wiersz, a potem wywołać last_insert_rowid() (lub odpowiednik w sterowniku) na tym samym połączeniu. To nadal działa, ale opiera się na stanie połączenia, a przy pulach połączeń czy wątkach łatwo o błąd. RETURNING id jest jawne, lokalne dla instrukcji i działa tak samo bez względu na to, co obsługuje połączenie.

Jeśli tabela nie deklaruje jawnego INTEGER PRIMARY KEY, i tak możesz pobrać niejawny identyfikator wiersza:

CREATE TABLE logs (event TEXT);

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

Każda zwykła tabela SQLite ma rowid, a RETURNING go odda.

Wiele kolumn i wyrażenia

RETURNING przyjmuje ten sam kształt co lista kolumn w SELECT. Wypisuj kolumny, używaj *, buduj wyrażenia, nadawaj im aliasy:

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 ('Zeszyt', 4.50, 3)
RETURNING id, product, price * quantity AS subtotal;

RETURNING * przydaje się, gdy chcesz wszystko, łącznie z wartościami domyślnymi, które wypełniła baza, bez wymieniania każdej kolumny:

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

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

Widzisz nowe id, przekazane name i znacznik czasu obliczony przez SQLite.

RETURNING z UPDATE

Przy UPDATE klauzula RETURNING daje wartości po aktualizacji, czyli wiersz w takiej postaci, jaką ma po wprowadzeniu zmian:

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;

Dostajesz nowe saldo Ada równe 125, a nie stare 100. Dzięki temu RETURNING świetnie nadaje się do atomowych liczników i operacji uznania i obciążenia: nie musisz odczytywać, liczyć, zapisywać i ponownie odczytywać.

Jeśli WHERE pasuje do wielu wierszy, dostajesz po jednym wierszu na każdy zmieniony:

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;

Trzy wiersze na wejściu, trzy na wyjściu. Kolejność nie jest gwarantowana: jeśli potrzebujesz konkretnej, posortuj wynik po stronie klienta.

RETURNING z DELETE

Przy DELETE klauzula RETURNING daje wiersze w takiej postaci, jaką miały tuż przed usunięciem. Przydaje się do archiwizacji, ścieżek audytu albo po prostu do potwierdzenia, co zostało usunięte:

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;

Dostajesz z powrotem dwie wygasłe sesje ze wszystkimi polami, choć w tabeli już ich nie ma. Jeśli chcesz je gdzieś przenieść, to idealny punkt wyjścia do tabeli archiwum: odczytaj wynik i wstaw go gdzie indziej w tej samej transakcji.

RETURNING z UPSERT

RETURNING działa też z INSERT ... ON CONFLICT ... DO UPDATE. Zwrócony wiersz odzwierciedla tę gałąź, która się wykonała: nowe wstawienie albo aktualizację po konflikcie:

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;

Uruchom tę instrukcję dwa razy. Za pierwszym razem wstawia wiersz i zwraca ('visits', 1). Za drugim razem zachodzi konflikt, wartość jest zwiększana i dostajesz ('visits', 2). Tak czy inaczej: jedna instrukcja i jeden wiersz na wyjściu, bez pytania "czy to było wstawienie, czy aktualizacja?", zanim pójdziesz dalej.

To najczystszy wzorzec w SQLite na "daj mi bieżącą wartość, a w razie potrzeby ją utwórz" bez dodatkowych przejść.

Kilka rzeczy, które warto wiedzieć

Garść szczegółów, które potrafią zaskoczyć:

• RETURNING zawsze widzi wiersz po zmianie przy INSERT i UPDATE oraz przed zmianą przy DELETE. Nie ma składni, która pozwoliłaby poprosić o drugą stronę.
• Kolejność zwracanych wierszy nie jest gwarantowana. Jeśli ma znaczenie, dodaj ORDER BY po stronie klienta.
• Nie możesz umieścić RETURNING wewnątrz podzapytania. To klauzula najwyższego poziomu w instrukcji zapisu, a nie wyrażenie.
• RETURNING nie zwraca danych zmodyfikowanych przez wyzwalacze BEFORE: zwraca wartości, które faktycznie zostały zapisane. Wyzwalacze AFTER działają pomiędzy zapisem a zwróceniem wiersza.
• Kolumny generowane i wartości DEFAULT są widoczne w wyniku. Właśnie dlatego RETURNING * to szybki sposób, by sprawdzić, co baza wypełniła za ciebie.

Dalej: import danych CSV

RETURNING sprawdza się świetnie, gdy zapisujesz jeden lub kilka wierszy naraz i chcesz od razu zobaczyć wynik. Gdy ładujesz tysiące wierszy z pliku, sięgniesz raczej po narzędzia SQLite do importu CSV. O nich jest następna strona.