SQL migrations are tedious, error-prone, and the one place where an LLM's confident hallucination can silently corrupt your production data. These SQL Migration Prompts are designed to force AI models into a structured, verifiable workflow that catches schema drift before it reaches your database.
Why Generic Prompts Fail Here
The problem isn't that LLMs can't write SQL — it's that they can't see your database. A generic prompt like "write a migration to add a users table" produces code that assumes your naming conventions, ignores your existing constraints, and never checks for idempotency. When you run that migration on a live system, you get ERROR: relation "users" already exists or worse, a dropped column you didn't mean to remove.
SQL migrations require reverse-engineering context. The LLM needs to know your current schema, your migration tool (Alembic, Flyway, Prisma), and your rollback strategy. Without that context, you're getting a guess, not a migration. These templates force the model to ask for and incorporate that context before writing a single line.
Template 1: The Incremental Schema Change
This template handles the 80% case: adding a column, modifying a constraint, or updating an index. It forces the LLM to produce both forward and rollback migrations, which most developers forget to request.
You are a senior database engineer. I need a SQL migration for the following change:
**Change description:** [Describe the change, e.g., "Add a `status` column to the `orders` table with a default value of 'pending'"]
**Current schema (relevant tables only):**
[Paste the output of `\d table_name` or the CREATE TABLE statement]
**Migration tool:** [Alembic / Flyway / Prisma / Raw SQL]
**Database:** [PostgreSQL / MySQL / SQLite]
Produce the migration in three parts:
1. **Forward migration** — the exact SQL to apply the change. Include IF NOT EXISTS / IF EXISTS guards where appropriate.
2. **Rollback migration** — the exact SQL to reverse the change. Must be lossless (no data loss).
3. **Validation query** — a SELECT statement that verifies the migration succeeded (e.g., checks the new column exists and has the right default).
Rules:
- Preserve existing indexes and constraints on any modified table.
- If the change requires a table rewrite (e.g., adding a NOT NULL column without a default), warn me and suggest a safer alternative.
- Output only the SQL and a one-line summary per part. No explanations.
Placeholder breakdown: The "Change description" is your intent; the "Current schema" is the ground truth the LLM needs to avoid conflicts. The "Migration tool" tells it whether to use ALTER TABLE syntax or tool-specific DSL. The "Validation query" is the part most developers skip — it makes the migration verifiable in CI.
Template 2: The Data Backfill Migration
This template is for when you're not just changing structure but transforming existing data. It's a different beast because it requires transactional logic and edge-case handling.
You are a data migration specialist. I need to transform existing data in my database.
**Goal:** [Describe the transformation, e.g., "Split the `full_name` column into `first_name` and `last_name` columns, handling NULLs and single-word names"]
**Current data sample (5 rows):**
[Paste 5 representative rows, including edge cases like NULLs or empty strings]
**Constraints:**
- [e.g., "The table has 10M rows, so avoid row-by-row updates."]
- [e.g., "We cannot lock the table for more than 30 seconds."]
Write a migration that:
1. Creates any new columns needed.
2. Backfills the new columns using a single UPDATE statement with a CASE expression (or a temp table + JOIN if that's faster).
3. Verifies the backfill: write a query that counts rows where the new columns are still NULL or empty.
4. Provides a rollback that restores the original columns from the new ones, if possible.
Additional rules:
- Use transactions. Wrap everything in BEGIN/COMMIT with a ROLLBACK on error.
- If the transformation is not reversible, say so explicitly and explain why.
- Optimize for batch operations, not readability. Use `UPDATE ... FROM` or `MERGE` if supported.
Why this works: The "Current data sample" is critical — it shows the LLM the actual mess it's dealing with. The constraint section prevents it from writing a naive loop that would take hours. The verification step catches silent data corruption, which is the most dangerous failure mode in backfills.
Template 3: The Multi-Environment Drift Repair
This is the edge case: your staging and production schemas have drifted, and you need a migration that reconciles them. This is where generic prompts fail hardest because they assume a single source of truth.
You are a database reliability engineer. I need a migration to repair schema drift between two environments.
**Problem:** [Describe the drift, e.g., "Production has a `created_at` column on `users` that staging doesn't have. Staging has an index on `users.email` that production lacks."]
**Production schema (relevant tables):**
[Paste]
**Staging schema (relevant tables):**
[Paste]
**Desired end state:** [Specify which environment is the source of truth, or describe the target schema]
Write a single migration script that:
1. Detects and logs which columns/indexes/constraints differ between the two environments.
2. Applies only the changes needed to converge on the desired state.
3. Uses conditional DDL (e.g., `DO $$ BEGIN IF NOT EXISTS ... END $$` for PostgreSQL) so it's safe to run multiple times.
4. Fails loudly if it detects a destructive change (e.g., a column with data that would be dropped).
Output format:
- A detection query first (SELECT statements that list the diffs).
- The migration SQL second.
- A post-migration verification query that confirms zero drift remains.
Do not assume either environment is correct — validate against the desired end state.
The key insight: This prompt forces the LLM to write conditional DDL, which is the only safe way to handle drift. The detection query at the start means you can review what it found before applying anything. This is the template that saves you from a 2 AM incident call.
How to Adapt These for Your Own Codebase
The templates are a starting point, not a final answer. Three adjustments make them hit home:
-
Paste real schema, not summaries. The more accurate your
\d tableoutput orCREATE TABLEstatements, the fewer hallucinations. I've found that LLMs are surprisingly good at spotting naming convention mismatches if you give them the actual DDL. -
Add your team's migration rules. If you require every migration to have a ticket number in the comment, add that to the prompt. If you use a specific rollback tool, mention it by name.
-
Include a "dry run" requirement. Add a line asking the LLM to first produce a
SELECTquery that simulates the change on a copy of the data (e.g.,CREATE TEMP TABLE ... AS SELECT ...). This catches syntax errors before you touch the real table.
Do These Prompts Work With Any LLM?
Yes, but with caveats. Claude 3.5 Sonnet and GPT-4 handle the multi-part output format best — they're more likely to follow the "no explanations" rule. Gemini is decent but tends to add commentary. DeepSeek and open-source models like Llama 3 sometimes miss the rollback requirement, so you may need to re-ask for it explicitly.
The bigger variable is context length. These prompts require you to paste real schema, which can eat 2,000-4,000 tokens. If you're using a model with a small context window (like older GPT-3.5), trim the schema to only the tables you're changing. For production use, I'd stick with Claude or GPT-4 — the cost per token is worth the fewer failed migrations.
The single adjustment that improves these prompts the most is adding the sentence: "If any part of this migration is not reversible, stop and explain why before writing any SQL." That one line forces the model to think about failure modes instead of blindly generating code.