Claude Code with PostgreSQL: Schema, Migrations, Queries
Using Claude Code with PostgreSQL
Postgres is the most common backing store for serious application work, and the most common place where AI coding tools fail loudly. SQL has subtle dialect differences, migrations are irreversible if mishandled, and ORM ecosystems carry decades of layered conventions that produce conflicting code samples in any model's training set. Claude Code can be the best collaborator you have ever had on a database project, or it can write a migration that drops a column with billions of rows because nobody told it the project does soft deletes.
The difference is not the model. It is the project context. Claude Code understands Postgres deeply: window functions, CTEs, partial indexes, JSONB operators, partitioning, replication, and the full EXPLAIN ANALYZE output format. What it does not know is your schema, your migration tool, your ORM choice, your naming conventions, and the operational rules you learned from the last few production incidents.
Without a project-specific CLAUDE.md, Claude mixes MySQL syntax into Postgres queries, picks ORM patterns from whichever library it sampled most recently, writes migrations without transaction wrappers, and proposes schema changes that would lock a busy table for minutes. With the right config, it writes parameterised queries that match your existing patterns and refuses to run destructive commands against production. If you are new to Claude Code, the Claude Code setup guide covers installation and authentication before any of this applies.
The PostgreSQL CLAUDE.md template
The CLAUDE.md at your project root is read before every Claude Code session. For a Postgres project, it has to answer: which Postgres version, which ORM (or none), where migrations live, what naming conventions apply, and which operations are gated. The CLAUDE.md explained guide covers the broader anatomy of a good config file. Below is a template that has worked across half a dozen production Postgres codebases.
# PostgreSQL project rules
## Stack
- PostgreSQL: 16.x (production), 16.x (local via Docker)
- Connection pooling: PgBouncer (transaction mode) in production
- ORM: Drizzle 0.36.x with drizzle-kit for migrations
- Raw SQL: postgres.js (`postgres`) for performance-critical paths
- Local dev DB: docker compose service `postgres`, port 5432
- Migration tool: drizzle-kit (push and migrate, never drop)
## Database conventions
- Schema: snake_case for tables, columns, indexes, and constraints
- Primary keys: `id uuid primary key default gen_random_uuid()` (never serial)
- Timestamps: `created_at`, `updated_at` (timestamptz, not timestamp)
- Soft deletes: `deleted_at timestamptz` (NULL means active)
- Foreign keys: always declared, always with explicit ON DELETE behaviour
- All boolean columns are NOT NULL with an explicit DEFAULT
- Indexes are named: `idx_{table}_{columns}` for btree, `gin_{table}_{column}` for GIN
## Project structure
- db/schema/: Drizzle schema files, one per domain (users.ts, orders.ts, ...)
- db/migrations/: Generated SQL migrations (never edited by hand after generation)
- db/queries/: Reusable typed query functions
- db/seed/: Seed scripts for local dev only
- db/index.ts: Connection pool, schema export, drizzle() instance
## Migration workflow
- Always: `pnpm db:generate` to produce a new SQL file from schema changes
- Always: review the generated SQL before running `pnpm db:migrate`
- Never: hand-edit a generated migration after it has been applied anywhere
- Never: combine schema changes and data backfills in the same migration
- Backfills go in their own numbered migration with explicit BEGIN/COMMIT
## Query rules
- NEVER concatenate user input into SQL. Always parameterise via the driver.
- Always destructure typed results from Drizzle queries (no `any` in query callers).
- Use `select` with explicit columns; avoid `select *` outside debug scripts.
- Transactions: use `db.transaction(async (tx) => ...)`, never manual BEGIN/COMMIT
- For analytical queries, write raw SQL in db/queries/ rather than fighting the ORM
## Hard rules
- NEVER run `drop table`, `drop column`, `truncate`, or `delete from {table}` without --confirm
- NEVER apply migrations directly to production; always via the migration runner in CI
- NEVER hardcode connection strings; read from DATABASE_URL only
- All destructive operations require a backup confirmation before running
Three rules in this CLAUDE.md prevent the most common Postgres failures with Claude Code.
The UUID primary key rule is structural. Claude defaults to serial because that is what most tutorials use. Auto-incrementing integers leak write volume, conflict on multi-region replication, and force a round-trip to the database to learn the new ID. UUIDs avoid all three.
The soft delete column rule is operational. Postgres training data is split between hard deletes, deleted_at columns, and separate audit log tables. Without an explicit choice, Claude oscillates. Pinning deleted_at timestamptz means every query Claude writes against a table includes the where deleted_at is null filter automatically.
The never combine schema and backfill rule prevents production incidents. A migration that adds a column and backfills it in the same transaction holds a row-level lock for the duration of the backfill. On a billion-row table that is an outage. Splitting DDL and DML into separate migrations keeps lock windows short.
Schema and migration workflow
Migrations are the change log of your schema, and a single bad one can take down your database. The workflow Claude follows is dictated by your ORM choice, so CLAUDE.md needs to be specific.
# Drizzle workflow (recommended default)
pnpm db:generate # produces a new SQL file in db/migrations/
pnpm db:migrate # applies pending migrations transactionally
pnpm db:check # verifies schema matches generated migrations
pnpm db:studio # opens drizzle-kit studio for browsing
Drizzle is the default we recommend for new Postgres projects with Claude Code. Schema is defined in TypeScript, migrations are generated SQL files you can review before running, and the runtime API is close to raw SQL with type inference. Claude generates correct Drizzle syntax consistently because the API surface is small, and reads your schema files to generate queries that match actual column types without guessing.
// db/schema/users.ts
import { pgTable, uuid, text, timestamp, boolean } from "drizzle-orm/pg-core";
export const users = pgTable("users", {
id: uuid("id").primaryKey().defaultRandom(),
email: text("email").notNull().unique(),
emailVerified: boolean("email_verified").notNull().default(false),
createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
deletedAt: timestamp("deleted_at", { withTimezone: true }),
});
Prisma is the alternative for teams that prefer a more opinionated schema language. It owns more of the stack: its own schema file, its own migration tool, and a generated client with rich type inference. Claude Code generates correct Prisma queries when it is in CLAUDE.md, but the abstraction is heavier, so for performance-critical paths you may drop to raw SQL anyway. The Claude Code with Prisma guide covers Prisma-specific configuration.
Raw SQL with postgres.js is the right call when you want full control and minimal abstraction. The trade-off is that Claude has no schema to type-check against, so include a db/schema.sql file that mirrors production and reference it explicitly in CLAUDE.md.
The pattern that has saved the most incidents is splitting destructive changes into multi-step migrations. Add the new column, deploy. Backfill in batches with a separate script. Switch reads. Switch writes. Drop the old column in a later release. Claude generates this pattern when the rule is in CLAUDE.md. Without it, it combines all four steps into one migration and proposes to run it during business hours.
Query patterns and performance
Claude Code generates queries constantly. List endpoints, search filters, aggregations, joins across half a dozen tables. The quality of those queries determines whether your application is fast or slow, secure or vulnerable, easy to refactor or impossible to change.
Three query patterns are non-negotiable. They go in CLAUDE.md and are not optional.
// Pattern 1: parameterised query with explicit columns
import { db } from "@/db";
import { users, orders } from "@/db/schema";
import { eq, isNull, and } from "drizzle-orm";
export async function getUserOrders(userId: string) {
return db
.select({
orderId: orders.id,
orderTotal: orders.totalCents,
orderStatus: orders.status,
createdAt: orders.createdAt,
})
.from(orders)
.where(and(eq(orders.userId, userId), isNull(orders.deletedAt)))
.orderBy(orders.createdAt);
}
// Pattern 2: transaction for multi-statement work
export async function createOrderWithItems(order: NewOrder, items: NewItem[]) {
return db.transaction(async (tx) => {
const [created] = await tx.insert(orders).values(order).returning();
if (items.length > 0) {
await tx.insert(orderItems).values(
items.map((item) => ({ ...item, orderId: created.id }))
);
}
return created;
});
}
// Pattern 3: raw SQL with parameter binding for performance paths
import { sql } from "drizzle-orm";
export async function searchProducts(query: string, limit: number) {
return db.execute(sql`
select id, name, price_cents,
ts_rank(search_vector, plainto_tsquery('english', ${query})) as rank
from products
where search_vector @@ plainto_tsquery('english', ${query})
and deleted_at is null
order by rank desc
limit ${limit}
`);
}
The parameterised query pattern prevents SQL injection. The Drizzle query builder parameterises automatically when you use the eq, and, or, like helpers. The raw SQL pattern uses tagged template literals (sql`...`), which Drizzle parameterises behind the scenes. The pattern that gets you in trouble is string concatenation: never write sql`select * from users where email = '${userEmail}'`, even in a script you think will never see real input. Claude follows this rule when it is in CLAUDE.md.
The transaction pattern matters because Postgres handles concurrent writes correctly only when related changes are wrapped in a transaction. Inserting an order and its line items separately leaves orphaned orders if anything fails between them. Wrapping in db.transaction(...) makes the whole thing atomic. The Claude Code environment variables guide covers the conventions for switching between dev and production databases.
The explicit-columns rule (select { id, name, ... } rather than select *) is about both performance and evolution. select * returns whatever columns currently exist, so adding a column changes query result shapes. Explicit column lists make schema changes safe and let you tune queries to fetch only the data you need.
For type-safe results, Drizzle gives you full inference: the return type reflects the columns you selected. With raw SQL via db.execute(sql\...`)`, type inference disappears. The pattern is to wrap the query in a function that casts to a known type, with a runtime check via Zod if the data crosses a trust boundary.
Performance traps that produce slow correct-looking code
The most common is the N+1 query. A list endpoint that returns 50 orders with their user, where Claude has written a for (const order of orders) { user = await getUser(order.userId) } loop, makes 51 round-trips for what should be one. The fix is a join or an inArray lookup.
// Bad: N+1, one query per order
for (const order of orders) {
const user = await db.select().from(users).where(eq(users.id, order.userId));
}
// Good: one query, joined results
const orderUsers = await db
.select({
order: orders,
user: users,
})
.from(orders)
.innerJoin(users, eq(users.id, orders.userId))
.where(/* ... */);
// Also good: one query for orders, one for users, joined in code
const orderUserIds = [...new Set(orders.map(o => o.userId))];
const userRows = await db.select().from(users).where(inArray(users.id, orderUserIds));
const userMap = new Map(userRows.map(u => [u.id, u]));
Add a CLAUDE.md rule: Never iterate over a result set making per-row queries. Use a join or an inArray lookup. The N+1 pattern shows up frequently because it is the most natural way to write code if you are not thinking about query count. The Claude Code debugging guide covers query tracing and request profiling.
Index strategy is the second area. Postgres will not use an index unless the planner decides it is faster than a sequential scan, and the decision depends on statistics, query shape, and index type. The CLAUDE.md rules:
## Indexing
- Every foreign key column gets a btree index
- Columns used in `where` filters get a btree index
- Columns used in `order by` get a btree index (covering the order columns)
- JSONB columns queried with `@>` or `?` get a GIN index
- Full-text search columns get a GIN index on a tsvector column
- Unique constraints are implicit indexes; do not add a separate index
- Composite indexes match the column order of the most common query
To verify an index is actually used, run EXPLAIN ANALYZE and read the output:
explain analyze
select * from orders
where user_id = '...' and status = 'paid'
order by created_at desc
limit 50;
Look for Index Scan rather than Seq Scan, and check the planner's row estimate against the actual count. An estimate 100x off the actual is usually a stale statistics problem fixed by ANALYZE table_name. Claude reads EXPLAIN output and proposes index changes when given the query and plan. The rule in CLAUDE.md: Before suggesting an index, run EXPLAIN ANALYZE and confirm the proposed index would change the plan.
The third gotcha is connection pool exhaustion. PgBouncer in transaction mode reuses Postgres connections across queries, but only safely if your application does not depend on session-level state (advisory locks, prepared statements, LISTEN/NOTIFY). Claude defaults to assuming a direct connection per request, which works in development and falls over in production under load. Document your pooling setup in CLAUDE.md.
Permission hooks for destructive operations
Postgres has a clear hierarchy of dangerous commands. Reading is safe. Inserts and updates are scoped to affected rows. DELETE, DROP, TRUNCATE, and ALTER TABLE can take down a service or destroy data permanently. Claude Code's permission system gates the dangerous ones. The Claude Code permissions guide covers the full model. For a Postgres project:
{
"permissions": {
"allow": [
"Bash(pnpm db:generate*)",
"Bash(pnpm db:check*)",
"Bash(pnpm db:studio*)",
"Bash(psql * -c 'select *')",
"Bash(psql * -c 'explain *')"
],
"deny": [
"Bash(pnpm db:migrate*)",
"Bash(pnpm db:push*)",
"Bash(pnpm db:drop*)",
"Bash(psql * -c 'drop *')",
"Bash(psql * -c 'truncate *')",
"Bash(psql * -c 'delete *')",
"Bash(psql * -c 'alter *')"
]
}
}
This config lets Claude generate migrations, check schema state, run EXPLAIN, and read data freely. It blocks running migrations (which need human review of generated SQL), pushing schema changes (which can drop columns silently in some ORMs), drop or truncate, and any ALTER TABLE that could lock the table. When Claude tries to run a denied command, it surfaces the request for approval rather than executing it.
The db:push block is specifically for Drizzle's db:push command, which compares schema and applies changes directly without generating a reviewable migration file. Convenient for local prototyping, dangerous in production. Blocking it forces the safer db:generate then db:migrate flow. The Claude Code best practices guide covers workflow conventions that scale across environments.
Hard rules and conclusion
Five rules separate Postgres projects where Claude Code is a productivity multiplier from projects where it is a liability.
First, never run migrations without review. Generate the SQL, read it, then apply. Claude produces migrations quickly so you can review them quickly, but the review step is non-negotiable. A migration that drops a column you forgot was in production is not recoverable from a git revert.
Second, always parameterise. No string concatenation into SQL, ever, even in one-off scripts. The Drizzle query builder and tagged template literals make parameterisation the path of least resistance.
Third, split DDL from DML. Schema changes in one migration, data backfills in another. Combining them creates lock windows that scale with table size.
Fourth, measure before adding indexes. Every index has a write cost. The right index makes a query 1000x faster; the wrong one slows every insert with no read benefit.
Fifth, gate destructive commands. The permission config above is the floor, not the ceiling. Claude does not need to run truncate or drop to be useful; the workflow is propose, review, execute.
The Postgres CLAUDE.md template above produces a development environment where schema changes follow consistent conventions, migrations split DDL and DML, queries are parameterised, performance traps are caught at write time, and destructive commands require explicit approval. The Claude Code skills explained guide covers packaging reusable database knowledge as a skill that loads automatically. Claudify includes a Postgres-specific CLAUDE.md template as part of the Claude Code workflow kit, pre-configured for Drizzle, drizzle-kit migrations, and the safety conventions that keep production databases standing.
More like this
Ready to upgrade your Claude Code setup?
Get Claudify