How to let an AI agent write database migrations without letting it run them

The short answer

Let an AI agent write migration files, and never let it apply them to a shared database. The agent generates the migration in the repository; a person reviews it in a pull request; CI applies it with a lock timeout and a statement timeout set. The agent’s own credentials point at a local or branch database, never production.

This keeps what agents are good at, turning “add a status column with a default” into correct SQL in seconds, and removes what goes wrong: commands like prisma migrate reset, drizzle-kit push --force or alembic downgrade base run against the wrong database, with nobody reading the plan first.¹²⁸

Write and run are different jobs

Writing a migration is text generation with a clear spec. It’s easy to review, easy to diff, and harmless until applied. Running a migration is an action with effects that may not be reversible, on a database other people depend on.

Most migration tools blur the two. Some commands do both at once:

The safe pattern gives the agent the commands in the “writes a file” column, and keeps the “applies” column for CI or a person.

The workflow

1. The agent works against a local or branch database. Its environment has that connection string and nothing else. A database branch or a local container both work.
2. The agent generates the migration file with the tool’s generate command, and may apply it locally to run tests.
3. The agent opens a pull request with the file. The SQL is in the diff.
4. A person reviews it using the checklist below.
5. CI applies it to staging, then production, with timeouts set.
6. The agent never holds production credentials. Not in .env, not in its shell history, not in a config file it can read.

Step 6 is the one that matters most. Every other step can fail; an agent that can’t reach production can’t run anything against it. It also makes the review honest: when the only path to production is the pull request, the person reviewing it knows their approval is the last check, and reads accordingly. A review that might be bypassed by the agent running the command itself tends to be skimmed.

A review checklist for agent-written migrations

Read the SQL, not the ORM schema diff. For each statement, ask:

1. Does it drop anything? DROP TABLE, DROP COLUMN, TRUNCATE. Is that intended, or is it a rename the generator didn’t recognise?
2. Does it rewrite or lock a big table? Changing a column type, or adding a constraint that validates every row, can lock writes for a long time on a large table.
3. Does it build an index without CONCURRENTLY? A standard index build locks out writes until it finishes; CREATE INDEX CONCURRENTLY doesn’t, but it can’t run inside a transaction block, and a failed concurrent build leaves an invalid index you have to drop and rebuild.⁴
4. Is the data migration separate from the schema change? Backfilling millions of rows in the same transaction as an ALTER holds locks for the whole backfill.
5. Can it be reversed? If not, say so in the pull request, and make sure there’s a backup from just before.
6. Does anything downstream depend on the old shape? Logical replication doesn’t replicate schema or DDL changes, so subscribers need their schema changed separately.³

Agents are often good at this review too, as a second reader. Ask a different session to list every lock and every drop in the migration, and compare its list with yours.

Apply it with timeouts

A migration that waits for a lock blocks every query queued behind it. Set two timeouts in the session that applies migrations:

Code A database command in SQL. The text around it says what it does.

SET lock_timeout = '5s';        -- give up if a lock isn't free within 5 seconds
SET statement_timeout = '15min'; -- give up if a statement runs too long

lock_timeout aborts any statement that waits longer than the limit to acquire a lock, per attempt; the default, 0, never gives up.⁵ statement_timeout aborts a statement that runs longer than the limit, measured from when it reaches the server.⁵ A migration that times out on a lock is a good outcome: it failed fast instead of stalling the application. Retry it at a quieter time.

Keep the agent away from apply commands

If the agent runs in a coding tool with a shell, add a guard on the commands that apply or destroy. In Claude Code that can be an ask rule or a hook; our hook post has a tested one. Also check what your package.json scripts do: npm run db:fresh may be prisma migrate reset under another name.

Two of our own tools help here. deedglass scan reads Claude Code’s settings files, finds Prisma, Drizzle Kit, Alembic, Rails, Knex, TypeORM and Sequelize in your dependencies, and says which spellings of their destructive commands would run without a prompt, without running any command.⁶ deedglass guard shell then asks first, with one sentence naming the reason, when a command would destroy database data.⁷ Neither replaces step 6. They’re for the day someone puts a production URL in .env anyway.

What this workflow won’t catch

• A correct migration with the wrong intent. The SQL does what it says; the product decision was wrong. Review catches some of this, not all.
• Data-dependent failures. A constraint that holds on staging data may fail on production data. Try migrations against a recent copy when you can.
• Application code that assumes the new schema before the migration runs. Deploy order still matters.

For the specific failure modes of the two tools agents use most, see drizzle-kit push –force and Prisma’s migrate reset. The checklist before an agent touches production covers the credentials side, and the Postgres write access guide shows a role setup. To see destructive SQL refused on a real database in your browser, try the playground; to check your own repository, run npx deedglass scan (the grade, explained).

Written with AI assistance and checked against our claims register.