Docs SQLite exécutables : Migrations

Q: Comment versionner un schéma SQLite ?

SQLite réserve un entier 32 bits par base, accessible via PRAGMA user_version. Au démarrage, vous lisez sa valeur, vous la comparez au numéro de la dernière migration que connaît votre code, et vous appliquez dans l'ordre celles qui manquent. Pas besoin de table dédiée — même si beaucoup d'applis en ajoutent une pour garder une trace d'audit.

Q: Peut-on annuler une migration SQLite ?

Encadrez chaque migration par BEGIN; ... COMMIT;. Si quoi que ce soit échoue à l'intérieur, ROLLBACK annule l'étape entière — aussi bien les changements de schéma que les données, puisque le DDL de SQLite est transactionnel. En revanche, pour revenir sur une migration déjà commitée, il vous faut un script down écrit à la main : SQLite ne le génère pas tout seul.

Q: Pourquoi ALTER TABLE est-il aussi limité dans SQLite ?

SQLite gère ALTER TABLE ADD COLUMN, RENAME TABLE, RENAME COLUMN et DROP COLUMN, mais pas les modifications arbitraires comme changer le type ou les contraintes d'une colonne. La parade, c'est la fameuse recette en 12 étapes : créer une nouvelle table avec la structure voulue, faire un INSERT INTO newtable SELECT ... FROM oldtable, supprimer l'ancienne, puis renommer la nouvelle.

Q: Vaut-il mieux utiliser un outil de migration ou écrire le sien ?

Pour une petite appli, une boucle maison sur des fichiers .sql numérotés pilotée par PRAGMA user_version tient en une trentaine de lignes et fait très bien le job. Sur des projets plus gros, des outils comme Alembic (Python), golang-migrate (Go) ou Knex (Node) prennent en charge l'ordonnancement, le verrouillage et le travail en équipe — autant de choses que vous finiriez par réimplémenter.

Les schémas évoluent. Anticipez-le.

La première version de votre schéma ne sera jamais la dernière. On ajoute des colonnes, on découpe des tables, on repense les index. La vraie question n'est pas de savoir si votre schéma va changer, mais comment ce changement va atterrir proprement sur chaque poste de dev, chaque serveur et chaque appareil utilisateur qui possède déjà une ancienne copie de la base.

C'est précisément le rôle des migrations SQLite : une suite de petits scripts ordonnés qui font passer la base de la version N à la version N+1. Exécutez-les dans l'ordre et n'importe quelle base se met à jour. Sans cette rigueur, vous finissez avec des bugs du style « ça marche chez moi » qui vous bouffent un après-midi entier.

Pour versionner un schéma SQLite, le moteur vous donne un seul outil intégré : PRAGMA user_version. C'est un entier 32 bits que la base stocke pour vous, et auquel SQLite ne touche jamais. À vous de décider ce qu'il représente.

PRAGMA user_version;
PRAGMA user_version = 1;
PRAGMA user_version;

Une base toute neuve démarre à 0. Réglez ce numéro sur celui de la dernière migration appliquée. Au démarrage de l'application, relisez-le pour savoir où vous en êtes.

Une boucle de migration minimaliste

Le principe est simple : chaque migration est un script SQL numéroté. Votre application lit la valeur actuelle de user_version, exécute dans l'ordre tous les scripts dont le numéro est supérieur, et met à jour user_version après chaque passage.

Voici la migration 1, qui crée le schéma initial :

BEGIN;

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

PRAGMA user_version = 1;

COMMIT;

Deux détails à remarquer. L'ensemble est encapsulé dans BEGIN; ... COMMIT;, donc c'est atomique — si le CREATE TABLE échoue, user_version n'est pas incrémenté et vous pouvez corriger puis relancer. Et PRAGMA user_version = 1 est la dernière instruction avant le commit : la version ne bascule que si tout le reste a réussi.

Imaginons maintenant qu'il faille ajouter une colonne created_at. C'est la migration 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';

Une base en version 0 exécute les deux. Une base en version 1 n'exécute que la seconde. Une base en version 2 n'exécute rien du tout. L'ordre, c'est le contrat.

Ce que `ALTER TABLE` permet (et ce qu'il ne permet pas)

Dans SQLite, ALTER TABLE est volontairement minimaliste. Voici ce qu'il prend en charge :

ADD COLUMN — ajoutez une nouvelle colonne, avec une valeur par défaut optionnelle.
DROP COLUMN — supprimez une colonne (depuis la version 3.35).
RENAME COLUMN — renomme une colonne (depuis la version 3.25).
RENAME TO — renomme la table elle-même.

Et c'est tout. Impossible de modifier le type d'une colonne, de changer une contrainte NOT NULL, de toucher à un CHECK, ni d'ajouter une FOREIGN KEY sur une colonne existante.

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

Quand SQLite ne sait pas faire une modification directement, la recette officielle, c'est de « reconstruire la table ». C'est plus verbeux, mais c'est du solide.

Reconstruire une table pour les gros changements

Le principe : on crée une nouvelle table avec la structure voulue, on y recopie les données, on supprime l'ancienne, puis on renomme la nouvelle pour qu'elle prenne sa place. Le tout dans une seule transaction.

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');

-- Maintenant nous voulons une contrainte CHECK sur email. Reconstruction :
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 doc complète de SQLite appelle ça la « recette en 12 étapes » et ajoute quelques précautions supplémentaires pour les triggers, les vues et les contraintes de clé étrangère — ça vaut le coup d'y jeter un œil avant de toucher à un schéma en production. Pour la majorité des cas, la version en quatre étapes vue plus haut suffit largement.

Petit avertissement : si des clés étrangères pointent vers la table que vous reconstruisez, exécutez PRAGMA foreign_keys = OFF avant la migration, puis PRAGMA foreign_keys = ON une fois terminé. Sinon, le DROP TABLE risque de casser l'intégrité référentielle en cours de route.

Piloter les migrations SQLite depuis votre application

Le suivi des versions est suffisamment simple pour que vous puissiez l'écrire vous-même. Voici comment faire en Python avec la bibliothèque standard :

-- Conceptuellement, votre application fait ceci au démarrage :
--
--   current = SELECT user_version FROM PRAGMA;
--   for i in range(current + 1, len(MIGRATIONS) + 1):
--       exécuter MIGRATIONS[i] dans une transaction
--       PRAGMA user_version = i;
--
-- Le SQL exécuté à chaque étape ressemble aux migrations ci-dessus.

PRAGMA user_version;

Les invariants à respecter :

Les migrations sont numérotées de manière consécutive à partir de 1. Pas de trous, pas de réordonnancement.
Chaque migration est encapsulée dans une transaction, avec l'incrément du PRAGMA user_version = N.
Une fois qu'une migration est committée et livrée, on n'y touche plus jamais. Tout changement ultérieur passe par une nouvelle migration.

C'est cette dernière règle qui saute le plus souvent en équipe. Si vous modifiez la migration 3 après qu'un collègue l'a déjà appliquée sur sa base, sa base restera silencieusement désynchronisée de la vôtre pour toujours.

Garder une trace d'audit

user_version vous dit où en est une base. Il ne vous dit pas quand chaque étape a tourné, ni ce qu'elle a fait. Une petite table de suivi règle le problème :

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;

Vous avez maintenant une ligne par migration avec un nom et un timestamp — bien pratique quand vous devez débugger ce genre de question : « pourquoi cette base contient une colonne que le code n'attend pas ? »

PRAGMA user_version reste la référence pour la boucle ; la table, elle, est là pour les humains.

Rollback : ce que les transactions vous offrent (et ce qu'elles ne couvrent pas)

Le DDL de SQLite est transactionnel. Imaginons que la migration 5 commence à créer une table, copie des données, puis incrémente user_version, et que la copie échoue en cours de route : un ROLLBACK annule tout, y compris le CREATE TABLE. La base se retrouve dans l'état exact qu'elle avait avant le BEGIN.

BEGIN;

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

-- Imaginons qu'un problème survient ici :
ROLLBACK;

-- La table n'existe pas :
SELECT name FROM sqlite_master WHERE type='table' AND name='accounts';

Jusqu'ici, on a couvert les migrations qui échouent. Mais on n'a pas encore parlé des migrations qui passent sans accroc et que vous regrettez ensuite. Pour ces cas-là, il faut écrire une down-migration séparée — un script qui annule le changement. SQLite ne fournit aucun retour arrière automatique. Si la migration 7 a ajouté une colonne, sa version « down » la supprime. Si la migration 7 a supprimé une colonne, la version « down » ne pourra pas récupérer les données ; au mieux, elle recréera la colonne vide.

En pratique, beaucoup de petits projets se passent complètement des down-migrations et s'appuient sur les sauvegardes pour faire office d'« annulation ». C'est un choix tout à fait défendable, à condition de bien faire les sauvegardes.

Quelques bonnes habitudes pour s'épargner des galères

Une migration = un changement logique. Une migration qui ajoute trois colonnes sans rapport entre elles est plus pénible à relire et plus difficile à annuler que trois migrations distinctes.
Testez vos migrations sur une copie de la prod. Les changements de schéma peuvent traîner sur de grosses tables ; s'en rendre compte en production, ce n'est jamais une partie de plaisir.
Ne modifiez jamais une migration déjà livrée. Ajoutez-en une nouvelle à la place.
Sauvegardez avant. Un petit .backup dans la CLI, ou une simple copie du fichier base fermée, c'est une assurance pas chère avant toute migration un peu sérieuse.
Méfiez-vous de PRAGMA foreign_keys. Désactivez-le pendant les reconstructions de table, et réactivez-le ensuite.

Pour les projets plus conséquents, tournez-vous vers un outil dédié — Alembic avec SQLAlchemy, golang-migrate, Knex, Flyway. Ils gèrent l'ordre d'exécution, les exécutions concurrentes et les conventions d'équipe que vous finiriez sinon par réinventer. Les principes restent ceux de la boucle plus haut ; l'outil ne fait que retirer le code répétitif.

La suite : le mode WAL et la concurrence

Les migrations s'exécutent en général pendant que l'application est hors ligne ou pendant qu'elle détient un verrou exclusif. Le reste du temps, votre base sert des lectures et des écritures sur plusieurs connexions à la fois — et le mode de journalisation par défaut de SQLite n'est pas toujours le mieux adapté. La page suivante détaille le mode WAL, ce qu'il change concrètement, et à quel moment passer le cap.

Questions fréquentes

Comment versionner un schéma SQLite ?

SQLite réserve un entier 32 bits par base, accessible via PRAGMA user_version. Au démarrage, vous lisez sa valeur, vous la comparez au numéro de la dernière migration que connaît votre code, et vous appliquez dans l'ordre celles qui manquent. Pas besoin de table dédiée — même si beaucoup d'applis en ajoutent une pour garder une trace d'audit.

Peut-on annuler une migration SQLite ?

Encadrez chaque migration par BEGIN; ... COMMIT;. Si quoi que ce soit échoue à l'intérieur, ROLLBACK annule l'étape entière — aussi bien les changements de schéma que les données, puisque le DDL de SQLite est transactionnel. En revanche, pour revenir sur une migration déjà commitée, il vous faut un script down écrit à la main : SQLite ne le génère pas tout seul.

Pourquoi ALTER TABLE est-il aussi limité dans SQLite ?

SQLite gère ALTER TABLE ADD COLUMN, RENAME TABLE, RENAME COLUMN et DROP COLUMN, mais pas les modifications arbitraires comme changer le type ou les contraintes d'une colonne. La parade, c'est la fameuse recette en 12 étapes : créer une nouvelle table avec la structure voulue, faire un INSERT INTO new_table SELECT ... FROM old_table, supprimer l'ancienne, puis renommer la nouvelle.

Vaut-il mieux utiliser un outil de migration ou écrire le sien ?

Pour une petite appli, une boucle maison sur des fichiers .sql numérotés pilotée par PRAGMA user_version tient en une trentaine de lignes et fait très bien le job. Sur des projets plus gros, des outils comme Alembic (Python), golang-migrate (Go) ou Knex (Node) prennent en charge l'ordonnancement, le verrouillage et le travail en équipe — autant de choses que vous finiriez par réimplémenter.

Concepts liés

Migrations SQLite : versionner le schéma avec user_version