SQLite: quando un database in un singolo file è la scelta giusta

SQLite viene spesso considerato un database «giocattolo», buono per gli esercizi e da sostituire appena il progetto diventa serio. È un'idea sbagliata. SQLite è presente in quasi ogni telefono, in tutti i principali browser e in moltissimi programmi desktop, ed è uno dei software più collaudati in circolazione. Non è adatto a tutto, ma per una gamma di casi molto ampia è la scelta più semplice e più affidabile.

Che cosa lo rende diverso

La maggior parte dei database relazionali funziona come un server: un processo separato a cui i programmi si collegano tramite la rete, con utenti, password e porte. SQLite invece è una libreria che il programma incorpora. Il database è un singolo file sul disco, e il programma lo legge e lo scrive direttamente.

Le conseguenze sono notevoli:

• nessuna installazione né configurazione: non c'è un servizio da avviare o aggiornare;
• nessuna latenza di rete: una query è una chiamata di funzione, non un viaggio di andata e ritorno verso un server;
• backup e trasferimento semplici: il database è un file che si può copiare, con le cautele descritte più avanti;
• SQL completo: transazioni ACID, JOIN, indici, viste, trigger, funzioni finestra, espressioni di tabella comuni.

Con Python, per esempio, è già incluso nella libreria standard:

import sqlite3

with sqlite3.connect("inventario.db") as conn:
    conn.execute("""CREATE TABLE IF NOT EXISTS articoli (
        id INTEGER PRIMARY KEY,
        nome TEXT NOT NULL,
        quantita INTEGER NOT NULL DEFAULT 0
    )""")
    conn.execute("INSERT INTO articoli (nome, quantita) VALUES (?, ?)", ("Cavo HDMI", 12))

Quando SQLite è la scelta giusta

• Applicazioni desktop e mobili che devono conservare dati in locale.
• Strumenti a riga di comando e script che accumulano risultati nel tempo, al posto di file CSV o JSON da rileggere ogni volta.
• Siti e applicazioni web con traffico modesto, dove le scritture sono relativamente poche rispetto alle letture.
• Analisi di dati di dimensioni medie: importare un CSV in SQLite e interrogarlo in SQL è spesso più comodo che lavorarci con un foglio di calcolo.
• Formato di scambio: un file SQLite può contenere più tabelle collegate, con tipi e indici, ed essere aperto da qualunque linguaggio.
• Test automatici, a patto che il codice non usi funzionalità specifiche del database di produzione.

I limiti da conoscere

Un solo scrittore alla volta

SQLite permette molti lettori contemporanei, ma una sola transazione di scrittura alla volta sull'intero database. Le altre attendono. Per un'applicazione con poche scritture brevi non è un problema; per un sistema con molti utenti che scrivono di continuo diventa un collo di bottiglia.

Niente accesso via rete

Il file deve stare su un disco locale. Metterlo su una cartella di rete condivisa a cui accedono più computer è sconsigliato dalla stessa documentazione di SQLite, perché i meccanismi di blocco dei file system di rete non sono sempre affidabili e il rischio è la corruzione dei dati.

Gestione degli utenti assente

Non esistono utenti e permessi: chi può leggere il file può leggere tutto il database. La sicurezza si affida ai permessi del sistema operativo.

Le impostazioni da conoscere subito

Modalità WAL

Nella modalità predefinita, una scrittura blocca anche i lettori. Attivando la modalità write-ahead log i lettori possono continuare a leggere mentre una scrittura è in corso:

PRAGMA journal_mode = WAL;

L'impostazione resta salvata nel file. In questa modalità accanto al database compaiono due file aggiuntivi, con estensione -wal e -shm, che fanno parte del database e non vanno cancellati.

Chiavi esterne

Per ragioni di compatibilità con le versioni storiche, SQLite non applica i vincoli di chiave esterna se non lo si chiede esplicitamente, a ogni nuova connessione:

PRAGMA foreign_keys = ON;

Dimenticarlo significa poter inserire ordini che puntano a clienti inesistenti senza alcun errore.

Tipi flessibili

SQLite usa una tipizzazione dinamica: una colonna dichiarata INTEGER può accettare anche del testo. Dalla versione 3.37 si possono creare tabelle STRICT, che rifiutano i valori del tipo sbagliato, come fanno gli altri database.

Tempo di attesa

Quando una connessione trova il database occupato da una scrittura, per impostazione predefinita può restituire subito un errore. Impostare un tempo di attesa, per esempio con PRAGMA busy_timeout = 5000;, le fa aspettare fino a cinque secondi prima di rinunciare.

Fare il backup correttamente

Copiare il file mentre un programma lo sta modificando può produrre una copia incoerente, soprattutto in modalità WAL. I metodi sicuri sono il comando .backup dello strumento a riga di comando, l'API di backup disponibile in molti linguaggi o l'istruzione VACUUM INTO 'copia.db', che crea una copia compatta e coerente.

In sintesi

• SQLite è una libreria che gestisce un database in un singolo file, senza server.
• È adatto ad applicazioni locali, script, siti con poche scritture, analisi e test.
• Ammette un solo scrittore alla volta e non va usato su cartelle di rete condivise.
• Conviene attivare WAL, foreign_keys e un busy_timeout, e valutare le tabelle STRICT.
• Il backup si fa con .backup o VACUUM INTO, non copiando il file in uso.