← headroom.walls.sh

Claude Code for Database Work

Claude Code handles database work well across the stack: raw SQL, Prisma schema changes, migration generation, query analysis, and data model design. The key is giving it the schema and the slow query together — it can't optimize what it can't read.

CLAUDE.md for a project with a database

# Project Database

## ORM / Query layer
- Prisma 5 with PostgreSQL

## Commands
- Generate migration: npx prisma migrate dev --name [name]
- Apply migrations: npx prisma migrate deploy
- Reset dev DB: npx prisma migrate reset
- Studio: npx prisma studio
- Generate client: npx prisma generate

## Conventions
- All schema changes go through Prisma migrations — no raw DDL in production
- Every new table needs a createdAt and updatedAt (managed by Prisma @updatedAt)
- Soft deletes: use a deletedAt DateTime? field, not hard deletes
- Always review generated migration SQL before running it on a shared dev database

Schema changes with Prisma

claude "add a tags field to the Post model — it should be a many-to-many relation to a new Tag model (id, name, slug). Generate a migration named 'add-post-tags'. Show me the migration SQL before I run it."

Claude Code edits the schema, generates the migration, and shows you the SQL. The review step matters — prisma migrate dev runs immediately in dev, so seeing the SQL first is cheap insurance.

claude "rename the 'author' field on Post to 'authorId' to make the foreign key naming explicit. This is a rename in the schema, not a new column — generate a migration that renames the column, not one that drops and re-adds it."

Column renames need a custom migration — Prisma generates a drop+re-add by default. Give Claude Code the constraint ("rename, not drop-and-add") and it will generate the correct RENAME COLUMN SQL.

Write raw SQL queries

claude "write a SQL query that returns the top 10 users by total order value in the last 30 days. Join users, orders, and order_items. Group by user. Include their email, name, order count, and total spent."

claude "write a query to find all products that have never been ordered. Use a LEFT JOIN with a NULL check rather than NOT IN — explain why NOT IN with a subquery is dangerous when order_items.product_id can be NULL."

Ask Claude Code for the explanation when it chooses one join pattern over another — the reasoning (NULL handling, index usage, row estimate differences) is often the most valuable part.

Optimize a slow query

claude "this query takes 4 seconds on 2M rows: [paste query]. Here's the EXPLAIN ANALYZE output: [paste output]. Identify the bottleneck and suggest the fix — indexes, query rewrite, or both."

Always include the EXPLAIN ANALYZE output. Claude Code can read the plan and identify sequential scans, bad row estimates, and missing indexes — but it needs the actual plan, not just the query.

claude "add the missing indexes suggested by this EXPLAIN ANALYZE output: [paste]. Write the CREATE INDEX statements as a new migration. Use CONCURRENTLY so production tables aren't locked."

The CONCURRENTLY flag matters for production — adding an index without it locks the table for writes. Claude Code will use it if you specify "no table locks" or "production-safe".

Prisma queries and N+1 problems

claude "this Prisma query has an N+1 problem — it loads posts and then fetches the author for each one separately: [paste code]. Fix it with a single include that loads posts and authors together."

claude "rewrite this prisma.findMany to use cursor-based pagination instead of offset pagination. The cursor should be the id field. Return nextCursor alongside the results."

claude "this query loads entire Post objects but the API only returns id, title, and createdAt. Add a select to fetch only those three fields."

Database migrations for a large table

claude "I need to add a NOT NULL column 'status' (enum: draft/published/archived, default 'draft') to a posts table with 50M rows. Write a migration strategy that doesn't lock the table: add nullable, backfill in batches, then add the NOT NULL constraint."

Large table migrations are where Claude Code's knowledge of PostgreSQL-specific patterns (batched backfills, ALTER TABLE ... SET NOT NULL with a CHECK CONSTRAINT first) is most valuable. Always specify the row count — the strategy changes based on scale.

claude "write the backfill script for the status column migration: update rows in batches of 10,000 with a LIMIT and cursor, sleep 100ms between batches to avoid lock pressure. Run as a standalone script, not inside a migration."

Seed data and test fixtures

claude "write a Prisma seed script (prisma/seed.ts) that creates: 3 users with different roles (admin/editor/viewer), 10 posts in various states, and realistic tag relationships. Use deterministic IDs so the seed is idempotent."

claude "write factory functions for tests: createUser(), createPost(authorId), createOrderWithItems(userId, productIds). Each should insert to the test database and return the created record. Use the Prisma client directly."

Schema design questions

claude "I need to store per-user notification preferences — each user can enable/disable notifications for ~20 different event types. Options: JSONB column on users table, a notification_preferences table with one row per user per type, or a bitmask. What are the tradeoffs for a table of 500k users?"

Claude Code will lay out the tradeoffs concretely: query patterns, index strategies, storage costs, and schema flexibility for each option. Give it the scale (500k users, 20 types) — the answer changes at different scales.

Monitor session budget during database work

Migration generation + review + large table backfill scripting can span multiple back-and-forth exchanges that add up. Sessions involving database schema design often run longer than they look because of the review cycles.

Common database + Claude Code patterns

→ Claude Code for REST API Development
→ Claude Code for TypeScript
→ Claude Code for Go
→ Claude Code for Python