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.

For a complete repository command that needs database creation, connection injection, process supervision, and cleanup as one lifecycle, follow the disposable Postgres integration-test session guide. Use the migration-specific workflow below when schema and data evidence are the subject of review.

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 and inspect the result. TTL only marks eligibility for an explicit cleanup_expired call or user-owned scheduler; it does not schedule deletion.

PGSandbox turns that into an MCP workflow instead of a pile of shell commands. 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.

For a foreign-key action change, add the PostgreSQL cascade proof: inspect the installed delete_rule and update_rule, exercise a target graph plus an unrelated control, and prove rollback restores every cascaded change.

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 a repository pinned to Prisma 6, use its installed CLI: prisma migrate deploy applies committed migrations, while prisma migrate dev is a development workflow that uses a shadow database for drift and migration-generation checks. The Prisma 6 CLI reference describes the distinction. Check the repository’s Prisma major before choosing a command; the scoped recipe below is not a latest-version installation guide.

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.

If the repository’s migration command runs inside Docker Compose while PostgreSQL stays on the host, use the Docker-to-host PostgreSQL connection guide to choose the container URL, verify the listener and HBA path, and keep the sandbox credential out of tracked Compose files.

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.

Prisma 6: give the shadow database its own sandbox

For a repository pinned to Prisma 6, migrate dev needs more than a target connection. PGSandbox task roles do not have CREATEDB; automatic shadow creation can fail with P3014. Do not solve that by giving the migration command the lifecycle admin credential.

Allocate two fresh sandboxes in the same intended Postgres profile: one target and one expendable shadow. Use the PGSandbox create/connection/delete contracts to retain both database IDs and obtain their scoped connection strings privately. Check the actual database identities, not just whether URL strings differ: two URLs can point at the same database. Never use production, staging, a shared development database, or the target itself as the shadow.

The following datasource belongs in an existing schema.prisma for Prisma 6.19.3. It follows the Prisma 6 shadow-database configuration. Keep the repository’s models and migration history; inject DATABASE_URL and SHADOW_DATABASE_URL into the child environment without printing or committing their values.

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

Prisma resets the explicitly configured shadow and replays migration history there. That database is disposable input to the check, not a backup. For Prisma 7, the shadow URL configuration moves to prisma.config.ts; this is not a Prisma 7 or latest-version recipe.

For an existing Prisma 6.19.3 repository with committed migrations and its local CLI installed, these commands follow the versioned CLI reference. Run them only in the disposable checkout with the two scoped URLs injected:

./node_modules/.bin/prisma --version
./node_modules/.bin/prisma migrate deploy
./node_modules/.bin/prisma migrate dev --skip-generate --name sandbox_check

migrate deploy applies committed migrations; it does not need a shadow database and is not a drift check. migrate dev is the development check and can create or apply migrations. Inspect its exit status and any generated files; a reset request or failed replay is not a pass. Do not add automatic reset acceptance to an unattended agent command.

In a synthetic rehearsal on November 1, 2026, PGSandbox 0.5.0, PostgreSQL 15 and Prisma 6.19.3 reproduced the automatic-shadow permission failure. An explicit second sandbox allowed history replay while preserving a row in the target. A manually added column triggered the drift control, and an invalid migration failed shadow replay. This validates that narrow fixture, not your application’s migrations, another Prisma major, or a production rollout.

Close database connections and explicitly delete both tracked sandbox IDs, including after a failed engine command. Inspect each deletion result independently; a successful migration does not prove cleanup. The selected shadow is reset by Prisma but still belongs to the PGSandbox lifecycle. TTL only makes it eligible for cleanup_expired; expiry does not schedule deletion.

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.

When a migration adds a materialized view, the schema diff proves only its structural identity. Use the PostgreSQL materialized-view testing workflow to prove initial population, deliberate staleness, ordinary refresh, concurrent-refresh readiness, and exact post-refresh rows.

When a migration adds a non-overlap rule, use the PostgreSQL exclusion-constraint proof to verify the installed operators and index, half-open range boundaries, insert and update violations, SQLSTATE 23P01, deferral timing, and exact final rows.

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.

If the migration depends on a PostgreSQL extension, verify more than the added catalog entry. The disposable extension testing workflow checks profile availability, installed version, application behavior, restore ordering, and cleanup as separate gates before the migration proof is accepted.

If the migration adds or changes a derived field, use the PostgreSQL generated-column testing workflow to verify the installed expression and stored/virtual kind, direct-write rejection, recomputation, trigger timing, and PostgreSQL-major compatibility.

For a version transition, the PostgreSQL extension upgrade workflow adds a source-version fixture, update-path proof, and an upgrade-versus-new-database comparison before the migration is accepted.

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.

For a partial index, add the dedicated PostgreSQL partial-index proof. It verifies the catalog predicate, qualifying row set, positive and negative plan paths, and uniqueness behavior when rows cross the predicate.

For a partitioned table, use the PostgreSQL Partition Boundary Proof. It verifies the installed key and hierarchy, exact boundary and default routing, cross-partition updates, pruning, attach/detach behavior, and sandbox cleanup.

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.

Run a backfill assertion that can actually fail

A successful SQL command does not mean the resulting data is correct. Make the expected result executable, then prove that removing the backfill makes the check fail.

This small synthetic fixture, not an application migration, uses ordinary PostgreSQL without a testing extension. Install PGSandbox and have psql for the selected PostgreSQL version on your PATH. The example was checked with PGSandbox 0.5.0 and PostgreSQL/psql 15. If you use another major version, select it explicitly and rerun both cases.

Save this as migration-check.sql in a scratch directory:

CREATE TABLE orders (
    id integer PRIMARY KEY,
    total_cents integer NOT NULL,
    discount_cents integer
);
INSERT INTO orders VALUES (1, 1000, NULL), (2, 1000, 250);
ALTER TABLE orders ADD COLUMN payable_cents integer;
UPDATE orders
SET payable_cents = total_cents - COALESCE(discount_cents, 0);

DO $$
BEGIN
    IF (SELECT count(*) FROM orders) <> 2 OR EXISTS (
        SELECT 1 FROM orders
        WHERE id NOT IN (1, 2) OR payable_cents IS DISTINCT FROM
            CASE id WHEN 1 THEN 1000 WHEN 2 THEN 750 END
    ) THEN
        RAISE EXCEPTION 'FAIL: backfill values or retained row set differ';
    END IF;
END
$$;
SELECT 'PASS: two legacy rows retained; both backfill cases match' AS result;

The primary key and count check preserve the two-row fixture; the value check catches an omitted backfill and incorrect handling of the nullable discount. IS DISTINCT FROM makes a remaining NULL fail rather than disappear into SQL’s three-valued logic. This is a deliberately tiny contract, not a complete set of business invariants.

From that directory, run:

pgsandbox with-database --postgres-version 15 \
  --name-hint migration-assertion --ttl-minutes 15 \
  --cleanup always --timeout-seconds 60 --result-format json \
  -- psql -X --set ON_ERROR_STOP=1 --file migration-check.sql

The session injects libpq connection variables into psql; there is no need to put a connection URL or password in the command arguments. -X ignores a personal psql startup file. PostgreSQL documents that ON_ERROR_STOP stops a script on a SQL error and returns exit code 3. Without that setting, later successful commands can hide an earlier error from a command-level result.

Now copy the file to migration-broken.sql, remove only the two-line UPDATE orders ... statement, and rerun the same command with --file migration-broken.sql. Keep the assertion unchanged. Each invocation creates a fresh database, so the negative control cannot borrow the first run’s corrected rows.

In the checked fixture, the correct backfill returned exit 0; the omitted backfill raised the exception and returned exit 3. Both session results reported cleanup.deleted: true. Inspect the command and cleanup fields separately: an assertion failure is expected in the negative control, but a cleanup failure still needs recovery. Use the session result and retained-database recovery instructions if deletion did not complete. Expiry alone will not remove it.

For a real migration, keep the same shape but replace the synthetic DDL/backfill with the repository’s actual prior-schema setup, seed fixture, migration command, and explicit postconditions. Do not replace the framework’s migration path with this sample. Neither result proves production lock duration, rollout safety, application compatibility, or performance.

If the repository already uses pgTAP, retain its suite and a TAP-aware runner such as pg_prove, which parses the emitted test results. A failed pgTAP assertion is not necessarily a SQL error; ON_ERROR_STOP alone is not a TAP verdict. Also verify that pgTAP is available and allowed on the selected profile before requesting it. PGSandbox is the disposable database lifecycle, not an automatic installer for every testing extension.

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: delete_database result verified / explicit cleanup_expired still required after expiry

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.