Writing Database Migrations With LLMs in October 2026: Review the Generated SQL, Avoid Table-Locking Changes, and Test the Rollback Before Production

Asking a model to "add a column for the customer's preferred language" or "split the name field into first and last name" gets you a migration file in seconds. It usually looks right. The trouble is that a migration is one of the few pieces of code that runs once, against your real data, often while live traffic is hitting the same tables. A subtle mistake does not show up as a failing test. It shows up as a locked table at peak hours, a backfill that silently writes nulls, or a rollback script that was never run and does not work.

This guide covers a practical workflow for using LLMs to write schema migrations without trusting them blindly. The examples use PostgreSQL because its locking behavior is well documented, but the same habits apply to MySQL, SQL Server, and most migration tools such as Alembic, Flyway, Liquibase, Django migrations, Rails migrations, and Prisma Migrate.

Give the Model the Real Schema, Not a Description of It

The most common source of bad generated migrations is missing context. If you describe the table from memory, the model fills the gaps with guesses: it invents column names, assumes a column is nullable when it is not, or forgets an existing index. Give it the facts instead.

• The current table definition. Dump it from the database, for example with pg_dump --schema-only -t orders, or paste the output of \d+ orders from psql. Your ORM model file is a weaker substitute, because it can drift from what is actually deployed.
• Approximate row counts and table size. A change that is instant on 10,000 rows can take an hour on 400 million. Say so in the prompt: "orders has about 200 million rows and receives writes constantly."
• Database engine and version. Locking rules change between versions. For example, PostgreSQL 11 and later can add a column with a constant default without rewriting the table, while older versions rewrite every row.
• Your migration tool and its conventions. Tell it whether you use Alembic, Rails, Flyway, or raw SQL files, and paste one existing migration from your repo so it copies your style and naming.
• The deployment model. Mention whether old and new application versions run side by side during a deploy. If they do, the schema must work with both at once, which rules out renames and drops in a single step.

A useful prompt reads more like a ticket than a wish: "Here is the current schema for users (pasted below). PostgreSQL 16, about 50 million rows, rolling deploys with old and new app versions overlapping for up to 15 minutes. Write an Alembic migration that adds a non-null preferred_language column defaulting to 'en'. Avoid long locks. Include a working downgrade."

Ask for Expand and Contract, Not One Big Change

Models tend to produce the shortest migration that reaches the end state. For anything beyond adding a nullable column, that is often the dangerous version. The safer pattern, usually called expand and contract, breaks a change into steps that each keep the old and new code working.

Take renaming users.fullname to users.display_name. The one-line version, ALTER TABLE users RENAME COLUMN fullname TO display_name;, is fast, but every running instance of the old app starts failing the moment it executes. The expand and contract version looks like this:

1. Expand: add display_name as a nullable column.
2. Dual write: deploy app code that writes to both columns and reads from the old one.
3. Backfill: copy existing values in small batches.
4. Switch reads: deploy code that reads from display_name.
5. Contract: once nothing references fullname, drop it in a later migration.

Tell the model explicitly that you want this sequence and that each step will ship as its own migration. Then check that no single generated file both adds and removes something that live code depends on.

Photo: Constructions and scaffolding at the Oosterdokseiland, Amsterdam-Centrum by Fons Heijnsbroek, CC0, via Wikimedia Commons

Review Generated SQL for Locking Problems

Most migration incidents are not about wrong data. They are about locks. In PostgreSQL, many ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which blocks reads and writes. Worse, a statement waiting for that lock queues behind long-running queries, and every new query then queues behind it, so a "quick" change can stall the whole table. Read every generated statement with these questions in mind.

Index creation

A plain CREATE INDEX blocks writes to the table until it finishes. On a busy table, ask for CREATE INDEX CONCURRENTLY instead. It takes longer and cannot run inside a transaction block, so most tools need a flag for it. In Alembic you wrap it in op.get_context().autocommit_block(); in Rails you add disable_ddl_transaction! and algorithm: :concurrently. Models frequently forget this detail, and the migration then fails at deploy time with "CREATE INDEX CONCURRENTLY cannot run inside a transaction block." If a concurrent build fails partway, it leaves an invalid index behind that you need to drop and recreate.

Adding NOT NULL constraints

ALTER TABLE ... ALTER COLUMN ... SET NOT NULL scans the whole table while holding a strong lock. A gentler path on PostgreSQL 12 and later is:

ALTER TABLE users
  ADD CONSTRAINT users_preferred_language_not_null
  CHECK (preferred_language IS NOT NULL) NOT VALID;

ALTER TABLE users
  VALIDATE CONSTRAINT users_preferred_language_not_null;

ALTER TABLE users
  ALTER COLUMN preferred_language SET NOT NULL;

ALTER TABLE users
  DROP CONSTRAINT users_preferred_language_not_null;

The NOT VALID step is quick, validation runs with a weaker lock that allows normal reads and writes, and PostgreSQL can then use the validated check constraint to skip the full scan when setting NOT NULL.

Foreign keys

The same trick applies: add the foreign key with NOT VALID, then run VALIDATE CONSTRAINT separately.

Type changes

Changing a column type, for example integer to bigint, generally rewrites the table and its indexes. If the model generates ALTER COLUMN id TYPE bigint on a large table, treat that as a red flag and plan a new column with a backfill instead.

Set a lock timeout

Ask the model to start each migration with a short lock timeout, for example SET lock_timeout = '5s';. If the lock cannot be acquired quickly, the migration fails cleanly and you retry, instead of silently blocking every query on the table while it waits.

Keep Backfills Out of the Schema Migration

When asked to add a column and populate it, a model will often write one statement: UPDATE users SET display_name = fullname;. On a large table that is a single long transaction that touches every row, holds row locks, generates a large burst of write-ahead log, and can create replication lag on replicas.

Ask for the backfill as a separate script or job that works in batches by primary key:

UPDATE users
SET display_name = fullname
WHERE id >= :start AND id < :start + 10000
  AND display_name IS NULL;

Loop over the key range, commit after each batch, and pause briefly between batches if replicas fall behind. The display_name IS NULL condition makes the job safe to restart after a failure, which matters because long backfills do get interrupted. Review the transformation logic itself carefully: if you are splitting names or parsing strings, the model's rule will be wrong for some real rows, so run it against a sample and look at the edge cases before running it on everything.

Make the Rollback Real

Generated down migrations are where models are most careless, partly because nobody runs them until something has already gone wrong. Common problems include a downgrade that drops a column the upgrade did not create, a downgrade that restores a dropped column but not its data, and one that simply contains pass.

Be honest about which changes are reversible:

• Adding a column, index, or table is easy to reverse.
• Dropping a column or table destroys data. The downgrade can recreate the structure, but not the contents. Either keep a backup copy of the data first or mark the migration as irreversible on purpose.
• Data transformations such as merging two columns usually lose information, so the reverse is a guess unless you kept the original values.

Then test it. In CI or on a local copy, run the full cycle: apply the migration, roll it back, and apply it again. In Alembic that is alembic upgrade head, then alembic downgrade -1, then alembic upgrade head. Rails offers bin/rails db:migrate:redo for the same check. If the second upgrade fails, the downgrade did not fully undo the change.

Photo: Some old moving boxes from Sweden by Boivie, CC BY 4.0, via Wikimedia Commons

Test Against Realistic Data Before Production

A migration that passes against an empty test database proves very little. Before running it in production:

1. Run it on a production-sized copy. Use a restored backup or a staging database with similar row counts, scrubbed of personal data as your policies require. Time each statement.
2. Check the query plan for new indexes. Run EXPLAIN on the queries the index was meant to help and confirm it is actually used.
3. Watch for lock waits. While the migration runs on staging, query pg_stat_activity and pg_locks from another session to see what it blocks.
4. Verify the data afterwards. Write simple checks: count rows where the new column is null, compare totals before and after a backfill, and spot-check a few records by hand.
5. Run the app's test suite against the migrated schema, and against the old app version too if deploys overlap.

Use the Model as a Reviewer, Too

LLMs are useful for the checking step as well as the writing step, as long as you treat their review as a second opinion rather than a sign-off. After generating or hand-writing a migration, start a fresh conversation, paste the schema, table sizes, database version, and the migration, and ask specific questions: "Which statements take an ACCESS EXCLUSIVE lock? Which ones rewrite the table? Does the downgrade fully reverse the upgrade? Is anything here unsafe while the previous app version is still running?" Specific questions get far more useful answers than "is this migration OK?"

Pair that with deterministic tooling where you can. Linters such as Squawk for PostgreSQL migrations and strong_migrations for Rails flag known risky operations automatically, and they will not miss the same pattern twice.

A Short Checklist

• Pasted the real current schema, row counts, engine version, and migration tool into the prompt.
• Split risky changes into expand and contract steps that ship separately.
• Indexes on busy tables are built concurrently, outside a transaction.
• Constraints are added as NOT VALID and validated in a separate step.
• A short lock_timeout is set at the top of each migration.
• Backfills run as a batched, restartable job, not a single UPDATE.
• The downgrade was actually run with an up, down, up cycle, and irreversible steps are marked as such.
• The migration was timed on production-sized data and its results were checked.

Used this way, a model saves you the tedious part of writing migrations: remembering syntax, producing boilerplate for your tool, and suggesting the safer multi-step version when you ask for it. The judgment about locks, data loss, and rollout order stays with you, and the testing steps above are what turn a plausible-looking file into one you can run against production with confidence.