database-migration-helper

name	database-migration-helper
description	Gestion des migrations de bases de données et changements de schéma avec stratégies zero-downtime, rollback et data migration. Se déclenche avec "migration", "database migration", "changer le schéma", "EF migrations", "Flyway", "Liquibase", "ALTER TABLE", "zero downtime migration".

Database Migration Helper

1. Qualifier le changement

Avant tout, caractériser l'opération :

Type	Risque	Stratégie recommandée
Ajout colonne nullable	Faible	Migration directe
Ajout colonne NOT NULL sans default	Élevé	Expand-contract obligatoire
Renommage colonne/table	Élevé	Alias + expand-contract
Suppression colonne/table	Élevé	Phase contract après code nettoyé
Changement de type	Élevé	Colonne intermédiaire + backfill
Ajout index (grande table)	Moyen	Index CONCURRENTLY / online
Data migration (backfill)	Variable	Batches, jamais full-table en une passe

---

2. Choisir la stratégie

Expand-Contract (zero-downtime, recommandé pour prod)

Trois phases déployées séparément :

1. Expand — ajouter la nouvelle colonne/table, sans toucher à l'ancienne.
2. Migrate — double-écriture dans le code, backfill des données existantes.
3. Contract — supprimer l'ancienne colonne/table une fois le code nettoyé déployé.

Migration avec fenêtre de maintenance

Acceptable uniquement si : trafic < 100 rps OU table < 100k lignes OU downtime explicitement accepté par le métier.

Blue-Green

Maintenir deux environnements avec bascule DNS/load-balancer. Adapté aux changements majeurs de schéma impossibles à rendre backward-compatible.

---

3. Écrire la migration selon l'outil

EF Core (C#)

dotnet ef migrations add AddUserEmailIndex --project src/Infrastructure
dotnet ef database update --connection "Server=...;Database=...;"
# Rollback vers migration précédente
dotnet ef database update NomMigrationPrecedente

// Migration générée — toujours vérifier le SQL produit
protected override void Up(MigrationBuilder mb)
{
    mb.AddColumn<string>("email_verified", "users", nullable: true);
    mb.CreateIndex("IX_users_email", "users", "email", unique: true);
}
protected override void Down(MigrationBuilder mb)
{
    mb.DropIndex("IX_users_email", "users");
    mb.DropColumn("email_verified", "users");
}

Flyway (SQL versionné)

db/migration/
  V1__create_users.sql
  V2__add_email_verified.sql
  V20260624_001__backfill_email.sql   # préfixe date pour éviter conflits

flyway -url=jdbc:postgresql://host/db -user=x -password=y migrate
flyway info        # état des migrations
flyway repair      # si checksum corrompu

Liquibase

# changelog-v3.yaml
databaseChangeLog:
  - changeSet:
      id: add-email-verified
      author: k.benazzouz
      changes:
        - addColumn:
            tableName: users
            columns:
              - column:
                  name: email_verified
                  type: boolean
                  defaultValueBoolean: false
      rollback:
        - dropColumn:
            tableName: users
            columnName: email_verified

Alembic (Python)

alembic revision --autogenerate -m "add_email_verified"
alembic upgrade head
alembic downgrade -1   # rollback d'une révision

SQL brut versionné

Nommage : V<YYYYMMDD>_<NNN>__<description>.sql + script rollback/R<YYYYMMDD>_<NNN>__<description>.sql obligatoire.

---

4. Backfill de données existantes (batches)

Ne jamais backfiller une grande table en une seule transaction — risque de lock prolongé et timeout.

-- PostgreSQL / SQL Server : backfill par batch de 10 000 lignes
DO $$
DECLARE
  last_id BIGINT := 0;
  batch   BIGINT := 10000;
BEGIN
  LOOP
    UPDATE users
    SET email_verified = false
    WHERE id > last_id AND id <= last_id + batch
      AND email_verified IS NULL;
    EXIT WHEN NOT FOUND;
    last_id := last_id + batch;
    PERFORM pg_sleep(0.1);  -- respiration entre batches
  END LOOP;
END $$;

-- SQL Server : même principe avec TOP
DECLARE @batch INT = 10000;
WHILE 1=1
BEGIN
  UPDATE TOP (@batch) users
  SET email_verified = 0
  WHERE email_verified IS NULL;
  IF @@ROWCOUNT = 0 BREAK;
  WAITFOR DELAY '00:00:00.100';
END

---

5. Grandes tables — opérations online

PostgreSQL

-- Index sans lock table (peut prendre du temps, c'est voulu)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Contrainte NOT NULL sans réécriture complète (PG 12+)
ALTER TABLE users ALTER COLUMN email SET NOT NULL; -- rapide si CHECK déjà validé
ALTER TABLE users ADD CONSTRAINT chk_email_notnull CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_email_notnull; -- scan en arrière-plan

MySQL

# pt-online-schema-change (Percona Toolkit) — pas de lock table
pt-online-schema-change --alter "ADD COLUMN email_verified TINYINT(1) DEFAULT 0" \
  D=mydb,t=users --execute
# gh-ost (GitHub) — alternative sans triggers
gh-ost --database mydb --table users \
  --alter "ADD COLUMN email_verified TINYINT(1) DEFAULT 0" \
  --execute

---

6. Tester avant production

# Dump anonymisé de prod vers staging
pg_dump prod_db | psql staging_db
# Mesurer la durée réelle
\timing on   -- psql
# Détecter les locks
-- PostgreSQL
SELECT pid, query, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event IS NOT NULL;
-- SQL Server
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;
# Valider le plan post-migration
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = '[e-mail]';

Checklist staging :

• Durée d'exécution mesurée sur volume réaliste
• Aucun lock bloquant > 5 s
• Rollback testé et fonctionnel
• Contraintes et index vérifiés (\d+ tablename en psql, sp_help en SQL Server)
• Application validée fonctionnellement après migration

---

7. Rollback strategy

• EF Core : dotnet ef database update <PreviousMigration>
• Flyway : écrire U2__undo_add_email_verified.sql (Flyway Pro) ou script manuel
• Liquibase : liquibase rollbackCount 1 ou rollbackToDate
• SQL brut : script R_<version>__rollback.sql toujours en binôme avec le script Up
• Snapshot DB avant migration sur prod (RDS automated backup, Azure point-in-time restore)

---

8. Garde-fous et anti-patterns

Ne jamais faire :

• ALTER TABLE bloquant sur table > 500k lignes en heures de pointe.
• Supprimer une colonne/table sans vérifier que TOUT le code ne l'utilise plus (y compris BI, jobs, legacy services).
• Écrire des migrations non idempotentes (IF NOT EXISTS manquant → crash à la re-exécution).
• Modifier une migration déjà appliquée en prod (casse les checksums Flyway/EF).
• Backfill en une seule transaction sur millions de lignes.
• Déployer code et migration dans le même release sans ordre défini.

Toujours faire :

• Ordonner : migration d'abord (backward compatible), code ensuite ; puis nettoyage contract.
• Versionner les migrations avec le code source dans le même repo, même PR.
• Logger la durée et l'issue de chaque migration en prod (table migration_log ou outil dédié).
• Prévoir une alerte monitoring post-migration (latence, erreurs applicatives).