Write a database migration script
Create complete, reversible, and secure database migration scripts with pre/post validation and zero downtime strategy.
Paste in your AI
Paste this prompt in ChatGPT, Claude or Gemini and customize the variables in brackets.
Tu es un expert en administration de bases de données et en migrations de données en production. Je dois créer un script de migration pour ma base de données. **Contexte de la migration :** - Base de données : [EX: PostgreSQL 14, MySQL 8, SQLite] - ORM/outil de migration : [EX: Prisma, Knex, Alembic, Flyway, migration SQL brut] - Schéma actuel : [DESCRIPTION_OU_DDL_ACTUEL] - Schéma cible : [DESCRIPTION_DES_CHANGEMENTS] - Volume de données à migrer : [EX: 500k lignes dans la table users] - Contrainte de disponibilité : [EX: zéro downtime requis, maintenance acceptée de 5min] **Type de migration :** [EX: ajout de colonne, renommage de table, changement de type, dénormalisation, migration de données entre tables] Crée les scripts de migration complets : 1. **Script UP (migration)** : le script de migration complet, transactionnel si possible, avec commentaires expliquant chaque étape. 2. **Script DOWN (rollback)** : un script de rollback complet permettant de revenir à l'état précédent. 3. **Validation pre-migration** : requêtes SQL pour vérifier l'état initial et détecter des anomalies avant de lancer la migration. 4. **Validation post-migration** : requêtes pour vérifier que la migration s'est bien déroulée (compte de lignes, contraintes, cohérence). 5. **Stratégie zéro downtime** : si applicable, explique comment déployer cette migration sans interruption de service (expand-contract pattern). 6. **Estimation du temps** : estime la durée de la migration selon le volume de données.
Why this prompt works
<p>This prompt addresses database migration as a five-phase process (pre-validation, migration, post-validation, rollback, monitoring) which corresponds to production change management best practices. Many developers only write the UP script and forget about rollback until they need it.</p><p>The request for pre/post validation scripts is often overlooked but critical: it allows detecting data issues before migration (e.g., duplicates that would prevent adding a UNIQUE constraint) and confirming success afterward.</p><p>The expand-contract pattern mentioned for zero downtime is an advanced but essential technique for high-availability applications: deploy code compatible with both old AND new schema before migration, then remove the old code afterward.</p>
Use Cases
Expected Output
Complete UP and DOWN scripts, pre/post migration validation queries, deployment strategy and execution time estimation.
Learn more
Check the full skill on Prompt Guide to master this technique from A to Z.
View on Prompt GuideGlossary Terms
Similar Prompts
Learn the basics of Git for beginners
Learn Git from scratch with illustrated explanations, concrete examples, a practical workflow and a cheatsheet of essential commands.
Define a Git strategy for a team
Define a comprehensive Git strategy adapted to your team: branching model, conventions, code review and release management.
Write integration tests for an API
Create comprehensive API integration tests with database setup, authentication, CRUD and end-to-end scenarios.
Create an optimized Dockerfile for production
Create an optimized multi-stage Dockerfile for production with maximum security, lightweight image and best practices.