Creare un database SQLite: come ottenere un nuovo file .db

Un database SQLite è solo un file

Se hai usato MySQL o Postgres, conosci la routine: esegui CREATE DATABASE myapp; e lasci che il server registri un nuovo database da qualche parte, lontano dagli occhi. SQLite non funziona così. Non c'è un server. Non c'è un'istruzione CREATE DATABASE. Un database è un singolo file normale su disco, di solito con estensione .db, .sqlite o .sqlite3, ma l'estensione è solo una convenzione.

Per crearne uno, punti lo strumento sqlite3 su un nome di file che non esiste ancora:

sqlite3 mydata.db

Tutto qui. Se mydata.db non esiste, SQLite è pronto a crearlo. Se esiste, SQLite lo apre. Stesso comando, in entrambi i casi. Il modello mentale è più vicino ad "aprire un documento" che ad "avviare un server di database".

Il file non compare finché non scrivi qualcosa

Ecco una cosa che confonde molte persone. Esegui il comando qui sopra, digita .quit e guarda nella tua directory. Il file non c'è.

SQLite è pigro. Non si preoccupa di creare il file su disco finché non c'è qualcosa da metterci dentro. Nel momento in cui crei una tabella o fai il commit di dei dati, il file compare:

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    body TEXT
);

Ora mydata.db esiste su disco. Fino a quella prima scrittura, il "database" vive solo nella memoria della connessione. A volte crea confusione, e a volte è utile, perché una sessione interrotta non lascia tracce.

Creare un database dalla CLI

Il flusso completo da una shell nuova:

$ sqlite3 mydata.db
SQLite version 3.45.0
Enter ".help" for usage hints.
sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
sqlite> .databases
main: /home/you/mydata.db r/w
sqlite> .quit

Alcuni comandi utili della CLI, già che ci sei:

• .databases: elenca i database collegati e i loro percorsi.
• .tables: elenca le tabelle del database corrente.
• .schema: mostra le istruzioni CREATE TABLE di tutto.
• .quit: esce.

Se sei già dentro sqlite3 senza nessun file aperto (l'hai avviato senza argomenti), puoi aprire un file con .open:

sqlite> .open mydata.db

Stessa regola: lo crea se manca, lo apre se c'è.

"If Not Exists" è già incluso

Una preoccupazione tipica di chi viene da altri database: e se il file c'è già? Lo sovrascrivo? No. Aprire un file esistente significa solo aprirlo. Non viene sovrascritto nulla. L'equivalente più vicino di CREATE DATABASE IF NOT EXISTS è semplicemente... aprire il file. SQLite gestisce entrambi i casi con lo stesso comando.

Se vuoi proprio ripartire da zero, elimina prima il file:

rm mydata.db
sqlite3 mydata.db

Assicurati però che sia quello che vuoi. Non si può annullare, e non c'è nessuno strumento di amministrazione per recuperarlo.

Creare un database da Python

La maggior parte delle volte non sarai nella CLI: creerai i database dal codice dell'applicazione. Python include un modulo sqlite3 nella libreria standard, senza bisogno di installare nulla:

-- Codice Python equivalente:
-- import sqlite3
-- conn = sqlite3.connect("mydata.db")
-- conn.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT)")
-- conn.commit()
-- conn.close()

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    body TEXT
);

sqlite3.connect("mydata.db") segue la stessa regola della CLI: crea il file se manca, lo apre se c'è. Tutti gli altri binding per i vari linguaggi (better-sqlite3 per Node, il driver database/sql di Go, rusqlite per Rust, eccetera) funzionano allo stesso modo: sotto avvolgono tutti la stessa libreria C.

Una scorciatoia specifica di Python: passare ":memory:" come percorso ti dà un database che vive interamente in RAM e sparisce quando la connessione si chiude. È l'argomento della prossima pagina.

Dove dovrebbe stare il file?

Visto che il database è un file, "dove lo metto?" è una domanda vera. Alcune regole pratiche:

• Per un'app: da qualche parte nella directory dei dati dell'utente o nella directory di lavoro della tua app. Non metterlo accanto all'eseguibile sui sistemi in cui quel percorso non è scrivibile.
• Per un progetto: nella radice del repository o in una cartella data/. Aggiungilo a .gitignore se contiene stato locale: inserire un database binario in Git raramente finisce bene.
• Per i test: usa :memory: o un file temporaneo. Veloce e si ripulisce da solo.

Anche i permessi contano. L'utente che esegue il processo deve avere accesso in lettura e scrittura sia al file sia alla sua directory padre (durante le scritture SQLite crea dei file di lock accanto al database).

Una rapida verifica

Per dimostrare che tutto funziona dall'inizio alla fine:

CREATE TABLE greetings (
    id INTEGER PRIMARY KEY,
    message TEXT NOT NULL
);

INSERT INTO greetings (message) VALUES ('hello'), ('world');

SELECT * FROM greetings;

Dovresti vedere due righe. Se funziona, il file del database esiste, lo schema è al suo interno e i dati si possono interrogare. Questa è tutta la storia del "creare un database" in SQLite: niente server, niente utenti, nessuna tabella dei permessi. Solo un file.

Prossimo passo: database in memoria

La stessa chiamata sqlite3.connect(...) che crea un file, con l'argomento giusto, crea un database che non tocca mai il disco. I database in memoria sono il modo più veloce per eseguire test, prototipare uno schema o tenere dati temporanei. È il prossimo argomento.