Connettersi a SQLite dalle applicazioni: Python, Node, Go, Java

Una connessione è solo un file aperto

SQLite non ha un server. Non c'è un demone in ascolto su una porta, nessun host da chiamare, nessuna credenziale da negoziare. "Connettersi" significa che il tuo driver apre un file su disco e inizia a leggerne e scriverne le pagine. Tutto il modello mentale è qui.

Ogni linguaggio ha un driver che avvolge la libreria C di SQLite. Le forme cambiano, ma i pezzi sono gli stessi: il percorso del file del database, una chiamata di apertura, un handle su cui eseguire le istruzioni e una chiamata di chiusura quando hai finito.

-- Concettualmente, ogni driver fa questo:
-- 1. Apre o crea il file al percorso indicato.
-- 2. Ottiene un handle.
-- 3. Esegue SQL tramite prepared statement.
-- 4. Chiude l'handle.

Il resto della pagina mostra come appare tutto questo nel codice reale, e le poche impostazioni da configurare prima della tua prima query.

Python: sqlite3 nella libreria standard

Python include sqlite3, senza bisogno di installare nulla. La forma base:

-- Python
import sqlite3

conn = sqlite3.connect("app.db")
conn.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
conn.execute("INSERT INTO notes (body) VALUES (?)", ("first note",))
conn.commit()

for row in conn.execute("SELECT id, body FROM notes"):
    print(row)

conn.close()

Alcune cose da sapere:

• sqlite3.connect("app.db") crea il file se non esiste. Passa ":memory:" per un database che vive solo in RAM.
• sqlite3.connect("file:app.db?mode=ro", uri=True) apre in sola lettura tramite la forma URI.
• Il ? nell'SQL è un placeholder: usa il binding dei parametri, mai la concatenazione di stringhe. Il prossimo capitolo approfondisce il tema.
• conn.commit() è necessario, a meno che tu non usi un context manager (with conn:) che fa il commit in automatico.

Per un'app che resta in esecuzione a lungo, imposta un busy timeout così le scritture concorrenti aspettano invece di dare errore:

-- Python
conn.execute("PRAGMA busy_timeout = 5000")   -- aspetta fino a 5s
conn.execute("PRAGMA journal_mode = WAL")    -- concorrenza migliore

Node.js: better-sqlite3

L'ecosistema Node offre diverse opzioni, ma better-sqlite3 è quella che scelgono quasi tutti i team. È sincrona (sembra sbagliato per Node, ma con SQLite è in realtà più veloce, perché le query rispondono in microsecondi).

-- Node.js
const Database = require("better-sqlite3");
const db = new Database("app.db");

db.exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");

const insert = db.prepare("INSERT INTO notes (body) VALUES (?)");
insert.run("first note");

const rows = db.prepare("SELECT id, body FROM notes").all();
console.log(rows);

db.close();

db.prepare(...) restituisce un oggetto istruzione riutilizzabile. .run() serve per le scritture, .all() restituisce tutte le righe, .get() ne restituisce una. È lo stesso schema della maggior parte dei driver SQL.

Imposta i pragma all'avvio:

-- Node.js
db.pragma("journal_mode = WAL");
db.pragma("busy_timeout = 5000");
db.pragma("foreign_keys = ON");   -- disattivato di default, lo vuoi quasi sempre

foreign_keys = ON merita una nota: SQLite non applica le chiavi esterne a meno che tu non glielo chieda, per ogni connessione. Se te ne dimentichi, le tue clausole REFERENCES sono solo decorative.

Go: database/sql con un driver

Il pacchetto standard database/sql di Go non dipende da un driver specifico. Per SQLite, le scelte comuni sono modernc.org/sqlite (Go puro, senza CGO) e github.com/mattn/go-sqlite3 (con CGO).

-- Go
import (
    "database/sql"
    _ "modernc.org/sqlite"
)

db, err := sql.Open("sqlite", "app.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)")
if err != nil { panic(err) }
defer db.Close()

_, err = db.Exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
_, err = db.Exec("INSERT INTO notes (body) VALUES (?)", "first note")

rows, _ := db.Query("SELECT id, body FROM notes")
defer rows.Close()
for rows.Next() {
    var id int; var body string
    rows.Scan(&id, &body)
    fmt.Println(id, body)
}

La query string dopo il nome del file è il modo in cui questo driver passa i pragma al momento della connessione: il formato cambia da driver a driver, quindi controlla la documentazione di quello che scegli.

sql.Open non apre davvero una connessione; lo fa la prima query. db è un pool di connessioni. Per SQLite, un pool piccolo (o anche db.SetMaxOpenConns(1) per carichi con molte scritture) di solito è la scelta giusta.

Java: JDBC

Il driver standard è org.xerial:sqlite-jdbc. Gli URL JDBC hanno la forma jdbc:sqlite:<path>:

-- Java
import java.sql.*;

try (Connection conn = DriverManager.getConnection("jdbc:sqlite:app.db")) {
    try (Statement st = conn.createStatement()) {
        st.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");
        st.execute("PRAGMA journal_mode = WAL");
        st.execute("PRAGMA busy_timeout = 5000");
    }

    try (PreparedStatement ps = conn.prepareStatement("INSERT INTO notes (body) VALUES (?)")) {
        ps.setString(1, "first note");
        ps.executeUpdate();
    }

    try (PreparedStatement ps = conn.prepareStatement("SELECT id, body FROM notes");
         ResultSet rs = ps.executeQuery()) {
        while (rs.next()) System.out.println(rs.getInt(1) + " " + rs.getString(2));
    }
}

In memoria: jdbc:sqlite::memory:. In sola lettura: aggiungi ?open_mode=1 oppure usa un oggetto SQLiteConfig.

PHP: PDO

Il DSN SQLite di PDO è sqlite:<path>:

-- PHP
$db = new PDO("sqlite:app.db");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");
$db->exec("PRAGMA journal_mode = WAL");
$db->exec("PRAGMA busy_timeout = 5000");

$stmt = $db->prepare("INSERT INTO notes (body) VALUES (?)");
$stmt->execute(["first note"]);

foreach ($db->query("SELECT id, body FROM notes") as $row) {
    echo $row["id"] . " " . $row["body"] . "\n";
}

sqlite::memory: per un database in memoria. Imposta sempre ATTR_ERRMODE sulle eccezioni: gli errori silenziosi sono difficili da individuare.

Stringhe di connessione e percorsi dei file

Nei vari driver vedrai due tipi di "stringa di connessione":

• Percorso semplice: app.db, ./data/app.db, /var/lib/myapp/app.db. I percorsi relativi sono relativi alla directory di lavoro del processo, che in produzione raramente è quello che vuoi. Preferisci i percorsi assoluti.
• Forma URI: file:app.db?mode=rwc&cache=shared. Ti permette di impostare flag come mode=ro (sola lettura), mode=rwc (lettura, scrittura e creazione, il default), cache=shared e nolock=1.

Valori speciali che incontrerai:

• :memory:: un database privato in memoria. Ogni connessione ha il suo.
• file::memory:?cache=shared: un database in memoria che più connessioni dello stesso processo possono condividere.
• "" (stringa vuota): un database temporaneo e privato su disco, eliminato alla chiusura.

JDBC antepone all'URI jdbc:sqlite:. PDO usa sqlite:. I driver Go e il modulo sqlite3 di Python accettano direttamente il percorso o l'URI.

E i pool di connessioni?

SQLite è un database con un solo processo di scrittura alla volta. In ogni momento esattamente una connessione tiene il lock di scrittura; tutte le altre aspettano. Mettere in un pool tanti processi che scrivono non rende le scritture più veloci: ti dà solo più concorrenti per lo stesso lock.

Detto questo, un pool piccolo è utile per:

• Letture concorrenti in modalità WAL, dove chi legge non blocca né gli altri lettori né chi scrive.
• Evitare il blocco in testa alla coda, quando una query lenta ferma tutta l'app.

Valori predefiniti ragionevoli per una web app:

• Modalità WAL attiva.
• Un busy_timeout di qualche secondo, così i conflitti aspettano con educazione invece di dare errore.
• Un pool di 1 connessione di scrittura + N di lettura, o anche una sola connessione condivisa se il traffico è leggero.
• Chiavi esterne attive, su ogni connessione.

-- Applica queste impostazioni a ogni nuova connessione:
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;   -- sicuro con WAL; più veloce di FULL

synchronous = NORMAL è l'abbinamento tipico con WAL: durevole in caso di crash dell'app, un po' meno garantito in caso di crash del sistema operativo, sensibilmente più veloce del FULL predefinito.

Chiudere le connessioni (e perché conta)

Ogni driver ha una chiamata di chiusura: conn.close(), db.Close(), db.close(). Non chiudere lascia aperti dei file descriptor e può far crescere il file WAL.

Nei servizi che restano attivi a lungo, lo schema più comune è una connessione (o un pool) per tutta la vita del processo, non aprire e chiudere a ogni richiesta. Aprire una connessione SQLite costa poco, ma riapplicare i pragma ogni volta è uno spreco ed è facile dimenticarsene.

-- Python: una connessione per processo, riutilizzata tra le richieste
DB = sqlite3.connect("app.db", check_same_thread=False)
DB.execute("PRAGMA journal_mode = WAL")
DB.execute("PRAGMA busy_timeout = 5000")
DB.execute("PRAGMA foreign_keys = ON")

In Python, in particolare, serve check_same_thread=False se userai la connessione da più thread, e ti servirà un lock o un pool per serializzare le chiamate.

Una checklist prima di andare in produzione

Prima di indirizzare traffico reale verso un database SQLite:

• Usa un percorso assoluto per il file del database.
• Attiva la modalità WAL (PRAGMA journal_mode = WAL).
• Imposta un busy_timeout tra 2 e 10 secondi.
• Usa i prepared statement con il binding dei parametri, mai l'interpolazione di stringhe.
• Attiva le chiavi esterne su ogni connessione.
• Assicurati che la directory che contiene il database sia scrivibile dal processo (in modalità WAL SQLite scrive un file -wal e uno -shm accanto al file principale).
• Pensa ai backup prima di averne bisogno: VACUUM INTO e il comando .backup vengono trattati più avanti.

Prossimo passo: le migrazioni

Connettersi è la parte facile. Quella difficile è far evolvere lo schema nel tempo senza modificare a mano i database di produzione. Le migrazioni sono il modo per trasformare ALTER TABLE in un processo ripetibile e sotto controllo di versione: è l'argomento della prossima pagina.