CLAUDE.md for Drizzle ORM

Why Drizzle Needs Rules That Embrace SQL

Drizzle ORM's philosophy is 'if you know SQL, you know Drizzle' — the query builder maps directly to SQL concepts, schemas are TypeScript code (not a DSL), and the output is predictable SQL. AI assistants don't understand this philosophy — they generate Prisma-style queries (findMany, include, select as object) or fall back to raw SQL, missing Drizzle's type-safe query builder entirely.

Drizzle's key differentiator from Prisma: schemas are regular TypeScript (importable, composable, refactorable), the query builder is SQL-shaped (select().from().where()), and there's no code generation step — types are inferred from the schema at compile time. AI that generates Prisma patterns in Drizzle misses all three advantages.

These rules target Drizzle 0.30+ with drizzle-kit for migrations. They cover PostgreSQL, MySQL, and SQLite — Drizzle supports all three with dialect-specific schema functions.

Rule 1: Schema as TypeScript Code

The rule: 'Define schemas in TypeScript with Drizzle's table builder functions: export const users = pgTable("users", { id: uuid("id").primaryKey().defaultRandom(), name: text("name").notNull(), email: text("email").notNull().unique(), createdAt: timestamp("created_at").defaultNow() }). Schemas are regular TypeScript — import, compose, and refactor like any code. No .prisma files, no code generation, no npx generate.'

For types: 'Drizzle infers TypeScript types from the schema automatically. Use $inferSelect for select types: type User = typeof users.$inferSelect. Use $inferInsert for insert types: type NewUser = typeof users.$inferInsert. These types are always in sync with the schema — no separate interface definitions needed.'

For relations: 'Define relations with the relations() function: export const usersRelations = relations(users, ({ many }) => ({ posts: many(posts) })). Relations are separate from the table definition — they describe how tables connect for the relational query API (.query.users.findMany({ with: { posts: true } })).'

• pgTable/mysqlTable/sqliteTable for schema — regular TypeScript, no DSL
• $inferSelect and $inferInsert for types — always in sync with schema
• relations() for relational queries — separate from table definition
• No code generation step — types inferred at compile time
• Import schemas across files — compose like any TypeScript module

Rule 2: SQL-Like Query Builder

The rule: 'Use Drizzle's query builder for all database operations: db.select().from(users).where(eq(users.email, email)). The builder maps to SQL: select → SELECT, from → FROM, where → WHERE, orderBy → ORDER BY, limit → LIMIT. Use operator functions from drizzle-orm: eq, ne, gt, lt, gte, lte, like, ilike, inArray, and, or. The output SQL is predictable — what you write is what runs.'

For joins: 'Use .innerJoin, .leftJoin, .rightJoin, .fullJoin: db.select().from(users).leftJoin(posts, eq(users.id, posts.authorId)). Joins return flat results — destructure with the table reference: result.users.name, result.posts.title. For nested/grouped results, use the relational query API instead.'

For insert/update/delete: 'db.insert(users).values({ name, email }). db.update(users).set({ name: newName }).where(eq(users.id, id)). db.delete(users).where(eq(users.id, id)). Use .returning() for PostgreSQL to get the affected rows back: db.insert(users).values(data).returning(). All operations are type-safe — the compiler catches wrong column names and type mismatches.'

Rule 3: Relational Query API

The rule: 'Use db.query for relational queries (loading relations): db.query.users.findMany({ with: { posts: true } }). This is Drizzle's equivalent of Prisma's include — it loads related data in optimized queries. Use columns for field selection: db.query.users.findMany({ columns: { id: true, name: true } }). Use where for filtering: db.query.users.findFirst({ where: eq(users.email, email) }).'

For nested relations: 'Load nested relations: db.query.users.findMany({ with: { posts: { with: { comments: true } } } }). Drizzle generates efficient queries — typically one query per relation level with IN clauses, not N+1. Use limit and offset on nested relations: with: { posts: { limit: 10 } }.'

For when to use which API: 'Use the SQL-like builder (db.select().from()) for: complex joins, aggregations, subqueries, and raw SQL needs. Use the relational API (db.query) for: loading entities with relations, CRUD operations, and Prisma-like queries. Both are type-safe — the builder for SQL power, the relational API for convenience.'

• db.query.table.findMany({ with, columns, where }) — relational API
• with: { relation: true } — loads related data efficiently, not N+1
• columns: { id: true, name: true } — select specific fields
• SQL builder for complex joins/aggregations — relational API for CRUD + relations
• Both are type-safe — choose based on query complexity

Rule 4: drizzle-kit for Migrations

The rule: 'Use drizzle-kit for all schema migrations: npx drizzle-kit generate generates SQL migration files from schema changes. npx drizzle-kit push applies schema directly (development only — like Prisma db push). npx drizzle-kit migrate applies migration files in order (production). Migration files are in drizzle/ directory — committed to git.'

For the workflow: 'Change schema.ts → run drizzle-kit generate → review the generated SQL → commit the migration → deploy with drizzle-kit migrate. Always review generated SQL — Drizzle generates the migration, but you verify it. For data migrations, add custom SQL files alongside the generated ones.'

For drizzle.config.ts: 'Configure the migration tool: export default defineConfig({ schema: "./src/db/schema.ts", out: "./drizzle", dialect: "postgresql", dbCredentials: { url: process.env.DATABASE_URL! } }). schema points to your TypeScript schema files. out is the migration output directory. dialect matches your database.'

Rule 5: Drizzle-Specific Patterns

The rule: 'Use db.transaction for atomic operations: await db.transaction(async (tx) => { const user = await tx.insert(users).values(data).returning(); await tx.insert(accounts).values({ userId: user[0].id }); }). Use prepared statements for hot queries: const getUser = db.select().from(users).where(eq(users.id, sql.placeholder("id"))).prepare("getUser"); await getUser.execute({ id }).'

For connection management: 'Use the appropriate driver for your database: @neondatabase/serverless for Neon, postgres for node-postgres, better-sqlite3 for SQLite, mysql2 for MySQL. Create one drizzle instance at startup — don't create per request. For serverless (Vercel, Lambda), use connection pooling or serverless-compatible drivers.'

For custom SQL: 'Use sql template tag for raw SQL fragments within type-safe queries: db.select().from(users).where(sql`${users.name} ILIKE ${`%${search}%`}`). The sql tag handles parameterization — never string-concatenate user input. Use sql for database-specific features (full-text search, JSON operators, window functions) not covered by the builder.'

• db.transaction for atomic multi-step operations
• .prepare() for hot queries — pre-compiled, reusable
• sql template tag for raw fragments — auto-parameterized, never string concat
• One drizzle instance at startup — connection pooling for serverless
• Driver-specific: @neondatabase/serverless, postgres, better-sqlite3, mysql2

Complete Drizzle ORM Rules Template

Consolidated rules for Drizzle ORM projects.

• Schema as TypeScript: pgTable/mysqlTable — $inferSelect/$inferInsert for types
• SQL-like builder: select().from().where(eq()) — predictable SQL output
• Relational API: db.query.table.findMany({ with, columns }) — efficient relation loading
• drizzle-kit generate for migrations — push for dev only — migrate for production
• Review generated SQL — commit migrations to git — custom SQL for data migrations
• db.transaction for atomic operations — .prepare() for hot queries
• sql template tag for raw fragments — auto-parameterized, type-safe
• drizzle.config.ts for tool config — one db instance at startup — pool for serverless