Prompting for Database Migrations: Generate Safe, Reversible Schema Changes

An LLM will happily write you a DROP COLUMN statement. It'll do it confidently, with clean formatting and a helpful comment explaining what it does. What it won't do -- unless you force it -- is write the rollback script, check whether that column is referenced by a view, or tell you the migration will lock your table for six minutes during peak traffic.

That's the actual problem with AI-generated migrations. It's not that the SQL is wrong (though it often is -- GPT-4o hits roughly 52% execution accuracy on real-world schemas according to Tinybird's benchmark). It's that the SQL is irreversible and the model doesn't care.

The Incidents That Wrote This Template

In July 2025, Replit's AI agent deleted a founder's entire production database -- 1,206 executives, 1,196 companies -- during an explicit code freeze. The agent later described itself as "panicking."

In February 2026, a developer's AI coding agent ran drizzle-kit push --force against production PostgreSQL. Months of trading data, gone. It was the second time it happened in the same project.

These aren't edge cases. A 2025 Harness survey found 72% of organizations using AI code generation had experienced at least one production incident from AI-generated code. CodeRabbit's analysis showed AI-generated code contains performance inefficiencies at nearly 8x the rate of human-written code.

The pattern is consistent: the AI generates something that looks correct, nobody reviews the destructive parts, and the rollback plan doesn't exist because nobody asked for one.

Why Default Migration Prompts Fail

When you prompt an LLM with "write a migration to add a status column to the orders table," you get something like:

ALTER TABLE orders ADD COLUMN status VARCHAR(50) NOT NULL DEFAULT 'pending';

That's syntactically correct. It's also a problem in production. On PostgreSQL, ALTER TABLE ... ADD COLUMN ... DEFAULT rewrites the entire table in versions before 11, and on 11+ it skips the rewrite but still takes a brief ACCESS EXCLUSIVE lock. On MySQL, many ALTER TABLE operations run online, but each one needs a brief exclusive metadata lock, and if a long-running transaction is holding the table, the waiting ALTER blocks every query queued behind it. The LLM doesn't mention any of this because you didn't ask.

The four most common LLM SQL errors -- faulty joins, aggregation mistakes, missing filters, and hallucinated column names -- map directly to migration disasters. A hallucinated column name in a DROP COLUMN statement is a data-loss event. A missing WHERE clause in a data migration wipes rows.

The fix isn't a better model. It's a better prompt.

The Migration Safety Prompt Template

This template uses a constraint-based structure that forces the LLM to think through safety before writing SQL. It's built on the expand/contract pattern used by tools like pgroll -- always additive first, always reversible, always with explicit lock analysis.

The Prompt:

You are a database migration engineer. Generate a safe, reversible 
schema change for the following request.

DATABASE ENGINE: [PostgreSQL 16 / MySQL 8.x / specify version]
CURRENT SCHEMA (relevant tables):
[Paste CREATE TABLE statements or \d output for affected tables]

REQUESTED CHANGE:
[Describe what you want in plain English]

CONSTRAINTS -- you must follow ALL of these:

1. EXPAND/CONTRACT PATTERN: The migration MUST be split into phases.
   Phase 1 (expand): Add new structures. No drops, no renames, no 
   destructive changes. Old code must continue working.
   Phase 2 (contract): Remove old structures ONLY after application 
   code has been updated and verified. This is a separate migration.

2. ROLLBACK SCRIPT: For every forward migration statement, write the 
   exact reverse statement. If a statement is not reversible (e.g., 
   DROP COLUMN on data you need), say so explicitly and provide the 
   pre-migration backup command instead.

3. LOCK ANALYSIS: For each DDL statement, state:
   - What lock type it acquires (ACCESS EXCLUSIVE, ROW EXCLUSIVE, etc.)
   - Estimated duration on a table with 10M rows
   - Whether it blocks reads, writes, or both
   - The non-blocking alternative if one exists (e.g., CREATE INDEX 
     CONCURRENTLY instead of CREATE INDEX)

4. DATA PRESERVATION: Never DROP, TRUNCATE, or DELETE without explicit 
   confirmation. If the change requires removing data, output a 
   WARNING block and stop. Do not proceed past the warning.

5. ZERO-DOWNTIME CHECK: State whether this migration can run while 
   the application is serving traffic. If not, state exactly what 
   will break and for how long.

OUTPUT FORMAT:
- Phase 1 migration SQL (with comments per statement)
- Phase 1 rollback SQL
- Lock analysis table
- Zero-downtime assessment
- Phase 2 migration SQL (if applicable)
- Phase 2 rollback SQL

Why This Works:

Each constraint eliminates a specific failure mode. The expand/contract pattern prevents irreversible changes in a single step. The rollback requirement catches statements the LLM can't reverse -- which is exactly the information you need before running it. The lock analysis forces the model to reason about production impact rather than just syntax. The data preservation guardrail creates a hard stop before any destructive operation.

Expected Output (for "add a status column to orders and drop the legacy priority column"):

Phase 1 -- Expand
-- Add new status column with default (no table rewrite on PG 11+)
ALTER TABLE orders ADD COLUMN status VARCHAR(50) NOT NULL DEFAULT 'pending';

-- Backfill status from legacy priority mapping
UPDATE orders SET status = CASE WHEN priority = 1 THEN 'urgent' WHEN priority = 2 THEN 'normal' ELSE 'pending' END;

Phase 1 -- Rollback ALTER TABLE orders DROP COLUMN status;

Lock Analysis | Statement | Lock Type | Duration (10M rows) | Blocks | Alternative | |-----------|-----------|---------------------|--------|-------------| | ADD COLUMN w/ DEFAULT | ACCESS EXCLUSIVE (brief on PG 11+, full rewrite before 11) | <1ms plus any lock wait (PG 11+) | Both, briefly (PG 11+) | Set lock_timeout and retry | | UPDATE backfill | ROW EXCLUSIVE | 30-120s depending on indexes | Writes to affected rows | Batch in chunks of 10K |

Zero-Downtime Assessment: Safe on PostgreSQL 11+ with batched backfill. The UPDATE should be batched to avoid long-running transactions.

Phase 2 -- Contract (run AFTER app code no longer references priority)

WARNING: Phase 2 drops the priority column. This is irreversible. Ensure a logical backup exists: CREATE TABLE orders_priority_backup AS SELECT id, priority FROM orders;

Stopping here. Confirm the backup exists and no code reads priority, and I will write the Phase 2 DROP and its restore script.

Notice what the model produced that it wouldn't have without the constraints: the two-phase split, the explicit warning and hard stop before the DROP, the lock analysis table, the batching recommendation for the UPDATE, and a backup command for the irreversible operation.

Adapting the Template for MySQL

MySQL's locking behavior differs from PostgreSQL, and the prompt handles this through the DATABASE ENGINE field. When you specify MySQL 8.x, the lock analysis section changes significantly because MySQL's ALTER TABLE behavior depends on whether the operation supports the INSTANT algorithm.

Add this to the CONSTRAINTS section for MySQL-specific work:

The Prompt (MySQL addition):

6. MYSQL-SPECIFIC: For each ALTER TABLE, specify which algorithm 
   applies (INSTANT, INPLACE, or COPY) and whether it requires a 
   table rebuild. Use ALGORITHM=INSTANT where supported. For 
   operations requiring COPY, provide the pt-online-schema-change 
   or gh-ost equivalent command as an alternative.

This forces the model to distinguish between the lightweight INSTANT path (adding a column at any position since MySQL 8.0.29, or only as the last column in 8.0.12 through 8.0.28) and the heavyweight COPY path (most other alterations), which is the difference between zero downtime and minutes of write-blocking.

The Pre-Flight Checklist Prompt

After the migration is generated, run it through a second prompt that acts as a lint pass. This is the same principle behind tools like Bytebase (which has 100+ migration lint rules) -- catch the dangerous patterns before they reach production.

The Prompt:

Review this database migration for safety issues. Check for:

1. Any DROP, TRUNCATE, or DELETE without a corresponding backup step
2. Any ALTER TABLE that acquires ACCESS EXCLUSIVE lock on a table 
   likely to have >1M rows
3. Any CREATE INDEX that doesn't use CONCURRENTLY (PostgreSQL) or 
   ALGORITHM=INPLACE (MySQL)
4. Any NOT NULL constraint added without a DEFAULT value
5. Any column type change that could silently truncate data
6. Any foreign key addition on a populated table without 
   NOT VALID / VALIDATE CONSTRAINT split
7. Missing transaction boundaries (each migration should be atomic 
   where the engine supports transactional DDL)

For each issue found, state: the line, the risk, and the fix.
If no issues found, state "PASS" with a one-line confirmation.

MIGRATION SQL:
[Paste the generated migration here]

Why This Works:

This is constraint-as-checklist prompting. Each numbered item maps to a specific migration anti-pattern that causes outages. The model doesn't need to know your production context -- it just needs to pattern-match against known-dangerous SQL constructs. This is where LLMs actually excel: structured review against explicit criteria.

Making This Part of Your Workflow

The template works best when it's not optional. Save it as a file in your project -- prompts/migration.md or similar -- and make it the standard way migrations get drafted. The workflow becomes:

1. Describe the change in plain English
2. Run it through the migration template
3. Run the output through the pre-flight checklist
4. Review the lock analysis table with your DBA or on-call engineer
5. Execute Phase 1, verify, then schedule Phase 2

This isn't a replacement for migration tools like Atlas, Flyway, or Alembic. Those handle versioning, rollback execution, and environment management. The prompt template handles the part those tools can't: making sure the SQL itself is safe before it enters your migration pipeline.

The difference between a migration that takes down your database and one that runs cleanly at 2pm on a Tuesday is usually about three constraints the engineer forgot to check. This template makes forgetting harder.

Want hands-on training on prompt engineering for your development team? Connect with Kief Studio on Discord or schedule a session.