AI Coding Agent Database Migrations, Done Safely

The SQL is not the hard part. Ask any competent model to add a column and it will produce correct syntax on the first try. The hard part is that the same statement is instant on an empty table and holds an exclusive lock for four minutes on a table with fifty million rows, and nothing in your schema file tells the model which one it is looking at. Good AI coding agent database migrations come from supplying the operational facts the agent cannot see, not from better prompting technique.

Give it the four facts it cannot infer

Before asking for a migration, state these in the prompt. Every one of them changes the correct answer:

1. Row counts for the tables involved. Not approximate categories, actual numbers.

2. Your database engine and version. Lock behaviour for the same operation differs meaningfully between engines and between versions of the same engine.

3. Whether the table is written to during deploys, and roughly how often.

4. Whether old and new application code will run simultaneously during the deploy. If you deploy with any kind of rolling release, the answer is yes, and it constrains everything.

That last one is the most commonly omitted and the most consequential. If both versions run at once, a migration and the code that depends on it cannot ship together, which forces the expand-and-contract pattern below. An agent told nothing about your deploy process will assume the simple case, because the simple case is what almost every tutorial in its training data describes.

Constrain the shape of the answer

Left unconstrained, models produce the textbook migration: one file, one transaction, schema change and backfill together. That is right for a small table and wrong for a large one. Add these constraints to the request:

• Separate schema changes from data backfills into different migrations. A backfill inside a schema migration holds locks for the duration of the backfill.

• Backfill in batches with a delay, not in one statement.

• No blocking operations on tables over a threshold you name. In PostgreSQL, adding a nullable column with no default is cheap, while operations requiring a table rewrite are not, and the ALTER TABLE documentation is explicit about which is which.

• Every migration reversible, or explicitly documented as one-way with the reason.

• Name the lock the migration will take and why it is acceptable.

• Set a lock timeout, so a migration that cannot acquire its lock fails fast instead of queueing behind a long transaction and blocking everything behind it.

The lock-naming constraint is the highest-value one. Asking the agent to state the lock it will acquire forces it to reason about the operation rather than pattern-match to a template, and it gives you something specific to check against the explicit locking documentation rather than a diff you have to evaluate from scratch. When the stated lock is wrong, that is usually a signal the whole approach is wrong.

Expand and contract, spelled out

For any change that is not purely additive, ask for the migration in three deploys rather than one. Renaming a column is the clearest example.

-- Deploy 1: expand. New column, nullable, no default. Cheap.
alter table orders add column customer_reference text;

-- Deploy 2: backfill in batches, application writes to both columns.
update orders
   set customer_reference = legacy_ref
 where customer_reference is null
   and id in (select id from orders where customer_reference is null limit 5000);

-- Deploy 3: contract. Only after all code reads the new column.
alter table orders drop column legacy_ref;

An agent will not produce this shape unless you ask for it, because the single-statement rename is correct in every tutorial it has ever seen. Ask for it by name and it will, reliably. The same three-step shape covers changing a column type, splitting one column into two, and adding a not-null constraint to an existing column, which are the other changes that catch people.

Indexes deserve their own rule

Index creation is where a plausible-looking migration causes the most damage, because building an index on a large table blocks writes for the duration by default. PostgreSQL supports building concurrently, which does not block writes but cannot run inside a transaction, takes considerably longer, and can leave an invalid index behind if it fails.

An agent will produce the plain form unless told otherwise, and your migration tool will usually wrap it in a transaction automatically, so the concurrent version fails with an error that reads as a tooling problem. Tell the agent explicitly which form you want and whether your migration runner can disable its transaction wrapper for a single migration.

Reviewing AI coding agent database migrations

Read the generated migration against a short list, in this order:

1. What lock does each statement take, and for how long on the row count you gave?

2. Does it run inside a transaction, and should it? Some index operations must run outside one.

3. Is the down migration real, or a stub that silently loses data?

4. Does it assume the application deploys atomically with the migration?

5. Would it still be correct if it were interrupted halfway and re-run?

That last question catches the most bugs. Migrations get interrupted by deploy timeouts more often than anyone plans for, and a batched backfill that is not idempotent turns one incident into two. Ask for the backfill to be safely re-runnable and check that it is, rather than trusting the claim.

Test it against real volume

A migration tested against a development database with two hundred rows has not been tested. Restore a recent copy of production, or generate synthetic data at the real order of magnitude, and time the migration there. This is the single step that catches the class of problem the agent cannot reason about, and it takes twenty minutes.

While it runs, watch for blocked queries rather than only the elapsed time. A migration that completes in ninety seconds while holding a lock that stalls every write for eighty of them is not a ninety-second migration, it is an eighty-second outage, and the difference is invisible if you only measure duration.

Keep the migration in version control alongside the application change that needs it, and let the agent see both. The general principle of committing your conventions where the agent will read them is covered in writing an AGENTS.md file, and it applies to migration policy as much as to code style. A short document stating your row-count thresholds and deploy model turns every future migration request into a better one automatically.

FAQ

Can I let an agent run migrations automatically?

Generating and reviewing, yes. Executing against production without a human confirming, no. A migration is one of the few operations where the undo is genuinely expensive, which puts it in a different category from ordinary code.

Which database gets the best results?

PostgreSQL and MySQL are the best represented in training data and produce the most reliable output. Less common engines need more explicit context and more careful review.

Should the agent write the down migration too?

Yes, and read it carefully. A down migration that drops a column you backfilled will lose the data with no warning. If a change is genuinely irreversible, say so in a comment rather than shipping a stub.

How do I stop it inventing columns that do not exist?

Give it the actual schema rather than describing it. Most hallucinated column names come from the model filling a gap you left, which is the pattern behind AI writing code that does not work in general.

Is it worth using an agent for migrations at all?

Yes, for the mechanical parts: writing the batched backfill loop, generating the reverse operation, and producing the three-deploy sequence once you have named the pattern. The judgment stays yours, and it should.

Where the agent genuinely helps

It is worth being clear about what you are gaining, because the framing above is mostly cautionary. An agent is very good at the mechanical parts of this work: writing a correct batched backfill loop with sensible bounds, producing the reverse operation without forgetting a constraint, expanding a named pattern into the three separate files your migration tool expects, and generating the seed data you need to test at volume. Those are the parts that are tedious rather than difficult, and they are where most of the time actually goes.

What it cannot do is know that the orders table is your hottest write path, or that last quarter's incident came from a lock on exactly this table. Supply that and the split works well. Withhold it and you are asking a competent engineer to plan a deploy having never seen your system.

Migrations are a good test of whether you are using an agent well, because the syntax is easy and the judgment is hard. If you supply the operational context, the output is genuinely useful. If you do not, you get a textbook answer to a question about your production database. Choosing a tool that can read your whole schema comfortably helps, and AI coding tools covers the differences that matter there.

Migrations are one task among several an agent touches with elevated access. how to set permissions for AI coding agents on a team covers scoping that access properly before it becomes a problem.