Database Migration Testing Before Agent PRs

Database migration testing before an agent opens a pull request means running the migration against a disposable Postgres database, capturing the schema change, checking the data edge cases, and cleaning up the database before review. The proof should show the command, target database, schema diff, bounded output, and cleanup result.

That sounds simple. In practice, coding agents often skip the part a human reviewer needs most: a database-specific proof record.

Unit tests can pass while a migration still fails on Postgres syntax, locks a table longer than expected, drops data by accident, or depends on a local database state the reviewer cannot reproduce. A migration generated by an agent needs the same basic review as human-written schema work, plus one extra boundary: the agent should prove the change somewhere disposable before it asks a human to review it.

The useful workflow is:

1. Create a fresh Postgres sandbox for the task.
2. Apply the repo’s migration command against that sandbox.
3. Capture a before and after schema view.
4. Seed or clone only the state needed to test the risky part.
5. Run validation queries with bounded output.
6. Save the proof in the PR notes.
7. Delete the sandbox or let TTL cleanup catch it.

PGSandbox turns that into an MCP workflow instead of a pile of shell commands. The information-gain point is the PR gate: a migration is not “tested” because the agent ran something. It is tested when the reviewer can see what changed in a real Postgres database, under a task-scoped role, with cleanup accounted for.

When the same seed state is needed across several migration tasks, use a local template deliberately rather than keeping a long-lived test database around. The Postgres template database vs task sandbox comparison explains when a reusable template state should restore into a fresh sandbox with its own role and TTL.

What should database migration testing prove?

Database migration testing should prove four things before review:

1. The migration command runs against the intended Postgres version.
2. The resulting schema is the schema the patch claims to create.
3. Important data cases still survive the change.
4. The validation database did not become a new shared environment.

Postgres-specific details matter here. The official CREATE INDEX docs say CREATE INDEX CONCURRENTLY lets normal operations continue while the index is built, but it takes more work, can leave an invalid index after failure, and cannot run inside a transaction block (https://www.postgresql.org/docs/current/sql-createindex.html). The ALTER TABLE docs also call out a common production-safe pattern: create a unique index concurrently, then attach it as a constraint to reduce long write blocking (https://www.postgresql.org/docs/current/sql-altertable.html).

Those are not abstract migration concerns. They are review questions:

• Did the agent create a large index with the right Postgres form?
• Did it try to run a concurrent operation inside a transaction-wrapped migration tool?
• Did it leave behind invalid schema objects after a failed attempt?
• Did it test the data state that makes a constraint valid or invalid?

Generic “migration succeeded” output is too thin. A good proof needs enough database context to catch the difference between a syntactically valid migration and a migration you would actually merge.

Step 1: create a disposable Postgres target

Start with a database that exists only for the agent task.

The target should not be production, staging, or the shared development database. It should be a database sandbox with its own authority, state, data boundary, time limit, and cleanup path. If migration reliability depends on a specific major version, start with the Local Postgres versioning guide and pin that selector before running the migration command.

With PGSandbox, the first move is to create a sandbox database with a short name hint, owner, labels, and TTL. The PGSandbox tool contract documents create_database as the lifecycle tool that creates an isolated database and login role. The architecture docs describe the resource model: one database, one scoped role, TTL metadata, and cleanup tied to tracked resources.

That separation is the point. The server may need lifecycle authority to create the database, but the migration command should run against the sandbox connection, not a long-lived admin credential. If the agent makes a bad migration, the damage lands in the task database.

Use a short TTL. For a PR-prep task, 60 to 240 minutes is usually enough. If the agent needs more time, it should extend the proof deliberately rather than leave a mystery database behind.

Step 2: use the repo’s real migration command

The migration test should run the command the repo actually expects.

For Prisma, that might be npx prisma migrate deploy in CI-like mode or npx prisma migrate dev in a development-only flow. Prisma’s docs say migrate deploy compares applied migrations with migration history, applies pending migrations, and updates the migrations table (https://www.prisma.io/docs/orm/prisma-migrate/understanding-prisma-migrate/mental-model). The same docs say migrate dev is intended for development with a disposable database, and Prisma uses a shadow database to detect drift and evaluate data-loss risk (https://www.prisma.io/docs/orm/prisma-migrate/understanding-prisma-migrate/shadow-database).

For Rails, Django, Ecto, Flyway, Atlas, Knex, Drizzle, or a custom SQL runner, the same rule holds: do not invent a one-off command just because the agent can run SQL. Use the repository’s migration path so the proof exercises the code that will run later.

PGSandbox’s repo workflow tools are built for this. prepare_for_repo writes a secret-free .pgsandbox/project.json with explicit command arrays. validate_schema_change runs an explicit or configured schema-change command against a fresh or selected sandbox, captures a before schema digest, captures an after digest, and returns a compact schema diff with bounded command output.

The command should be an argv array, not a shell string. That keeps the proof boring in the best way: the agent cannot smuggle extra shell behavior into the validation command, and the reviewer can see the exact program and arguments.

Step 3: capture the before and after schema

A migration proof without a schema diff is mostly a log.

Before the command runs, capture the current schema shape. After it runs, capture the new shape. The diff does not need to dump every catalog row into the PR. It needs to show the reviewer the important objects:

• Added, removed, or changed tables.
• Added, removed, or changed columns.
• New indexes and constraints.
• Extension changes.
• Views and materialized views if the repo uses them.
• Any failed command output or SQLSTATE details.

PGSandbox’s schema workflow gives agents a compact result instead of asking them to paste pages of \d output. The MCP tool docs document validate_schema_change, schema snapshots, and schema diff tools for before and after migration review.

That diff is the proof artifact the PR needs. A human reviewer can compare it against the migration file and ask better questions:

• Did the agent create an index but forget the constraint?
• Did it drop a column when it meant to rename it?
• Did it add a NOT NULL constraint without proving old rows satisfy it?
• Did the migration touch more objects than the task required?

If the schema diff surprises the agent, the agent should fix the migration before opening the PR.

For a deeper version of this review artifact, use the Postgres schema snapshots for agent migration reviews workflow. It focuses on named before/after checkpoints, compact object diffs, invalid-index checks, bounded data checks, and the exact PR evidence block a reviewer can trust.

When the migration changes a query path or adds an index, pair the schema diff with a Postgres EXPLAIN plan review. A plan does not prove the migration succeeded, but it can show whether the affected query still touches the intended relations and whether Postgres estimates the access path the patch expects.

Step 4: seed the data cases that make the migration risky

Most dangerous migrations are dangerous because of data, not syntax.

Atlas describes data-migration testing as a flow of setting up an empty database, applying migrations up to the previous point, seeding test data, running the migration under test, and making assertions on the result (https://atlasgo.io/blog/2024/10/09/strategies-for-reliable-migrations). That is the right mental model for agent work too.

Seed the smallest set of rows that can break the migration:

• A row with NULL in a column that will become required.
• Duplicate values before adding a unique constraint.
• A row that uses the old enum or status value.
• Parent and child rows before changing foreign keys.
• Soft-deleted rows if the app filters them differently.
• A large-ish representative row if the migration rewrites JSON or text.

This is where an agent can do useful work before review. It can read the migration, infer the risky cases, create a seed command, run the migration, and query the result. But the output must be bounded. A proof needs row counts, representative rows, and failing cases, not a transcript-sized table dump.

If the migration depends on production-shaped data, do not copy production rows casually. Use a schema-only clone, masked data, or a reduced approved source. The Postgres clone database sandbox guide covers the safer clone path: read from the source, restore into a tracked destination sandbox, run task SQL only against that destination, and clean up when finished.

Step 5: check rollback or forward-fix behavior deliberately

Do not assume every migration needs a rollback test. Some teams use forward-only migrations, and some migration tools do not model rollback as a first-class command.

What you do need is an explicit recovery story.

For low-risk schema additions, the recovery story may be “apply the next migration that removes the unused object.” For a risky data migration, it may be “keep the old column until the backfill and read path are verified.” For an index failure, Postgres documents that failed concurrent index builds can leave an invalid index that should be dropped and retried (https://www.postgresql.org/docs/current/sql-createindex.html).

Ask the agent to state which recovery path applies:

1. Reversible migration command exists and was tested.
2. Forward fix is the expected path.
3. Manual cleanup is required for a known Postgres artifact, such as an invalid index.
4. The migration should not open a PR until the recovery path is clear.

That keeps the PR note honest. “No rollback tested” can be acceptable. “No rollback considered” is not.

Step 6: write the PR proof note before opening the PR

The migration proof should be short enough to fit in a PR body and specific enough for a reviewer to trust.

Use this structure:

Migration validation
- Sandbox: pgsandbox_<name>_<id>, Postgres <major>, TTL <minutes>
- Command: ["npm", "run", "db:migrate"]
- Before schema: <snapshot or digest>
- After schema: <summary of changed tables/indexes/constraints>
- Seed cases: null email, duplicate slug, existing paid subscription
- Validation SQL: bounded row counts and targeted assertions
- Result: passed / failed with SQLSTATE ...
- Cleanup: deleted sandbox and role / TTL cleanup pending

Do not paste an unmasked connection string. Do not paste large row dumps. Do not ask the reviewer to trust “I tested it locally” when the agent can attach a compact proof record instead.

With PGSandbox, this maps directly to the product surface:

1. Create or clone the sandbox.
2. Optionally run prepare_for_repo with the migration command.
3. Run validate_schema_change.
4. Run seed or validation commands as needed.
5. Use schema snapshots for named checkpoints when the before-state matters.
6. Delete the sandbox or run cleanup_expired.

That is the difference between database migration testing as a phrase and database migration testing as an agent workflow.

Common mistakes

The first mistake is testing against the shared development database. Shared state makes a weak proof because old rows, old extensions, and old manual changes can hide what the migration actually did.

The second mistake is running ad hoc SQL instead of the repo migration command. If CI or production will use a framework command, test that command.

The third mistake is checking only the happy path. Migration risk usually lives in legacy rows, duplicate values, existing constraints, extension availability, lock behavior, and generated SQL that a clean database cannot expose.

The fourth mistake is pasting too much proof. A PR note should include the command, schema diff, key validation results, and cleanup state. It should not include secrets or full table output.

The fifth mistake is leaving the sandbox alive without a reason. The validation database is a task resource, not a new environment.

FAQ

Is database migration testing different for coding agents?

Yes. The database rules are the same, but the proof surface is different. A coding agent should show the exact command it ran, the disposable database it targeted, the schema diff it observed, the seed cases it checked, and the cleanup result before it opens a PR.

Should the agent test migrations in CI or before the PR?

Both can help. The pre-PR sandbox gives the agent fast feedback while it is still editing the patch. CI remains the team-owned gate after the PR exists. The sandbox proof is not a replacement for CI; it is a way to avoid opening review with an unproven migration.

Can I use Testcontainers for migration testing?

Yes, especially when the repo already has a Testcontainers-based integration test harness. Use the test harness when it answers the database question. Use a task-scoped Postgres sandbox when the agent needs a database-level proof loop before a test exists or when the reviewer needs a schema diff and cleanup record. The Testcontainers comparison covers that boundary.

Does PGSandbox run my migrations automatically?

PGSandbox does not guess your framework or mutate repo settings permanently. It can store an explicit command array with prepare_for_repo, inject the sandbox database URL into the command environment, run the command with validate_schema_change, and return bounded proof. The agent still chooses the command that fits the repository.

What if the migration needs real data?

Use the smallest safe data shape. Start with seed rows or a schema-only clone. Use masked or reduced realistic data only when the bug truly depends on it and the source is approved. A disposable sandbox limits where writes land; it does not automatically make sensitive data safe to expose.