Writing Database Migration Scripts With an LLM

Migrations are the highest-leverage and highest-risk thing you can ask a model to write. High leverage because they are repetitive, mechanical and tedious. High risk because a bad one destroys data that no rollback restores, and the failure often shows up hours after the deploy that caused it.

The way to get the leverage without the risk is not a better prompt. It is a workflow where the generated script is treated as a proposal that has to survive mechanical verification before it goes anywhere near production data.

Give it both schemas, not a description

The single biggest determinant of output quality is what you put in. A description of the change invites the model to guess at column types, nullability, defaults, index names and foreign keys, and those guesses are individually plausible and collectively wrong.

Dump the actual current schema. Every database will give you this: pg_dump --schema-only, SHOW CREATE TABLE, or whatever your ORM emits. Then write the target schema explicitly, or the specific delta you want, and give both to the model. Now the task is transformation rather than recall.

Include the things that are not in a naive schema dump but will break your migration: existing indexes and their sizes, foreign key constraints pointing at the table, triggers, views that reference the columns, and approximate row counts. A migration that is instant on ten thousand rows can lock a table for twenty minutes on eighty million, and the model has no way to know which you have unless you tell it.

Say which database and which version. SQL dialect differences around concurrent index builds, transactional DDL, generated columns and locking behaviour are exactly where a generic answer is wrong. The same principle applies when generating queries — see generating SQL safely.

Reversibility beats elegance

Ask for the down migration first, or at least demand it in the same response. A migration you cannot reverse is a one-way door, and the moment to discover that it has no reverse is not two minutes after you walked through it.

Some operations are genuinely irreversible: dropping a column loses its data, narrowing a type loses precision, a destructive backfill overwrites the original values. For those, the reverse is not a down migration but a copy. Rename the column instead of dropping it, or write the old values to a shadow table first, and delete the copy in a separate migration weeks later when you are confident.

The expand-and-contract shape is the reason this works in practice. Add the new column, backfill it, deploy code that writes both and reads the new one, then remove the old column in a later migration. Each step is individually reversible even though the overall change is not, and that is the property you actually need. It costs three deploys instead of one, and it is worth it every time.

Models will happily produce a single elegant migration that does all of it at once, because that is what the training data mostly contains. Ask explicitly for the multi-step version and state that each step must be independently deployable.

Dry run everything

Never let a generated migration meet production data without running it somewhere else first. The order of preference is a restored production snapshot, then a staging database with representative volume, then an empty schema — and the last of those catches only syntax errors.

Wrap the run in a transaction and roll it back where the database allows transactional DDL. PostgreSQL does; MySQL largely does not, which means a half-applied migration is a real state you can end up in and should plan for. That difference alone changes how you structure the script.

BEGIN;
-- generated migration here
SELECT count(*) FROM orders;
SELECT count(*) FROM orders WHERE status_v2 IS NULL;
ROLLBACK;

Time it on realistic volume. The question is not whether the statement succeeds but how long it holds a lock, because a five-minute exclusive lock on a hot table is an outage regardless of the eventual result. Check the query plan for the backfill too; a full scan you did not expect is the usual culprit.

Verify with counts and invariants

A migration that runs without error is not a migration that worked. The check is arithmetic, and you should write the assertions before you run anything.

Row counts before and after, per table. Null counts on the new column, which should be zero after a backfill unless nullable by design. Distinct value counts on both sides of a data transformation, which catches a mapping that collapsed two categories into one. Aggregate sums on numeric columns, which catch precision loss from a type change. And spot-check a sample of rows against the old values by hand.

Chunked backfills need their own check. A loop updating a million rows in batches can silently skip rows whose keys change during the run, or process some twice. Verify the terminal condition explicitly rather than trusting that the loop ended, and make the update idempotent so a re-run is safe. Idempotency is the property that turns a scary partial failure into a shrug — the general pattern is in idempotency in automated actions.

Have the model generate the verification queries too, in a separate request from the migration itself. Asking it to check its own script in one pass tends to produce checks that confirm what the script does rather than what you wanted.

What not to delegate

Some categories are not worth the risk regardless of how good the output looks.

Anything that drops or overwrites data as its primary purpose. The reason is asymmetric: if the generation is right you saved fifteen minutes, and if it is wrong you have lost records permanently. Write those by hand and have a second person read them.

Migrations on tables with complex referential integrity, where the order of operations across several foreign keys determines whether the change is possible at all. Models are weak at holding multi-table constraint graphs in mind and will produce an ordering that works in a small test and deadlocks under load.

Anything involving encrypted, hashed or tokenised columns, where a transformation that looks like a straightforward re-encoding silently destroys the ability to decrypt. And anything touching a table where you cannot afford a lock, because the mitigation there is operational knowledge about your traffic pattern rather than SQL.

Conversely, the strong cases are large and boring: adding nullable columns, renaming with a compatibility view, generating a hundred near-identical migrations across sharded schemas, or converting between two well-understood representations. Model choice matters less than the workflow here, but the general shape of what to look for is in choosing a model for migration work.

Data migrations are a different problem

Changing a schema is a bounded operation. Transforming the data inside it is not, because the transformation has to be correct for every row including the ones nobody remembers creating.

Ask for the transformation to be written as a pure function that maps one old row to one new row, tested against real sample rows before it goes anywhere near a bulk update. Pull a few hundred rows from production, including the oldest ones and any with unusual values, and run the function against them offline.

The rows that break it are almost always historical: a format that changed three years ago, a null that predates a constraint, a text field that once held free text and now holds an enum. Those exist in every real database and appear in no schema dump, which is why the sample has to be real data rather than generated fixtures.

Write the result to a new column or a new table rather than updating in place. Then the verification is a comparison rather than an act of faith, and the rollback is dropping something rather than restoring something. The related workflow for generating the surrounding config changes is covered in using an LLM for config generation.

A working checklist

Before running a generated migration anywhere that matters: both schemas were supplied rather than described, the database and version were named, a down migration exists or the change was restructured to be reversible, it ran against a production-shaped snapshot, the lock duration was measured, count and invariant checks were written in advance and passed, and the backfill is idempotent.

If any of those is missing, the time saved by generating the script has been borrowed rather than earned. Migrations are one of the few places where the review is genuinely more important than the writing, and that is true whether a person or a model wrote it.