AI-generated database migrations meet real rows and old code

A database migration written by a coding agent has to work with the rows already in production and with the code still running there. Review it for both, and for any step that cannot be undone. The agent writes the migration and the backfill; a person approves anything destructive, and the agent never runs a migration against production.

The database and tool behaviour below comes from each project’s official docs, last verified on 11 October 2026. None of it was tested for this post. This is part 17 of the series.

A review table for AI-written migrations

A migration that applies cleanly to an empty development database tells you little about a production table full of rows. Each check comes from a linked source.

The lock details in this post are for PostgreSQL 18. Other databases have their own lock rules, so check your database’s docs for the same statements.

A migration is code: write a new one, never edit an old one

A migration is a file that changes the schema, and it belongs in version control with the code that needs it. Django’s migrations docs say migration files are “designed to be committed to, and distributed as part of, its codebase”. So they go through the same pull request and review as the code. Part 11 already marks migrations as a high-risk area.

Migration tools keep a record of which migrations have run. Rails’ migrations guide says “editing existing migrations that have been already committed to source control is not a good idea”. If the old version has already run on production, “you should write a new migration that performs the changes you require”.

Flyway checks for edits. Its validate command stores a checksum when a migration runs and fails if “differences in migration names, types or checksums are found”.

A coding agent asked to fix a failing migration can reach for the old file, since the error may point there. Tell it in the task: migrations that have run anywhere outside your machine are read only. A fix goes in a new migration at the end of the list.

Read the SQL, too. An ORM (object-relational mapper, the library that maps tables to classes) generates it from a model change. The SQL is what locks the table. Django’s sqlmigrate “takes migration names and returns the SQL they would run” without running it.

Existing rows decide whether a migration is safe

Existing rows are the part an empty test database never shows. Martin Fowler and Pramod Sadalage cover this in evolutionary database design. Database refactorings “involve three different changes that have to be done together”: the schema, the data in it, and the code that reads it. A migration that only changes the schema has done one of the three.

In PostgreSQL’s ALTER TABLE docs, old rows come up in these places:

• A new column with a non-volatile default stores the default once, in the table’s metadata, so no rewrite is needed. A volatile default such as clock_timestamp() “will cause the entire table and its indexes to be rewritten”.
• SET NOT NULL fails if any row holds a NULL, and PostgreSQL checks by “scanning the entire table”. The rows need a value first.
• A new CHECK constraint or foreign key is normally checked against every existing row when you add it.

The backfill, which fills in values for old rows, is a data change. Rails’ guide says “it is generally not advised to perform data migrations using migration files”. Its reasons include “Data migrations can be hard to rollback safely and predictably” and that they “may lock your tables”. It suggests the maintenance_tasks gem instead.

Running the backfill in small batches is common practice, so no single update holds locks on the whole table.

An example, for a made-up orders table that gets a required region column. It splits the work across two releases:

-- Release 1: add the column. No default, so no table rewrite.
ALTER TABLE orders ADD COLUMN region text;

-- Between releases: a batched job, outside the migration, runs
-- statements like this until no NULLs are left.
UPDATE orders SET region = 'unknown'
WHERE region IS NULL AND id BETWEEN 1 AND 10000;

-- Release 2: enforce it without a long lock.
ALTER TABLE orders ADD CONSTRAINT orders_region_not_null
  CHECK (region IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_region_not_null;

NOT VALID skips the scan of existing rows, while new writes are still checked. VALIDATE CONSTRAINT then scans with a SHARE UPDATE EXCLUSIVE lock, which lets reads and writes carry on. After that, PostgreSQL’s ALTER TABLE docs say SET NOT NULL skips its own scan when “a valid CHECK constraint exists” that proves no NULL can exist.

Old code keeps running while the schema changes

The code already in production keeps running during a migration, and during the rollout after it. Flyway’s undo docs offer an alternative to undo scripts: “maintain backwards compatibility between the DB and all versions of the code currently deployed in production”. Then “the old version of the application is still compatible with the DB, so you can simply roll back the application code”.

Part 15 introduced expand and contract for this. Each phase becomes its own release, and the order matters. An example for a made-up rename of email to email_address:

The evolutionary database design article calls the middle stretch a “transition phase”. For a renamed table, it suggests a view with the old name “that existing applications can use” until they move over.

A coding agent asked to “rename the column” can produce one pull request: the rename, every query updated, all tests green. That change is correct for a single deploy with no old code running anywhere. During a rolling deploy, old and new code run side by side. Ask for the release sequence in the task, and have the agent say which release each change belongs to.

Locks and table rewrites on large tables

In PostgreSQL, “An ACCESS EXCLUSIVE lock is acquired unless explicitly noted” for each ALTER TABLE form (ALTER TABLE). The explicit locking page says that mode “guarantees that the holder is the only transaction accessing the table in any way”. Reads wait too.

The rows come from the ALTER TABLE, CREATE INDEX and explicit locking pages, last verified on 11 October 2026.

A short lock still waits for its turn. The locking docs say a transaction asking for a lock “will wait indefinitely for conflicting locks to be released”. Once acquired, “a lock is normally held until the end of the transaction”.

So a fast ALTER TABLE can sit behind a long-running query. Wrapped in one transaction with a slow backfill, it holds its lock until the backfill finishes.

lock_timeout puts a limit on the wait. It aborts “any statement that waits longer than the specified amount of time while attempting to acquire a lock”, and its default of zero disables it. The docs advise against setting it in postgresql.conf “because it would affect all sessions”, so set it in the migration’s own session:

SET lock_timeout = '5s';

The 5s is an example; pick a value with whoever runs the database, and plan a retry for when it fires.

CREATE INDEX CONCURRENTLY lets writes continue while the index builds, and the CREATE INDEX docs add two catches. A regular CREATE INDEX “can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot”. And a failed build leaves behind an index marked invalid; the docs recommend dropping it and trying again.

Rails and Django run each migration in a transaction on databases that support it, so you turn that off for this one migration. Rails has disable_ddl_transaction! for “queries that you can’t execute inside a transaction”. Django sets atomic = False on the migration class.

A down migration cannot bring back dropped data

A down migration cannot bring back data that the up migration destroyed. Write a down step for DROP COLUMN and it can add the column back. Every row then gets NULL or the default, and the old values are gone.

Rails’ migrations guide covers this case: a migration that “might destroy some data” should raise ActiveRecord::IrreversibleMigration in its down block. Django raises IrreversibleError when you try to reverse an operation that has no reverse, such as a RunPython without a reverse function (Django migrations).

Flyway’s undo migrations, a Teams edition feature, carry the same warning: “you should take care if you have destructive changes (drop, delete, truncate etc) in your deployment”. Undo also assumes “the whole migration succeeded”. On a database without DDL transactions, a migration can fail partway, and the docs say an undo “will not help under such conditions”.

Destructive statements wait for a person

DROP TABLE, TRUNCATE, DROP COLUMN and a DELETE or UPDATE across many rows all lose data that no down step brings back. An agent can write them, and they go in their own migration, in their own pull request. That pull request says what is lost and why nothing still reads it. A person approves it once a recent backup exists and someone has restored from it.

A restore is what proves the backup. The Google SRE book’s data integrity chapter puts it this way: “No one really wants to make backups; what people really want are restores”. Its one lesson to take away is “you only know that you can recover your recent state if you actually do so”. Flyway’s undo docs also pair backward compatibility with “a proper, well tested, backup and restore strategy”.

Production migrations stay out of the agent’s reach

The agent applies and reverses its migration on a local or throwaway database, ideally one with realistic volumes. It never runs one against production. That is a credentials question first: if the agent’s environment holds no production connection string, a prompt cannot change that.

Part 6 covers keeping production credentials out of the agent’s reach. Part 15 covers who authorises the production migration in the release.

Two prompts: plan the migration, review the migration

Both prompts are examples, rely on no tool-specific feature, and keep the agent away from shared databases.

Prompt 1: plan and write a schema change

An example prompt for when a task needs a schema change, before any migration file exists.

Task: <describe the feature or change that needs a schema change>

Before writing any migration, plan it:
1. The schema change, and the SQL it will produce for <your database>.
2. Existing rows: NULLs, defaults, values a new constraint would reject,
   and how many rows the change touches, if you can find out locally.
3. The lock each statement takes and whether it rewrites or scans the
   table. Cite the database docs for each.
4. Whether the code now in production still works after this migration.
   If not, split it into expand and contract releases and number them.
5. Any backfill, as a separate batched job, not in the migration.
6. The down step, and what it cannot restore.
7. Any destructive statement (DROP, TRUNCATE, large DELETE or UPDATE).
   Put each one in its own migration and flag it for human approval.

Then write the migrations as new files. Never edit a migration that has
already run anywhere outside this machine.
Apply and reverse them only on the local development database.
Never connect to or run anything against staging, production or any
shared database. Never print, expose or commit secret values.
Do not commit, push, deploy, run destructive commands or make external
changes unless I explicitly authorise it.
If two reasonable attempts fail, stop and report the failure rather
than repeatedly guessing.

The numbered plan doubles as the pull request description.

Prompt 2: review a migration before it merges

An example prompt for a migration already written, run in a fresh session so the reviewer does not share the writer’s reasoning.

Task: review the migrations in <branch or pull request> for
<your database and version>.

Do not edit files. For each migration, report:
- Is it a new file, or an edit to a migration that may have run?
- The SQL it runs, and the lock and rewrite or scan for each statement,
  with a link to the database docs.
- What happens to existing rows, including NULLs and values a new
  constraint rejects.
- Whether the previous release of the code still works with the new
  schema.
- Data changes mixed into a schema migration.
- Whether the down step really reverses it, and what data it cannot
  restore.
- Every destructive statement, marked "needs human approval".

Mark anything you could not confirm as unverified.
Never connect to staging, production or any shared database.
Never print, expose or commit secret values.
Do not commit, push, deploy, run destructive commands or make external
changes unless I explicitly authorise it.
If two reasonable attempts fail, stop and report the failure rather
than repeatedly guessing.

Check each lock claim against your database’s docs before you act on it.

Quick answers

Can an AI coding agent write database migrations?

An AI coding agent can write database migrations, backfill jobs and rollback notes. Each migration then gets a normal code review, plus checks for existing rows, old code still running, table locks and what a rollback cannot restore.

Why should you never edit a migration that has already run?

A migration tool records which migrations have run, so an edited file is skipped or rejected. Rails does nothing with it on db:migrate, and Flyway’s validate fails on a checksum mismatch. The fix goes in a new migration.

How do you add a NOT NULL column to a large PostgreSQL table?

Adding a NOT NULL column to a large PostgreSQL table works in steps. Add it nullable, backfill in batches, add a CHECK constraint as NOT VALID, then validate it. PostgreSQL skips the full-table scan for SET NOT NULL when a valid CHECK proves no NULL exists.

Can a down migration restore dropped data?

A down migration cannot restore dropped data. It can recreate a dropped column or table, but the old values are gone. Only a backup can bring them back, and only a tested restore shows that the backup works.

What does CREATE INDEX CONCURRENTLY do in PostgreSQL?

CREATE INDEX CONCURRENTLY builds a PostgreSQL index without blocking inserts, updates or deletes. It takes longer, cannot run inside a transaction block, and a failed build leaves an invalid index to drop before trying again.

More on AI coding is in the AI category.