Database in memoria SQLite: database veloci e usa e getta con :memory:

Un database che vive nella RAM

SQLite ha un nome file speciale: :memory:. Apri un database con quel nome e SQLite salta del tutto il disco: l'intero database vive nella RAM. Tabelle, indici, transazioni, chiavi esterne: ogni funzionalità si comporta esattamente allo stesso modo. L'unica differenza è che, quando la connessione si chiude, il database non c'è più.

Dalla riga di comando:

sqlite3 :memory:

Ora sei al prompt di SQLite con un database nuovo e vuoto che esiste solo in memoria. Crea una tabella, inserisci qualche riga, interrogala: tutto normale:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('prima nota'), ('seconda nota');
SELECT * FROM notes;

Chiudi la sessione e quei dati evaporano. Non resta nessun file, perché nessun file è mai stato creato.

Perché ne vorresti uno

Un database che non sopravvive a un riavvio sembra un bug, non una funzionalità. In realtà è utile in tre situazioni.

Test. Ogni test ottiene un database pulito in pochi millisecondi. Niente file temporanei da ripulire, niente stato residuo dall'esecuzione precedente, niente file di fixture condiviso che resta bloccato. La maggior parte delle suite di test in Python, Node e Go che usano SQLite apre :memory: proprio per questo.

Analisi usa e getta. Carichi un CSV, esegui qualche query, butti via tutto. Più veloce che avviare un database vero e più comodo che analizzare il file nel codice ogni volta.

Cache e spazio di lavoro. Dentro un programma che gira a lungo, un database SQLite in memoria è un motore di query al volo sorprendentemente efficace per i dati che hai già caricato.

Il filo comune: vuoi l'SQL, non vuoi la persistenza.

Prestazioni: più veloce, ma senza magie

I database in memoria saltano il disco, quindi le scritture che normalmente finirebbero sul filesystem diventano semplici aggiornamenti in memoria. I carichi di lavoro limitati dall'I/O diventano sensibilmente più veloci. Quelli limitati dalla CPU (pianificazione di query complesse, grandi ordinamenti) cambiano appena, perché SQLite teneva già in memoria le pagine più usate.

Una breve dimostrazione di quanto la sintassi sia identica:

CREATE TABLE numbers (n INTEGER);

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 1000
)
INSERT INTO numbers SELECT n FROM seq;

SELECT COUNT(*), SUM(n), AVG(n) FROM numbers;

Questo è stato eseguito su un database in memoria, ma è lo stesso SQL che eseguiresti su un file. Al motore del database non importa.

In memoria o su file: quando scegliere cosa

Il compromesso è semplice, e vale la pena esplicitarlo:

• Database su file (mydata.db): persiste tra un riavvio e l'altro. Più processi possono aprirlo. Sopravvive ai crash (con la modalità WAL, nella maggior parte dei casi). Usalo per tutto ciò che deve ricordare qualcosa.
• Database in memoria (:memory:): sparisce alla chiusura. È privato della connessione che lo ha aperto (di default). Più veloce per lavori usa e getta con molte scritture. Usalo per test, lavori temporanei e cache di breve durata.

Se hai dubbi, ti serve un file. Quello in memoria è il caso speciale.

Ogni connessione ha il suo

Un dettaglio sottile che sorprende molti: aprire :memory: due volte ti dà due database separati. Non condividono tabelle, non condividono dati, non si vedono affatto.

-- Terminal 1
sqlite3 :memory:
sqlite> CREATE TABLE t (x); INSERT INTO t VALUES (1);

-- Terminal 2
sqlite3 :memory:
sqlite> SELECT * FROM t;
Error: no such table: t

Non è un bug, è una scelta di progetto. :memory: significa "un database privato per questa connessione". Lo stesso vale dentro un singolo programma: se il tuo codice apre due connessioni a :memory:, ognuna ottiene il proprio database isolato.

Condividere un database in memoria tra connessioni

Se ti serve davvero che più connessioni vedano lo stesso database in memoria, SQLite lo supporta tramite i nomi file URI e la cache condivisa. La stringa magica è file::memory:?cache=shared:

sqlite3 'file::memory:?cache=shared'

Qualsiasi connessione nello stesso processo che apre esattamente quell'URI si unisce allo stesso database. Chiudile tutte e il database sparisce.

Puoi anche dare un nome a un database in memoria, utile quando vuoi più database condivisi distinti:

sqlite3 'file:mydb?mode=memory&cache=shared'

Il nome mydb qui è solo un'etichetta: il file continua a non esistere. Due connessioni che aprono file:mydb?mode=memory&cache=shared condividono un database; una connessione che apre file:other?mode=memory&cache=shared ne ottiene un altro.

Salvare su disco un database in memoria

A volte fai un intero lavoro in memoria e poi decidi di voler conservare il risultato. La CLI ha il dot-command .backup per questo:

sqlite3 :memory:
sqlite> CREATE TABLE results (id INTEGER, score REAL);
sqlite> INSERT INTO results VALUES (1, 0.91), (2, 0.87);
sqlite> .backup snapshot.db
sqlite> .quit

Ora snapshot.db è un normale database su file con lo stesso contenuto. Puoi aprirlo più tardi con sqlite3 snapshot.db e riprendere da dove avevi lasciato.

Funziona anche al contrario: .restore carica un database su file nella memoria della connessione corrente:

sqlite3 :memory:
sqlite> .restore snapshot.db
sqlite> SELECT * FROM results;

Dal codice di un'applicazione, l'API C di SQLite espone lo stesso meccanismo con sqlite3_backup_init, e la maggior parte dei binding per i vari linguaggi lo incapsula. Il modulo sqlite3 di Python, per esempio, ha Connection.backup().

Un errore comune

A volte si prova a "salvare" un database in memoria collegando un file e copiando:

ATTACH DATABASE 'output.db' AS disk;
CREATE TABLE disk.notes AS SELECT * FROM main.notes;
DETACH DATABASE disk;

Funziona per semplici copie di tabelle, ma non conserva esattamente indici, trigger, viste o chiavi esterne. Per una copia fedele di un intero database usa .backup (o l'API di backup): fa una copia binaria esatta a livello di pagina.

Cosa portarti a casa

• :memory: è un nome file speciale di SQLite che crea un database nella RAM, senza nessun file dietro.
• L'SQL è identico a quello di un database su file: stesse tabelle, stesse query, stessi vincoli.
• Ogni connessione a :memory: è privata; usa gli URI con cache condivisa (file::memory:?cache=shared) quando più connessioni devono condividerne uno.
• È lo strumento giusto per test, analisi usa e getta e cache di breve durata, non per ciò che deve sopravvivere a un riavvio.
• Porta su disco un database in memoria con .backup quando decidi di volerlo conservare.

Prossimo passo: creare tabelle

Hai già visto passare CREATE TABLE in qualche esempio. La prossima pagina rallenta e lo spiega come si deve: definizioni delle colonne, tipi, i vincoli che puoi aggiungere e le piccole scelte che rendono uno schema piacevole da usare nel tempo.