README.md
# drizzle-orm
> TypeScript-first SQL ORM with zero dependencies, compile-time type safety, and SQL-like syntax. Optimized for edge runtimes, serverless environments, and PostgreSQL.
## What it does
`drizzle-orm` turns your agent into a Drizzle expert. Unlike ORMs that hide SQL, Drizzle surfaces it — queries read like SQL but are fully type-checked at compile time with zero runtime overhead. This skill covers:
- **Schema definition** — `pgTable`, `pgEnum`, `pgSchema`, `pgView`, column types, defaults, generated columns, constraints
- **Relations** — one-to-one, one-to-many, many-to-many with the relational query builder (`db.query.users.findMany({ with: { posts: true } })`)
- **CRUD + joins** — from simple inserts to lateral joins, CTEs, subqueries, window functions
- **Migrations** — `drizzle-kit generate` / `migrate` / `push` / `pull`, programmatic migrations, production patterns, team workflows
- **Advanced PostgreSQL** — branded types, enums, arrays, JSONB patterns, composite keys, foreign key actions, self-referencing tables, multi-schema architecture, materialized views
- **Performance** — connection pooling, prepared statements, N+1 prevention, index strategy, edge runtime/serverless integration, bundle size
- **Prisma migration** — feature-by-feature comparison, performance benchmarks, schema/query syntax differences, full migration playbook
It triggers automatically when code imports `drizzle-orm`, `drizzle-kit`, or `drizzle-orm/pg-core`.
## When to use it
Invoke this skill when you hear:
- *"Set up Drizzle with PostgreSQL in my Next.js app."*
- *"Design a schema for a multi-tenant SaaS with row-level security."*
- *"Write a Drizzle query that joins users with their most recent post."*
- *"Generate and run migrations for this schema change."*
- *"Migrate our Prisma codebase to Drizzle."*
- *"Optimize this Drizzle query — it's doing N+1."*
- *"Set up Drizzle on a Vercel edge function with Neon."*
## Example walkthrough
**Prompt**
> Define a `posts` table with a many-to-one relation to `users`, then write a query that gets the latest 10 posts with their author info.
**What the skill produces**
**Schema**
```typescript
import { pgTable, text, timestamp, integer } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
export const users = pgTable('users', {
id: integer().primaryKey().generatedAlwaysAsIdentity(),
name: text().notNull(),
email: text().notNull().unique(),
});
export const posts = pgTable('posts', {
id: integer().primaryKey().generatedAlwaysAsIdentity(),
title: text().notNull(),
body: text().notNull(),
authorId: integer().references(() => users.id).notNull(),
createdAt: timestamp().defaultNow().notNull(),
});
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts),
}));
export const postsRelations = relations(posts, ({ one }) => ({
author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));
```
**Query (relational query builder — preferred)**
```typescript
const latest = await db.query.posts.findMany({
with: { author: { columns: { name: true, email: true } } },
orderBy: (posts, { desc }) => [desc(posts.createdAt)],
limit: 10,
});
// Fully typed: latest[0].author.name is inferred correctly
```
**Query (core query builder — for when you need SQL control)**
```typescript
const latest = await db
.select({
post: posts,
author: { name: users.name, email: users.email },
})
.from(posts)
.leftJoin(users, eq(users.id, posts.authorId))
.orderBy(desc(posts.createdAt))
.limit(10);
```
The skill will also explain **when to use which builder**: relational for clean object shapes, core for fine-grained SQL control (CTEs, lateral joins, window functions).
## Installation
```bash
npx skills add thatjuan/agent-skills --skill drizzle-orm
```
## Bundled resources
| File | Purpose |
|------|---------|
| `SKILL.md` | Setup, schema definition, column types, relations, CRUD queries, transactions, migration basics, views, CTEs |
| `references/pg-migrations.md` | `drizzle.config.ts` reference, migration strategies (generate/migrate/push/pull), programmatic migrations, custom migrations, production patterns, team workflows |
| `references/advanced-schemas.md` | Branded types, PostgreSQL enums, arrays, JSONB patterns, composite keys, foreign key actions, self-referencing tables, multi-schema architecture, views, materialized views |
| `references/query-patterns.md` | Subqueries, CTEs, raw SQL, prepared statements, dynamic queries, aggregations, batch operations, cursor pagination, streaming, lateral joins |
| `references/performance.md` | Connection pooling, prepared statements, N+1 prevention, query optimization, index strategy, edge runtime/serverless integration, bundle size |
| `references/vs-prisma.md` | Feature comparison, performance benchmarks, schema/query syntax comparison, migration system differences, migration guide from Prisma to Drizzle |
## Tips
- **`push` is for prototyping, `migrate` is for production.** `drizzle-kit push` writes schema directly to the DB (great for local iteration). `drizzle-kit generate` + `migrate` produces versioned SQL files (the right pattern for team workflows).
- **Prepare hot queries.** `db.select().from(users).where(eq(users.id, placeholder('id'))).prepare('get_user')` reuses the query plan — significant speedup on serverless cold paths.
- **The relational query builder isn't slow.** It generates a single optimized query with JSON aggregation — not N+1. Use it freely for nested reads.
- **Drizzle Studio > hand-rolling admin UIs.** `npx drizzle-kit studio` spins up a local DB browser that understands your schema.
- **Edge runtimes.** Prefer `drizzle-orm/neon-http` or `drizzle-orm/postgres-js` over `node-postgres` in edge environments — the former two are edge-compatible.
## Related skills
- None in this repo yet — pair with `claude-api` for AI-backed DB work, or `temporal` for long-running transactional workflows.
references/advanced-schemas.md
# Advanced Schemas
Complex PostgreSQL schema patterns with Drizzle ORM.
## Table of Contents
- [Custom Types and Branded Types](#custom-types-and-branded-types)
- [PostgreSQL Enums](#postgresql-enums)
- [Arrays](#arrays)
- [JSON and JSONB Patterns](#json-and-jsonb-patterns)
- [Composite Primary Keys](#composite-primary-keys)
- [Foreign Key Actions](#foreign-key-actions)
- [Self-Referencing Tables](#self-referencing-tables)
- [Multi-Schema Architecture](#multi-schema-architecture)
- [Views and Materialized Views](#views-and-materialized-views)
- [Default Value Patterns](#default-value-patterns)
- [Check Constraints](#check-constraints)
## Custom Types and Branded Types
### Branded Types for Type Safety
```typescript
type UserId = number & { __brand: 'user_id' };
type PostId = number & { __brand: 'post_id' };
export const users = pgTable('users', {
id: serial().$type<UserId>().primaryKey(),
name: text().notNull(),
});
export const posts = pgTable('posts', {
id: serial().$type<PostId>().primaryKey(),
authorId: integer().$type<UserId>().notNull().references(() => users.id),
});
// Compiler prevents: posts.authorId = somePostId (type mismatch)
```
### Custom Column Defaults
```typescript
import { sql } from 'drizzle-orm';
export const items = pgTable('items', {
id: uuid().defaultRandom().primaryKey(),
slug: text().$defaultFn(() => generateSlug()), // Runtime JS default
code: varchar({ length: 10 }).$defaultFn(() => nanoid(10)),
createdAt: timestamp().default(sql`NOW()`),
sortOrder: integer().default(sql`nextval('items_sort_seq')`),
});
```
### $onUpdate for Automatic Updates
```typescript
export const users = pgTable('users', {
id: serial().primaryKey(),
name: text().notNull(),
updatedAt: timestamp().$onUpdate(() => new Date()),
});
```
## PostgreSQL Enums
### Basic Enum
```typescript
import { pgEnum, pgTable, serial } from 'drizzle-orm/pg-core';
export const statusEnum = pgEnum('status', ['active', 'inactive', 'pending']);
export const users = pgTable('users', {
id: serial().primaryKey(),
status: statusEnum().default('pending').notNull(),
});
```
### Enum in Custom Schema
```typescript
import { pgSchema } from 'drizzle-orm/pg-core';
export const mySchema = pgSchema('my_schema');
export const colorEnum = mySchema.enum('color', ['red', 'green', 'blue']);
export const products = mySchema.table('products', {
id: serial().primaryKey(),
color: colorEnum().default('red'),
});
```
### Text Column with Enum Inference
For lightweight enum-like behavior without a PostgreSQL enum type:
```typescript
export const users = pgTable('users', {
role: text({ enum: ['admin', 'user', 'guest'] }).default('user'),
});
// TypeScript type: 'admin' | 'user' | 'guest'
// Database type: text (no CREATE TYPE)
```
## Arrays
PostgreSQL array columns using the `array()` wrapper:
```typescript
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
export const posts = pgTable('posts', {
id: serial().primaryKey(),
tags: text().array(), // text[]
scores: integer().array(), // integer[]
matrix: integer().array().array(), // integer[][]
});
// Querying arrays
import { sql, arrayContains, arrayContained, arrayOverlaps } from 'drizzle-orm';
await db.select().from(posts).where(arrayContains(posts.tags, ['typescript']));
await db.select().from(posts).where(arrayOverlaps(posts.tags, ['ts', 'js']));
await db.select().from(posts).where(
sql`${posts.tags} @> ARRAY['typescript']::text[]`
);
```
## JSON and JSONB Patterns
### Typed JSON Columns
```typescript
interface UserPreferences {
theme: 'light' | 'dark';
locale: string;
notifications: { email: boolean; push: boolean };
}
export const users = pgTable('users', {
id: serial().primaryKey(),
preferences: jsonb().$type<UserPreferences>().default({
theme: 'light',
locale: 'en',
notifications: { email: true, push: false },
}),
});
```
### Querying JSONB
```typescript
import { sql } from 'drizzle-orm';
// Access nested fields
await db.select({
id: users.id,
theme: sql<string>`${users.preferences}->>'theme'`,
}).from(users);
// Filter by JSON field
await db.select().from(users).where(
sql`${users.preferences}->>'theme' = 'dark'`
);
// JSONB containment
await db.select().from(users).where(
sql`${users.preferences} @> '{"theme": "dark"}'::jsonb`
);
```
## Composite Primary Keys
```typescript
import { pgTable, integer, text, primaryKey, timestamp } from 'drizzle-orm/pg-core';
// Junction table
export const userRoles = pgTable('user_roles', {
userId: integer().notNull().references(() => users.id, { onDelete: 'cascade' }),
roleId: integer().notNull().references(() => roles.id, { onDelete: 'cascade' }),
assignedAt: timestamp().defaultNow(),
}, (table) => [
primaryKey({ columns: [table.userId, table.roleId] }),
]);
// With custom name
export const bookAuthors = pgTable('book_authors', {
bookId: integer().notNull(),
authorId: integer().notNull(),
}, (table) => [
primaryKey({ name: 'book_author_pk', columns: [table.bookId, table.authorId] }),
]);
```
## Foreign Key Actions
```typescript
export const posts = pgTable('posts', {
id: serial().primaryKey(),
authorId: integer().notNull()
.references(() => users.id, {
onDelete: 'cascade', // Delete posts when user deleted
onUpdate: 'cascade', // Update FK when user ID changes
}),
});
// Standalone foreign key declaration (multi-column)
export const orderItems = pgTable('order_items', {
orderId: integer().notNull(),
productId: integer().notNull(),
warehouseId: integer().notNull(),
}, (table) => [
foreignKey({
name: 'inventory_fk',
columns: [table.productId, table.warehouseId],
foreignColumns: [inventory.productId, inventory.warehouseId],
}).onDelete('restrict').onUpdate('cascade'),
]);
```
### Action Reference
| Action | Behavior |
|--------|----------|
| `cascade` | Delete/update child rows automatically |
| `restrict` | Prevent parent modification (checked immediately) |
| `no action` | Prevent parent modification (checked at end of transaction, default) |
| `set null` | Set FK column to NULL on parent deletion |
| `set default` | Set FK column to its default on parent deletion |
## Self-Referencing Tables
```typescript
import { AnyPgColumn } from 'drizzle-orm/pg-core';
export const categories = pgTable('categories', {
id: serial().primaryKey(),
name: text().notNull(),
parentId: integer().references((): AnyPgColumn => categories.id),
});
// Query with self-join
import { alias } from 'drizzle-orm/pg-core';
const parent = alias(categories, 'parent');
await db.select({
category: categories.name,
parentName: parent.name,
}).from(categories)
.leftJoin(parent, eq(categories.parentId, parent.id));
```
## Multi-Schema Architecture
### Defining Multiple Schemas
```typescript
import { pgSchema, pgTable, serial, text } from 'drizzle-orm/pg-core';
// Public schema (default)
export const publicUsers = pgTable('users', {
id: serial().primaryKey(),
name: text().notNull(),
});
// Tenant schema
export const tenantSchema = pgSchema('tenant');
export const tenantOrders = tenantSchema.table('orders', {
id: serial().primaryKey(),
userId: integer().notNull(),
});
// Auth schema
export const authSchema = pgSchema('auth');
export const sessions = authSchema.table('sessions', {
id: uuid().defaultRandom().primaryKey(),
userId: integer().notNull(),
expiresAt: timestamp().notNull(),
});
```
### Cross-Schema References
```typescript
export const tenantOrders = tenantSchema.table('orders', {
id: serial().primaryKey(),
userId: integer().notNull().references(() => publicUsers.id),
});
```
### drizzle-kit Schema Filtering
```typescript
// drizzle.config.ts
export default defineConfig({
schemaFilter: ['public', 'tenant'], // manage these schemas
// tablesFilter: ['!_prisma_*'], // exclude Prisma tables
});
```
## Views and Materialized Views
### Regular Views
```typescript
import { pgView } from 'drizzle-orm/pg-core';
export const activeUsers = pgView('active_users').as((qb) =>
qb.select({
id: users.id,
name: users.name,
email: users.email,
}).from(users).where(eq(users.isActive, true))
);
// Query like a table
await db.select().from(activeUsers);
```
### Materialized Views
```typescript
import { pgMaterializedView } from 'drizzle-orm/pg-core';
export const userStats = pgMaterializedView('user_stats').as((qb) =>
qb.select({
userId: users.id,
postCount: count(posts.id),
lastPostAt: max(posts.createdAt),
}).from(users)
.leftJoin(posts, eq(users.id, posts.authorId))
.groupBy(users.id)
);
// With storage parameters
export const heavyStats = pgMaterializedView('heavy_stats')
.with({ fillfactor: 90, autovacuum_enabled: true })
.tablespace('fast_ssd')
.as((qb) => { /* ... */ });
// Refresh
await db.refreshMaterializedView(userStats);
await db.refreshMaterializedView(userStats).concurrently();
await db.refreshMaterializedView(userStats).withNoData();
```
### Existing Views
For views managed outside Drizzle (prevents drizzle-kit from generating CREATE VIEW):
```typescript
export const legacyReport = pgView('legacy_report', {
id: serial(),
total: numeric(),
period: text(),
}).existing();
```
## Default Value Patterns
```typescript
import { sql } from 'drizzle-orm';
export const records = pgTable('records', {
// Static defaults
status: text().default('pending'),
count: integer().default(0),
isActive: boolean().default(true),
// SQL expression defaults
id: uuid().default(sql`gen_random_uuid()`),
createdAt: timestamp().default(sql`NOW()`),
sortKey: integer().default(sql`nextval('sort_seq')`),
// Runtime JS defaults (executed at insert time)
slug: text().$defaultFn(() => generateSlug()),
code: text().$defaultFn(() => nanoid(12)),
// Auto-generated UUID
publicId: uuid().defaultRandom(),
});
```
## Check Constraints
```typescript
import { check } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
export const products = pgTable('products', {
id: serial().primaryKey(),
name: text().notNull(),
price: numeric({ precision: 10, scale: 2 }).notNull(),
discountPrice: numeric({ precision: 10, scale: 2 }),
quantity: integer().default(0),
}, (table) => [
check('price_positive', sql`${table.price} > 0`),
check('quantity_non_negative', sql`${table.quantity} >= 0`),
check('discount_less_than_price',
sql`${table.discountPrice} IS NULL OR ${table.discountPrice} < ${table.price}`
),
]);
```
references/performance.md
# Performance
Connection pooling, query optimization, and runtime integration patterns for Drizzle ORM with PostgreSQL.
## Table of Contents
- [Connection Pooling](#connection-pooling)
- [Prepared Statements](#prepared-statements)
- [N+1 Prevention](#n1-prevention)
- [Query Optimization](#query-optimization)
- [Index Strategy](#index-strategy)
- [Edge Runtime and Serverless](#edge-runtime-and-serverless)
- [Bundle Size](#bundle-size)
- [Monitoring](#monitoring)
## Connection Pooling
### node-postgres Pool
```typescript
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 20, // max connections
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 5000,
});
const db = drizzle({ client: pool });
```
### postgres.js
```typescript
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
const client = postgres(process.env.DATABASE_URL!, {
max: 20,
idle_timeout: 30,
connect_timeout: 10,
});
const db = drizzle(client);
```
### External Poolers (PgBouncer, Supabase Pooler)
When using an external connection pooler, connect through the pooler URL and configure the client for single connections:
```typescript
// Through PgBouncer (transaction mode)
const pool = new Pool({
connectionString: process.env.PGBOUNCER_URL,
max: 1, // pooler manages connections
});
const db = drizzle({ client: pool });
```
### Neon Serverless
```typescript
import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';
// HTTP mode — one query per request, no persistent connection
const sql = neon(process.env.DATABASE_URL!);
const db = drizzle(sql);
// WebSocket mode — for transactions and multiple queries
import { drizzle } from 'drizzle-orm/neon-serverless';
import { Pool } from '@neondatabase/serverless';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle({ client: pool });
```
## Prepared Statements
Prepared statements eliminate repeated SQL parsing. Drizzle concatenates SQL once and reuses the compiled query plan.
```typescript
// Define once
const getUserById = db.select().from(users)
.where(eq(users.id, sql.placeholder('id')))
.prepare('get_user_by_id');
// Execute many times (SQL parsing happens once)
const user1 = await getUserById.execute({ id: 1 });
const user2 = await getUserById.execute({ id: 2 });
```
### When to Use Prepared Statements
| Scenario | Benefit |
|----------|---------|
| Hot path queries (auth, user lookup) | Eliminates repeated SQL generation |
| Complex joins / CTEs | Avoids expensive query planning on each call |
| High-throughput endpoints | Reduces CPU overhead |
| Queries executed in loops | Prevents N repeated preparations |
### When to Skip
- One-off admin queries
- Dynamic queries where the SQL structure changes per request
- Queries used only during initialization
## N+1 Prevention
### Problem: N+1 with Manual Joins
```typescript
// BAD: N+1 — 1 query for users + N queries for posts
const users = await db.select().from(usersTable);
for (const user of users) {
const posts = await db.select().from(postsTable)
.where(eq(postsTable.authorId, user.id));
}
```
### Solution 1: Relational Query API
The relational query builder fetches nested data in a single SQL query using lateral joins:
```typescript
// GOOD: single query with nested relations
const usersWithPosts = await db.query.users.findMany({
with: { posts: true },
});
```
### Solution 2: Explicit JOIN
```typescript
// GOOD: single query, manual aggregation
const rows = await db.select({ user: users, post: posts })
.from(users)
.leftJoin(posts, eq(users.id, posts.authorId));
// Reduce to nested structure
const result = rows.reduce<Record<number, { user: User; posts: Post[] }>>((acc, row) => {
if (!acc[row.user.id]) acc[row.user.id] = { user: row.user, posts: [] };
if (row.post) acc[row.user.id].posts.push(row.post);
return acc;
}, {});
```
### Solution 3: Batch Loading with IN
```typescript
// GOOD: 2 queries total
const users = await db.select().from(usersTable);
const userIds = users.map(u => u.id);
const posts = await db.select().from(postsTable)
.where(inArray(postsTable.authorId, userIds));
```
## Query Optimization
### Select Only Needed Columns
```typescript
// BAD: fetches all columns
await db.select().from(users);
// GOOD: fetches only needed columns
await db.select({ id: users.id, name: users.name }).from(users);
// GOOD: exclude sensitive/large columns
const { password, bio, ...cols } = getTableColumns(users);
await db.select(cols).from(users);
```
### Use Pagination
```typescript
// Offset-based (simple, slower for deep pages)
await db.select().from(users).limit(20).offset(page * 20);
// Cursor-based (consistent performance)
await db.select().from(users)
.where(gt(users.id, lastId))
.orderBy(asc(users.id))
.limit(20);
```
### Use Transactions for Multi-Step Writes
Transactions reduce round-trips and ensure atomicity:
```typescript
await db.transaction(async (tx) => {
const [user] = await tx.insert(users).values({ name: 'John' }).returning();
await tx.insert(profiles).values({ userId: user.id, bio: '...' });
await tx.insert(auditLog).values({ action: 'user_created', targetId: user.id });
});
```
### Avoid Unnecessary Queries
```typescript
// BAD: query + update
const user = await db.select().from(users).where(eq(users.id, 1));
if (user) await db.update(users).set({ lastLogin: new Date() }).where(eq(users.id, 1));
// GOOD: single conditional update
await db.update(users)
.set({ lastLogin: new Date() })
.where(eq(users.id, 1))
.returning();
```
## Index Strategy
### Foreign Key Indexes
PostgreSQL does not auto-index foreign key columns. Index them explicitly:
```typescript
export const posts = pgTable('posts', {
id: serial().primaryKey(),
authorId: integer().notNull().references(() => users.id),
categoryId: integer().references(() => categories.id),
}, (table) => [
index('posts_author_idx').on(table.authorId),
index('posts_category_idx').on(table.categoryId),
]);
```
### Composite Indexes for Common Queries
```typescript
export const events = pgTable('events', {
id: serial().primaryKey(),
userId: integer().notNull(),
type: text().notNull(),
createdAt: timestamp().defaultNow(),
}, (table) => [
// Covers: WHERE userId = ? AND type = ? ORDER BY createdAt
index('events_user_type_created').on(table.userId, table.type, table.createdAt),
]);
```
### Unique Indexes
```typescript
export const users = pgTable('users', {
id: serial().primaryKey(),
email: varchar({ length: 255 }).notNull(),
}, (table) => [
uniqueIndex('users_email_idx').on(table.email),
]);
```
## Edge Runtime and Serverless
### Bundle Size Comparison
| ORM | Bundle Size | Cold Start |
|-----|-------------|------------|
| Drizzle | ~35KB | ~10ms |
| Prisma | ~230KB | ~250ms |
| TypeORM | ~200KB | ~150ms |
### Cloudflare Workers / Vercel Edge
```typescript
// Neon HTTP — stateless, edge-compatible
import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';
const sql = neon(process.env.DATABASE_URL!);
const db = drizzle(sql);
export default {
async fetch(request: Request) {
const users = await db.select().from(usersTable);
return Response.json(users);
},
};
```
### PGlite (Embedded PostgreSQL)
```typescript
import { drizzle } from 'drizzle-orm/pglite';
import { PGlite } from '@electric-sql/pglite';
const client = new PGlite();
const db = drizzle(client);
```
### Serverless Connection Management
For serverless environments, the connection pool configuration differs:
```typescript
// Lambda / Vercel Functions
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 1, // single connection per function instance
});
// Use an external pooler (PgBouncer, Neon, Supabase) for connection reuse
```
## Bundle Size
Drizzle has zero runtime dependencies. The bundle includes only what is imported:
```typescript
// Minimal import — only what is used
import { drizzle } from 'drizzle-orm/node-postgres';
import { eq } from 'drizzle-orm';
import { pgTable, serial, text } from 'drizzle-orm/pg-core';
```
Tree-shaking eliminates unused dialect code. Only the PostgreSQL core is bundled when using `drizzle-orm/pg-core`.
## Monitoring
### Query Logging
```typescript
import { drizzle } from 'drizzle-orm/node-postgres';
const db = drizzle({
client: pool,
logger: true, // logs all queries to console
});
// Custom logger
const db = drizzle({
client: pool,
logger: {
logQuery(query, params) {
console.log({ query, params, timestamp: Date.now() });
},
},
});
```
### Performance Timing
```typescript
const start = performance.now();
const result = await db.select().from(users);
const duration = performance.now() - start;
console.log(`Query took ${duration.toFixed(2)}ms, ${result.length} rows`);
```
references/pg-migrations.md
# PostgreSQL Migrations
Complete guide to managing PostgreSQL schema changes with drizzle-kit.
## Table of Contents
- [drizzle.config.ts Reference](#drizzleconfigt-reference)
- [Migration Strategies](#migration-strategies)
- [Migration Workflow](#migration-workflow)
- [Programmatic Migrations](#programmatic-migrations)
- [Custom Migrations](#custom-migrations)
- [Production Patterns](#production-patterns)
- [Team Workflows](#team-workflows)
- [Troubleshooting](#troubleshooting)
## drizzle.config.ts Reference
```typescript
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
// Required
dialect: 'postgresql',
schema: './src/db/schema.ts',
// Schema path patterns
// schema: './src/db/schema/*.ts', // glob pattern
// schema: ['./src/users.ts', './src/posts.ts'], // array of files
// Output directory for migration files (default: './drizzle')
out: './drizzle',
// Database connection
dbCredentials: {
url: process.env.DATABASE_URL!,
// OR individual fields:
// host: 'localhost',
// port: 5432,
// user: 'postgres',
// password: 'password',
// database: 'mydb',
// ssl: true,
},
// Driver override (auto-detected from dialect in most cases)
// driver: 'pglite' | 'aws-data-api',
// Migration tracking
migrations: {
table: '__drizzle_migrations', // migration log table name
schema: 'drizzle', // schema for migration table (PG only)
prefix: 'timestamp', // 'timestamp' | 'supabase' | 'unix' | 'none'
},
// Table/schema filtering
tablesFilter: ['users', 'posts', 'project_*'], // glob patterns
schemaFilter: ['public', 'tenant_*'], // default: ['public']
// Extension filtering (ignore tables created by extensions)
extensionsFilters: ['postgis'],
// Introspection settings (for `pull` command)
introspect: {
casing: 'camel', // 'preserve' | 'camel'
},
// Role management (Neon/Supabase)
entities: {
roles: {
provider: 'neon', // 'neon' | 'supabase'
// include: ['specific_role'],
// exclude: ['excluded_role'],
},
},
// Behavior flags
strict: false, // require confirmation before push (default: false)
verbose: true, // print detailed SQL (default: true)
breakpoints: true, // insert statement separators (default: true)
});
```
### Multi-Environment Configuration
```bash
# Dev config
npx drizzle-kit generate --config=drizzle-dev.config.ts
# Production config
npx drizzle-kit migrate --config=drizzle-prod.config.ts
```
## Migration Strategies
### Strategy 1: Generate + Migrate (Recommended for Production)
Schema is the source of truth. drizzle-kit generates SQL migration files, which are version-controlled and applied explicitly.
```bash
# 1. Edit schema files
# 2. Generate migration SQL
npx drizzle-kit generate
# 3. Review generated SQL in ./drizzle/XXXX_migration_name/migration.sql
# 4. Apply to database
npx drizzle-kit migrate
```
Each `generate` run creates a timestamped directory:
```
drizzle/
├── 0000_initial/
│ ├── migration.sql
│ └── snapshot.json
├── 0001_add_posts/
│ ├── migration.sql
│ └── snapshot.json
└── meta/
└── _journal.json
```
The `snapshot.json` records schema state. The `_journal.json` tracks migration order. Migration files are immutable once applied — new changes require new migrations.
### Strategy 2: Push (Development / Prototyping)
Applies schema changes directly without generating migration files. No audit trail.
```bash
npx drizzle-kit push
```
Useful for:
- Rapid prototyping
- Local development iterations
- Databases that can be recreated from scratch
### Strategy 3: Pull (Database-First)
The database is the source of truth. Extract the schema into TypeScript files.
```bash
npx drizzle-kit pull
```
Generates TypeScript schema files from the current database state. Useful for:
- Existing databases managed by external tools
- Reverse-engineering a database into Drizzle
- One-time import before switching to code-first
### Strategy 4: Export (External Tools)
Export schema as SQL for use with external migration tools (Atlas, Liquibase, Flyway).
```bash
npx drizzle-kit export
```
## Migration Workflow
### Generating Migrations
```bash
# Standard generation
npx drizzle-kit generate
# With custom name
npx drizzle-kit generate --name=add-user-roles
# Custom empty migration for manual SQL
npx drizzle-kit generate --custom --name=seed-users
```
#### Rename Detection
When drizzle-kit detects a column or table rename, it prompts interactively to confirm intent, preventing accidental data loss from misidentified renames vs. drop+create.
### Applying Migrations
```bash
# Apply all pending migrations
npx drizzle-kit migrate
```
drizzle-kit tracks applied migrations in the `__drizzle_migrations` table (configurable). Each migration runs once.
### Verifying Migrations
```bash
# Check for race conditions in generated migrations
npx drizzle-kit check
# Upgrade migration snapshots (after drizzle-kit version upgrade)
npx drizzle-kit up
```
### Visual Database Browser
```bash
npx drizzle-kit studio
```
Opens Drizzle Studio — a web-based GUI for browsing and editing database data.
## Programmatic Migrations
Apply migrations at application startup or in CI/CD pipelines:
### node-postgres
```typescript
import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
const db = drizzle(process.env.DATABASE_URL!);
await migrate(db, { migrationsFolder: './drizzle' });
```
### postgres.js
```typescript
import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';
const migrationClient = postgres(process.env.DATABASE_URL!, { max: 1 });
const db = drizzle(migrationClient);
await migrate(db, { migrationsFolder: './drizzle' });
await migrationClient.end();
```
### Neon Serverless
```typescript
import { drizzle } from 'drizzle-orm/neon-http';
import { migrate } from 'drizzle-orm/neon-http/migrator';
import { neon } from '@neondatabase/serverless';
const sql = neon(process.env.DATABASE_URL!);
const db = drizzle(sql);
await migrate(db, { migrationsFolder: './drizzle' });
```
### Vercel Postgres
```typescript
import { drizzle } from 'drizzle-orm/vercel-postgres';
import { migrate } from 'drizzle-orm/vercel-postgres/migrator';
import { sql } from '@vercel/postgres';
const db = drizzle(sql);
await migrate(db, { migrationsFolder: './drizzle' });
```
## Custom Migrations
For operations drizzle-kit cannot auto-generate (data migrations, seeding, custom DDL):
```bash
npx drizzle-kit generate --custom --name=seed-users
```
This creates an empty migration file for manual SQL:
```sql
-- drizzle/0002_seed-users/migration.sql
INSERT INTO "users" ("name", "email") VALUES ('Admin', 'admin@example.com');
INSERT INTO "users" ("name", "email") VALUES ('Demo', 'demo@example.com');
```
Custom migrations run in order alongside generated migrations via `drizzle-kit migrate`.
### Common Custom Migration Use Cases
- Data seeding / backfill
- Creating PostgreSQL extensions (`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`)
- Creating custom functions / triggers
- Row-level security policies
- Partition table setup
- Granting permissions
## Production Patterns
### CI/CD Pipeline Integration
```yaml
# Example GitHub Actions step
- name: Run migrations
run: npx drizzle-kit migrate
env:
DATABASE_URL: ${{ secrets.DATABASE_URL }}
```
### Application Startup Migration
```typescript
// src/db/index.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { Pool } from 'pg';
import * as schema from './schema';
const pool = new Pool({ connectionString: process.env.DATABASE_URL! });
const db = drizzle({ client: pool, schema });
// Run migrations before accepting requests
await migrate(db, { migrationsFolder: './drizzle' });
export { db };
```
### Migration Safety Checklist
| Check | Description |
|-------|-------------|
| Review generated SQL | Inspect every `migration.sql` before applying |
| Test on staging first | Run migrations against a staging database |
| Backup before migrating | `pg_dump` before applying destructive changes |
| Avoid renaming in production | Rename detection can misidentify changes |
| One migration per deploy | Avoid batching unrelated schema changes |
| Lock timeout | Set `statement_timeout` to prevent long locks |
### Handling Destructive Changes
When dropping columns or tables:
1. Deploy code that no longer reads the column
2. Generate migration that drops the column
3. Apply migration in a separate deploy
For column renames:
1. Add new column with migration
2. Backfill data with a custom migration
3. Deploy code using new column
4. Drop old column in a later migration
## Team Workflows
### Git Workflow
Migration files are committed to version control:
```
drizzle/
├── 0000_initial/
│ ├── migration.sql # committed
│ └── snapshot.json # committed
├── meta/
│ └── _journal.json # committed
```
### Handling Merge Conflicts
If two developers generate migrations concurrently:
1. Both merge their branches
2. Run `npx drizzle-kit check` to detect conflicts
3. If conflicts found, regenerate: delete conflicting migration, run `npx drizzle-kit generate` again
### Migration Ordering
Migrations are ordered by their timestamp prefix. The `_journal.json` file tracks the canonical order. When merging branches with concurrent migrations, timestamps ensure proper ordering.
## Troubleshooting
| Issue | Solution |
|-------|----------|
| "Migration already applied" | Migration tracking table records applied migrations — do not re-run |
| Snapshot mismatch | Run `npx drizzle-kit up` to upgrade snapshot format |
| Column rename misdetected | Use `--custom` migration for manual rename SQL |
| Push conflicts with existing data | `push` cannot handle data migrations — use `generate` + `migrate` |
| Migration table in wrong schema | Configure `migrations.schema` in `drizzle.config.ts` |
| Concurrent migration runs | Use advisory locks or run migrations in a single-instance context |
references/query-patterns.md
# Query Patterns
Advanced query techniques with Drizzle ORM for PostgreSQL.
## Table of Contents
- [Subqueries](#subqueries)
- [Common Table Expressions (CTEs)](#common-table-expressions-ctes)
- [Raw SQL](#raw-sql)
- [Prepared Statements](#prepared-statements)
- [Dynamic Queries](#dynamic-queries)
- [Aggregations](#aggregations)
- [Batch Operations](#batch-operations)
- [Cursor-Based Pagination](#cursor-based-pagination)
- [Streaming Large Result Sets](#streaming-large-result-sets)
- [Column Utilities](#column-utilities)
- [Lateral Joins](#lateral-joins)
## Subqueries
### Inline Subqueries
```typescript
const sq = db.select({ id: users.id })
.from(users)
.where(eq(users.role, 'admin'))
.as('admin_users');
// In FROM
const admins = await db.select().from(sq);
// In JOIN
const posts = await db.select()
.from(posts)
.innerJoin(sq, eq(posts.authorId, sq.id));
// In WHERE with inArray
const adminIds = db.select({ id: users.id }).from(users).where(eq(users.role, 'admin'));
const adminPosts = await db.select().from(posts).where(inArray(posts.authorId, adminIds));
```
### Correlated Subqueries
```typescript
// Count posts per user as a subquery column
const usersWithPostCount = await db.select({
...getTableColumns(users),
postCount: db.$count(posts, eq(posts.authorId, users.id)),
}).from(users);
```
### $count Utility
```typescript
// Standalone count
const totalUsers = await db.$count(users);
const activeUsers = await db.$count(users, eq(users.isActive, true));
// As subquery in select
const result = await db.select({
id: users.id,
name: users.name,
postCount: db.$count(posts, eq(posts.authorId, users.id)),
}).from(users);
```
## Common Table Expressions (CTEs)
### Basic CTE
```typescript
const activeUsers = db.$with('active_users').as(
db.select().from(users).where(eq(users.isActive, true))
);
const result = await db.with(activeUsers)
.select()
.from(activeUsers);
```
### CTE with Computed Columns
Computed fields in CTEs require `.as()` aliases:
```typescript
const userEmails = db.$with('user_emails').as(
db.select({
id: users.id,
lowerEmail: sql<string>`lower(${users.email})`.as('lower_email'),
}).from(users)
);
const result = await db.with(userEmails)
.select({ email: userEmails.lowerEmail })
.from(userEmails);
```
### CTE with INSERT
```typescript
const newUser = db.$with('new_user').as(
db.insert(users).values({ name: 'John', email: 'john@ex.com' }).returning()
);
const result = await db.with(newUser).select().from(newUser);
```
### CTE with UPDATE
```typescript
const avgPrice = db.$with('avg_price').as(
db.select({ value: sql`avg(${products.price})`.as('value') }).from(products)
);
await db.with(avgPrice)
.update(products)
.set({ cheap: true })
.where(lt(products.price, sql`(select * from ${avgPrice})`));
```
### Multiple CTEs
```typescript
const cte1 = db.$with('active').as(
db.select().from(users).where(eq(users.isActive, true))
);
const cte2 = db.$with('admins').as(
db.select().from(users).where(eq(users.role, 'admin'))
);
const result = await db.with(cte1, cte2)
.select()
.from(cte1)
.innerJoin(cte2, eq(cte1.id, cte2.id));
```
## Raw SQL
### sql Template Tag
The `sql` template tag provides safe parameterized SQL:
```typescript
import { sql } from 'drizzle-orm';
// In select
await db.select({
id: users.id,
fullName: sql<string>`${users.firstName} || ' ' || ${users.lastName}`,
}).from(users);
// In where
await db.select().from(users).where(
sql`${users.createdAt} > NOW() - INTERVAL '30 days'`
);
// Complete raw query
const result = await db.execute(
sql`SELECT * FROM users WHERE email = ${email}`
);
// With typed results
const result = await db.execute<{ id: number; name: string }>(
sql`SELECT id, name FROM users WHERE role = ${role}`
);
```
### sql.raw() for Unparameterized SQL
```typescript
// WARNING: only use with trusted, non-user input
const tableName = 'users';
await db.execute(sql`SELECT * FROM ${sql.raw(tableName)}`);
// Dynamic column ordering
const orderCol = 'created_at';
const direction = 'DESC';
await db.select().from(users).orderBy(sql.raw(`${orderCol} ${direction}`));
```
### sql.join() for Building Lists
```typescript
const conditions = [
eq(users.role, 'admin'),
gt(users.createdAt, new Date('2024-01-01')),
];
await db.select().from(users).where(
sql.join(conditions, sql` AND `)
);
```
## Prepared Statements
### PostgreSQL Prepared Statements
PostgreSQL prepared statements require a name:
```typescript
import { sql } from 'drizzle-orm';
const getUserById = db.select().from(users)
.where(eq(users.id, sql.placeholder('id')))
.prepare('get_user_by_id');
// Reuse with different values
const user1 = await getUserById.execute({ id: 1 });
const user2 = await getUserById.execute({ id: 2 });
```
### Multiple Placeholders
```typescript
const searchUsers = db.select().from(users)
.where(and(
eq(users.role, sql.placeholder('role')),
sql`lower(${users.name}) like ${sql.placeholder('name')}`,
))
.limit(sql.placeholder('limit'))
.prepare('search_users');
const results = await searchUsers.execute({
role: 'admin',
name: '%john%',
limit: 10,
});
```
### Prepared Relational Queries
```typescript
const getUserWithPosts = db.query.users.findFirst({
where: (users, { eq }) => eq(users.id, sql.placeholder('id')),
with: { posts: true },
}).prepare('get_user_with_posts');
const user = await getUserWithPosts.execute({ id: 1 });
```
## Dynamic Queries
### Conditional WHERE Clauses
```typescript
async function searchUsers(filters: {
name?: string;
role?: string;
minAge?: number;
}) {
const conditions = [];
if (filters.name) {
conditions.push(ilike(users.name, `%${filters.name}%`));
}
if (filters.role) {
conditions.push(eq(users.role, filters.role));
}
if (filters.minAge) {
conditions.push(gte(users.age, filters.minAge));
}
return db.select().from(users)
.where(conditions.length ? and(...conditions) : undefined);
}
```
### Dynamic Select
```typescript
async function getUsers(includeEmail: boolean) {
return db.select({
id: users.id,
name: users.name,
...(includeEmail ? { email: users.email } : {}),
}).from(users);
}
```
### Dynamic Order By
```typescript
type SortField = 'name' | 'createdAt' | 'email';
type SortDir = 'asc' | 'desc';
function getOrderBy(field: SortField, dir: SortDir) {
const column = {
name: users.name,
createdAt: users.createdAt,
email: users.email,
}[field];
return dir === 'asc' ? asc(column) : desc(column);
}
await db.select().from(users).orderBy(getOrderBy('name', 'desc'));
```
## Aggregations
### Built-in Aggregate Functions
```typescript
import { count, countDistinct, sum, sumDistinct,
avg, avgDistinct, max, min } from 'drizzle-orm';
// Count
await db.select({ total: count() }).from(users);
await db.select({ total: count(users.id) }).from(users);
await db.select({ unique: countDistinct(users.role) }).from(users);
// Sum / Avg
await db.select({ totalScore: sum(scores.value) }).from(scores);
await db.select({ avgAge: avg(users.age) }).from(users);
// Min / Max
await db.select({
oldest: min(users.createdAt),
newest: max(users.createdAt),
}).from(users);
```
### GROUP BY and HAVING
```typescript
await db.select({
role: users.role,
userCount: count(users.id),
avgAge: avg(users.age),
}).from(users)
.groupBy(users.role)
.having(({ userCount }) => gt(userCount, 5));
```
### Window Functions (via raw SQL)
```typescript
await db.select({
id: users.id,
name: users.name,
rowNum: sql<number>`ROW_NUMBER() OVER (ORDER BY ${users.createdAt})`,
rank: sql<number>`RANK() OVER (PARTITION BY ${users.role} ORDER BY ${users.createdAt})`,
}).from(users);
```
## Batch Operations
### Bulk Insert
```typescript
const newUsers = Array.from({ length: 1000 }, (_, i) => ({
name: `User ${i}`,
email: `user${i}@example.com`,
}));
// Single statement insert
await db.insert(users).values(newUsers);
```
### Bulk Upsert
```typescript
const records = [
{ id: 1, name: 'Updated A', email: 'a@ex.com' },
{ id: 2, name: 'Updated B', email: 'b@ex.com' },
];
await db.insert(users)
.values(records)
.onConflictDoUpdate({
target: users.id,
set: { name: sql`excluded.name` },
});
```
### Batch Queries in Transaction
```typescript
const results = await db.transaction(async (tx) => {
const newUser = await tx.insert(users)
.values({ name: 'John', email: 'john@ex.com' })
.returning();
const newPost = await tx.insert(posts)
.values({ title: 'Hello', authorId: newUser[0].id })
.returning();
return { user: newUser[0], post: newPost[0] };
});
```
## Cursor-Based Pagination
```typescript
async function getPage(cursor?: number, pageSize = 20) {
return db.select()
.from(users)
.where(cursor ? gt(users.id, cursor) : undefined)
.orderBy(asc(users.id))
.limit(pageSize);
}
// Usage
const page1 = await getPage();
const page2 = await getPage(page1[page1.length - 1]?.id);
```
### Keyset Pagination with Multiple Columns
```typescript
async function getPage(cursor?: { createdAt: Date; id: number }, pageSize = 20) {
return db.select()
.from(posts)
.where(cursor
? or(
gt(posts.createdAt, cursor.createdAt),
and(eq(posts.createdAt, cursor.createdAt), gt(posts.id, cursor.id))
)
: undefined
)
.orderBy(asc(posts.createdAt), asc(posts.id))
.limit(pageSize);
}
```
## Streaming Large Result Sets
```typescript
const iterator = await db.select().from(users).iterator();
for await (const row of iterator) {
processUser(row);
}
// With prepared statements
const query = db.select().from(users)
.where(eq(users.isActive, true))
.prepare('active_users');
const iter = await query.iterator();
for await (const row of iter) {
processUser(row);
}
```
## Column Utilities
### getTableColumns
```typescript
import { getTableColumns } from 'drizzle-orm';
// All columns
const allCols = getTableColumns(users);
await db.select(allCols).from(users);
// Exclude columns
const { password, ...safeCols } = getTableColumns(users);
await db.select(safeCols).from(users);
// Add computed columns
await db.select({
...getTableColumns(users),
fullName: sql<string>`${users.firstName} || ' ' || ${users.lastName}`,
}).from(users);
```
## Lateral Joins
PostgreSQL lateral joins allow subqueries to reference columns from preceding FROM items:
```typescript
// Get top 3 posts per user
const topPosts = db.select().from(posts)
.where(eq(posts.authorId, users.id))
.orderBy(desc(posts.createdAt))
.limit(3)
.as('top_posts');
await db.select()
.from(users)
.leftJoinLateral(topPosts, sql`true`);
// Inner lateral join
await db.select()
.from(users)
.innerJoinLateral(topPosts, sql`true`);
// Cross lateral join
await db.select()
.from(users)
.crossJoinLateral(topPosts);
```
references/vs-prisma.md
# Drizzle vs Prisma
Feature comparison, architectural differences, and migration guide for teams evaluating or switching between Drizzle ORM and Prisma.
## Table of Contents
- [Architecture Comparison](#architecture-comparison)
- [Feature Matrix](#feature-matrix)
- [Performance Comparison](#performance-comparison)
- [Schema Definition](#schema-definition)
- [Query Syntax](#query-syntax)
- [Migration Systems](#migration-systems)
- [When to Choose Drizzle](#when-to-choose-drizzle)
- [When to Choose Prisma](#when-to-choose-prisma)
- [Migration Guide: Prisma to Drizzle](#migration-guide-prisma-to-drizzle)
## Architecture Comparison
| Aspect | Drizzle | Prisma |
|--------|---------|--------|
| Query engine | Pure TypeScript, runs in-process | Rust binary (Query Engine), separate process |
| Type generation | Runtime inference (`$inferSelect`) | Build-time code generation (`prisma generate`) |
| Schema format | TypeScript files | `.prisma` DSL |
| Dependencies | Zero runtime deps | `@prisma/client`, `@prisma/engines` |
| SQL access | Direct SQL-like API + raw SQL | Abstracted API + `$queryRaw` |
| Connection handling | Bring-your-own driver | Managed by Prisma engine |
## Feature Matrix
| Feature | Drizzle | Prisma |
|---------|---------|--------|
| Type-safe queries | Yes (inferred) | Yes (generated) |
| Relations/nested reads | Yes (relational query API) | Yes (include/select) |
| Raw SQL | `sql` template tag | `$queryRaw`, `$executeRaw` |
| Transactions | Yes (callback + nested savepoints) | Yes (interactive + sequential) |
| Migrations | drizzle-kit generate/migrate/push | prisma migrate dev/deploy |
| Introspection | `drizzle-kit pull` | `prisma db pull` |
| Database GUI | Drizzle Studio | Prisma Studio |
| Connection pooling | BYO (pg Pool, postgres.js) | Built-in + Prisma Accelerate |
| Edge runtime | Native support | Via Prisma Accelerate/Driver Adapters |
| PostgreSQL | Full support | Full support |
| MySQL | Full support | Full support |
| SQLite | Full support | Full support |
| MSSQL | Full support | Preview |
| CockroachDB | Full support | Full support |
| Views | pgView, pgMaterializedView | `@@view` (preview) |
| Enums | pgEnum (native) | enum in schema |
| JSON typed columns | `.$type<T>()` | `Json` type (no granular typing) |
| Composite PKs | Yes | Yes (@@id) |
| Partial indexes | Via raw SQL | Not supported |
| Prepared statements | `.prepare()` with placeholders | Automatic (internal) |
| Streaming/iterators | `.iterator()` | `findManyStream()` (preview) |
| Middleware/extensions | No (use SQL hooks) | Yes (Prisma Client extensions) |
## Performance Comparison
| Metric | Drizzle | Prisma |
|--------|---------|--------|
| Bundle size | ~35KB | ~230KB |
| Cold start | ~10ms | ~250ms |
| Simple SELECT | Baseline | ~2-3x slower |
| Complex JOIN | Baseline | ~2-5x slower |
| Memory usage | ~10MB | ~50MB |
| INSERT (batch) | Baseline | ~1.5x slower |
Drizzle's performance advantage comes from:
- No separate query engine process
- No serialization/deserialization between JS and Rust
- Direct driver communication
- Minimal abstraction layer
Prisma's overhead comes from:
- Rust Query Engine binary communication via JSON-RPC
- Schema validation layer
- Query plan compilation in the engine
## Schema Definition
### Prisma
```prisma
// schema.prisma
model User {
id Int @id @default(autoincrement())
email String @unique
name String
role Role @default(USER)
posts Post[]
createdAt DateTime @default(now())
}
model Post {
id Int @id @default(autoincrement())
title String
author User @relation(fields: [authorId], references: [id])
authorId Int
}
enum Role {
ADMIN
USER
}
```
### Drizzle Equivalent
```typescript
// schema.ts
import { pgTable, serial, text, varchar, integer, timestamp,
pgEnum } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
export const roleEnum = pgEnum('role', ['admin', 'user']);
export const users = pgTable('users', {
id: serial().primaryKey(),
email: varchar({ length: 255 }).notNull().unique(),
name: text().notNull(),
role: roleEnum().default('user'),
createdAt: timestamp().defaultNow(),
});
export const posts = pgTable('posts', {
id: serial().primaryKey(),
title: text().notNull(),
authorId: integer().notNull().references(() => users.id),
});
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts),
}));
export const postsRelations = relations(posts, ({ one }) => ({
author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));
```
## Query Syntax
### Find Many
```typescript
// Prisma
const users = await prisma.user.findMany({
where: { role: 'ADMIN' },
include: { posts: true },
orderBy: { createdAt: 'desc' },
take: 10,
});
// Drizzle (relational)
const users = await db.query.users.findMany({
where: (users, { eq }) => eq(users.role, 'admin'),
with: { posts: true },
orderBy: (users, { desc }) => [desc(users.createdAt)],
limit: 10,
});
// Drizzle (SQL-like)
const users = await db.select().from(usersTable)
.where(eq(usersTable.role, 'admin'))
.orderBy(desc(usersTable.createdAt))
.limit(10);
```
### Create
```typescript
// Prisma
const user = await prisma.user.create({
data: { email: 'a@b.com', name: 'John' },
});
// Drizzle
const [user] = await db.insert(users)
.values({ email: 'a@b.com', name: 'John' })
.returning();
```
### Upsert
```typescript
// Prisma
await prisma.user.upsert({
where: { email: 'a@b.com' },
create: { email: 'a@b.com', name: 'John' },
update: { name: 'John Updated' },
});
// Drizzle
await db.insert(users)
.values({ email: 'a@b.com', name: 'John' })
.onConflictDoUpdate({
target: users.email,
set: { name: 'John Updated' },
});
```
### Transaction
```typescript
// Prisma
const result = await prisma.$transaction(async (tx) => {
const user = await tx.user.create({ data: { name: 'John', email: 'j@b.com' } });
const post = await tx.post.create({ data: { title: 'Hello', authorId: user.id } });
return { user, post };
});
// Drizzle
const result = await db.transaction(async (tx) => {
const [user] = await tx.insert(users).values({ name: 'John', email: 'j@b.com' }).returning();
const [post] = await tx.insert(posts).values({ title: 'Hello', authorId: user.id }).returning();
return { user, post };
});
```
## Migration Systems
| Aspect | Drizzle Kit | Prisma Migrate |
|--------|-------------|----------------|
| Schema format | TypeScript | Prisma DSL |
| Generate | `drizzle-kit generate` | `prisma migrate dev` |
| Apply (dev) | `drizzle-kit migrate` | `prisma migrate dev` |
| Apply (prod) | `drizzle-kit migrate` | `prisma migrate deploy` |
| Push (no files) | `drizzle-kit push` | `prisma db push` |
| Introspect | `drizzle-kit pull` | `prisma db pull` |
| Custom SQL | `--custom` flag | Manual SQL in migration dir |
| Rename detection | Interactive prompt | Automatic (sometimes wrong) |
| Migration format | SQL files | SQL files |
| Shadow database | Not needed | Required for `dev` |
Key differences:
- Prisma requires a shadow database for development migrations; Drizzle does not
- Drizzle generates pure SQL; Prisma wraps SQL in its migration engine
- Drizzle prompts interactively for ambiguous renames; Prisma guesses
## When to Choose Drizzle
- Edge runtime / serverless deployment (Cloudflare Workers, Vercel Edge)
- Bundle size matters (mobile, embedded, edge)
- Cold start latency matters
- Team prefers SQL-like syntax over abstracted APIs
- Need direct control over SQL output
- Need PostgreSQL-specific features (lateral joins, materialized views, partial indexes)
- Want zero runtime dependencies
- TypeScript-only codebase (no extra DSL)
## When to Choose Prisma
- Team prefers high-level abstraction over SQL
- Prisma Client Extensions ecosystem is needed
- Need built-in middleware/hooks for query interception
- Using Prisma Accelerate for global edge caching
- Team has existing Prisma infrastructure and expertise
- Need auto-generated CRUD operations with minimal code
- Rapid prototyping with less SQL knowledge required
## Migration Guide: Prisma to Drizzle
### Step 1: Install Drizzle
```bash
npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg
```
### Step 2: Introspect Existing Database
```bash
npx drizzle-kit pull
```
This generates TypeScript schema files from the Prisma-managed database.
### Step 3: Review Generated Schema
Check `drizzle/schema.ts` against the Prisma schema for accuracy. Add relations:
```typescript
// Generated tables are correct, but relations need manual addition
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts),
}));
```
### Step 4: Set Up drizzle.config.ts
```typescript
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
dialect: 'postgresql',
schema: './src/db/schema.ts',
out: './drizzle',
dbCredentials: { url: process.env.DATABASE_URL! },
});
```
### Step 5: Replace Queries Incrementally
Replace Prisma client calls with Drizzle queries one module at a time. Both ORMs can coexist during migration since they connect to the same database independently.
### Step 6: Remove Prisma
Once all queries are migrated:
```bash
npm uninstall prisma @prisma/client
rm -rf prisma/
```
### Type Mapping
| Prisma Type | Drizzle Equivalent |
|-------------|-------------------|
| `Int` | `integer()` |
| `BigInt` | `bigint({ mode: 'number' })` |
| `String` | `text()` or `varchar()` |
| `Boolean` | `boolean()` |
| `DateTime` | `timestamp()` |
| `Json` | `jsonb().$type<T>()` |
| `Float` | `real()` or `doublePrecision()` |
| `Decimal` | `numeric()` |
| `Bytes` | `bytea()` |
| `@id @default(autoincrement())` | `serial().primaryKey()` |
| `@id @default(uuid())` | `uuid().defaultRandom().primaryKey()` |
| `@default(now())` | `.defaultNow()` |
| `@unique` | `.unique()` |
| `@relation` | `relations()` + `.references()` |
| `@@index` | `index().on()` |
| `@@unique` | `unique().on()` |
SKILL.md
---
name: drizzle-orm
description: "Type-safe SQL ORM for TypeScript with zero runtime overhead. Use when code imports drizzle-orm, drizzle-kit, or drizzle-orm/pg-core, user asks about Drizzle schema design, queries, relations, migrations, or database management with Drizzle ORM. Covers PostgreSQL focus with pgTable, pgEnum, pgSchema, pgView, and drizzle-kit CLI."
---
# Drizzle ORM
TypeScript-first SQL ORM with zero dependencies, compile-time type safety, and SQL-like syntax. Optimized for edge runtimes, serverless environments, and PostgreSQL.
## Installation
```bash
# Core ORM + PostgreSQL driver
npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg
# Alternative drivers
npm install postgres # postgres.js
npm install @neondatabase/serverless # Neon serverless
npm install @electric-sql/pglite # PGlite embedded
```
## Database Connection
```typescript
// Simple connection string
import { drizzle } from 'drizzle-orm/node-postgres';
const db = drizzle(process.env.DATABASE_URL!);
// With connection pool
import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL! });
const db = drizzle({ client: pool });
// With schema for relational queries
import * as schema from './schema';
const db = drizzle({ client: pool, schema });
// With SSL
const db = drizzle({
connection: { connectionString: process.env.DATABASE_URL!, ssl: true }
});
```
## Schema Definition (PostgreSQL)
### Table Declaration
```typescript
import { pgTable, serial, text, varchar, integer, boolean,
timestamp, json, jsonb, uuid } from 'drizzle-orm/pg-core';
export const users = pgTable('users', {
id: integer().primaryKey().generatedAlwaysAsIdentity(),
email: varchar({ length: 255 }).notNull().unique(),
name: text().notNull(),
role: text({ enum: ['admin', 'user', 'guest'] }).default('user'),
metadata: jsonb().$type<{ theme: string; locale: string }>(),
isActive: boolean().default(true),
createdAt: timestamp().defaultNow().notNull(),
updatedAt: timestamp().defaultNow().notNull(),
});
```
### Type Inference
```typescript
type User = typeof users.$inferSelect; // SELECT result type
type NewUser = typeof users.$inferInsert; // INSERT input type
```
### PostgreSQL Column Types
| Type | Import | TypeScript | Notes |
|------|--------|------------|-------|
| `integer()` | pg-core | `number` | 4-byte signed |
| `smallint()` | pg-core | `number` | 2-byte signed |
| `bigint({ mode: 'number' })` | pg-core | `number` | 8-byte, mode selects JS type |
| `serial()` | pg-core | `number` | Auto-increment 4-byte |
| `bigserial({ mode: 'number' })` | pg-core | `number` | Auto-increment 8-byte |
| `text()` | pg-core | `string` | Unlimited length |
| `varchar({ length: 255 })` | pg-core | `string` | Variable with max |
| `char({ length: 10 })` | pg-core | `string` | Fixed-length |
| `boolean()` | pg-core | `boolean` | |
| `timestamp()` | pg-core | `Date` | `{ withTimezone: true }`, `{ mode: 'string' }` |
| `date()` | pg-core | `Date` | `{ mode: 'string' }` for raw |
| `time()` | pg-core | `string` | `{ withTimezone: true }` |
| `interval()` | pg-core | `string` | `{ fields: 'day' }` |
| `json()` | pg-core | `unknown` | Use `.$type<T>()` |
| `jsonb()` | pg-core | `unknown` | Binary JSON, use `.$type<T>()` |
| `uuid()` | pg-core | `string` | `.defaultRandom()` for auto-gen |
| `numeric({ precision, scale })` | pg-core | `string` | `{ mode: 'number' }` for JS number |
| `real()` | pg-core | `number` | 4-byte float |
| `doublePrecision()` | pg-core | `number` | 8-byte float |
| `bytea()` | pg-core | `Buffer` | Binary data |
| `point()` | pg-core | `[number, number]` | `{ mode: 'xy' }` for object |
### Enums
```typescript
import { pgEnum } from 'drizzle-orm/pg-core';
export const roleEnum = pgEnum('role', ['admin', 'user', 'guest']);
export const users = pgTable('users', {
id: serial().primaryKey(),
role: roleEnum().default('user'),
});
```
### Identity Columns (PostgreSQL 10+)
```typescript
// Preferred over serial() for new schemas
id: integer().primaryKey().generatedAlwaysAsIdentity({ startWith: 1000 }),
id: integer().primaryKey().generatedByDefaultAsIdentity(),
```
## Indexes & Constraints
```typescript
import { pgTable, serial, text, integer, index, uniqueIndex,
primaryKey, foreignKey, unique, check } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
export const posts = pgTable('posts', {
id: serial().primaryKey(),
title: text().notNull(),
authorId: integer().notNull().references(() => users.id, { onDelete: 'cascade' }),
slug: text().notNull(),
status: text({ enum: ['draft', 'published'] }),
}, (table) => [
index('title_idx').on(table.title),
uniqueIndex('slug_idx').on(table.slug),
check('status_check', sql`${table.status} in ('draft', 'published')`),
]);
// Composite primary key (junction tables)
export const usersToGroups = pgTable('users_to_groups', {
userId: integer().notNull().references(() => users.id),
groupId: integer().notNull().references(() => groups.id),
}, (table) => [
primaryKey({ columns: [table.userId, table.groupId] }),
]);
// Composite unique constraint
export const example = pgTable('example', {
a: integer(),
b: text(),
}, (table) => [
unique('ab_unique').on(table.a, table.b),
unique('nulls_example').on(table.a).nullsNotDistinct(), // PG 15+
]);
```
## Relations
Relations are application-level abstractions, separate from database foreign keys. They enable the relational query API (`db.query`).
```typescript
import { relations } from 'drizzle-orm';
// One-to-many
export const authorsRelations = relations(authors, ({ many }) => ({
posts: many(posts),
}));
export const postsRelations = relations(posts, ({ one }) => ({
author: one(authors, {
fields: [posts.authorId],
references: [authors.id],
}),
}));
// Many-to-many (through junction table)
export const usersRelations = relations(users, ({ many }) => ({
usersToGroups: many(usersToGroups),
}));
export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
user: one(users, { fields: [usersToGroups.userId], references: [users.id] }),
group: one(groups, { fields: [usersToGroups.groupId], references: [groups.id] }),
}));
// Disambiguating multiple relations to same table
export const postsRelations = relations(posts, ({ one }) => ({
author: one(users, {
fields: [posts.authorId], references: [users.id],
relationName: 'author',
}),
reviewer: one(users, {
fields: [posts.reviewerId], references: [users.id],
relationName: 'reviewer',
}),
}));
```
## Queries
### SELECT
```typescript
import { eq, ne, gt, gte, lt, lte, like, ilike, inArray,
isNull, isNotNull, and, or, between, sql, desc, asc, count } from 'drizzle-orm';
// Basic
const allUsers = await db.select().from(users);
// Partial select
const names = await db.select({ id: users.id, name: users.name }).from(users);
// Filtering
await db.select().from(users).where(eq(users.email, 'user@example.com'));
await db.select().from(users).where(
and(eq(users.role, 'admin'), gt(users.createdAt, new Date('2024-01-01')))
);
await db.select().from(users).where(inArray(users.id, [1, 2, 3]));
// Sorting & pagination
await db.select().from(users).orderBy(desc(users.createdAt)).limit(10).offset(20);
// Distinct / Distinct On (PostgreSQL)
await db.selectDistinct().from(users);
await db.selectDistinctOn([users.id]).from(users).orderBy(users.id);
```
### INSERT
```typescript
// Single row with returning
const [newUser] = await db.insert(users)
.values({ email: 'user@example.com', name: 'John' })
.returning();
// Multiple rows
await db.insert(users).values([
{ email: 'a@example.com', name: 'A' },
{ email: 'b@example.com', name: 'B' },
]);
// Upsert
await db.insert(users)
.values({ id: 1, email: 'new@example.com', name: 'Dan' })
.onConflictDoUpdate({
target: users.email,
set: { name: 'Dan Updated' },
});
await db.insert(users)
.values({ id: 1, email: 'new@example.com', name: 'Dan' })
.onConflictDoNothing({ target: users.email });
// Insert from select
await db.insert(employees).select(
db.select({ name: users.name }).from(users).where(eq(users.role, 'employee'))
);
```
### UPDATE
```typescript
await db.update(users).set({ name: 'Jane' }).where(eq(users.id, 1));
await db.update(users).set({ updatedAt: sql`NOW()` }).where(eq(users.id, 1));
// With returning
const updated = await db.update(users)
.set({ name: 'Jane' })
.where(eq(users.id, 1))
.returning();
// UPDATE...FROM
await db.update(users)
.set({ cityId: cities.id })
.from(cities)
.where(and(eq(cities.name, 'Seattle'), eq(users.name, 'John')));
```
### DELETE
```typescript
await db.delete(users).where(eq(users.id, 1));
const deleted = await db.delete(users).where(eq(users.id, 1)).returning();
```
### Joins
```typescript
// Inner join
const result = await db.select({ user: users, post: posts })
.from(users)
.innerJoin(posts, eq(users.id, posts.authorId));
// Left join (pet fields become nullable)
await db.select().from(users).leftJoin(posts, eq(users.id, posts.authorId));
// Aggregation with join
await db.select({
authorName: authors.name,
postCount: count(posts.id),
}).from(authors)
.leftJoin(posts, eq(authors.id, posts.authorId))
.groupBy(authors.id);
// Self-join with alias
import { alias } from 'drizzle-orm/pg-core';
const parent = alias(users, 'parent');
await db.select().from(users).leftJoin(parent, eq(parent.id, users.parentId));
```
### Relational Queries
```typescript
// Requires schema passed to drizzle() constructor
const usersWithPosts = await db.query.users.findMany({
with: { posts: { with: { comments: true } } },
columns: { id: true, name: true },
where: (users, { eq }) => eq(users.role, 'admin'),
orderBy: (users, { desc }) => [desc(users.createdAt)],
limit: 10,
});
const user = await db.query.users.findFirst({
where: (users, { eq }) => eq(users.id, 1),
with: { posts: true },
});
```
## Transactions
```typescript
// Auto-rollback on error
await db.transaction(async (tx) => {
await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100` })
.where(eq(accounts.id, 1));
await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100` })
.where(eq(accounts.id, 2));
});
// Conditional rollback
await db.transaction(async (tx) => {
const [account] = await tx.select().from(accounts).where(eq(accounts.id, 1));
if (account.balance < 100) {
tx.rollback();
return;
}
await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100` })
.where(eq(accounts.id, 1));
});
// Nested transactions (savepoints)
await db.transaction(async (tx) => {
await tx.insert(users).values({ name: 'John', email: 'john@ex.com' });
await tx.transaction(async (tx2) => {
await tx2.insert(posts).values({ title: 'Post', authorId: 1 });
});
});
// PostgreSQL isolation levels
await db.transaction(async (tx) => { /* ... */ }, {
isolationLevel: 'serializable',
accessMode: 'read write',
deferrable: true,
});
```
## Migrations with drizzle-kit
### Configuration
```typescript
// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
dialect: 'postgresql',
schema: './src/db/schema.ts',
out: './drizzle',
dbCredentials: {
url: process.env.DATABASE_URL!,
},
});
```
### Core Commands
```bash
# Generate SQL migration from schema changes
npx drizzle-kit generate
# Apply migrations to database
npx drizzle-kit migrate
# Push schema directly (no migration files — for rapid prototyping)
npx drizzle-kit push
# Pull existing database schema into TypeScript
npx drizzle-kit pull
# Generate custom empty migration
npx drizzle-kit generate --custom --name=seed-users
# Verify migration consistency
npx drizzle-kit check
# Launch visual database browser
npx drizzle-kit studio
```
### Programmatic Migration
```typescript
import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
const db = drizzle(process.env.DATABASE_URL!);
await migrate(db, { migrationsFolder: './drizzle' });
```
See **[PostgreSQL Migrations](./references/pg-migrations.md)** for complete migration strategies, team workflows, and production patterns.
## PostgreSQL Schemas
```typescript
import { pgSchema, serial, text } from 'drizzle-orm/pg-core';
export const tenantSchema = pgSchema('tenant_1');
export const tenantUsers = tenantSchema.table('users', {
id: serial().primaryKey(),
name: text().notNull(),
});
// Generates: CREATE SCHEMA "tenant_1"; CREATE TABLE "tenant_1"."users" (...)
```
## Views
```typescript
import { pgView, pgMaterializedView } from 'drizzle-orm/pg-core';
export const activeUsers = pgView('active_users').as((qb) =>
qb.select().from(users).where(eq(users.isActive, true))
);
export const userStats = pgMaterializedView('user_stats').as((qb) =>
qb.select({
userId: users.id,
postCount: count(posts.id),
}).from(users).leftJoin(posts, eq(users.id, posts.authorId)).groupBy(users.id)
);
// Refresh at runtime
await db.refreshMaterializedView(userStats);
await db.refreshMaterializedView(userStats).concurrently();
```
## Common Table Expressions (CTEs)
```typescript
const sq = db.$with('active').as(
db.select().from(users).where(eq(users.isActive, true))
);
const result = await db.with(sq).select().from(sq);
// CTE with INSERT
const inserted = db.$with('new_user').as(
db.insert(users).values({ name: 'John', email: 'john@ex.com' }).returning()
);
await db.with(inserted).select().from(inserted);
```
## Red Flags
| Problem | Risk |
|---------|------|
| `any`/`unknown` on JSON columns without `.$type<T>()` | Lost type safety |
| Raw string concatenation instead of `sql` template | SQL injection |
| Multi-step mutations outside `db.transaction()` | Partial writes |
| `select()` without `.limit()` on large tables | Memory exhaustion |
| Missing indexes on foreign key columns | Slow joins |
| Missing indexes on frequently filtered columns | Full table scans |
| Using `serial()` instead of `generatedAlwaysAsIdentity()` | `serial` is legacy in PG 10+ |
## References
- **[PostgreSQL Migrations](./references/pg-migrations.md)** — drizzle-kit configuration, migration strategies, team workflows, production patterns, custom migrations, programmatic migration API. Load when managing database schema changes.
- **[Advanced Schemas](./references/advanced-schemas.md)** — Custom types, branded types, composite keys, arrays, multi-schema, views, materialized views, pgEnum patterns. Load when designing complex database schemas.
- **[Query Patterns](./references/query-patterns.md)** — Subqueries, CTEs, raw SQL, prepared statements, dynamic queries, batch operations, aggregations, window functions. Load when optimizing queries or handling complex filtering.
- **[Performance](./references/performance.md)** — Connection pooling, prepared statements, N+1 prevention, query optimization, edge runtime integration, serverless patterns. Load when scaling or optimizing database performance.
- **[Drizzle vs Prisma](./references/vs-prisma.md)** — Feature comparison, migration guide, architectural differences, when to choose Drizzle. Load when evaluating ORMs or migrating from Prisma.