Rédiger des migrations de base de données qui ne casseront pas la production

Vous avez une base de données qui tourne depuis des mois. Les utilisateurs en dépendent. Les tables ont grossi, les requêtes ont été optimisées et le schéma s'est stabilisé dans une forme qui fonctionne. Puis arrive une demande de fonctionnalité qui nécessite une nouvelle colonne, un renommage de table ou une migration de données.

Au moment où vous exécutez ce ALTER TABLE en production, vous faites un pari. Si la migration prend trop de temps, les requêtes s'empilent. Si elle verrouille la table, les utilisateurs voient des erreurs. Si elle échoue à mi-chemin, vous avez besoin d'un moyen de revenir en arrière. Et si vous n'avez pas de plan de rollback, la seule option est de restaurer à partir d'une sauvegarde, ce qui signifie perdre toutes les données saisies depuis la dernière sauvegarde.

C'est pourquoi les migrations de base de données sécurisées ne consistent pas seulement à écrire du SQL correct. Elles consistent à structurer les changements pour qu'ils puissent être revus, testés et annulés sans panique.

Chaque migration a besoin de deux fichiers

Le modèle le plus simple qui sauve les équipes à plusieurs reprises est la paire de migrations up et down.

Voici un exemple concret d'une paire de migrations up et down :

-- 20241101_add_last_login_at.up.sql
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP;

-- 20241101_add_last_login_at.down.sql
ALTER TABLE users DROP COLUMN last_login_at;

Une migration up contient le SQL qui effectue le changement. Une migration down contient le SQL qui l'annule. Chaque changement reçoit les deux fichiers, stockés ensemble avec un identifiant unique pour que l'ordre soit clair.

20241101_add_email_index.sql          -- up
20241101_add_email_index_down.sql     -- down

L'identifiant peut être un timestamp, un numéro de séquence ou un préfixe de date. Ce qui importe, c'est que quiconque regarde le dossier puisse voir l'ordre exact des changements. Lorsqu'une migration s'exécute en production et que quelque chose tourne mal, la migration down vous offre un moyen rapide et prévisible de revenir en arrière.

Sans migration down, votre seule solution de repli est une restauration complète de la base de données. Cela prend du temps, nécessite une coordination et risque de perdre des données récentes. Une migration down s'exécute en quelques secondes.

Quand les migrations down ne suffisent pas

Les migrations down fonctionnent bien pour les changements réversibles : ajouter une colonne, créer un index, insérer des données de référence. Mais certains changements sont difficiles à annuler.

Supprimer une colonne est un exemple courant. Une fois la colonne supprimée et les données effacées, une migration down qui réajoute la colonne ne peut pas ramener les données. Le même problème s'applique au renommage de tables, au changement de type de colonne ou à la suppression de contraintes dont d'autres parties du système dépendent.

Pour ces cas, l'approche sécurisée consiste à diviser le changement en plusieurs petites migrations, chacune réversible par elle-même :

1. Ajouter une nouvelle colonne avec le type souhaité.
2. Remplir les données par lots.
3. Mettre à jour le code de l'application pour utiliser la nouvelle colonne.
4. Supprimer l'ancienne colonne.

Chaque étape a sa propre migration up et down. Si l'étape 3 révèle un problème, vous pouvez revenir proprement sur les étapes 2 et 1. Vous n'arrivez jamais à un point où la seule issue est une restauration.

Écrire des migrations qui peuvent s'exécuter plusieurs fois

Les pipelines échouent. Les chutes de réseau, les timeouts se produisent, et parfois une migration s'exécute à moitié avant que le processus ne plante. Lorsque le pipeline réessaie, la migration s'exécute à nouveau.

Si votre migration suppose que le changement n'a pas encore été appliqué, elle échouera lors de la deuxième exécution. Cet échec bloque tout le pipeline et nécessite une intervention manuelle.

Rendez chaque migration idempotente. Utilisez IF NOT EXISTS lors de la création de tables ou d'index. Utilisez IF EXISTS lors de la suppression d'objets. Vérifiez si une colonne existe déjà avant de la modifier. L'objectif est simple : exécuter deux fois la même migration doit produire le même résultat que l'exécuter une fois.

Éviter les verrous longs sur les grandes tables

Un ALTER TABLE qui change un type de colonne peut verrouiller toute la table pendant plusieurs minutes sur une table contenant des millions de lignes. Pendant ce temps, chaque lecture et écriture sur cette table attend. Les utilisateurs voient des timeouts. Les files d'attente s'allongent.

La solution est d'éviter les modifications de schéma en une seule étape sur les grandes tables. Utilisez plutôt une approche en plusieurs étapes :

• Ajoutez une nouvelle colonne avec le type souhaité.
• Mettez à jour les lignes par lots pour remplir la nouvelle colonne.
• Ajoutez un index si nécessaire.
• Supprimez l'ancienne colonne dans une migration ultérieure.

Chaque étape verrouille brièvement. L'application peut continuer à fonctionner entre les étapes. Ce modèle est plus long à écrire mais beaucoup plus sûr à exécuter.

Garder les valeurs spécifiques à l'environnement hors des fichiers de migration

Un fichier de migration doit fonctionner de la même manière en développement, en staging et en production. Si vous codez en dur un nom de base de données, un mot de passe ou une chaîne de connexion dans le SQL, le fichier devient lié à un seul environnement. Vous ne pouvez pas l'exécuter ailleurs sans le modifier, et modifier un fichier de migration après qu'il a été revu va à l'encontre du but du contrôle de version.

Utilisez des paramètres, des variables d'environnement ou une configuration fournie par l'outil de migration. Le SQL lui-même ne doit contenir que la logique de schéma et de données, pas de détails d'environnement.

Stocker les migrations avec le code de l'application

Il existe deux approches courantes : conserver les fichiers de migration dans le même dépôt que le code de l'application, ou les conserver dans un dépôt séparé. Les deux fonctionnent, mais le choix affecte la façon dont les équipes coordonnent les changements.

Lorsque les migrations vivent dans le même dépôt, chaque pull request qui modifie le schéma inclut également la migration. La revue de code couvre à la fois le changement d'application et le changement de base de données. Cela facilite la détection des incohérences, comme une requête qui référence une colonne qui n'a pas encore été ajoutée.

Lorsque les migrations vivent dans un dépôt séparé, les changements d'application et de base de données peuvent évoluer selon des calendriers différents. Cela est utile lorsque plusieurs services partagent une même base de données, mais nécessite plus de coordination pour maintenir la synchronisation entre le schéma et le code.

Dans les deux cas, l'essentiel est que les fichiers de migration soient versionnés, revus et traçables. Un changement de base de données doit laisser le même type de piste d'audit qu'un changement de code.

Liste de contrôle pratique pour écrire des migrations sécurisées

Avant de fusionner une migration dans le pipeline, passez en revue ces vérifications :

• Chaque migration a-t-elle une migration down correspondante ?
• La migration down peut-elle réellement restaurer l'état précédent sans perte de données ?
• La migration est-elle idempotente ? Peut-elle s'exécuter deux fois sans erreur ?
• La migration va-t-elle verrouiller une grande table pendant plus de quelques secondes ?
• Les valeurs spécifiques à l'environnement sont-elles absentes du fichier SQL ?
• Le fichier de migration est-il stocké dans un dépôt avec le code de l'application ou un dépôt dédié à la base de données ?

L'essentiel à retenir

Les migrations de base de données sécurisées ne consistent pas à éviter le changement. Elles consistent à rendre le changement réversible, testable et revu. Chaque fichier de migration que vous écrivez est un petit contrat : voici ce qui change, et voici comment l'annuler. Lorsque ce contrat est clair, l'équipe peut avancer plus vite car elle sait qu'elle a un moyen de revenir en arrière.