Migrazioni in SQLite: versionare lo schema con user_version

Gli schemi cambiano. Preparati.

La prima versione del tuo schema non è mai l'ultima. Si aggiungono colonne, si dividono tabelle, si ripensano gli indici. La domanda non è se il tuo schema cambierà, ma se la modifica arriverà pulita su ogni portatile, server e dispositivo che ha già una copia più vecchia del database.

A questo servono le migrazioni: una sequenza di piccoli script ordinati che portano un database dalla versione N alla N+1. Eseguili in ordine e qualsiasi database si mette in pari. Salta la disciplina e ti ritrovi con bug del tipo "sul mio computer funziona" che richiedono un intero pomeriggio per essere scovati.

SQLite ti dà esattamente uno strumento integrato per questo: PRAGMA user_version. È un intero a 32 bit che il database conserva per te e che SQLite stesso non tocca mai. Il significato lo decidi tu.

PRAGMA user_version;
PRAGMA user_version = 1;
PRAGMA user_version;

Un database nuovo parte da 0. Impostalo al numero della migrazione che hai appena applicato. Leggilo all'avvio per sapere a che punto sei.

Un ciclo di migrazione minimo

Il modello mentale: ogni migrazione è uno script SQL numerato. La tua app legge il user_version corrente, esegue in ordine ogni script con un numero più alto e aggiorna user_version dopo ciascuno.

Ecco la migrazione 1, che crea lo schema iniziale:

BEGIN;

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

PRAGMA user_version = 1;

COMMIT;

Due cose da notare. Tutto è racchiuso in BEGIN; ... COMMIT;, quindi è atomico: se CREATE TABLE fallisce, user_version non viene incrementato e puoi correggere e riprovare. E PRAGMA user_version = 1 è l'ultima istruzione prima del commit, quindi la versione cambia solo se tutto il resto è andato a buon fine.

Ora supponiamo che tu debba aggiungere una colonna created_at. È la migrazione 2:

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

BEGIN;

ALTER TABLE users ADD COLUMN created_at TEXT
    NOT NULL DEFAULT (datetime('now'));

PRAGMA user_version = 2;

COMMIT;

SELECT sql FROM sqlite_master WHERE name = 'users';

Un database alla versione 0 le esegue entrambe. Uno alla versione 1 esegue solo la seconda. Uno alla versione 2 non esegue nulla. L'ordine è il contratto.

Cosa può e non può fare ALTER TABLE

ALTER TABLE in SQLite è volutamente limitato. Supporta:

• ADD COLUMN: aggiunge in fondo una nuova colonna con un valore predefinito facoltativo.
• DROP COLUMN: rimuove una colonna (dalla 3.35).
• RENAME COLUMN: rinomina una colonna (dalla 3.25).
• RENAME TO: rinomina la tabella stessa.

Nient'altro. Non puoi cambiare il tipo di una colonna, cambiare NOT NULL, modificare un vincolo CHECK o aggiungere una FOREIGN KEY a una colonna esistente.

-- Non supportato:
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);
ALTER TABLE users ADD CONSTRAINT users_email_check CHECK (email LIKE '%@%');

Quando ti serve una modifica che SQLite non sa fare direttamente, la ricetta ufficiale è "ricostruire la tabella". È più prolissa ma del tutto affidabile.

Ricostruire una tabella per le modifiche più grandi

Lo schema è: crea una nuova tabella con la forma che vuoi, copia i dati, elimina la vecchia, rinomina la nuova al suo posto. Tutto dentro una transazione.

BEGIN;

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);
INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada'), ('b@x.com', 'Boris');

-- Ora vogliamo un vincolo CHECK su email. Ricostruiamo:
CREATE TABLE users_new (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE CHECK (email LIKE '%@%'),
    name  TEXT NOT NULL
);

INSERT INTO users_new (id, email, name)
    SELECT id, email, name FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

La documentazione completa di SQLite la chiama ricetta in 12 passi e aggiunge qualche cautela in più per trigger, viste e riferimenti di chiave esterna: vale la pena leggerla una volta prima di farlo su uno schema di produzione. Nella maggior parte dei casi basta la versione in quattro passi vista sopra.

Un avvertimento: se hai chiavi esterne che puntano alla tabella che stai ricostruendo, esegui PRAGMA foreign_keys = OFF prima della migrazione e PRAGMA foreign_keys = ON dopo. Altrimenti DROP TABLE può rompere l'integrità referenziale a metà strada.

Guidare le migrazioni dalla tua applicazione

La contabilità è abbastanza semplice da poterla scrivere da te. In Python con la libreria standard:

-- Concettualmente, la tua app fa questo all'avvio:
--
--   current = SELECT user_version FROM PRAGMA;
--   for i in range(current + 1, len(MIGRATIONS) + 1):
--       esegui MIGRATIONS[i] dentro una transazione
--       PRAGMA user_version = i;
--
-- L'SQL eseguito a ogni passo è come quello delle migrazioni viste sopra.

PRAGMA user_version;

Gli invarianti chiave:

• Le migrazioni sono numerate in modo consecutivo a partire da 1. Nessun buco, nessun riordino.
• Ogni migrazione è racchiusa in una transazione insieme all'incremento PRAGMA user_version = N.
• Una volta che una migrazione è confermata e rilasciata, non la modifichi mai. Le nuove modifiche vanno in una nuova migrazione.

L'ultima regola è quella che i team infrangono più spesso. Se modifichi la migrazione 3 dopo che il database di un collega l'ha già applicata, il suo database resterà per sempre, e in silenzio, disallineato dal tuo.

Tenere un registro storico

user_version ti dice dove si trova un database. Non ti dice quando è stato eseguito ogni passo né cosa ha fatto. Una piccola tabella di servizio risolve il problema:

CREATE TABLE schema_migrations (
    version    INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);

BEGIN;

CREATE TABLE posts (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    body   TEXT NOT NULL
);

INSERT INTO schema_migrations (version, name)
    VALUES (1, 'create_posts');
PRAGMA user_version = 1;

COMMIT;

SELECT * FROM schema_migrations;

Ora hai una riga per migrazione con nome e timestamp: comodo quando devi capire "perché questo database ha una colonna che il codice non si aspetta?"

PRAGMA user_version resta la fonte di verità per il ciclo; la tabella è per le persone.

Rollback: cosa ti danno le transazioni e cosa no

Il DDL di SQLite è transazionale. Se la migrazione 5 inizia a creare una tabella, copiare dati e incrementare user_version, e la copia fallisce a metà, ROLLBACK annulla tutto, compreso il CREATE TABLE. Il database torna esattamente com'era prima di BEGIN.

BEGIN;

CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
INSERT INTO accounts (id, balance) VALUES (1, 100);

-- Immagina che qui qualcosa vada storto:
ROLLBACK;

-- La tabella non esiste:
SELECT name FROM sqlite_master WHERE type='table' AND name='accounts';

Questo copre le migrazioni fallite. Non copre le migrazioni confermate con successo di cui ora ti penti. Per quelle scrivi una migrazione di ritorno separata (down-migration): uno script che annulla la modifica. SQLite non ha un'inversione automatica. Se la migrazione 7 ha aggiunto una colonna, la versione di ritorno la elimina. Se la migrazione 7 ha eliminato una colonna, la versione di ritorno non può recuperare i dati; il massimo che può fare è ricreare la colonna vuota.

In pratica, molti piccoli progetti saltano del tutto le migrazioni di ritorno e si affidano ai backup per "annullare". È una scelta valida, a patto di fare davvero i backup.

Qualche abitudine che ti evita guai

• Una migrazione per ogni modifica logica. Una migrazione che aggiunge tre colonne non correlate è più difficile da rivedere e da annullare di tre migrazioni separate.
• Prova le migrazioni su una copia della produzione. Le modifiche allo schema possono essere lente sulle tabelle grandi; scoprirlo in produzione non è divertente.
• Non modificare mai una migrazione già rilasciata. Aggiungine una nuova.
• Prima fai un backup. Un rapido .backup nella CLI, o una copia del file quando il database è chiuso, è un'assicurazione economica prima di qualsiasi migrazione non banale.
• Attenzione a PRAGMA foreign_keys. Disattivalo durante la ricostruzione delle tabelle, riattivalo dopo.

Per i progetti più grandi, usa uno strumento dedicato: Alembic con SQLAlchemy, golang-migrate, Knex, Flyway. Gestiscono l'ordinamento, le esecuzioni concorrenti e le convenzioni di squadra che altrimenti dovresti reinventare. I principi sono gli stessi del ciclo visto sopra; lo strumento elimina solo il codice ripetitivo.

Prossimo passo: modalità WAL e concorrenza

Di solito le migrazioni vengono eseguite mentre l'applicazione è offline o tiene un lock esclusivo. Il resto del tempo, il tuo database serve letture e scritture da più connessioni contemporaneamente, e la modalità di journal predefinita di SQLite non è sempre la più adatta. La prossima pagina tratta la modalità WAL, cosa cambia e quando passarci.