Handing an AI Assistant Your Database Migrations
Let It Draft the File and Never the Rollout
Yes to the draft, no to the deploy plan. An assistant writes valid migration syntax reliably, and it cannot see how large your table is or what else is querying it right now.
The failures are not syntax failures. They are a rename emitted as a drop plus an add, an index build that blocks writes for the length of the build, and a backfill sitting inside the same transaction as the schema change.
Treat the generated file as a first draft and read it against three questions. What lock does this take, how long does it hold, and what is the reverse. On a large table, split the change across 3 deploys instead of 1.
Why a Migration Is Not Just Another File in the Diff
- ● Runs once against real data
- ● Takes locks other queries wait behind
- ● Rollback is a second migration
Application code runs many times and can be rolled back by shipping the previous build. A migration runs once, against data that already exists, and shipping the previous build does not undo it.
That single property changes the review standard. A function with a bug throws an error for one request, while a migration with a bug can hold a lock that every other query queues behind.
The second difference is invisible in the diff. The same ALTER TABLE statement is instant on a table with 1,000 rows and a multi-minute stall on the same table in production.
Nothing in the file tells the assistant which one it is looking at. It has your schema definition, not your row counts, your traffic pattern, or your slow query log.
Where Assistants Slip on Schema Changes
- ● Renames emitted as drop plus add
- ● Index builds that block writes
- ● Backfills inside the schema step
The first slip is the rename. Autogeneration compares two schema snapshots, and a renamed column is indistinguishable from one column dropped and another added.
Alembic is explicit in its own documentation about what autogenerate cannot detect. Django asks an interactive question instead, which is exactly the prompt an assistant in a non-interactive shell answers badly.
The second slip is index creation. A plain CREATE INDEX on PostgreSQL blocks writes for the whole build, and the CONCURRENTLY form avoids that but cannot run inside a transaction block.
Most frameworks wrap each migration in a transaction by default, so the concurrent form fails unless you turn the wrapper off. Rails needs disable_ddl_transaction and Django needs atomic set to False on the migration class.
The third slip is the backfill. An assistant asked to add a non-null column with a computed value will happily update every row inside the migration.
That works in a test database and holds a write lock for the duration in production. The backfill belongs in its own batched job, running after the column exists and is still nullable.
What Each Migration Tool Gives the Assistant to Work With
The tool decides how much context sits in the file the assistant is editing, which is why the same prompt behaves differently across stacks.
| Tool | How the change is expressed | Rollback story | What the assistant tends to miss |
|---|---|---|---|
| Alembic (Python) | Upgrade and downgrade functions, autogenerated from model metadata | Explicit downgrade function you must write | Renames become drop plus add, server defaults and constraint names are unreliable |
| Django migrations | Python operations generated by makemigrations | Reversible operations auto-reverse, RunPython needs a reverse callable | Interactive rename prompt, atomic wrapper blocking concurrent index builds |
| Rails Active Record | Ruby change method, or an up and down pair | Reversible for known operations, manual for raw SQL | Raw execute calls placed inside change, which cannot auto-reverse |
| Prisma Migrate | Generated SQL file from a schema diff | Forward-only, you write a corrective migration | Data loss warnings that appeared in the CLI and not in the file |
| Flyway | Hand-written versioned SQL files | Undo scripts are a separate concept, not the default | Tidying an already-applied file, which breaks checksum validation |
| Liquibase | Changelog entries in XML, YAML, JSON, or SQL | Rollback declared per changeset, inferred for common types | Missing rollback blocks on custom SQL changesets |
Two patterns fall out of that table. Tools that keep an explicit reverse path give the assistant a place to be wrong visibly, and forward-only tools give it no such place.
Flyway deserves its own warning. Once a versioned file has been applied its checksum is recorded, so a helpful cleanup pass over old migrations breaks validation on the next run.
The Expand and Contract Pattern Is the Real Safety Net
Most migration pain comes from changing a shape while running code still reads it. Expand and contract removes the overlap by never letting both happen in one deploy.
Deploy one expands. Add the new column as nullable, add the new table, add the new index, and change nothing that existing code depends on.
Deploy two moves. Write to both shapes, backfill old rows in batches, then switch reads across once the backfill has caught up.
Deploy three contracts. Drop the old column or table after confirming nothing reads it, which is a log search rather than a guess.
This is the part an assistant will not propose unless you ask. Given a prompt like rename status to state, it produces one migration, because one migration is what the prompt described.
The pattern also makes the reverse trivial for the first two steps. A nullable column that nothing reads can be dropped at any time, which is the working definition of a safe deploy.
A Review Checklist for a Generated Migration
Read the file with these six questions rather than reading it for correctness. Correctness is the part the assistant is already good at.
What lock does each statement take? On PostgreSQL, adding a nullable column is cheap while rewriting a table or validating a constraint is not. Check the lock level for the specific statement, not the general operation.
Is there a table rewrite hiding in it? PostgreSQL 11 removed the rewrite for adding a column with a non-volatile default, so the server version changes the answer. Type changes still rewrite.
Can the constraint be added in two steps? Adding it as NOT VALID and validating afterwards takes a weaker lock for the long part of the work.
Does it touch data? Any UPDATE in a migration is a backfill wearing a schema change costume. Move it out and batch it.
Is the reverse written, and has it run? A downgrade function nobody has executed is documentation, not a rollback.
Does the order match the deploy? A migration that drops a column the running code still selects will fail requests for the window between the two.
The Statements Worth Grepping For First
Before reading a generated migration line by line, scan it for the handful of statements that cause outages. A grep is faster than a careful read and catches the worst cases immediately.
-- Reject on sight in a migration an assistant wrote:
DROP COLUMN -- data gone, and the old code may still select it
DROP TABLE
ALTER COLUMN ... SET NOT NULL -- fails the moment one row is null
ALTER COLUMN ... TYPE -- can rewrite the whole table under a lock
TRUNCATE
-- Usually fine, but check the lock and the index build:
CREATE INDEX -- prefer CONCURRENTLY on a live table
ADD COLUMN ... DEFAULT ... -- safe on modern Postgres, not everywhere
None of these are automatically wrong. They are the statements where “the assistant produced a plausible migration” and “the migration is safe to run at your traffic” come apart, and where expand-and-contract is the answer rather than a single step.
Run the generated file against a restored copy of production before it goes anywhere near the real database. A migration that passes on an empty test schema tells you almost nothing about the one that matters.
Which Migration Work Fits an Assistant and Which Does Not
- ● Draft and boilerplate - yes
- ● Lock strategy and ordering - you
- ● Data backfill - separate job
Boilerplate and syntax: Hand it over. Generating the join table, the foreign keys, the naming-convention-compliant index name, and the matching downgrade is what the tool is genuinely good at.
Translating a change between stacks: Good fit. Turning a described change into Alembic operations or a Liquibase changeset is mechanical work with a verifiable result.
Lock strategy on a large table: Keep it. This needs row counts, traffic shape, and a maintenance window decision that lives outside the repository.
Data backfills: Keep the design at least. Ask for a batched job with an explicit batch size and a resume point, and never accept one inside the migration file.
Destructive steps such as drop column or drop table: Keep it, and require a person to confirm the read path is dead. This is the one category where a wrong file is not fixed by editing code.
Any database you cannot restore quickly: Keep everything. If the restore path is untested, migration review is the only safety net you have.
Two Habits That Keep a Bad Migration Reversible
Set a lock timeout at the top of risky migrations. A statement that fails after 1 second because it could not take a lock is a retry, while a statement that waits is a queue of blocked queries behind it.
Then run the migration against a copy with production-scale data before it runs against production. That is the check that turns an invisible table rewrite into a visible one.
Both habits are cheap, and neither is something the assistant adds on its own. Our notes on reviewing AI-generated code cover the wider review split, and undoing assistant changes safely covers the file-level side of the same problem.
What to Ask Before You Merge the File
The useful question is not whether an assistant can write migrations. It writes them fine. The question is whether your review catches the three things it structurally cannot know.
Ask what lock the statement takes, ask how long it holds at real row counts, and ask what the reverse deploy looks like. If any of the three has no answer in the pull request, the file is not ready no matter who wrote it.
For the wider question of what an assistant should execute rather than draft, the limits on letting an assistant run commands is the companion piece to this one.
FAQ
Can an AI coding assistant write a safe database migration?
Yes for the file, no for the rollout. Generated migrations are usually correct as SQL and blind to locking, table size, and deploy order, which is where outages come from.
Why does my assistant turn a column rename into a drop and add?
Because autogeneration compares two schema snapshots and a rename looks identical to a drop plus an add. Alembic and Prisma both emit destructive SQL unless you edit the file by hand.
What is the catch with CREATE INDEX CONCURRENTLY in a migration?
CREATE INDEX CONCURRENTLY cannot run inside a transaction block, and most frameworks wrap each migration in one. You disable the wrapper explicitly with disable_ddl_transaction in Rails or atomic set to false in Django.
What is the expand and contract pattern?
Expand and contract splits one breaking change into three deploys. Add the new shape, move reads and writes over, then drop the old shape once nothing references it.
How do I stop a migration from taking down a busy table?
Set a short lock_timeout so a blocked statement fails fast instead of queueing behind every other query. A failed migration is recoverable, a lock queue on a busy table is an outage.
Some links may be affiliate links. We may earn a commission at no extra cost to you.
This article was written with AI assistance. It is researched and fact-checked, not based on personal hands-on testing unless explicitly stated.
Comments
Post a Comment