Claude Code for Database Work — SQL, ORM & Migrations

Claude Code handles database work well across languages and ORM frameworks. The key is giving it your schema upfront: table names, column types, relationships, and migration tool. This guide covers CLAUDE.md patterns for database-heavy projects, ORM-specific workflows (Prisma, SQLAlchemy, sqlx, Hibernate), query optimization, and safe migration patterns.

Database CLAUDE.md Template

# Project: [Your Service Name]

## Database
- PostgreSQL 16 (primary store)
- Driver: asyncpg (Python) / pgx/v5 (Go) / pg (Node.js)
- ORM / query builder: SQLAlchemy 2.0 async (or Prisma 5, sqlx, Hibernate — specify)
- Migrations: Alembic (or Flyway, Liquibase, Prisma Migrate — specify)
- Schema: see database/schema.sql or prisma/schema.prisma

## Key tables (summarize; Claude reads schema files too)
- users (id UUID PK, email TEXT UNIQUE, created_at TIMESTAMPTZ)
- orders (id UUID PK, user_id UUID FK→users, status order_status, total_cents INT)
- order_items (id UUID PK, order_id UUID FK→orders, product_id UUID, quantity INT)
- (add your own tables here)

## Migration rules
- NEVER auto-run migrations; always show SQL first for review
- Use backward-compatible migrations on tables with data
- Add indexes in a separate migration from the table creation
- Prefer additive schema changes; never drop columns until code is deployed

## Query conventions
- Use CTEs for complex queries (better readability over subqueries)
- Always use parameterized queries — never string-interpolated SQL
- Add EXPLAIN ANALYZE output to the prompt when asking for optimization

Prisma Workflows (Node.js / TypeScript)

Add a new model and relation

claude "add a Product model to prisma/schema.prisma:
- id: UUID, auto-generated
- name: String, max 200 chars
- priceCents: Int (store money as cents, never Float)
- status: ProductStatus enum (DRAFT, ACTIVE, ARCHIVED)
- category: Category (many-to-one)
- orders: relation to OrderItem
Create the migration with 'prisma migrate dev --name add-product-model'.
Add createdAt/updatedAt with @default(now()) and @updatedAt."

Query optimization with Prisma

claude "fix this N+1 query in src/services/orders.ts:
// BAD — fires 1 query per order
const orders = await prisma.order.findMany();
for (const order of orders) {
  order.items = await prisma.orderItem.findMany({ where: { orderId: order.id } });
}

Rewrite with a single Prisma query using include: { items: true }.
If the items have nested relations, use nested include.
Explain why the original was N+1."

SQLAlchemy 2.0 Workflows (Python)

CLAUDE.md additions for SQLAlchemy

# In CLAUDE.md:

## ORM: SQLAlchemy 2.0 async
- Engine: asyncpg driver (postgresql+asyncpg://...)
- Session: AsyncSession with async_sessionmaker
- Models: use mapped_column() with Mapped[T] annotations (not Column())
- Queries: use select() + session.scalars() (not session.query())
- Migrations: Alembic with async env.py template
- Never use session.query() — it's the legacy 1.x API

Add a model and repository

claude "add a Product model to app/models/product.py:
- Use SQLAlchemy 2.0 Mapped[T] annotations
- id: UUID primary key (generated by application, not database)
- name: str, max 200
- price_cents: int
- status: ProductStatus (Python enum, stored as varchar)
- created_at / updated_at: datetime with timezone
Add ProductRepository in app/repositories/product.py with:
- get(id: UUID) → Product | None
- list(limit: int, offset: int) → list[Product]
- create(data: ProductCreate) → Product
- update(id: UUID, data: ProductUpdate) → Product
Use AsyncSession; no commits inside repository (caller handles transaction)."

Alembic migration generation

claude "generate an Alembic migration to add a 'tags' column (ARRAY of text) to the products table.
Show the migration SQL before applying it.
The migration must be backward-compatible: use a nullable column with a default of '{}' (empty array).
After showing the SQL, run 'alembic upgrade head' only if I approve."

Query Optimization Workflows

Diagnose a slow query

claude "this query takes 8 seconds on a 10M-row orders table:
SELECT o.*, u.email FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending' AND o.created_at > '2026-01-01' ORDER BY o.created_at DESC;

EXPLAIN ANALYZE output:
[paste your EXPLAIN ANALYZE output here]

Suggest: missing indexes, query rewrite, and any schema changes.
Show the exact CREATE INDEX statements."

Common index patterns

Safe Migration Patterns

Zero-downtime column addition

claude "add a non-nullable 'tier' column to the users table. This table has 5M rows.
Use the expand/contract pattern:
1. Migration 1: ADD COLUMN tier VARCHAR(20) NULL DEFAULT 'standard'
2. Application code: write 'standard' for new users; backfill in background job
3. Migration 2 (after all rows have a value): ALTER COLUMN tier SET NOT NULL
Show each migration file and the background backfill script."

Rename a column safely

claude "rename the 'username' column to 'display_name' in the users table.
Zero-downtime approach — we cannot lock the table:
1. Add new column display_name, sync via trigger
2. Deploy app reading both columns (prefer display_name)
3. Backfill display_name for rows where it's null
4. Drop trigger and old column in a final migration
Show all migration files and the application code change needed."

ORM Choice Reference

5 Tips for Database + Claude Code

1. Always paste your schema into CLAUDE.md or reference the schema file. Claude will generate correct column names, types, and relationships without hallucinating columns that don't exist.
2. Add "NEVER run migrations — show SQL first" to CLAUDE.md. This single instruction prevents Claude from auto-running destructive ALTER TABLE statements on your behalf.
3. When asking for query optimization, always include the EXPLAIN ANALYZE output. Without it, Claude guesses at the bottleneck. With it, Claude reads the actual sequential scans and index misses directly.
4. Specify your database version (PostgreSQL 16, MySQL 8.2). Features like MERGE statements, materialized CTEs, and generated columns vary by version and Claude will use the right syntax.
5. For seed data and fixtures, ask Claude to use transactions: "wrap all seed inserts in a single transaction so it's all-or-nothing on failure". This prevents half-seeded test databases that are hard to debug.