Coding
AI Prompt for Safe SQL Migrations
Write safe database migrations with this AI prompt: zero-downtime strategies, batched backfills, rollback scripts, and pre-flight checks included.
The prompt
Role: You are a database reliability engineer who writes migrations that never cause incidents. Context: Dialect: [DIALECT]. Table size: [TABLE_SIZE]. Downtime tolerance: [DOWNTIME_TOLERANCE: zero / maintenance window]. Change: [MIGRATION_DESCRIPTION] Task: 1) Write the migration script(s), split into safe steps if needed (e.g. add nullable column → backfill in batches → add NOT NULL). 2) For each step, state the lock it takes and why it is safe at [TABLE_SIZE]. 3) Write the complete ROLLBACK script. 4) List pre-flight checks (disk space, replica lag, backup) and a verification query to run after. Format: Numbered steps with the SQL for each, then lock analysis, then rollback, then the checklists. Ask the AI to estimate lock duration for each step at your table size and to suggest the batch size math for backfills. Vague migrations cause incidents; quantified ones get approved quickly. Constraints: Never lock the table in a way that blocks writes beyond [DOWNTIME_TOLERANCE]. Backfills must be batched with progress logging. Every migration gets a rollback — no exceptions.
Variables to fill in
Replace each [variable] in the prompt with your own details:
[DIALECT] [TABLE_SIZE] [DOWNTIME_TOLERANCE] [MIGRATION_DESCRIPTION]
Example input
DIALECT=PostgreSQL, CHANGE=add non-nullable 'status' column to orders table (2M rows) with default 'pending', DOWNTIME=zero-downtime required, ROLLBACK=must be reversible
How to use this prompt
State the downtime tolerance and table size — they determine whether the migration needs batching. Always ask for the rollback; forward-only migrations are a gamble.
Tips for the best results
- Run the migration on a production-sized staging copy first, timed.
- Ask for the batch size math — it should be justified, not a magic number.
- Keep the verification query; run it in your deploy pipeline after migrating.
Frequently asked questions
Why not just ALTER TABLE directly?
It designs the multi-step safe path (add nullable → backfill → add constraint) instead of the naive single statement that locks your table.
Can I run the migration without downtime?
Usually yes with the expand-migrate-contract pattern the prompt uses: add nullable, backfill in batches, then add constraints. State your downtime tolerance upfront.