cd /news/ai-agents/ai-coding-agent-database-migrations-… · home › topics › ai-agents › article
[ARTICLE · art-172286] src=dev.to ↗ pub= topic=ai-agents verified=true sentiment=↑ positive

AI Coding Agent Database Migrations: A Safe Plan for Production Schema Changes

A developer outlined a workflow for letting AI coding agents propose and test database schema migrations without granting them production write access. The approach pairs a written change contract with credential separation, task-scoped disposable databases, and an expand-contract rollout, so agents can generate and verify migrations while production DDL runs only through an approved CI job. The guide argues that additive changes, such as adding a display_name column alongside full_name, preserve rollback room that a direct rename would eliminate.

recommendation

Scope agent database credentials to disposable task databases and require an approved CI job for production DDL; use expand-contract so additive columns precede any rename or drop.

more in playbook: Working with AI coding agents
by read8 min views4 publishedNov 19, 2026

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.

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.

Before asking an agent to edit a model, give it a short contract. The contract removes ambiguity and makes review much faster.


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.

Ask the agent for a plan before it writes SQL. A useful plan answers these questions:

For example, replacing users.full_name with display_name is not a rename command. It is a compatibility change. The plan might be:

display_name. full_name, and writes both fields. 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.

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.

“The migration ran” is not sufficient evidence. Test four properties.

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",
});

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.

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.

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.

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.

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.

The final control is architectural: production migrations run from CI, not from an agent terminal.

- 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.

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.

Before merge, confirm all of the following:

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.

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.

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.

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.

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.

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.

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.

── more in #ai-agents 4 stories · sorted by recency
sponsored brought to you by zahid.host 4,200+ EU-deployed projects
reading about agents? ship yours in a single git push.

Run your AI side-project on zahid.host

EU-based hosting, git-push deploys, automatic HTTPS, no cold starts. Free tier with a custom domain — perfect for shipping the agent you just read about.

$git push zahid main
→ Live at https://your-agent.zahid.host ✓
Get free account → Pricing
from €0/mo · no card required
LIVE [news/ai-coding-agent-data…] indexed:0 read:8min 2026-11-19 · —