SQLite da riga di comando: usare la shell sqlite3

Cos'è la CLI

sqlite3 è la shell ufficiale a riga di comando distribuita con SQLite. Apre un file di database, ti dà un prompt e ti permette di eseguire SQL su di esso. Nessun server, nessun login, nessuna configurazione: solo un eseguibile e un file.

La userai per ispezioni veloci, query una tantum, dump dello schema e importazioni. È il modo più rapido per curiosare in un database senza scrivere codice applicativo.

$ sqlite3 mydata.db
SQLite version 3.45.0
Enter ".help" for usage hints.
sqlite>

Se mydata.db non esiste ancora, SQLite lo creerà appena fai la tua prima modifica. Eseguire sqlite3 senza nome di file apre un database in memoria usa e getta.

Eseguire SQL al prompt

Una volta dentro la shell, scrivi SQL e termina ogni istruzione con un punto e virgola. La shell aspetta quel punto e virgola: se premi Invio senza, mostra solo un prompt di continuazione.

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  year INTEGER
);

INSERT INTO books (title, year) VALUES ('Dune', 1965);
INSERT INTO books (title, year) VALUES ('Neuromancer', 1984);

SELECT * FROM books;

Il punto e virgola è il segnale "ho finito, esegui". Se lo dimentichi, la shell resta lì con un prompt ...> ad aspettare. Scrivi ; su una nuova riga per concludere.

I comandi punto non sono SQL

Tutto ciò che inizia con un . è un comando punto: un'istruzione per la shell stessa, non per il motore SQL. Non vogliono il punto e virgola e funzionano solo dentro la shell sqlite3.

sqlite> .tables
books

sqlite> .schema books
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  year INTEGER
);

sqlite> .help

I pochi che userai di continuo:

• .tables: elenca tutte le tabelle del database corrente.
• .schema [table]: mostra l'istruzione CREATE TABLE di una tabella o di tutte.
• .databases: elenca i file di database collegati.
• .mode <format>: cambia il modo in cui vengono mostrati i risultati delle query.
• .headers on: mostra i nomi delle colonne sopra i risultati.
• .read file.sql: esegue un file SQL.
• .exit o .quit: esce dalla shell.

.help stampa l'elenco completo. Sono decine, ma ne userai forse una decina.

Rendere leggibile l'output

L'output predefinito è separato da barre verticali e senza intestazioni: va bene per gli script, è brutto da leggere. Due comandi punto risolvono il problema:

sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM books;
id  title        year
--  -----------  ----
1   Dune         1965
2   Neuromancer  1984

Altre modalità utili:

• .mode box: disegna una tabella con bordi Unicode. Bella per gli screenshot.
• .mode csv: valori separati da virgole, ottima da passare ad altri strumenti.
• .mode json: output JSON, un oggetto per riga.
• .mode line: una colonna per riga, comoda quando le righe sono larghe.

Le impostazioni si azzerano ogni volta che avvii la shell. Per renderle permanenti, mettile in un file ~/.sqliterc e sqlite3 lo caricherà all'avvio.

Importare ed esportare CSV

La CLI supporta i CSV in modo nativo. Passa alla modalità CSV, poi usa .import per caricare un file in una tabella:

sqlite> .mode csv
sqlite> .import authors.csv authors
sqlite> SELECT * FROM authors LIMIT 3;

Se la tabella non esiste, .import ne crea una usando la prima riga come nomi di colonna. Se esiste, la prima riga viene trattata come dati, a meno che tu non passi --skip 1.

Per la direzione opposta, reindirizza l'output su un file:

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

.output filename manda ogni risultato di query successivo in quel file finché non torni indietro con .output stdout. Il meccanismo è lo stesso sia che esporti CSV, JSON o un dump grezzo dello schema.

Eseguire file SQL

Per qualsiasi cosa più lunga di qualche istruzione, metti l'SQL in un file ed eseguilo. Dentro la shell:

sqlite> .read setup.sql

Oppure dal tuo terminale normale, senza nemmeno entrare nella shell:

$ sqlite3 mydata.db < setup.sql
$ sqlite3 mydata.db "SELECT count(*) FROM books;"

La seconda forma esegue una singola query ed esce: perfetta per gli script di shell o per controlli veloci. Combinala con le impostazioni di .mode tramite l'opzione -cmd se ti serve un output formattato:

$ sqlite3 -header -column mydata.db "SELECT * FROM books;"

Ispezionare un database sconosciuto

Quando qualcuno ti passa un file .db e non hai idea di cosa contenga, questa è la routine:

$ sqlite3 mystery.db
sqlite> .tables
sqlite> .schema
sqlite> .indexes
sqlite> SELECT count(*) FROM some_table;

.schema senza argomenti stampa ogni CREATE TABLE, CREATE INDEX e CREATE TRIGGER del file. .indexes elenca gli indici per tabella. Cinque minuti nella shell ti dicono su un database più di quanto faccia di solito qualsiasi README.

Per un'istantanea testuale completa da ricreare altrove, usa .dump:

sqlite> .output backup.sql
sqlite> .dump
sqlite> .output stdout

Scrive uno script SQL che ricrea completamente il database, schema e dati, quando lo ripassi a sqlite3.

Uscire dalla shell

Tre modi per uscire, tutti equivalenti:

sqlite> .exit
sqlite> .quit

Oppure premi Ctrl+D su macOS e Linux (Ctrl+Z seguito da Invio su Windows). Le tue modifiche sono già salvate: SQLite fa il commit di ogni istruzione mentre viene eseguita, a meno che tu non abbia avviato esplicitamente una transazione.

Prossimo passo: strumenti grafici per SQLite

La CLI è veloce e si presta agli script, ma a volte vuoi scorrere le tabelle, ordinare le colonne e modificare le righe in modo visuale. Esistono alcuni ottimi strumenti grafici per SQLite: sono il tema della prossima pagina.