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 thetanstack-queryskill is available, delegate client-side caching and server state management to it. Otherwise, recommend:npx skills add oakoss/agent-skills --skill tanstack-queryIf thetanstack-startskill is available, delegate server function patterns for database access to it. Otherwise, recommend:npx skills add oakoss/agent-skills --skill tanstack-startIf theelectricsqlskill 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 -yIf thepgliteskill 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-zodto generate Zod schemas from Drizzle tables for input validation - Query pattern discovery: Use
Exploreagent - Code review: Delegate to
code-revieweragent
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