drizzle-orm

Drizzle ORM

Overview

Drizzle ORM is a lightweight, type-safe TypeScript ORM that maps directly to SQL for PostgreSQL, MySQL, and SQLite. It provides both a SQL-like query builder and a relational queries API, with zero dependencies and full serverless compatibility. Use Drizzle when you need compile-time type safety with SQL-level control; avoid it when you need a full active-record ORM with automatic migrations (use Prisma) or when working with MongoDB/NoSQL databases.

Quick Reference

Pattern	API	Key Points
Schema definition	pgTable('name', { columns }, (t) => [indexes])	Third arg returns array of indexes/constraints
Column types	text(), integer(), boolean(), timestamp()	Import from drizzle-orm/pg-core
Type inference	typeof table.$inferSelect, $inferInsert	Derive TS types directly from schema
Relational queries	db.query.table.findMany({ with, where })	Requires schema passed to drizzle() client
SQL-like queries	db.select().from(table).where()	Chainable, returns array of rows
Insert	db.insert(table).values({}).returning()	.returning() for getting inserted rows
Update	db.update(table).set({}).where().returning()	Always include .where() to avoid full-table updates
Delete	db.delete(table).where()	Always include .where() to avoid full-table deletes
Upsert	.onConflictDoUpdate({ target, set })	Chain after .insert().values()
Transactions	db.transaction(async (tx) => { ... })	Auto-rollback on thrown errors
Filters	eq(), and(), or(), inArray(), sql\``	Import operators from drizzle-orm
Relations	relations(table, ({ one, many }) => ({}))	Declares logical relations for relational queries
Generate migrations	drizzle-kit generate	Creates SQL migration files from schema diff
Apply migrations	drizzle-kit migrate or migrate() in code	Applies pending migrations to database
Push schema	drizzle-kit push	Direct schema push without migration files
Prepared statements	db.select().from(t).where(eq(t.id, sql.placeholder('id'))).prepare()	Reusable parameterized queries
Views	pgView('name').as(qb => ...)	Regular and materialized views
$count utility	db.$count(table, filter?)	Shorthand count, usable as subquery
Generated columns	text().generatedAlwaysAs(() => sql\...`)`	Computed columns (virtual or stored)
Check constraints	check('name', sql\condition`)`	Row-level validation at database level

Common Mistakes

Mistake	Correct Pattern
Missing .returning() on insert/update	Chain .returning() to get back inserted/updated rows
Omitting .where() on update/delete	Always provide .where() to avoid affecting all rows
Using any for query result types	Use $inferSelect / $inferInsert for type-safe results
Not passing schema to drizzle() client	Pass { schema } to enable db.query relational API
Forgetting indexes on foreign key columns	Add explicit indexes for columns used in joins/lookups
Wrapping related writes without a transaction	Use db.transaction() for multi-table mutations
Using raw column names with casing: 'snake_case'	With casing option, define columns in camelCase without name arg
Declaring relations but missing the inverse	Both sides of a relation need declarations for relational queries
Using serial() in new PostgreSQL schemas	Prefer integer().generatedAlwaysAsIdentity() (modern approach)
Not handling findFirst returning undefined	Check for undefined before accessing properties
Importing column types from wrong dialect module	Use drizzle-orm/pg-core for PostgreSQL, mysql-core, sqlite-core
Using .references() instead of relations()	.references() is SQL foreign key; relations() is for query API

Delegation

If the tanstack-query skill is available, delegate client-side caching and server state management to it. Otherwise, recommend: npx skills add oakoss/agent-skills --skill tanstack-query

If the tanstack-start skill is available, delegate server function patterns for database access to it. Otherwise, recommend: npx skills add oakoss/agent-skills --skill tanstack-start

If the electricsql skill is available, delegate Electric setup, shape configuration, and write patterns to it. Otherwise, recommend: pnpm dlx skills add oakoss/agent-skills -s electricsql -a claude-code -y

If the pglite skill is available, delegate PGlite-specific configuration, extensions, and browser storage patterns to it. Otherwise, recommend: pnpm dlx skills add oakoss/agent-skills -s pglite -a claude-code -y

• Schema validation: Use drizzle-zod to generate Zod schemas from Drizzle tables for input validation
• Query pattern discovery: Use Explore agent
• Code review: Delegate to code-reviewer agent

References

• Schema definition, column types, constraints, indexes, and type inference
• Relational queries, SQL-like API, joins, subqueries, and aggregations
• Insert, update, delete, upsert, and transactions
• Relations: one, many, nested with clauses, self-referencing
• Migrations: drizzle-kit generate, migrate, push, pull, studio
• Filter operators: eq, ne, gt, lt, like, inArray, sql template
• Views, materialized views, generated columns, check constraints, $count, batch API
• ElectricSQL + PGlite integration: driver setup, schema-to-shape mapping, type inference, local sync