Database work in Claude Code: schemas, migrations, queries and seed data

Claude Code handles database work by reading a project’s schema files, migration history and ORM models, then drafting schema changes, migration files, queries and seed scripts that follow those conventions. The qualifier that matters: Claude Code writes the files and proves them on a disposable database, and a CI pipeline, not the agent, applies migrations to shared environments.

This page is for developers who use Claude Code in the terminal and now want it near their data. The feature needs three new tables, the report query takes eight seconds, and the test suite has no realistic data. Claude Code can draft all of that in minutes. The risk is the step after: a migration that locks a 20-million-row table, or an agent whose shell holds a production connection string.

What you’ll walk away with from database work in Claude Code

• A session setup in which Claude Code can change schema files but cannot reach a shared database.
• Copy-paste prompts for schema design, migration files, slow queries, ORM queries, seed data and a usage map built by subagents.
• A disposable-database check (up, down, up, schema diff) that proves a migration without reading every line of SQL.
• A headless claude -p audit that returns JSON a CI job can act on.
• The failure modes that hit database work specifically, with the recovery for each.

Set the boundaries before the first prompt

Database mistakes are expensive because they touch shared state. Three settings keep Claude Code on the safe side of that line, and the order matters: credentials first, rules second, context third.

Keep shared credentials out of Claude Code’s shell

Claude Code runs commands with your environment. With Claude Code v2.1.283 or later (both release channels on 2026-10-12), auto mode is the starting permission mode for interactive terminal sessions on the models it supports, so a classifier, not you, approves most commands. If DATABASE_URL in that shell points at staging or production, one migrate deploy reaches it. If Claude Code is not installed yet, start with installation; for how permission modes and sandboxing bound a session, see permissions and sandboxing.

Start Claude Code in a shell where DATABASE_URL points at a local or disposable database. A throwaway Postgres container is enough:

# Terminal: a scratch database that you can destroy at any time
docker run --rm -d --name pg-scratch -e POSTGRES_PASSWORD=scratch -p 55432:5432 postgres:17
export DATABASE_URL="postgresql://postgres:scratch@localhost:55432/postgres"
claude

If the agent needs to read staging, give it a read-only database role through an MCP server rather than a connection string in the shell. Database MCP servers covers the role and the install for Postgres MCP Pro, DBHub, Supabase, Neon and MongoDB. For Postgres MCP Pro in Claude Code, the project-scoped entry is:

# Terminal, repo root: writes .mcp.json; ${STAGING_DATABASE_URL} expands from your shell at launch
claude mcp add -s project postgres -e 'DATABASE_URI=${STAGING_DATABASE_URL}' \
  -- uvx postgres-mcp --access-mode=restricted

--access-mode=restricted runs statements in read-only transactions with execution-time limits; point STAGING_DATABASE_URL at a read-only role anyway, so the database enforces the limit and not only the server. Postgres MCP Pro is a PyPI package run through uvx; the npm package named postgres-mcp is unrelated. Its last PyPI release was 0.3.0 on 2025-05-16 (checked 2026-09-26), so check the project’s activity before a team depends on it.

Deny the commands that write to shared databases

Add deny rules to .claude/settings.json so the usual spellings of “apply this migration” never run from a session. Adapt the list to your migration tool:

{
  "permissions": {
    "deny": [
      "Bash(npx prisma migrate deploy *)",
      "Bash(npx prisma db push *)",
      "Bash(npx drizzle-kit push *)",
      "Bash(npx drizzle-kit migrate *)"
    ],
    "ask": [
      "Bash(npx prisma migrate reset *)"
    ]
  }
}

A deny rule is a typo-catcher, not a boundary

Claude Code’s permission documentation says a Bash rule matches the command text Claude writes, so it “isn’t a security boundary around the program”. The same write reached through sh -c, a package script or a different binary path does not match. The credential in step one is the boundary. The deny list only stops the common spelling.

Tell Claude Code where the database lives in the repo

Put the database conventions in CLAUDE.md so every session starts with them. See the memory system for where the file goes.

## Database
- Schema: prisma/schema.prisma. Migrations: prisma/migrations/ (never edit an applied one).
- Create a migration file only: `npx prisma migrate dev --create-only --name <change>`.
- The only database you may touch is $DATABASE_URL (local scratch). Never run `migrate deploy`.
- IDs are UUIDv7 strings, timestamps are `timestamptz` named created_at / updated_at.
- Every migration ships with a tested down path or a written reason why it is irreversible.

Design the schema in plan mode

Plan mode lets Claude Code read the schema and propose a design without editing anything. Press Shift+Tab until the mode indicator shows plan mode, or start with claude --permission-mode plan. Ask for the design and the queries it must serve in the same prompt, because indexes follow from queries.

Copy-paste prompt: schema design for a teams feature

Read prisma/schema.prisma and the last five migrations in prisma/migrations/ to learn our conventions (ID type, naming, timestamps, soft deletes).

Design the schema for a teams feature:
- teams: name, unique slug, billing email
- team_memberships: user, team, role (owner, admin, member); a user can belong to many teams; exactly one owner per team
- team_invitations: email, role, token, expires 7 days after creation; one open invitation per (team, email)

List the five queries the app will run most often against these tables, then derive the indexes from them.
For every foreign key, state the ON DELETE behaviour and why.
Say which constraint enforces "exactly one owner per team" in the database, or say plainly that it cannot and where the application must enforce it.
Show the schema diff only. Do not write a migration yet.

Review the plan against this table before you approve it. Each row is cheap to change now and expensive after the migration has run.

Check	What a wrong answer looks like
Conventions match	id serial in a schema that uses UUID strings everywhere else
Delete behaviour	ON DELETE CASCADE from users to teams, which deletes a team when its owner leaves
Invariants in the database	“exactly one owner” enforced only in application code, with no partial unique index
Indexes follow queries	an index on every foreign key, and none on the (team_id, role) lookup the app runs most

Generate the migration file, then prove it on a disposable database

Claude Code writes the migration. It does not apply it anywhere except the scratch database. Zero-downtime patterns such as expand and contract, batched backfills and online index builds are covered in depth in database and code migrations, so this section stays on the loop you run for every migration.

1. Create the file without applying it. Use your tool’s create-only command: npx prisma migrate dev --create-only --name add_teams for Prisma, or npx drizzle-kit generate for Drizzle. Then ask Claude Code to review and complete the generated SQL.

   Copy-paste prompt: finish and risk-rate a migration file

   Open the newest migration in prisma/migrations/. It was generated from the schema change we agreed.
   
   For each statement, add a SQL comment that answers: does it take a lock that blocks writes, for roughly how long on a table of the size in the comment at the top of the file, and is it reversible?
   Rewrite any statement that blocks writes on an existing table: add NOT NULL columns as nullable first, build indexes on existing tables with CREATE INDEX CONCURRENTLY in a migration of their own (it cannot run inside a transaction block), and add foreign keys with NOT VALID followed by VALIDATE CONSTRAINT.
   Write the down migration in a file named down.sql next to it. If a step cannot be undone without data loss, say so at the top of down.sql instead of pretending.
   Do not run anything against any database.

2. Lint it. Squawk flags locking statements in Postgres migrations. Run npx -y squawk-cli prisma/migrations/*/migration.sql (squawk-cli 2.66.0 on 2026-09-26) and paste any finding back to Claude Code.

3. Run up, down, up on the scratch database and diff the schema. This is the proof that the migration and its rollback both work. Plain psql keeps the migration runner’s own ledger out of the comparison. The loop assumes the scratch database already holds every migration before add_teams, which is the state the create-only Prisma run leaves it in. Neither Prisma’s create-only run nor drizzle-kit generate (drizzle-kit 0.31.11 has no down migrations) writes down.sql; the prompt in step 1 has Claude Code write it. On Drizzle, the generated file sits at drizzle/<number>_<name>.sql rather than in a folder, so point M and the two file paths at it and at your hand-written down file.

   # Terminal or Claude Code session: touches only the scratch database
   M=$(ls -d prisma/migrations/*_add_teams)
   pg_dump --schema-only "$DATABASE_URL" > /tmp/schema-before.sql
   psql -v ON_ERROR_STOP=1 "$DATABASE_URL" -f "$M/migration.sql"
   psql -v ON_ERROR_STOP=1 "$DATABASE_URL" -f "$M/down.sql"
   pg_dump --schema-only "$DATABASE_URL" > /tmp/schema-after-down.sql
   diff /tmp/schema-before.sql /tmp/schema-after-down.sql && echo "down path restores the schema"
   psql -v ON_ERROR_STOP=1 "$DATABASE_URL" -f "$M/migration.sql"

   Claude Code can run this loop itself, because the only database in its shell is the scratch one. An empty diff and a clean second apply are the evidence you need; you do not have to read the SQL line by line to trust them.

4. Open the pull request with the evidence. The lint output, the schema diff and the test run go in the description. CI repeats the up-down-up check on a fresh database, and the schema owner approves. After merge, the pipeline applies the migration. See deployment.

Write and tune queries against real plans

Claude Code writes correct SQL more often when it can see a plan instead of guessing at one. Give it the query, the EXPLAIN output and access to the schema, and ask for a measurable result.

Copy-paste prompt: diagnose a slow query

This query takes about 8 seconds on staging. The plan below is from EXPLAIN (ANALYZE, BUFFERS) run as the read-only role.

<paste the query>
<paste the plan>

Read our schema and the existing indexes for every table in the query.
1. Name the plan node that costs the most time and explain why the planner chose it (row estimate vs actual, missing index, sort spilling to disk).
2. Propose at most two fixes, ranked: a query rewrite, an index, or a code change that stops sending this query.
3. For an index, give the CREATE INDEX CONCURRENTLY statement and name the write-heavy tables it slows down.
4. Rewrite the query if that helps, and give me a psql command I can run on the scratch database to compare both versions with EXPLAIN (ANALYZE).

ORM code hides the SQL, and the hidden SQL is where N+1 loops and unbounded includes live. Ask for both the ORM code and the SQL it produces, and make a test the acceptance check.

Copy-paste prompt: ORM query with its SQL and a test

Read src/db/schema.ts (Drizzle). Write a function listRecentOrders(customerId, cursor) that returns completed or shipped orders from the last 30 days, with customer name and email and each order's items with product names, sorted by total descending, paginated with a cursor, 50 per page.

Use the relational query API. Log the SQL it generates and show it to me.
If the SQL issues more than one query per page, explain why and fix it.
Write a Vitest test that seeds two customers with orders on both sides of the 30-day boundary and asserts the exact IDs returned on page 1 and page 2.
Run the test against $DATABASE_URL and show me the result.

Seed realistic data and test factories

Seed scripts fail in two predictable ways: they insert rows in the wrong dependency order, and they create duplicates on a second run. State both constraints in the prompt.

Copy-paste prompt: idempotent seed script

Read the schema and scripts/seed.ts. Extend the seed to create:
- 50 users, 10 teams with 3-15 members each (one owner per team)
- 200 products in 5 categories
- 500 orders over the last 6 months, weighted toward the most recent month, fewer on weekends
- reviews for about 60% of products, mostly 4-5 stars

Insert in foreign-key order and use the IDs returned by earlier inserts, never hardcoded ones.
Make it idempotent: upsert on natural keys (email, slug, SKU) so a second run changes nothing.
Use a fixed random seed so every run produces the same data.
Run it twice against $DATABASE_URL and show me row counts after each run. They must match.

For tests, ask for factories that follow tests/fixtures/ (createTestUser(overrides?), createTestTeam(owner, overrides?)) and wrap each test in a transaction that rolls back. Factories keep a test’s data next to its assertion, which makes the test readable without the seed file. Test strategy in general is on the testing page.

Map database usage with subagents before a risky change

Before you rename a column or split a table, find every place that depends on it. Subagents search in parallel and return summaries, so the main session keeps its context for the change itself.

Copy-paste prompt: usage map before a schema change

We plan to split users.name into first_name and last_name. Use three Explore subagents in parallel:
1. Find every read and write of users.name: ORM calls, raw SQL, views, triggers, seed files, fixtures and analytics exports. List file and line, grouped by read or write.
2. Find every raw SQL string built from user input anywhere in the codebase and flag any that is not parameterized.
3. Read prisma/migrations/ and list every past migration that dropped or rewrote data, and whether it had a down path.
Merge the results into one table and propose the order of changes (expand, dual write, backfill, switch reads, contract). Do not edit any file.

To keep a database specialist around across sessions, define it once in .claude/agents/. See custom subagents.

Run database audits headless with claude -p

A scheduled or per-pull-request audit does not need a person at the keyboard. claude -p runs one prompt and exits. Restrict it to read-only tools with --tools and ask for structured output with --json-schema, so a script can read the findings from the structured_output field.

# CI or terminal: read-only schema audit that returns machine-readable findings
claude -p "Read prisma/schema.prisma and prisma/migrations/. Report: tables without a primary key, \
foreign keys without a supporting index, nullable columns that every write path sets, \
and naming that breaks the conventions in CLAUDE.md. One finding per issue." \
  --tools "Read,Grep,Glob" \
  --output-format json \
  --json-schema '{"type":"object","properties":{"findings":{"type":"array","items":{"type":"object","properties":{"table":{"type":"string"},"column":{"type":"string"},"issue":{"type":"string"},"fix":{"type":"string"}},"required":["table","issue","fix"]}}},"required":["findings"]}' \
  | jq '.structured_output.findings'

--tools sets which built-in tools exist in the run at all, so the audit has no Bash, Edit or Write to reach for. That is a stronger limit than --allowedTools, which only pre-approves tools: claude -p starts in Manual mode (auto mode on third-party providers or with telemetry off, from v2.1.285), and although anything that would prompt is denied in a run with nobody to answer, Manual mode still runs file reads and the built-in read-only shell commands without asking. In CI, add --max-budget-usd to cap spend, and never run this job on untrusted pull-request code while a secret is in the environment. The safe CI pattern is in CI/CD with Claude Code.

How do you know the database work is right?

You check the evidence each step leaves behind, not the agent’s summary and not every line of SQL.

Output	Gate that proves it	Who signs off
Schema design	The plan lists the queries it serves and the constraint behind each invariant	You, before approving plan mode
Migration file	Squawk passes; up, down, up on a fresh database with an empty schema diff; CI repeats it	Schema owner, through CODEOWNERS on the migrations directory
Query or index	EXPLAIN (ANALYZE) before and after on the same data; after deploy, mean time for that query in pg_stat_statements	Author, with the plans in the pull request
ORM function	A test that asserts exact rows, including the boundary cases	CI
Seed script	Two runs, identical row counts	CI

The reviewer reads the pull request description, the plans and the diff of the schema, and spends their attention on the lines those gates cannot judge: delete behaviour, irreversible steps and data that leaves the database. How to review an agent’s pull request covers the review itself.

What breaks in database work with Claude Code, and how to recover

The migration locks a large table. The statement was correct but took an ACCESS EXCLUSIVE lock, for example ALTER TABLE … ADD COLUMN … NOT NULL DEFAULT with a volatile default, or an index built without CONCURRENTLY. Recovery: cancel the migration if it is still waiting (SELECT pg_cancel_backend(pid) on the blocked statement), then rerun the migration prompt with the row count in the file header and follow the zero-downtime patterns.

CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Your migration runner wraps each file in a transaction. Move the index into a migration of its own and use the runner’s documented way to run that file outside a transaction.

The SQLite or Cloudflare D1 migration fails on an ALTER TABLE. SQLite supports ADD COLUMN, RENAME COLUMN (since 3.25.0) and DROP COLUMN (since 3.35.0, with restrictions), but not changing a column’s type or adding a constraint to an existing column, and it has no ADD COLUMN IF NOT EXISTS. Ask Claude Code for the table-rebuild sequence (create new table, copy, drop, rename). On plain SQLite, run it inside one transaction with PRAGMA foreign_keys = OFF set before the transaction starts. On D1, do not add BEGIN/COMMIT: D1 runs the migration file in a transaction for you (Wrangler 4.135.0 strips a single BEGIN TRANSACTION;/COMMIT; pair and rejects a file with more than one), so use PRAGMA defer_foreign_keys = on inside the file instead. Either way, test the rebuild on a copy of real data, because the copy step is where rows go missing.

Claude Code ran a migration against the wrong database. The session inherited a DATABASE_URL that pointed at staging. Recovery: apply the down migration there, or restore from the backup taken before the change. Prevention: start sessions from the scratch-database shell, and check with echo $DATABASE_URL in the session before database work.

The seed script violates a foreign key or duplicates rows on the second run. It inserted in the wrong order or used plain inserts. Rerun the seed prompt, which requires foreign-key order and upserts on natural keys, and keep the “run twice, compare counts” check.

The down migration exists but cannot restore data. Dropping a column in the up path and re-adding it in the down path gives you an empty column. Treat every destructive up step as irreversible: take a backup or keep the old column until the contract phase, and say so at the top of down.sql.

Claude Code keeps using the old column name after a migration. It is working from its earlier reasoning, not the current schema. Ask it to re-read the schema file, or run /clear and start the next task in a fresh context.

Where to go next with database work

Database and code migrations Expand and contract, batched backfills and framework upgrades with Claude Code.

Database MCP servers Give the agent read-only access to a live schema and query statistics.

SQL optimization patterns Tool-neutral recipes for EXPLAIN plans, N+1 queries and indexing.

API development Build the endpoints that sit on top of the schema you just shipped.

The same workflow in the other tools: database work in Codex and database design in Cursor. The cross-tool view is in database design and queries.