Esportare dati da SQLite: CSV, JSON e dump SQL dalla CLI

Esportare è un lavoro della shell, non un'istruzione SQL

SQLite non ha un COPY ... TO o un SELECT INTO OUTFILE come Postgres o MySQL. L'esportazione dei dati avviene invece nella shell da riga di comando sqlite3, tramite i dot-command: .mode, .headers, .output, .dump. Una volta che conosci questi quattro, puoi produrre CSV, JSON, testo semplice o un dump SQL completo da qualsiasi database.

Il modello mentale: dici alla shell come formattare i risultati (.mode), se includere i nomi delle colonne (.headers) e dove mandare l'output (.output). Poi esegui una query, e i suoi risultati finiscono nel file.

Prepariamo un piccolo database con cui fare pratica:

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

INSERT INTO users (name, email, signup_date) VALUES
    ('Ada Lovelace', 'ada@example.com', '2025-01-15'),
    ('Boris Johnson', 'boris@example.com', '2025-02-03'),
    ('Carmen Diaz', 'carmen@example.com', '2025-03-22');

SELECT * FROM users;

Tre righe, quattro colonne. Le esporteremo in diversi formati.

CSV: .mode csv con intestazioni

Il CSV è il formato di esportazione più comune: fogli di calcolo, pipeline di dati e la maggior parte degli altri strumenti lo capiscono. Dentro la shell sqlite3:

sqlite> .mode csv
sqlite> .headers on
sqlite> .output users.csv
sqlite> SELECT * FROM users;
sqlite> .output stdout

Cosa è appena successo:

• .mode csv formatta ogni riga come valori separati da virgole, mettendo tra virgolette i campi che contengono virgole, virgolette o a capo.
• .headers on aggiunge una prima riga con i nomi delle colonne. Senza, il tuo CSV non ha intestazione, e di solito non è quello che vuoi.
• .output users.csv reindirizza i risultati su un file. Da qui in poi l'output delle query va lì invece che sullo schermo.
• La SELECT viene eseguita e scrive nel file senza mostrare nulla.
• .output stdout riporta l'output nel terminale, così vedi i risultati della query successiva.

Il file ottenuto:

id,name,email,signup_date
1,"Ada Lovelace",ada@example.com,2025-01-15
2,"Boris Johnson",boris@example.com,2025-02-03
3,"Carmen Diaz",carmen@example.com,2025-03-22

Puoi esportare il risultato di qualsiasi query, non solo tabelle intere: filtra, fai join, aggrega, poi reindirizza:

sqlite> .output recent_users.csv
sqlite> SELECT name, email FROM users WHERE signup_date >= '2025-02-01';
sqlite> .output stdout

Una sola riga dalla shell

Non serve nemmeno entrare nella shell interattiva. Passa i dot-command e l'SQL a sqlite3 dalla shell del tuo sistema operativo:

sqlite3 mydb.sqlite <<EOF
.headers on
.mode csv
.output users.csv
SELECT * FROM users;
EOF

È la forma giusta per script e cron job: ripetibile, senza digitare nulla a mano. Il .output vale solo per quella sessione, quindi non lascia stato in giro.

JSON: .mode json

Per le esportazioni destinate a una web app o a uno strumento che legge JSON, .mode json produce un array di oggetti, uno per riga:

sqlite> .mode json
sqlite> .output users.json
sqlite> SELECT * FROM users;
sqlite> .output stdout

Il file:

[{"id":1,"name":"Ada Lovelace","email":"ada@example.com","signup_date":"2025-01-15"},
{"id":2,"name":"Boris Johnson","email":"boris@example.com","signup_date":"2025-02-03"},
{"id":3,"name":"Carmen Diaz","email":"carmen@example.com","signup_date":"2025-03-22"}]

In JSON le intestazioni sono implicite (sono le chiavi), quindi .headers qui non si applica. Se vuoi una struttura personalizzata, con oggetti annidati o campi rinominati, costruiscila dentro la query con json_object():

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users (name, email) VALUES
    ('Ada Lovelace',  'ada@example.com'),
    ('Boris Johnson', 'boris@example.com'),
    ('Carmen Diaz',   'carmen@example.com');

SELECT json_object(
    'id', id,
    'name', name,
    'contact', json_object('email', email)
) AS user_json
FROM users;

Così ottieni una stringa JSON per riga, con il pieno controllo sulla struttura. Combinala con json_group_array() per raccogliere l'intero risultato in un unico documento JSON.

Dump SQL completo: .dump

.dump è una cosa diversa da CSV e JSON. Produce un file .sql che contiene lo schema e tutti i dati sotto forma di istruzioni CREATE TABLE e INSERT: abbastanza per ricostruire il database partendo da zero:

sqlite3 mydb.sqlite .dump > backup.sql

Un estratto di ciò che viene fuori:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    signup_date TEXT NOT NULL
);
INSERT INTO users VALUES(1,'Ada Lovelace','ada@example.com','2025-01-15');
INSERT INTO users VALUES(2,'Boris Johnson','boris@example.com','2025-02-03');
INSERT INTO users VALUES(3,'Carmen Diaz','carmen@example.com','2025-03-22');
COMMIT;

Il ripristino è l'operazione speculare: passa il file a un database nuovo:

sqlite3 restored.sqlite < backup.sql

.dump è lo strumento giusto per i backup, per gli snapshot dei dati di test sotto controllo di versione e per spostare un database tra macchine. Conserva indici, trigger, viste: tutto ciò che c'è nello schema.

Dump di una sola tabella

.dump accetta il nome di una tabella (o un pattern) per limitare l'output:

sqlite3 mydb.sqlite ".dump users" > users_only.sql

Così esporti solo lo schema e le righe della tabella users. È utile quando vuoi copiare una tabella in un altro database senza portarti dietro il resto. Puoi anche usare un pattern: .dump 'log_%' esporta tutte le tabelle che iniziano con log_.

Schema senza dati

A volte ti serve la struttura senza le righe: per la documentazione, per un ambiente di sviluppo pulito o per confrontare gli schemi di più database. .schema stampa solo le istruzioni CREATE:

sqlite3 mydb.sqlite .schema > schema.sql

Aggiungi il nome di una tabella per ottenerne solo una:

sqlite3 mydb.sqlite ".schema users" > users_schema.sql

L'output è SQL semplice (CREATE TABLE, CREATE INDEX, CREATE TRIGGER), pronto da eseguire su un database vuoto.

Altre modalità utili

.mode ha più opzioni oltre a CSV e JSON. Alcune da conoscere:

.mode column        -- colonne allineate, comode da leggere nel terminale
.mode markdown      -- tabelle separate da pipe, compatibili con GitHub
.mode html          -- output in <table> HTML
.mode tabs          -- valori separati da tabulazioni (TSV)
.mode insert users  -- genera istruzioni INSERT per la tabella indicata
.mode quote         -- valori tra virgolette in stile SQL, utili per l'ispezione

.mode markdown è ottimo per incollare i risultati di una query in un README o in una pull request. .mode insert <table> è un modo rapido per generare dati di seed: esegui una SELECT, cattura le istruzioni INSERT e incollale in un file di fixture.

sqlite> .mode insert users
sqlite> .output seed.sql
sqlite> SELECT * FROM users WHERE signup_date >= '2025-02-01';
sqlite> .output stdout

Qualche nota pratica

• .output stdout (o .output senza argomenti) ripristina l'output nel terminale. Se te ne dimentichi, i risultati della query successiva spariscono in silenzio nel file.
• Le esportazioni CSV non conservano i tipi. Nel file tutto diventa testo, e per reimportarlo serve uno schema di destinazione che lo interpreti. Usa .dump se ti interessa fare il giro completo verso un altro database SQLite.
• Le esportazioni grandi vengono scritte in streaming. .output scrive le righe man mano che vengono prodotte, quindi puoi esportare tabelle più grandi della RAM senza problemi.
• Per un backup a caldo di un database in uso, .dump funziona, ma il comando dedicato .backup (che vedremo più avanti) è più veloce e sicuro perché usa l'API di backup online di SQLite.

Prossimo passo: interrogare i dati

Ora hai un quadro completo della scrittura dei dati: INSERT, UPDATE, DELETE, UPSERT, RETURNING, l'importazione da CSV e l'esportazione. Adesso tocca all'altra metà del lavoro con un database: leggere i dati in modo efficiente. L'istruzione SELECT è quella su cui passerai più tempo, ed è il tema della prossima pagina.