Guida SQLite interattiva: Clausola RETURNING

Q: Come ottengo l'ID di una riga appena inserita in SQLite?

Usa INSERT ... RETURNING id (oppure RETURNING rowid se la tabella non ha una chiave primaria esplicita). Ti restituisce il valore generato all'interno della stessa istruzione, quindi non ti serve una seconda query come lastinsertrowid().

Q: RETURNING funziona con UPSERT e ON CONFLICT?

Sì. INSERT ... ON CONFLICT ... DO UPDATE ... RETURNING ... restituisce la riga sia che sia stata appena inserita sia che sia stata aggiornata dalla gestione del conflitto. È il modo più pulito per fare un upsert e leggere lo stato risultante in un solo passaggio.

Un modo per vedere cosa è appena successo

Quando esegui un INSERT, un UPDATE o un DELETE, SQLite ti dice quante righe sono state toccate, ma non quali righe né quali sono i loro valori finali. La soluzione classica è una SELECT successiva. Sono due passaggi, due istruzioni e una piccola finestra in cui qualcun altro potrebbe modificare la riga nel frattempo.

RETURNING risolve il problema. Lo aggiungi a un'istruzione di scrittura, elenchi le colonne che vuoi indietro e SQLite ti consegna le righe coinvolte come se avessi appena eseguito una SELECT su di esse:

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

INSERT INTO notes (title) VALUES ('Prima nota')
RETURNING id, title, created_at;

Un'istruzione, un passaggio, e ottieni l'id generato e il valore predefinito di created_at che il database ha compilato per te.

RETURNING è stato aggiunto in SQLite 3.35.0 (marzo 2021). Se la tua istruzione viene rifiutata con un errore di sintassi, verifica con SELECT sqlite_version();: le build più vecchie non conoscono la parola chiave.

Recuperare l'ID generato

Il motivo più comune per usare RETURNING è recuperare la chiave primaria generata in automatico subito dopo l'inserimento:

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

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

Prima di RETURNING, facevi l'insert e poi chiamavi last_insert_rowid() (o l'equivalente del tuo driver) sulla stessa connessione. Funziona ancora, ma si basa sullo stato della connessione: è facile sbagliare con pool di connessioni o thread. RETURNING id è esplicito, limitato all'istruzione e funziona allo stesso modo qualunque cosa ospiti la connessione.

Se la tua tabella non dichiara un INTEGER PRIMARY KEY esplicito, puoi comunque ottenere l'identificatore implicito della riga:

CREATE TABLE logs (event TEXT);

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

Ogni tabella SQLite normale ha un rowid, e RETURNING te lo consegna.

Più colonne ed espressioni

RETURNING accetta la stessa forma della lista di colonne di una SELECT. Elenca colonne, usa *, costruisci espressioni, assegna alias:

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

RETURNING * è comodo quando vuoi tutto, compresi i valori predefiniti compilati dal database, senza nominare ogni colonna:

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

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

Vedi il nuovo id, il name che hai passato e il timestamp calcolato da SQLite.

RETURNING con UPDATE

Con un UPDATE, RETURNING ti dà i valori dopo l'aggiornamento, cioè la riga com'è dopo che le tue modifiche sono state applicate:

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;

Ottieni il nuovo saldo di Ada, 125, non il vecchio 100. Questo rende RETURNING perfetto per contatori atomici e operazioni di accredito e addebito: non devi leggere, calcolare, scrivere e rileggere.

Se il WHERE corrisponde a più righe, ottieni una riga per ogni riga coinvolta:

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;

Tre righe in ingresso, tre righe in uscita. L'ordine non è garantito: se ti serve un ordine preciso, ordina il risultato lato client.

RETURNING con DELETE

Con DELETE, RETURNING ti dà le righe com'erano subito prima della cancellazione. Utile per archiviare, per le tracce di audit o semplicemente per confermare cosa è stato rimosso:

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;

Ottieni le due sessioni scadute con tutti i loro campi intatti, anche se non esistono più nella tabella. Se vuoi spostarle altrove, è la base perfetta per una tabella di archivio: leggi il risultato e inseriscilo altrove nella stessa transazione.

RETURNING con UPSERT

RETURNING funziona anche con INSERT ... ON CONFLICT ... DO UPDATE. La riga restituita riflette il ramo che è stato eseguito, il nuovo insert o l'aggiornamento per conflitto:

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;

Esegui l'istruzione due volte. La prima volta inserisce e restituisce ('visits', 1). La seconda volta scatta il conflitto, il valore viene incrementato e ottieni ('visits', 2). In entrambi i casi, un'istruzione e una riga in uscita: non devi chiederti "ha inserito o aggiornato?" prima di proseguire.

È lo schema più pulito in SQLite per "dammi il valore attuale, creandolo se serve" senza passaggi aggiuntivi.

Alcune cose da sapere

Qualche dettaglio che coglie di sorpresa:

RETURNING vede sempre la riga dopo la modifica per INSERT e UPDATE, e prima della modifica per DELETE. Non c'è una sintassi per chiedere l'altro lato.
L'ordine delle righe restituite non è garantito. Aggiungi un ORDER BY lato client se ti interessa.
Non puoi mettere RETURNING dentro una subquery. È una clausola di primo livello dell'istruzione di scrittura, non un'espressione.
RETURNING non restituisce i dati modificati dai trigger BEFORE: restituisce i valori effettivamente scritti. I trigger AFTER vengono eseguiti tra la scrittura e la restituzione della riga.
Le colonne generate e i valori DEFAULT sono visibili nel risultato. È proprio per questo che RETURNING * è un modo rapido per controllare cosa ha compilato il database al posto tuo.

Prossimo passo: importare dati CSV

RETURNING è ottimo quando scrivi una riga o poche alla volta e vuoi vedere subito il risultato. Quando carichi migliaia di righe da un file, userai invece gli strumenti di importazione CSV di SQLite: è la prossima pagina.

Domande frequenti

SQLite supporta la clausola RETURNING?

Sì, dalla versione 3.35.0 (uscita a marzo 2021). Puoi aggiungere RETURNING alle istruzioni INSERT, UPDATE e DELETE per ottenere le righe coinvolte. Se usi una versione di SQLite più vecchia, il parser la rifiuterà: verifica con SELECT sqlite_version();.

Come ottengo l'ID di una riga appena inserita in SQLite?

Usa INSERT ... RETURNING id (oppure RETURNING rowid se la tabella non ha una chiave primaria esplicita). Ti restituisce il valore generato all'interno della stessa istruzione, quindi non ti serve una seconda query come last_insert_rowid().

RETURNING può restituire più di una colonna?

Sì. Elenca le colonne che vuoi separate da virgole, proprio come in una SELECT: RETURNING id, name, created_at. Puoi anche usare RETURNING * per ottenere tutte le colonne, oppure scrivere espressioni come RETURNING id, price * quantity AS total.

RETURNING funziona con UPSERT e ON CONFLICT?

Sì. INSERT ... ON CONFLICT ... DO UPDATE ... RETURNING ... restituisce la riga sia che sia stata appena inserita sia che sia stata aggiornata dalla gestione del conflitto. È il modo più pulito per fare un upsert e leggere lo stato risultante in un solo passaggio.

Concetti correlati