Claude Code for Database Work and Migrations

Claude code database work usually starts the same way: you open a terminal, describe the schema change you need in plain English, and let the agent write the migration file for you. That is the entire pitch. Claude Code reads your existing schema, understands your ORM's conventions, generates a migration that matches your project's style, and can even run it against a local database to confirm it applies cleanly before you touch anything real. This article walks through the actual workflow: connecting Claude Code to a database, writing and reviewing migrations, debugging slow queries, and the guardrails you need so an agent with shell access never runs a destructive command against production by accident.

Why use an agent for database work at all

Database work is repetitive in a way that makes it a good fit for an agent, but risky in a way that makes it a bad fit for blind automation. Writing a migration to add a nullable column, backfill it, then make it non-nullable is the same three-step dance every time. Writing a query to find duplicate rows before a unique constraint goes on is the same investigative process every time. Claude Code is good at both because it can read the existing migration history, match your naming conventions, and chain the necessary steps without you having to remember the order.

The risk is that a terminal-based coding agent has real shell access. It can run psql, prisma migrate deploy, or a raw DROP TABLE if you let it. The right way to use Claude Code for database work is to treat it like a very fast junior engineer: happy to write the migration, not authorized to apply it to anything that matters without you reading the diff first.

Setting up a safe local database connection

Before Claude Code can be useful for schema work, it needs to see the schema. The cleanest setup is a local Postgres instance (Docker is fine) with a .env file pointing at it, and a project CLAUDE.md or AGENTS.md file that tells the agent where the database lives and how migrations are structured.

A minimal Docker Compose file for local Postgres:

services:
  db:
    image: postgres:16
    environment:
      POSTGRES_USER: dev
      POSTGRES_PASSWORD: dev
      POSTGRES_DB: appdb
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

Bring it up and confirm Claude Code can reach it:

docker compose up -d
psql postgresql://dev:dev@localhost:5432/appdb -c "select 1;"

Then point your ORM's connection string at that same database in .env.local, and add a short note in your project instructions file so the agent does not go hunting for connection details every session:

## Database
- Local Postgres via docker compose, connection string in .env.local
- ORM: Prisma, schema at prisma/schema.prisma
- Migrations live in prisma/migrations/, never edit an already-applied migration
- Never run migrate deploy or reset against anything but the local db

That last line matters more than it looks. Claude Code will read your CLAUDE.md or AGENTS.md file before acting, and an explicit "never do X against production" instruction is the single highest-leverage guardrail you can add.

Using an MCP server for direct database access

Terminal commands work, but a Postgres MCP server gives Claude Code structured access to your schema, table stats, and query plans without shelling out to psql for every question. Anthropic and the community maintain several database MCP servers; the pattern is the same regardless of which one you pick.

Add it to your Claude Code MCP config:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-postgres", "postgresql://dev:dev@localhost:5432/appdb"]
    }
  }
}

Once connected, you can ask things like "what indexes exist on the orders table" or "show me the row count for each table" and Claude Code queries the database directly instead of guessing from the schema file. This is especially useful for migrations that touch large tables, where you want to know the actual row count before deciding whether a migration needs to run in batches.

If you would rather not grant an MCP server standing access, a plain read-only Postgres role works just as well for exploration:

CREATE ROLE claude_readonly WITH LOGIN PASSWORD 'devonly';
GRANT CONNECT ON DATABASE appdb TO claude_readonly;
GRANT USAGE ON SCHEMA public TO claude_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO claude_readonly;

Point the MCP server or your local connection string at that role for any session where the agent is just investigating, not writing migrations.

Writing migrations with Claude Code and Prisma

Say you need to add an email_verified_at timestamp column to a users table, backfill existing rows based on an old verified boolean, then drop the boolean once the backfill is confirmed. That is a three-migration sequence, and it is exactly the kind of task where an agent earns its keep.

Prompt Claude Code with the actual requirement, not the SQL:

Add an email_verified_at timestamp column to users. Backfill it to now()
for every row where verified = true. Leave the verified column in place
for now, we'll drop it in a follow-up migration once the app code stops
reading it.

Claude Code reads prisma/schema.prisma, adds the field, and runs the generator:

npx prisma migrate dev --name add_email_verified_at --create-only

The --create-only flag is worth using every time you work with an agent: it generates the SQL file without applying it, so you get a chance to read the migration before it touches your local database. A generated migration for this change looks like:

ALTER TABLE "users" ADD COLUMN "email_verified_at" TIMESTAMP(3);

UPDATE "users" SET "email_verified_at" = now() WHERE "verified" = true;

Read it. Confirm the backfill logic matches what you asked for, then apply it locally:

npx prisma migrate dev

For the follow-up migration that drops the verified column, do not let Claude Code write and apply it in the same session where it just added the column. Ship the first migration, deploy the app code that reads email_verified_at instead of verified, confirm it in production, and only then ask for the drop migration in a separate pass. Sequencing destructive changes behind a deploy is a standard practice; an agent will follow it if you say so explicitly, but it will not infer the sequencing on its own unless your prompt asks for it.

Writing migrations with a SQL-first tool

If your project uses a SQL-first migration tool instead of an ORM (Drizzle, Atlas, plain node-pg-migrate, or hand-rolled numbered SQL files), the workflow is the same shape: describe the change, let Claude Code draft the file matching your existing naming convention, review the diff, apply locally.

A Drizzle migration for adding an index looks like this once generated:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id
ON orders (customer_id);

The CONCURRENTLY keyword matters on any table with meaningful row counts, because a regular CREATE INDEX takes a lock that blocks writes for the duration of the build. If you are working against a table you know is large, say so in the prompt:

Add an index on orders.customer_id. This table has tens of millions of
rows in production, so the migration needs to build the index without
locking writes.

That single sentence is enough for Claude Code to reach for CONCURRENTLY and to note in its migration comment that concurrent index builds cannot run inside a transaction block, which some migration runners wrap by default. If your tool wraps every migration in a transaction, ask the agent to check whether this specific migration needs to opt out.

Debugging slow queries

Claude Code is also useful for the diagnostic side of database work. Paste a slow query and ask it to reason through the plan:

This query takes 4 seconds in production:

SELECT o.*, c.name FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending' AND o.created_at > now() - interval '7 days'
ORDER BY o.created_at DESC;

Here's the EXPLAIN ANALYZE output: [paste plan]

Given the actual EXPLAIN ANALYZE output, Claude Code can point at the specific line doing a sequential scan and suggest the composite index that fixes it, rather than guessing from the query text alone. This is a case where giving the agent real data beats giving it a description: an agent reasoning from a plan you pasted will be right far more often than one reasoning from your summary of the plan.

A typical fix for the query above:

CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders (status, created_at DESC)
WHERE status = 'pending';

That is a partial index, scoped to the pending status, which keeps it small and cheap to maintain if pending is a minority of rows. Claude Code will suggest this shape once it sees that the query always filters on status = 'pending', but it needs the actual plan to know the index is worth the partial clause rather than a plain composite index.

Guardrails: what not to let the agent do unattended

A few rules keep Claude Code useful for database work without turning it into a liability:

• Never point a coding agent's default database connection at production. Use a local or staging database as the default, and require an explicit, separate step (ideally a human running the deploy command) to apply migrations anywhere real data lives.
• Use --create-only or your migration tool's dry-run equivalent so you always read the generated SQL before it runs.
• Grant read-only database roles for any session where the agent is just investigating schema or debugging queries, and only grant write access in the narrow window where it is actually generating and applying a local migration.
• Write the guardrails into your project's CLAUDE.md or AGENTS.md file, not just into a one-off prompt. Instructions in the project file get read every session; a rule you type once in chat gets forgotten the moment the context resets.
• Ask for migrations to be reversible where the tool supports it. A down migration that Claude Code writes alongside the up migration is cheap insurance and worth asking for by default.
• For destructive changes (dropping a column, dropping a table, changing a column type in a way that loses data), ask Claude Code to propose the migration but explicitly hold off on running it. Read it twice.

A repeatable workflow for schema changes

Putting the pieces together, a solid repeatable flow for using Claude Code on schema changes looks like this:

1. Describe the requirement in plain English, including any known table size or performance constraints.
2. Let Claude Code read the current schema and migration history so the new migration matches existing conventions.
3. Generate the migration with a --create-only or dry-run flag so nothing applies automatically.
4. Read the generated SQL. Confirm column types, defaults, nullability, and index strategy match intent.
5. Apply the migration locally and run your test suite against it.
6. For anything destructive, split the change across two deploys: add or backfill first, drop or tighten constraints second, with app code changes shipped in between.
7. Apply to staging, verify, then apply to production through your normal deploy pipeline, not through an agent's terminal session.

That sequence is not specific to Claude Code, it is just how careful schema changes should work. What the agent adds is speed on steps 1 through 5: it can draft a correct migration in the time it takes you to type the requirement, which leaves more of your attention for the review step that actually matters.

FAQ

Can Claude Code connect directly to my production database? It can, if you give it a connection string, but you should not set that up as the default. Keep the agent's working connection pointed at a local or staging database, and treat any production access as a manual, supervised step, ideally through a read-only role for investigation only.

Does Claude Code understand my specific ORM's migration format? Yes, as long as it can read your existing migration files and schema definition. It infers conventions (naming, column ordering, how you handle timestamps) from what is already in the repository, which is why keeping a clean, consistent migration history helps the agent produce better output.

What happens if the agent writes a bad migration? The same thing that happens if a person writes one: it either fails to apply, or it applies and causes a problem. The mitigation is the same too, review the generated SQL before running it, test it against a local database first, and keep migrations reversible where your tooling supports a down migration.

Should I let Claude Code run `migrate deploy` automatically in CI? That is a call for your team's deploy process, not a database question specific to Claude Code. Many teams keep migration application as a separate, gated step in CI even when everything else is automated, precisely because a bad migration is harder to undo than a bad deploy of application code.

Is it safe to ask Claude Code to write a script that deletes rows? Ask for the SELECT version first. Have it write and run the query that identifies which rows would be deleted, review the row count and a sample, then convert it to a DELETE only after you have confirmed the selection is correct. This two-step habit catches most of the mistakes that make deletion scripts dangerous.

Can it help with database backups before a risky migration? Yes, it can write the pg_dump command or your cloud provider's backup command for you, but confirm the backup actually completed and is restorable before running anything destructive. An agent can write the command; it cannot verify the backup succeeded unless you ask it to check the output or the resulting file size.