drizzle-orm

What this skill does

# 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](references/schema-definition.md)
- [Relational queries, SQL-like API, joins, subqueries, and aggregations](references/queries.md)
- [Insert, update, delete, upsert, and transactions](references/mutations.md)
- [Relations: one, many, nested with clauses, self-referencing](references/relations.md)
- [Migrations: drizzle-kit generate, migrate, push, pull, studio](references/migrations.md)
- [Filter operators: eq, ne, gt, lt, like, inArray, sql template](references/filters-and-operators.md)
- [Views, materialized views, generated columns, check constraints, $count, batch API](references/views-and-advanced.md)
- [ElectricSQL + PGlite integration: driver setup, schema-to-shape mapping, type inference, local sync](references/electric-integration.md)