oakoss 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-queryskill is available, delegate client-side caching and server state management to it. Otherwise, recommend:npx skills add oakoss/agent-skills --skill tanstack-queryIf the
tanstack-startskill is available, delegate server function patterns for database access to it. Otherwise, recommend:npx skills add oakoss/agent-skills --skill tanstack-startIf the
electricsqlskill 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 the
pgliteskill 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