A database migration script is a versioned, repeatable change to a database’s schema or data — adding a column, backfilling a value, splitting a table — that’s meant to run safely in production without losing data or taking the application down. AI speeds up writing the migration itself, but the real risk in migrations isn’t syntax, it’s forgetting the operational details: locking behavior, rollback paths, and what happens if the script fails halfway through. A good prompt gets the AI to think about those details explicitly instead of just generating the happy-path SQL.
Why Are Database Migrations Riskier Than Regular Code Changes?
Application code can usually be rolled back by redeploying an older version. A migration that’s already altered live data or a live schema often can’t be undone the same way — dropping a column loses data permanently, and a long-running schema change can lock a table and take an application offline for the duration. That asymmetry is why migrations deserve a more deliberate process than a typical pull request, including a rollback plan written before the migration runs, not after something breaks.
AI Prompts for Writing a Safer Migration
Give the model your schema, your database engine, and the specific change you need, then ask it to think through the operational risk, not just the SQL:
- Drafting the migration: “I need to [add a NOT NULL column / rename a table / split a column] on [database engine], current schema: [paste]. Write the migration, and separately write the down/rollback migration that reverses it.”
- Locking review: “Review this migration for [database engine] and identify whether any statement will take a long-held lock on a large table, and suggest a lower-lock alternative if one exists.”
- Backfill safety: “This migration backfills a column on a table with [N] rows. Rewrite it to run in small batches with a pause between batches, so it doesn’t lock the table or overload replication for an extended period.”
- Failure handling: “If this migration fails after step 2 of 3, what state does the database end up in, and what manual steps would be needed to recover?”
What Should You Always Check Before Running a Generated Migration?
Run it against a staging copy of production-scale data first, not just an empty local database — many locking and performance problems only show up at real table sizes. Check whether the change is backward-compatible with the currently deployed application code, since in a zero-downtime deploy the old code may still be running against the new schema for a few minutes. And confirm the migration is idempotent or at least safely re-runnable, in case it needs to be retried after a partial failure. AI can draft code that accounts for all of this when asked directly, but it won’t volunteer these checks unless you prompt for them — treat the model as a careful junior engineer who needs the requirements spelled out, not as someone who already knows your production constraints.
How Does This Fit Alongside Your Other AI-Assisted Dev Workflows?
Treat a migration script like any other production code: it deserves a test, ideally with realistic test data and fixtures rather than trivial sample rows, and a clear commit message explaining what it changes and why — see our guide on writing clear commit messages and pull requests. If your migrations are part of a larger infrastructure-as-code setup, our piece on Terraform and infrastructure-as-code prompts covers the adjacent infrastructure layer.
Frequently Asked Questions
Is it safe to let AI write database migrations for a production system?
It’s safe as a drafting step, not as a final, unreviewed step. AI is good at producing correct SQL quickly, but a human with knowledge of your production scale and uptime requirements should review every migration before it runs against real data, especially for locking behavior and rollback safety.
What’s the biggest mistake people make with database migrations?
Running a schema change or backfill on a large table as a single long transaction, which can hold a lock and effectively take the application down for the duration. Breaking large backfills into small, resumable batches is one of the most reliable ways to avoid this.
Should every migration have a rollback script?
Ideally yes, though some migrations — like one that drops a column with data you don’t want to keep — genuinely can’t be cleanly reversed. In those cases, the right substitute for a rollback script is a verified backup taken immediately before the migration runs.
For more on migration safety patterns, see the django-safe-migrations documentation, which catalogs common unsafe migration patterns and their fixes. To strengthen the tests around your migration, see our guide to AI prompts for writing unit tests.
