Agent-safe database migrations with a shadow database

Never let an agent's migration touch a database you care about on the first run. A disposable shadow database catches destructive DDL, broken backfills, and the classic 'rename via drop-and-create' before they reach even your dev data.

Expected result

Agent-written migrations run against a throwaway shadow database first; a schema diff and a destructive-operation scan gate whether they ever run against your real dev database. Your MCP database connection is read-only.

Difficulty

Advanced

Estimated time

~60 min

Version notes

Commands shown are Django-flavored; Rails equivalent uses bin/rails db:migrate and db:rollback with a shadow via DATABASE_URL. Prisma has shadow databases built in (shadowDatabaseUrl) — use that instead of the manual script.

1. 01Local Postgres 15+ with createdb rights
2. 02A migration tool with plain-SQL output (Django, Rails, Prisma, dbmate all work)
3. 03Agents access the DB only through a connection you control

01

Give the agent a read-only database connection

First rule: the agent's live connection (MCP or otherwise) cannot write. Create a role that can inspect schema and data but not modify it. Migrations happen through files + your runner, never through the live connection.

CREATE ROLE agent_ro LOGIN PASSWORD :'pw';
GRANT CONNECT ON DATABASE myapp_dev TO agent_ro;
GRANT USAGE ON SCHEMA public TO agent_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO agent_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO agent_ro;

Copy

02

Script the shadow database lifecycle

The shadow DB is created from your current schema, gets the candidate migrations, and is destroyed. Make it one command so it actually gets used.

#!/usr/bin/env bash
# bin/shadow-migrate — run candidate migrations against a throwaway DB
set -euo pipefail
SHADOW=myapp_shadow
dropdb --if-exists "$SHADOW"
createdb "$SHADOW"
pg_dump --schema-only myapp_dev | psql -q "$SHADOW"
DATABASE_URL=postgres://localhost/$SHADOW python manage.py migrate
echo 'Shadow migration OK'

Copy

! Common failures

Shadow migrate passes but dev fails on data-dependent constraints

The shadow is schema-only, so NOT NULL additions and unique constraints can pass empty and fail on real rows. Seed the shadow with a sanitized sample (pg_sample or a fixtures load) if your migrations do backfills.

Agent bypasses the runner and executes ALTER TABLE through the MCP connection

Then your MCP role isn't actually read-only — verify with SELECT current_user and test an ALTER by hand. The grants in step 1 must be on the connection the agent uses, not a different one.

Reverse migration fails because the agent wrote RunPython with no reverse function

Make 'every RunPython provides reverse_code (use RunPython.noop only for pure backfills)' an explicit repo rule. The reversibility check in step 5 is what enforces it.