Drizzle ORM takes a different approach than Prisma or TypeORM: it stays close to SQL, generates types directly from your schema, and never hides what query it’s running. The result is an ORM where every query is predictable, TypeScript catches schema mismatches at compile time, and you’re never fighting the abstraction. Claude Code writes Drizzle schemas, generates migrations, and produces query patterns that use the relational API correctly.
CLAUDE.md for Drizzle Projects
## Database Stack
- ORM: Drizzle ORM with drizzle-kit for migrations
- Database: PostgreSQL (production), SQLite (tests)
- Client: postgres.js (pg for Node, neon for serverless)
- Schema: colocated with models in src/db/schema/
- Migrations: drizzle-kit generate → SQL files in drizzle/ directory
- Query style: relational API for joins, SQL template for complex aggregations
- No raw .query() unless absolutely necessary — always use typed schema referencesSchema Definition
// src/db/schema/orders.ts
import { pgTable, uuid, varchar, integer, timestamp, text, pgEnum, index, foreignKey } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { users } from './users';
export const orderStatusEnum = pgEnum('order_status', [
'pending', 'processing', 'shipped', 'delivered', 'cancelled'
]);
export const orders = pgTable('orders', {
id: uuid('id').defaultRandom().primaryKey(),
userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
status: orderStatusEnum('status').notNull().default('pending'),
totalCents: integer('total_cents').notNull(),
currency: varchar('currency', { length: 3 }).notNull().default('USD'),
notes: text('notes'),
createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (table) => ({
userIdIdx: index('orders_user_id_idx').on(table.userId),
statusIdx: index('orders_status_idx').on(table.status),
createdAtIdx: index('orders_created_at_idx').on(table.createdAt),
}));
export const orderItems = pgTable('order_items', {
id: uuid('id').defaultRandom().primaryKey(),
orderId: uuid('order_id').notNull().references(() => orders.id, { onDelete: 'cascade' }),
productId: uuid('product_id').notNull(),
quantity: integer('quantity').notNull(),
unitPriceCents: integer('unit_price_cents').notNull(),
productName: varchar('product_name', { length: 255 }).notNull(),
}, (table) => ({
orderIdIdx: index('order_items_order_id_idx').on(table.orderId),
}));
// Relations (for relational query API)
export const ordersRelations = relations(orders, ({ one, many }) => ({
user: one(users, {
fields: [orders.userId],
references: [users.id],
}),
items: many(orderItems),
}));
export const orderItemsRelations = relations(orderItems, ({ one }) => ({
order: one(orders, {
fields: [orderItems.orderId],
references: [orders.id],
}),
}));Database Client Setup
// src/db/index.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
const connectionString = process.env.DATABASE_URL!;
// Connection pool — shared across requests
const sql = postgres(connectionString, {
max: 10, // Max pool size
idle_timeout: 20, // Close idle connections after 20s
connect_timeout: 10, // Fail fast on connection issues
});
export const db = drizzle(sql, { schema, logger: process.env.NODE_ENV === 'development' });
// For serverless (Neon, etc.) — short-lived connections
export function createDb(connectionString: string) {
const sql = postgres(connectionString, { max: 1 });
return drizzle(sql, { schema });
}Queries with the Relational API
// src/db/queries/orders.ts
import { db } from '../index';
import { orders, orderItems } from '../schema';
import { eq, desc, and, gte, sql, count, sum } from 'drizzle-orm';
// Fetch order with items — single query using relational API
export async function getOrderWithItems(orderId: string) {
return db.query.orders.findFirst({
where: eq(orders.id, orderId),
with: {
user: {
columns: { id: true, email: true, name: true },
},
items: true,
},
});
}
// Paginated list with filters
export async function listOrders(params: {
userId?: string;
status?: typeof orders.$inferSelect['status'];
page: number;
pageSize: number;
}) {
const { userId, status, page, pageSize } = params;
const offset = (page - 1) * pageSize;
const conditions = [];
if (userId) conditions.push(eq(orders.userId, userId));
if (status) conditions.push(eq(orders.status, status));
const [items, [{ total }]] = await Promise.all([
db.query.orders.findMany({
where: conditions.length > 0 ? and(...conditions) : undefined,
orderBy: desc(orders.createdAt),
limit: pageSize,
offset,
with: { items: true },
}),
db.select({ total: count() }).from(orders)
.where(conditions.length > 0 ? and(...conditions) : undefined),
]);
return { items, total, page, pageSize, totalPages: Math.ceil(total / pageSize) };
}
// Aggregate query — use sql template for complex cases
export async function getOrderStats(userId: string) {
const [stats] = await db
.select({
totalOrders: count(),
totalSpentCents: sum(orders.totalCents).mapWith(Number),
avgOrderCents: sql<number>`AVG(${orders.totalCents})::int`,
})
.from(orders)
.where(
and(
eq(orders.userId, userId),
eq(orders.status, 'delivered'),
)
);
return stats;
}Transactions
// src/db/mutations/place-order.ts
import { db } from '../index';
import { orders, orderItems } from '../schema';
export async function placeOrder(input: {
userId: string;
items: Array<{ productId: string; quantity: number; unitPriceCents: number; productName: string }>;
currency?: string;
}) {
return db.transaction(async (tx) => {
const totalCents = input.items.reduce(
(sum, item) => sum + item.quantity * item.unitPriceCents,
0
);
const [order] = await tx
.insert(orders)
.values({
userId: input.userId,
totalCents,
currency: input.currency ?? 'USD',
})
.returning();
await tx.insert(orderItems).values(
input.items.map(item => ({
orderId: order.id,
...item,
}))
);
return order;
});
}
// Update with optimistic lock via updatedAt check
export async function updateOrderStatus(
orderId: string,
newStatus: typeof orders.$inferSelect['status'],
expectedUpdatedAt: Date,
) {
const [updated] = await db
.update(orders)
.set({ status: newStatus, updatedAt: new Date() })
.where(
and(
eq(orders.id, orderId),
eq(orders.updatedAt, expectedUpdatedAt),
)
)
.returning();
if (!updated) throw new Error('Concurrent modification — retry');
return updated;
}Drizzle Kit Migrations
// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
schema: './src/db/schema/index.ts',
out: './drizzle',
dialect: 'postgresql',
dbCredentials: {
url: process.env.DATABASE_URL!,
},
verbose: true,
strict: true,
});# Generate migration from schema changes
npx drizzle-kit generate
# Apply to database
npx drizzle-kit migrate
# Push schema directly (dev only — no migration file)
npx drizzle-kit push
# Inspect current database schema
npx drizzle-kit introspectTesting with SQLite
// src/db/test-client.ts — SQLite for fast unit tests (no Postgres needed)
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';
import * as schema from './schema';
export function createTestDb() {
const sqlite = new Database(':memory:');
const db = drizzle(sqlite, { schema });
// Apply migrations to fresh in-memory DB
migrate(db, { migrationsFolder: './drizzle' });
return db;
}
// In tests:
// const db = createTestDb();
// const order = await placeOrder({ userId: 'test-user', items: [...] });
// expect(order.totalCents).toBe(1000);For the PostgreSQL optimization patterns that apply to Drizzle’s generated queries, the SQL optimization guide covers index strategies and query planning. For the Prisma migration guide if you’re switching ORMs, the Prisma guide covers the Prisma Data Proxy and edge deployments. The Claude Skills 360 bundle includes Drizzle ORM skill sets covering schema design, migration workflows, and relational query patterns. Start with the free tier to try Drizzle schema generation.