An AI coding agent can generate a migration in seconds. It can also choose the shortest path: rename a live column, rebuild an index at the wrong time, or point a reset command at the wrong database. The SQL may look tidy in a pull request while the operational change is unsafe.
The useful boundary is simple: let the agent propose and prove a migration, but do not let it decide when or where production DDL runs. This guide gives you a repeatable workflow for AI coding agent database migrations: plan the change, test it in an isolated database, use an expand-contract rollout, and apply it only through a constrained deployment path.
That is not distrust of the agent. It is how you preserve the advantage of fast implementation without treating production state as a disposable test fixture.
Why a migration needs a different agent policy
Application code is usually easy to roll back: deploy the previous artifact. A schema migration changes shared state. It can block writes, invalidate an older application version, or make a rollback impossible after data has been transformed.
Agents amplify three ordinary migration risks:
| Risk | What the agent optimizes for | What production needs |
|---|---|---|
| Destructive DDL | A short patch | A reversible sequence |
| Large backfill | One completed task | Bounded load and checkpoints |
| Multiple branches | Each branch passes locally | A single ordered schema history |
| Environment choice | Any available connection string | A deliberate, least-privilege target |
The solution is not a longer prompt saying “be careful.” Make the safe path the only path available. An agent should be able to inspect schema metadata, create a migration file, run a disposable database, and produce a plan. It should not possess a production credential that can apply arbitrary SQL.
Put a migration contract in the repository
Before asking an agent to edit a model, give it a short contract. The contract removes ambiguity and makes review much faster.
# database-change.md
For every schema change:
- Inspect the current schema and migration history first.
- Generate a forward migration and a rollback assessment.
- Never run reset, push --force, drop, truncate, or apply commands against production.
- Test on a task-scoped disposable database.
- Use expand → migrate code → backfill → contract for breaking changes.
- Include lock risk, expected row count, and verification query in the PR.
- Production apply happens only in CI after approval.
This is a policy, not just documentation. Enforce it with credentials and CI. A database user for an agent sandbox can create and drop objects in a task-specific database. The deploy identity can run only the approved migration job. No interactive agent session needs production write access.
Start with a plan, not a migration file
Ask the agent for a plan before it writes SQL. A useful plan answers these questions:
- What is the current schema and application compatibility window?
- Does the operation acquire a lock, rewrite a table, or scan many rows?
- Can old and new application versions run at the same time?
- How is existing data backfilled, throttled, and verified?
- What is the rollback point after each step?
For example, replacing users.full_name with display_name is not a rename command. It is a compatibility change. The plan might be:
- Add nullable
display_name. - Deploy code that reads
display_namewith a fallback tofull_name, and writes both fields. - Backfill old rows in small, restartable batches.
- Verify null count, row count, and application reads.
- Stop writing the old column after the compatibility window.
- Remove
full_namein a separately reviewed contract migration.
The first migration is intentionally boring:
-- 001_add_display_name.sql
ALTER TABLE users ADD COLUMN display_name text;
CREATE INDEX CONCURRENTLY IF NOT EXISTS users_display_name_idx
ON users (display_name);
Your exact syntax depends on the engine. The important part is the sequence. A direct rename can break a worker or web process that has not rolled yet. Additive changes give deployment and rollback room.
Give every agent its own database target
Parallel coding agents should not share a mutable development database. One branch may add a column while another branch assumes it does not exist; a reset or seed can invalidate both results.
Use one of these isolation levels:
| Situation | Good target |
|---|---|
| Unit and schema tests | Local ephemeral container |
| Feature branch with realistic fixtures | Branch database or task-scoped clone |
| Integration test in CI | Fresh database created by the pipeline |
| Production deployment | CI-only target with approval and audit log |
Name the target after the task and set an expiry. For example, app_agent_4821 is safer than a generic app_dev. The connection string should be injected only into that task environment; never place a broad credential in a checked-in agent instruction or shell history.
This pairs naturally with isolated Git worktrees, but the worktree alone is not enough. Filesystem isolation prevents source conflicts. Database isolation prevents state conflicts.
Test the migration as an operational change
“The migration ran” is not sufficient evidence. Test four properties.
1. Schema correctness
Apply migrations from an empty database and from a representative older schema. Then check expected constraints, indexes, and types. Schema-diff tools help, but a small assertion test catches drift too.
const columns = await db.query(`
select column_name, is_nullable
from information_schema.columns
where table_name = 'users'
`);
expect(columns).toContainEqual({
column_name: "display_name",
is_nullable: "YES",
});
2. Application compatibility
Run the previous application version against the expanded schema, then the new version. For an expand-contract change, both must work until the old deployment is gone. Add a test for the fallback read, not only the new happy path.
3. Data correctness
Use a scrubbed production-shaped fixture or a generated dataset with edge cases: nulls, duplicate values, oversized strings, old records, and tenant boundaries. After a backfill, compare counts and invariants. Do not let the same agent edit the protected expected results without explicit review.
4. Operational safety
Measure duration and inspect the query plan where relevant. A statement that is safe on 100 rows can lock a busy table at ten million. Record the migration’s expected lock behavior, timeout, batch size, and stop condition in the pull request.
Make backfills restartable and observable
The dangerous phrase in an agent task is “update all rows.” A one-shot update may run for too long, create replication lag, or overwhelm the primary database.
Prefer an idempotent batch job. It should select a small ordered window, update only eligible rows, emit progress, and safely run again after a failure.
const batchSize = 500;
const rows = await db.query<{ id: string; full_name: string }>(`
select id, full_name
from users
where display_name is null
order by id
limit $1
`, [batchSize]);
for (const row of rows) {
await db.query(
`update users
set display_name = $1
where id = $2 and display_name is null`,
[row.full_name, row.id],
);
}
In production, use a single set-based statement only after you understand its locking and load behavior. For a batch worker, expose a metric such as rows remaining, failed batches, and age of the oldest unprocessed row. The agent can help write this, but a human should decide the rate limit and the acceptable load.
Turn the review into a migration packet
A normal code diff hides important facts. Require a compact migration packet in the pull request:
## Migration packet
- Change: add `display_name`; no destructive DDL in this release.
- Compatibility: old and new app versions can read/write during rollout.
- Lock risk: additive column; index uses engine's online/concurrent mode.
- Backfill: 500 rows per batch, idempotent, resume-safe.
- Validation: null count reaches zero; sampled values match `full_name`.
- Rollback: deploy old application; leave added column in place.
- Contract step: separate PR after compatibility window and backup check.
This makes it harder for a superficially green patch to conceal a risky operation. It also tells an on-call engineer what to watch after deployment.
Gate the only production apply path
The final control is architectural: production migrations run from CI, not from an agent terminal.
# Pseudocode for a deployment workflow
- name: Verify migration plan
run: ./scripts/migrate lint && ./scripts/migrate test
- name: Require database-change approval
uses: org/required-approval@v1
- name: Apply approved migrations
env:
DATABASE_URL: ${{ secrets.CI_MIGRATION_DATABASE_URL }}
run: ./scripts/migrate apply --non-interactive
Keep the secret available only to that protected job. Log migration version, commit SHA, actor, start and finish time, and outcome. Add a preflight backup or restore-point check when your recovery process supports it. A backup that has never been restored is only a hope, so rehearse the recovery path separately.
Approval is not a rubber stamp. Reviewers should ask whether the plan preserves compatibility, whether the backfill can stop, and whether the contract step is being deferred until evidence is available.
A practical prompt for the agent
Use a narrow prompt that asks for evidence rather than autonomy:
Inspect the repository’s schema and migration conventions. Propose a plan before editing. Create only an additive migration unless the task explicitly authorizes a contract step. Use the task database, run schema and compatibility tests, and produce a migration packet with lock risk, backfill plan, verification query, and rollback assessment. Do not run any production command or use production credentials.
The wording sets expectations, but the credential boundary does the real work. If the agent makes a poor inference, its maximum action should be a bad draft in an isolated environment—not an irreversible production event.
Database migration checklist for coding agents
Before merge, confirm all of the following:
- [ ] The agent inspected existing schema and migration ordering.
- [ ] The change is additive, or the contract step has explicit approval.
- [ ] Tests ran against a fresh and an upgrade-path database.
- [ ] Old and new application versions are compatible during rollout.
- [ ] The backfill is idempotent, batched, and observable.
- [ ] Verification queries and rollback conditions are in the migration packet.
- [ ] The agent has no production write credential.
- [ ] CI is the sole production apply path and records an audit event.
Fast agents are most valuable when they reduce repetitive work, not when they erase the checks that make stateful systems recoverable. Keep schema changes small, make compatibility explicit, and reserve the final production action for a system designed to be accountable.
FAQ
Can an AI coding agent safely create database migrations?
Yes, when it creates a reviewed proposal and proves it in an isolated database. Do not give it interactive production credentials or let it choose the deployment target.
What is the safest pattern for an AI-generated breaking schema change?
Use expand-contract: add a compatible structure, deploy code that supports both forms, backfill and verify, then remove the old structure in a later change. This gives you a rollback window.
Should an agent run database migrations in production?
An agent can trigger a protected workflow only if your policy allows it, but the actual apply should use a CI-only identity, an approved commit, and an audit trail. Avoid agent-held credentials that can run arbitrary production SQL.
How do I test a migration written by an AI agent?
Test empty-schema setup, upgrade-path setup, old/new application compatibility, data invariants, and operational behavior such as runtime and lock risk. A green type check is not migration evidence.
Do small applications need expand-contract migrations?
Not every low-risk change needs the full pattern. Use it for renamed or removed fields, large tables, background workers, rolling deployments, or any change where old and new code may coexist. A small app should still keep backups, use an isolated test database, and gate production applies.
What should a migration rollback plan include?
State whether code can roll back, whether data has changed irreversibly, what version remains compatible, the verification query, and the stop condition. Often the safest immediate rollback is reverting application code while leaving an additive schema change in place.
Top comments (1)
Expand and contract plus a gated deploy identity is the right shape. The detail I like most is giving the agent a separate identity for deploys, so it can write the migration but can't run it in production without someone else's sign-off.