sqlite-auto-migration

Vécu projet : EVQuest (13+ migrations, v4 à v13+) Date : Mars 2026 Stack : Python, SQLite

Contexte

App desktop avec BDD locale SQLite. Mises à jour fréquentes du schéma (13+ migrations sur EVQuest). L'utilisateur final installe des mises à jour régulières — la BDD doit évoluer automatiquement.

Problème

L'utilisateur final ne doit pas gérer les migrations manuellement. L'app doit fonctionner que la BDD soit vierge (premier lancement) ou existante (mise à jour). Au démarrage, un script lit la version du schéma et exécute toutes les migrations manquantes séquentiellement.

Solution

SQLite offre un entier persisté dans le fichier .db via PRAGMA user_version. Pas de table de migrations à créer — c'est natif.

import sqlite3

def get_schema_version(conn: sqlite3.Connection) -> int:
    return conn.execute("PRAGMA user_version").fetchone()[0]

def set_schema_version(conn: sqlite3.Connection, version: int):
    conn.execute(f"PRAGMA user_version = {version}")

Structure des migrations

# Chaque migration est une fonction idempotente
MIGRATIONS = {
    4: migrate_v4,   # Ajout table sessions
    5: migrate_v5,   # Ajout colonnes stats
    6: migrate_v6,   # Index sur hands.played_at
    # ...
    13: migrate_v13, # Restructuration profiles
}

def auto_migrate(db_path: str):
    conn = sqlite3.connect(db_path)
    current = get_schema_version(conn)

    for version in sorted(MIGRATIONS.keys()):
        if version > current:
            MIGRATIONS[version](conn)
            set_schema_version(conn, version)
            conn.commit()

    conn.close()

Règles pour les migrations idempotentes

def migrate_v6(conn):
    # ✅ CREATE TABLE IF NOT EXISTS — safe à re-exécuter
    conn.execute("""
        CREATE TABLE IF NOT EXISTS sessions (
            id INTEGER PRIMARY KEY,
            started_at TEXT NOT NULL
        )
    """)

    # ✅ Vérifier avant d'ajouter une colonne
    columns = [r[1] for r in conn.execute("PRAGMA table_info(hands)")]
    if "stack_size" not in columns:
        conn.execute("ALTER TABLE hands ADD COLUMN stack_size REAL")

    # ✅ CREATE INDEX IF NOT EXISTS
    conn.execute("""
        CREATE INDEX IF NOT EXISTS idx_hands_played_at
        ON hands(played_at)
    """)

Intégration au démarrage

# main.py — avant de lancer le serveur API
from poker_coach.storage.auto_migrate import auto_migrate

def startup():
    db_path = get_db_path()  # %APPDATA%/AppName/app.db ou local
    auto_migrate(db_path)
    # Puis lancer le serveur normalement

Vérification

# Inspecter la version du schéma
python -c "
import sqlite3
conn = sqlite3.connect('app.db')
print('Schema version:', conn.execute('PRAGMA user_version').fetchone()[0])
print('Tables:', [r[0] for r in conn.execute(
    \"SELECT name FROM sqlite_master WHERE type='table'\"
).fetchall()])
"

Points d'attention

• Jamais de migration destructive sans backup automatique préalable. Si une migration supprime des données ou renomme des colonnes, copier le fichier .db avant.
• Toujours idempotent — une migration doit pouvoir être re-exécutée sans erreur (crash pendant la migration → relance → ça passe).
• Tester le chemin complet : BDD vide (premier lancement) → dernière version en une passe.
• Tester le chemin incrémental : BDD v4 → appliquer v5 à v13 séquentiellement.

Pourquoi

PRAGMA user_version est préféré à une table de migrations car c'est natif SQLite — pas de table à créer, pas de dépendance. Les fonctions de migration sont idempotentes (IF NOT EXISTS, vérification de colonnes) pour survivre aux crashes mid-migration. Ce pattern est spécifique à SQLite (apps desktop, données locales) — pour PostgreSQL, utiliser Alembic ou Prisma Migrate.

Dernière mise à jour : mars 2026