README.md
# postgres-drizzle
PostgreSQL and Drizzle ORM best practices. This skill activates automatically when writing database schemas, queries, migrations, or any database-related code.
Covers the **stable drizzle-orm 0.x** API (npm `latest`) and flags where **v1.0
(beta/RC, Relational Queries v2 / `defineRelations`)** differs, so generated code
matches the version a project actually uses.
## Topics Covered
| Category | Topics |
|----------|--------|
| **Schema** | Column types, constraints, indexes, enums, JSONB, generated columns |
| **Queries** | Operators, joins, aggregations, subqueries, transactions, prepared statements |
| **Relations** | One-to-many, many-to-many, relational queries API (0.x and v1.0 RQB v2) |
| **Migrations** | drizzle-kit commands, workflows, custom SQL migrations, configuration |
| **PostgreSQL** | PG17/18 features, RLS (SQL + Drizzle `pgPolicy`), partitioning, full-text search |
| **Performance** | Indexing strategies, EXPLAIN, connection pooling, pagination |
## Example Usage
```
"Create a users table with email and timestamps"
"Add a posts table with foreign key to users"
"Write a query to get users with their posts"
"Set up drizzle migrations for production"
"Optimize this slow database query"
```
## Skill Structure
- **[SKILL.md](SKILL.md)** - Main skill file (version check, decision trees, core patterns)
- **Reference Files:**
- [SCHEMA.md](references/SCHEMA.md) - Column types, constraints, indexes
- [QUERIES.md](references/QUERIES.md) - Query patterns and operators
- [RELATIONS.md](references/RELATIONS.md) - Relations API, relational queries, RQB v2
- [MIGRATIONS.md](references/MIGRATIONS.md) - drizzle-kit workflows
- [POSTGRES.md](references/POSTGRES.md) - PostgreSQL 17/18 features
- [PERFORMANCE.md](references/PERFORMANCE.md) - Optimization and pooling
- [CHEATSHEET.md](references/CHEATSHEET.md) - Quick reference
## Quick Start
```typescript
import { eq, relations } from 'drizzle-orm';
import { pgTable, uuid, text, timestamp, index } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema';
// Schema (schema.ts)
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
email: text('email').notNull().unique(),
createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});
// Connection — pass the schema to enable db.query.*
export const db = drizzle(process.env.DATABASE_URL!, { schema });
// Query
const user = await db.query.users.findFirst({
where: eq(users.email, 'user@example.com'),
});
```
## Resources
- **Drizzle Docs**: https://orm.drizzle.team
- **PostgreSQL Docs**: https://www.postgresql.org/docs/current/
references/CHEATSHEET.md
# Drizzle + PostgreSQL Quick Reference
Syntax below is stable drizzle-orm 0.x (npm `latest`). For v1.0 (beta/RC)
projects, relations and `db.query.*` filters differ — see the
[RQB v2 section in RELATIONS.md](RELATIONS.md#relational-queries-v2-drizzle-orm-v10).
---
## Schema Definition
### Column Types
```typescript
import { pgTable, uuid, text, varchar, integer, bigint, boolean,
timestamp, date, numeric, json, jsonb, pgEnum, serial,
index, uniqueIndex, check } from 'drizzle-orm/pg-core';
// Primary Keys
id: uuid('id').primaryKey().defaultRandom(), // UUIDv4
id: uuid('id').primaryKey().default(sql`uuidv7()`), // UUIDv7 (PG18+)
id: integer('id').primaryKey().generatedAlwaysAsIdentity(), // Identity
id: serial('id').primaryKey(), // Serial (legacy)
// Strings
name: text('name').notNull(),
email: varchar('email', { length: 255 }).unique(),
// Numbers
age: integer('age'),
price: numeric('price', { precision: 10, scale: 2 }),
count: bigint('count', { mode: 'number' }),
// Boolean
active: boolean('active').default(true),
// Timestamps
createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
updatedAt: timestamp('updated_at', { withTimezone: true }).$onUpdate(() => new Date()),
// JSON
data: jsonb('data').$type<{ key: string }>(),
// Arrays
tags: text('tags').array(),
```
### Constraints
```typescript
email: text('email').notNull().unique(),
status: text('status').notNull().default('pending'),
// Foreign Key
authorId: uuid('author_id').references(() => users.id, { onDelete: 'cascade' }),
// Check constraints are table-level (no .check() on columns)
}, (table) => [
check('price_positive', sql`${table.price} > 0`),
]);
```
### Indexes
```typescript
}, (table) => [
index('idx_name').on(table.column), // B-tree (default)
uniqueIndex('idx_unique').on(table.column), // Unique
index('idx_composite').on(table.col1, table.col2), // Composite
index('idx_partial').on(table.col).where(sql`...`), // Partial
index('idx_gin').using('gin', table.data), // GIN (method first!)
index('idx_gin_path').using('gin', table.data.op('jsonb_path_ops')),
]);
```
### Enums
```typescript
export const statusEnum = pgEnum('status', ['pending', 'active', 'archived']);
status: statusEnum('status').default('pending'),
```
---
## Relations
```typescript
import { relations } from 'drizzle-orm';
// One-to-Many
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts),
}));
export const postsRelations = relations(posts, ({ one }) => ({
author: one(users, {
fields: [posts.authorId],
references: [users.id],
}),
}));
// Many-to-Many (via junction table)
export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
user: one(users, { fields: [usersToGroups.userId], references: [users.id] }),
group: one(groups, { fields: [usersToGroups.groupId], references: [groups.id] }),
}));
```
---
## Type Inference
```typescript
import type { InferSelectModel, InferInsertModel } from 'drizzle-orm';
type User = InferSelectModel<typeof users>;
type NewUser = InferInsertModel<typeof users>;
```
---
## Query Operators
```typescript
import { eq, ne, gt, gte, lt, lte, like, ilike, inArray, isNull,
isNotNull, and, or, not, between, sql } from 'drizzle-orm';
eq(col, value) // =
ne(col, value) // <>
gt(col, value) // >
gte(col, value) // >=
lt(col, value) // <
lte(col, value) // <=
like(col, '%pat%') // LIKE
ilike(col, '%pat%') // ILIKE (case-insensitive)
inArray(col, [1,2,3]) // IN
isNull(col) // IS NULL
isNotNull(col) // IS NOT NULL
between(col, a, b) // BETWEEN
and(cond1, cond2) // AND
or(cond1, cond2) // OR
not(cond) // NOT
```
---
## Select Queries
```typescript
// Basic
await db.select().from(users);
await db.select({ id: users.id }).from(users);
// Where
await db.select().from(users).where(eq(users.id, id));
// Conditional filters (undefined skips condition)
await db.select().from(users).where(and(
eq(users.active, true),
term ? ilike(users.name, `%${term}%`) : undefined,
));
// Order, Limit, Offset
await db.select().from(users)
.orderBy(desc(users.createdAt))
.limit(20)
.offset(40);
// Join
await db.select().from(users)
.leftJoin(posts, eq(posts.authorId, users.id));
```
---
## Relational Queries
```typescript
// Must pass schema to drizzle()
const db = drizzle(client, { schema });
// Find many
await db.query.users.findMany();
await db.query.users.findMany({
where: eq(users.active, true),
orderBy: [desc(users.createdAt)],
limit: 20,
});
// Find first
await db.query.users.findFirst({
where: eq(users.id, id),
});
// With relations
await db.query.users.findFirst({
where: eq(users.id, id),
with: {
posts: true,
profile: true,
},
});
// Nested relations with filters
await db.query.users.findFirst({
with: {
posts: {
where: eq(posts.published, true),
orderBy: [desc(posts.createdAt)],
limit: 10,
with: { comments: true },
},
},
});
// Select specific columns
await db.query.users.findFirst({
columns: { id: true, email: true },
with: {
posts: { columns: { title: true } },
},
});
```
---
## Insert
```typescript
// Single
const [user] = await db.insert(users)
.values({ email, name })
.returning();
// Multiple
await db.insert(users).values([
{ email: 'a@b.com', name: 'A' },
{ email: 'b@b.com', name: 'B' },
]);
// Upsert
await db.insert(users)
.values({ email, name })
.onConflictDoUpdate({
target: users.email,
set: { name },
});
// Ignore conflict
await db.insert(users)
.values({ email, name })
.onConflictDoNothing();
```
---
## Update
```typescript
await db.update(users)
.set({ status: 'active' })
.where(eq(users.id, id));
// With returning
const [updated] = await db.update(users)
.set({ status: 'active' })
.where(eq(users.id, id))
.returning();
// Increment
await db.update(posts)
.set({ views: sql`${posts.views} + 1` })
.where(eq(posts.id, id));
```
---
## Delete
```typescript
await db.delete(users).where(eq(users.id, id));
const [deleted] = await db.delete(users)
.where(eq(users.id, id))
.returning();
```
---
## Transactions
```typescript
await db.transaction(async (tx) => {
const [user] = await tx.insert(users).values({ ... }).returning();
await tx.insert(profiles).values({ userId: user.id });
return user;
});
// Rollback
await db.transaction(async (tx) => {
await tx.insert(users).values({ ... });
if (condition) tx.rollback(); // Throws
});
```
---
## Aggregations
```typescript
import { count, sum, avg, min, max } from 'drizzle-orm';
// Count shorthand
const total = await db.$count(users);
const active = await db.$count(users, eq(users.active, true));
// Count
const [{ total }] = await db.select({ total: count() }).from(users);
// Group by
await db.select({
authorId: posts.authorId,
postCount: count(),
}).from(posts).groupBy(posts.authorId);
// Having
.having(gt(count(), 10));
```
---
## Prepared Statements
```typescript
const getUser = db.select().from(users)
.where(eq(users.id, sql.placeholder('id')))
.prepare('get_user');
const user = await getUser.execute({ id });
// Behind transaction-mode PgBouncer/Supavisor: postgres(url, { prepare: false })
```
---
## drizzle-kit Commands
```bash
npx drizzle-kit generate # Generate migration from schema
npx drizzle-kit generate --custom # Empty migration for hand-written SQL
npx drizzle-kit migrate # Apply migrations
npx drizzle-kit push # Push schema directly (dev)
npx drizzle-kit pull # Introspect existing DB
npx drizzle-kit studio # Open Drizzle Studio
npx drizzle-kit check # Verify migrations
```
---
## drizzle.config.ts
```typescript
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
schema: './src/db/schema.ts',
out: './drizzle',
dialect: 'postgresql',
dbCredentials: {
url: process.env.DATABASE_URL!,
},
});
```
---
## Connection Setup
### postgres.js (Recommended)
```typescript
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema });
```
### node-postgres
```typescript
import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema';
// One-liner: Drizzle creates a Pool internally
export const db = drizzle(process.env.DATABASE_URL!, { schema });
// Or bring your own Pool
import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 20 });
export const db = drizzle(pool, { schema });
```
---
## Error Codes
| Code | Name | Description |
|------|------|-------------|
| 23505 | unique_violation | Duplicate key |
| 23503 | foreign_key_violation | FK constraint |
| 23502 | not_null_violation | NULL in NOT NULL |
| 23514 | check_violation | CHECK constraint |
| 42P01 | undefined_table | Table doesn't exist |
---
## PostgreSQL 18 Features
| Feature | Syntax |
|---------|--------|
| UUIDv7 | `SELECT uuidv7();` |
| Async I/O | `SET io_method = 'worker';` |
| Skip Scan | Automatic for B-tree |
| RETURNING OLD/NEW | `RETURNING OLD.col, NEW.col` |
---
## Quick Tips
1. **Check drizzle-orm version first** — 0.x (`relations()`) vs 1.0 (`defineRelations`)
2. **Use UUIDv7** (PG18+) or identity columns over UUIDv4/serial for index locality
3. **Use relational queries** to avoid N+1
4. **Add indexes** on foreign keys and frequently filtered columns
5. **Use partial indexes** for filtered subsets
6. **Use `timestamp(..., { withTimezone: true })`** everywhere
7. **Use `EXPLAIN (ANALYZE, BUFFERS)`** to debug slow queries
8. **Use `tx`, not `db`,** inside transactions
9. **Use connection pooling** in production
10. **Run `generate` not `push`** for production migrations
references/MIGRATIONS.md
# Drizzle Migrations
Comprehensive reference for managing database migrations with drizzle-kit
(stable drizzle-kit 0.3x; v1.0 differences noted inline).
## Contents
- [Configuration](#configuration)
- [Commands](#commands)
- [Migration Workflow](#migration-workflow)
- [Push vs Generate](#push-vs-generate)
- [Migration Patterns](#migration-patterns)
- [Custom Migrations](#custom-migrations)
- [Migration Table](#migration-table)
- [Rollback Strategies](#rollback-strategies)
- [Best Practices](#best-practices)
- [Troubleshooting](#troubleshooting)
---
## Configuration
### drizzle.config.ts
```typescript
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
// Schema location
schema: './src/db/schema.ts',
// Migration output directory
out: './drizzle',
// Database dialect
dialect: 'postgresql',
// Database credentials
dbCredentials: {
url: process.env.DATABASE_URL!,
},
// Optional: must match the casing passed to drizzle() at runtime
casing: 'snake_case',
// Optional: verbose logging; strict prompts before risky push statements
verbose: true,
strict: true,
// Optional: where the migrations journal table lives
// (defaults: table "__drizzle_migrations" in schema "drizzle")
migrations: {
table: '__drizzle_migrations',
schema: 'drizzle',
},
});
```
Note: drizzle-kit@1.0 (beta/RC) removes the `--strict` flag/`strict` behavior
because `push` always prompts for confirmation on data-loss statements
(`--force` to skip).
### Multiple Schema Files
```typescript
export default defineConfig({
schema: './src/db/schema/*.ts', // Glob pattern
// or
schema: [
'./src/db/schema/users.ts',
'./src/db/schema/posts.ts',
],
// ...
});
```
### Environment-Specific Config
```typescript
import { defineConfig } from 'drizzle-kit';
const isProd = process.env.NODE_ENV === 'production';
export default defineConfig({
schema: './src/db/schema.ts',
out: './drizzle',
dialect: 'postgresql',
dbCredentials: {
url: isProd
? process.env.DATABASE_URL!
: process.env.DEV_DATABASE_URL!,
},
});
```
---
## Commands
### generate
Generate SQL migrations from schema changes.
```bash
npx drizzle-kit generate
npx drizzle-kit generate --name=add_posts # readable file name
npx drizzle-kit generate --custom --name=seed-users # empty file for hand-written SQL
```
Output:
```
drizzle/
0000_initial.sql
0001_add_posts.sql
meta/
0000_snapshot.json
0001_snapshot.json
_journal.json
```
The `meta/` folder and `_journal.json` are part of the migration state — commit
them, and never edit or hand-create files in `drizzle/` outside of `generate`
(the journal won't know about them and `migrate` will skip or mismatch).
### migrate
Apply pending migrations to the database.
```bash
npx drizzle-kit migrate
```
### push
Push schema directly to database (no migration files).
```bash
npx drizzle-kit push
```
**Use cases:**
- Rapid prototyping
- Local development
- Schema experimentation
### pull
Introspect existing database and generate schema.
```bash
npx drizzle-kit pull
```
**Use cases:**
- Adopting Drizzle on existing project
- Syncing schema from production
- Reverse engineering
### check
Verify migration integrity.
```bash
npx drizzle-kit check
```
### studio
Launch Drizzle Studio (database browser).
```bash
npx drizzle-kit studio
```
---
## Migration Workflow
### Development Workflow
```bash
# 1. Modify schema in TypeScript
# Edit src/db/schema.ts
# 2. Generate migration
npx drizzle-kit generate
# 3. Review generated SQL
cat drizzle/0001_*.sql
# 4. Apply migration (local)
npx drizzle-kit migrate
```
### Production Workflow
#### Option 1: Programmatic Migration
```typescript
// src/db/migrate.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';
const runMigrations = async () => {
const connection = postgres(process.env.DATABASE_URL!, { max: 1 });
const db = drizzle(connection);
console.log('Running migrations...');
await migrate(db, { migrationsFolder: './drizzle' });
console.log('Migrations complete!');
await connection.end();
};
runMigrations().catch(console.error);
```
```bash
# Run before app starts
npx tsx src/db/migrate.ts
```
#### Option 2: CI/CD Migration
```yaml
# .github/workflows/deploy.yml
- name: Run migrations
run: npx drizzle-kit migrate
env:
DATABASE_URL: ${{ secrets.DATABASE_URL }}
```
#### Option 3: Application Startup
```typescript
// src/index.ts
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { db } from './db';
async function main() {
// Run migrations on startup
await migrate(db, { migrationsFolder: './drizzle' });
// Start application
app.listen(3000);
}
```
---
## Push vs Generate
| Aspect | `push` | `generate` + `migrate` |
|--------|--------|------------------------|
| Migration files | No | Yes |
| Version control | No | Yes |
| Rollback support | No | Manual |
| Team collaboration | Difficult | Easy |
| Production use | Not recommended | Recommended |
| Speed | Fast | Slower |
### Transitioning from Push to Migrate
```bash
# 1. Pull current schema as baseline
npx drizzle-kit pull
# 2. Mark current state as migrated
# (Create empty initial migration or use introspect)
# 3. Future changes use generate
npx drizzle-kit generate
```
---
## Migration Patterns
### Adding a Column
```typescript
// Before
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
email: text('email').notNull(),
});
// After
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
email: text('email').notNull(),
name: text('name'), // New nullable column
});
```
Generated SQL:
```sql
ALTER TABLE "users" ADD COLUMN "name" text;
```
### Adding a Required Column
```typescript
// Add with default for existing rows
name: text('name').notNull().default('Unknown'),
```
Generated SQL:
```sql
ALTER TABLE "users" ADD COLUMN "name" text NOT NULL DEFAULT 'Unknown';
```
### Renaming a Column or Table
`drizzle-kit generate` cannot tell a rename from a drop+create, so it prompts
interactively ("column renamed or deleted?"). Answer "renamed" to get `ALTER ...
RENAME`; answering wrong (or blindly accepting in CI) produces DROP + ADD and
**loses data**. Always review the generated SQL for renames:
```sql
-- What you want to see
ALTER TABLE "users" RENAME COLUMN "name" TO "full_name";
```
### Adding an Index
```typescript
export const users = pgTable('users', {
// ...
}, (table) => [
index('users_email_idx').on(table.email), // New index
]);
```
Generated SQL:
```sql
CREATE INDEX "users_email_idx" ON "users" ("email");
```
### Adding a Foreign Key
```typescript
export const posts = pgTable('posts', {
id: uuid('id').primaryKey().defaultRandom(),
authorId: uuid('author_id')
.notNull()
.references(() => users.id), // New FK
});
```
Generated SQL:
```sql
ALTER TABLE "posts"
ADD CONSTRAINT "posts_author_id_users_id_fk"
FOREIGN KEY ("author_id") REFERENCES "users"("id");
```
### Creating a New Table
```typescript
export const comments = pgTable('comments', {
id: uuid('id').primaryKey().defaultRandom(),
content: text('content').notNull(),
postId: uuid('post_id').notNull().references(() => posts.id),
createdAt: timestamp('created_at').notNull().defaultNow(),
});
```
### Dropping a Table
Remove the table definition from schema. Generated SQL:
```sql
DROP TABLE "old_table";
```
---
## Custom Migrations
### Adding Custom SQL
Generate an empty, journal-registered migration file — do NOT create SQL files
in `drizzle/` by hand (they won't be tracked in `meta/_journal.json`):
```bash
npx drizzle-kit generate --custom --name=posts-search
```
Then fill in the generated file:
```sql
-- drizzle/0005_posts-search.sql
-- Add full-text search
ALTER TABLE posts ADD COLUMN search_vector tsvector;
CREATE INDEX posts_search_idx ON posts USING gin(search_vector);
CREATE OR REPLACE FUNCTION posts_search_trigger() RETURNS trigger AS $$
BEGIN
NEW.search_vector := to_tsvector('english', NEW.title || ' ' || NEW.content);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER posts_search_update
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION posts_search_trigger();
```
### Data Migrations
```sql
-- Generated with: npx drizzle-kit generate --custom --name=backfill-names
-- drizzle/0006_backfill-names.sql
-- Migrate data from old structure to new
UPDATE users SET full_name = first_name || ' ' || last_name
WHERE full_name IS NULL;
-- Backfill computed column
UPDATE posts SET word_count = array_length(string_to_array(content, ' '), 1);
```
---
## Migration Table
Drizzle tracks applied migrations in `__drizzle_migrations`, which lives in the
`drizzle` schema by default (not `public`):
```sql
SELECT * FROM drizzle.__drizzle_migrations;
```
| id | hash | created_at |
|----|------|------------|
| 1 | abc123 | 2024-01-15 |
| 2 | def456 | 2024-01-20 |
Change the location with the `migrations: { table, schema }` config option
(must match between drizzle-kit and any programmatic `migrate()` call via
`migrationsTable`/`migrationsSchema`).
---
## Rollback Strategies
Drizzle doesn't generate automatic rollbacks. Strategies:
### Manual Rollback Script
```sql
-- drizzle/0003_add_feature.sql
ALTER TABLE users ADD COLUMN feature_flag boolean DEFAULT false;
-- drizzle/rollback/0003_add_feature.sql (manual)
ALTER TABLE users DROP COLUMN feature_flag;
```
### Point-in-Time Recovery
Use PostgreSQL's backup/restore for critical rollbacks.
### Feature Flags
Design migrations to be additive when possible:
```typescript
// Add nullable column (safe)
newFeature: text('new_feature'),
// Later, make required after backfill
newFeature: text('new_feature').notNull(),
```
---
## Best Practices
### 1. Review Generated SQL
Always review before applying:
```bash
npx drizzle-kit generate
cat drizzle/0001_*.sql
```
### 2. Test Migrations
```bash
# Test on copy of production data
pg_dump production_db | psql test_db
npx drizzle-kit migrate --config=drizzle.config.test.ts
```
### 3. Keep Migrations Small
- One feature per migration
- Easier to review and rollback
- Faster to apply
### 4. Use Transactions
PostgreSQL wraps DDL in transactions by default. For large data migrations:
```sql
BEGIN;
-- Migration statements
COMMIT;
```
### 5. Handle Downtime
For zero-downtime deployments, build indexes without locking writes:
```sql
CREATE INDEX CONCURRENTLY users_email_idx ON users(email);
```
Caveat: `CREATE INDEX CONCURRENTLY` cannot run inside a transaction, and the
Drizzle migrator applies each migration file transactionally. Run concurrent
index builds outside the migration pipeline (ops script/psql), or accept a
brief lock with a plain `CREATE INDEX` in the migration.
### 6. Version Control
```gitignore
# .gitignore
# Don't ignore migrations!
# drizzle/ <- Include this in version control
```
### 7. CI Validation
```yaml
# Validate schema matches migrations
- name: Check migrations
run: |
npx drizzle-kit generate
git diff --exit-code drizzle/
```
---
## Troubleshooting
### "Migration already applied"
```sql
-- Check migration status (note the drizzle schema)
SELECT * FROM drizzle.__drizzle_migrations;
```
If a migration ran manually and only needs recording, insert its hash into that
table — but prefer fixing the workflow (only ever apply via `migrate`).
### "Schema out of sync"
```bash
# Pull current state
npx drizzle-kit pull
# Compare with your schema
diff src/db/schema.ts drizzle/schema.ts
```
### "Cannot drop column"
Check for dependencies:
```sql
-- Find dependent objects
SELECT * FROM pg_depend WHERE refobjid = 'table_name'::regclass;
```
### Concurrent Migration Issues
Use advisory locks:
```typescript
await db.execute(sql`SELECT pg_advisory_lock(12345)`);
await migrate(db, { migrationsFolder: './drizzle' });
await db.execute(sql`SELECT pg_advisory_unlock(12345)`);
```
references/PERFORMANCE.md
# Performance Optimization
Comprehensive reference for PostgreSQL and Drizzle ORM performance optimization.
## Contents
- [Indexing Strategies](#indexing-strategies)
- [Query Optimization](#query-optimization)
- [Drizzle Query Optimization](#drizzle-query-optimization)
- [Connection Pooling](#connection-pooling)
- [Caching Strategies](#caching-strategies)
- [Pagination Best Practices](#pagination-best-practices)
- [Bulk Operations](#bulk-operations)
- [Performance Checklist](#performance-checklist)
- [Monitoring Queries](#monitoring-queries)
---
## Indexing Strategies
### B-Tree Indexes (Default)
Best for: equality, range queries, sorting, LIKE with left anchor.
```sql
-- Single column
CREATE INDEX users_email_idx ON users(email);
-- Composite (order matters!)
CREATE INDEX orders_user_date_idx ON orders(user_id, created_at DESC);
-- Unique
CREATE UNIQUE INDEX users_email_unique ON users(email);
```
**In Drizzle:**
```typescript
export const users = pgTable('users', {
email: text('email').notNull(),
createdAt: timestamp('created_at').notNull(),
}, (table) => [
index('users_email_idx').on(table.email),
index('users_created_idx').on(table.createdAt),
]);
```
### Partial Indexes
Index only rows matching a condition:
```sql
-- Index only active users
CREATE INDEX active_users_email_idx ON users(email)
WHERE deleted_at IS NULL;
-- Index only pending orders
CREATE INDEX pending_orders_idx ON orders(created_at)
WHERE status = 'pending';
```
**Benefits:** Smaller size, faster updates, more efficient queries.
**In Drizzle:**
```typescript
}, (table) => [
index('active_users_idx')
.on(table.email)
.where(sql`deleted_at IS NULL`),
]);
```
### Covering Indexes (INCLUDE)
Include columns for index-only scans:
```sql
CREATE INDEX orders_user_idx ON orders(user_id)
INCLUDE (status, total);
-- This query uses index-only scan (no table access)
SELECT status, total FROM orders WHERE user_id = 123;
```
### GIN Indexes for JSONB
| Class | Size | Operators | Best For |
|-------|------|-----------|----------|
| `jsonb_ops` (default) | 60-80% | @>, ?, ?\|, ?& | Key existence |
| `jsonb_path_ops` | 20-30% | @> only | Containment |
```sql
-- Default (supports key existence)
CREATE INDEX data_gin_idx ON events USING gin(data);
-- Smaller, faster for containment only
CREATE INDEX data_gin_path_idx ON events USING gin(data jsonb_path_ops);
```
**In Drizzle** (method first, columns after — there is no `.on().using()` chain):
```typescript
}, (table) => [
index('data_gin_idx').using('gin', table.data),
index('data_gin_path_idx').using('gin', table.data.op('jsonb_path_ops')),
]);
```
### Expression Indexes
Index computed values:
```sql
-- Case-insensitive search
CREATE INDEX users_email_lower_idx ON users(lower(email));
-- Date extraction
CREATE INDEX orders_month_idx ON orders(date_trunc('month', created_at));
-- JSONB field
CREATE INDEX events_type_idx ON events((data->>'type'));
```
**Important:** Query must match expression exactly.
```sql
-- Uses index
SELECT * FROM users WHERE lower(email) = 'user@example.com';
-- Does NOT use index
SELECT * FROM users WHERE email = 'USER@example.com';
```
---
## Query Optimization
### EXPLAIN ANALYZE
```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = '123' AND status = 'pending';
```
| Option | Description |
|--------|-------------|
| ANALYZE | Execute query, show actual times |
| BUFFERS | Show buffer/cache hits and reads |
| COSTS | Show planner estimates |
| TIMING | Show per-node timing |
### Reading Query Plans
**Key metrics:**
- `actual time`: Startup..total time in ms
- `rows`: Estimated vs actual row count
- `loops`: Number of iterations
- `Buffers: shared hit/read`: Cache hits vs disk reads
**Problem indicators:**
- Large discrepancy between estimated and actual rows
- High `shared read` (cold cache, missing indexes)
- Seq Scan on large tables
- Nested Loop with high loop count
### Example Analysis
```sql
-- Bad plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = '123' AND status = 'pending';
-- Seq Scan on orders (cost=0.00..50000.00)
-- Filter: (user_id = '123' AND status = 'pending')
-- Rows Removed by Filter: 999000
-- Buffers: shared hit=10000 read=40000
-- After adding index
-- Index Scan using orders_user_status_idx
-- Index Cond: (user_id = '123' AND status = 'pending')
-- Buffers: shared hit=10
```
---
## Drizzle Query Optimization
### Prepared Statements
```typescript
// Prepare once
const getUserById = db
.select()
.from(users)
.where(eq(users.id, sql.placeholder('id')))
.prepare('get_user_by_id');
// Execute many times (reuses plan)
const user1 = await getUserById.execute({ id: 'uuid-1' });
const user2 = await getUserById.execute({ id: 'uuid-2' });
```
**Pooler caveat:** server-side prepared statements assume a stable session. Behind
a transaction-mode pooler, either disable them (postgres.js: `postgres(url,
{ prepare: false })`) or use PgBouncer 1.21+ with `max_prepared_statements > 0`.
See [Transaction Pooling Limitations](#transaction-pooling-limitations).
### Avoid N+1 Queries
**Bad (N+1):**
```typescript
const allPosts = await db.select().from(posts);
for (const post of allPosts) {
const [author] = await db
.select()
.from(users)
.where(eq(users.id, post.authorId));
// N+1 queries!
}
```
**Good (Relational Query):**
```typescript
const postsWithAuthors = await db.query.posts.findMany({
with: { author: true },
});
// Single round trip
```
**Good (Manual Join):**
```typescript
const postsWithAuthors = await db
.select()
.from(posts)
.leftJoin(users, eq(posts.authorId, users.id));
```
### Select Only Needed Columns
```typescript
// Bad - selects all columns
const allUsers = await db.select().from(users);
// Good - selects only needed columns
const userEmails = await db
.select({ id: users.id, email: users.email })
.from(users);
// With relational queries
const userEmails = await db.query.users.findMany({
columns: { id: true, email: true },
});
```
### Batch Operations
```typescript
// Bad - individual inserts
for (const user of users) {
await db.insert(usersTable).values(user);
}
// Good - batch insert
await db.insert(usersTable).values(users);
// For very large batches, chunk them
const BATCH_SIZE = 1000;
for (let i = 0; i < users.length; i += BATCH_SIZE) {
await db.insert(usersTable).values(users.slice(i, i + BATCH_SIZE));
}
```
### Use Transactions for Multiple Operations
```typescript
// Bad - multiple round trips
const user = await db.insert(users).values({ ... }).returning();
const profile = await db.insert(profiles).values({ userId: user.id });
// Good - single transaction
await db.transaction(async (tx) => {
const [user] = await tx.insert(users).values({ ... }).returning();
await tx.insert(profiles).values({ userId: user.id });
});
```
---
## Connection Pooling
### Why Pool?
Each PostgreSQL connection uses ~10MB RAM. PgBouncer connections use ~2KB.
### PgBouncer Configuration
```ini
[databases]
myapp = host=localhost port=5432 dbname=myapp
[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 10
reserve_pool_size = 5
```
### Pooling Modes
| Mode | Connection Release | Use Case |
|------|-------------------|----------|
| Session | After disconnect | Legacy apps |
| Transaction | After each transaction | Most applications |
| Statement | After each statement | Simple queries only |
### Transaction Pooling Limitations
- No `SET SESSION` state (use `SET LOCAL` inside a transaction)
- Prepared statements: postgres.js prepares statements by default, which breaks
in transaction mode unless the pooler tracks them. PgBouncer 1.21+ supports
protocol-level prepared statements via `max_prepared_statements = 200`;
otherwise set `prepare: false` in postgres.js. Named `.prepare()` statements
from Drizzle have the same constraint.
- Temp tables, advisory session locks, LISTEN/NOTIFY must stay within one transaction/session
### Drizzle with postgres.js
postgres.js has built-in connection pooling:
```typescript
import postgres from 'postgres';
const client = postgres(process.env.DATABASE_URL!, {
max: 20, // Max connections
idle_timeout: 30, // Close idle connections after 30s
connect_timeout: 10, // Connection timeout
// prepare: false, // Required behind transaction-mode PgBouncer < 1.21 / Supavisor
});
```
### Drizzle with node-postgres Pool
```typescript
import { Pool } from 'pg';
import { drizzle } from 'drizzle-orm/node-postgres';
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 20,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 10000,
});
const db = drizzle(pool, { schema });
```
---
## Caching Strategies
### Query Result Caching
```typescript
import { Redis } from 'ioredis';
const redis = new Redis();
async function getCachedUser(userId: string) {
const cacheKey = `user:${userId}`;
// Try cache first
const cached = await redis.get(cacheKey);
if (cached) return JSON.parse(cached);
// Query database
const user = await db.query.users.findFirst({
where: eq(users.id, userId),
});
// Cache result
if (user) {
await redis.setex(cacheKey, 3600, JSON.stringify(user));
}
return user;
}
```
### Cache Invalidation
```typescript
// Invalidate on update
async function updateUser(userId: string, data: Partial<User>) {
await db.update(users).set(data).where(eq(users.id, userId));
await redis.del(`user:${userId}`);
}
```
---
## Pagination Best Practices
### Offset-Based (Simple, Slow for Large Offsets)
```typescript
async function getPage(page: number, pageSize = 20) {
return db
.select()
.from(posts)
.orderBy(desc(posts.createdAt))
.limit(pageSize)
.offset((page - 1) * pageSize);
}
```
### Cursor-Based (Better Performance)
```typescript
async function getPostsAfter(cursor?: string, limit = 20) {
return db
.select()
.from(posts)
.where(cursor ? lt(posts.id, cursor) : undefined)
.orderBy(desc(posts.id))
.limit(limit);
}
// Usage
const page1 = await getPostsAfter(undefined, 20);
const lastId = page1[page1.length - 1]?.id;
const page2 = await getPostsAfter(lastId, 20);
```
### Keyset Pagination (Most Efficient)
```typescript
async function getPostsAfter(
cursor?: { createdAt: Date; id: string },
limit = 20
) {
return db
.select()
.from(posts)
.where(
cursor
? or(
lt(posts.createdAt, cursor.createdAt),
and(
eq(posts.createdAt, cursor.createdAt),
lt(posts.id, cursor.id)
)
)
: undefined
)
.orderBy(desc(posts.createdAt), desc(posts.id))
.limit(limit);
}
```
---
## Bulk Operations
### Bulk Insert
```typescript
// Insert many rows efficiently
await db.insert(events).values(
items.map(item => ({
type: item.type,
data: item.data,
createdAt: new Date(),
}))
);
```
### Bulk Update with CASE
```typescript
// Update multiple rows with different values
await db.execute(sql`
UPDATE products
SET price = CASE id
${sql.join(
updates.map(u => sql`WHEN ${u.id} THEN ${u.price}`),
sql` `
)}
END
WHERE id IN ${sql`(${sql.join(updates.map(u => u.id), sql`, `)})`}
`);
```
### Bulk Upsert
```typescript
await db
.insert(products)
.values(newProducts) // array of rows
.onConflictDoUpdate({
target: products.sku,
set: {
price: sql`excluded.price`, // "excluded" = the row that failed to insert
updatedAt: new Date(),
},
});
```
---
## Performance Checklist
### PostgreSQL Configuration
- [ ] Set `shared_buffers` to 25% of RAM
- [ ] Set `effective_cache_size` to 50-75% of RAM
- [ ] Configure `work_mem` based on workload (OLTP: 4-16MB, OLAP: 64-256MB)
- [ ] Enable `io_method = worker` (PostgreSQL 18)
- [ ] Tune `io_workers` (~1/4 of CPU cores)
### Indexing
- [ ] Create indexes for foreign keys
- [ ] Use partial indexes for filtered subsets
- [ ] Use covering indexes for hot queries
- [ ] Use GIN with `jsonb_path_ops` for JSONB containment
- [ ] Monitor unused indexes and remove them
### Queries
- [ ] Use `EXPLAIN (ANALYZE, BUFFERS)` for optimization
- [ ] Use prepared statements for repeated queries
- [ ] Use relational queries API to avoid N+1
- [ ] Select only needed columns
- [ ] Use cursor-based pagination for large datasets
### Application
- [ ] Use connection pooling
- [ ] Batch insert/update operations
- [ ] Cache frequently accessed data
- [ ] Use transactions appropriately
### Maintenance
- [ ] Ensure autovacuum is configured
- [ ] Run `ANALYZE` after bulk data changes
- [ ] Monitor table/index bloat
- [ ] Reindex periodically (CONCURRENTLY)
---
## Monitoring Queries
### Slow Queries
```sql
-- Enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = 1000; -- 1 second
```
### pg_stat_statements
```sql
-- Enable extension
CREATE EXTENSION pg_stat_statements;
-- Top queries by time
SELECT
query,
calls,
mean_exec_time,
total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
```
### Index Efficiency
```sql
-- Index usage vs table size
SELECT
t.tablename,
pg_size_pretty(pg_table_size(t.tablename::regclass)) AS table_size,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan AS scans
FROM pg_tables t
JOIN pg_stat_user_indexes i ON t.tablename = i.relname
WHERE t.schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
```
references/POSTGRES.md
# PostgreSQL 17/18 Features & Configuration
Comprehensive reference for PostgreSQL 18 features (released September 2025),
configuration, and best practices. Features marked PG18 require version 18+;
everything else applies to 17 as well.
## Contents
- [PostgreSQL 18 New Features](#postgresql-18-new-features)
- [Memory Configuration](#memory-configuration)
- [Row-Level Security (RLS)](#row-level-security-rls)
- [Table Partitioning](#table-partitioning)
- [JSONB Operations](#jsonb-operations)
- [Full-Text Search](#full-text-search)
- [Useful System Views](#useful-system-views)
- [Maintenance](#maintenance)
---
## PostgreSQL 18 New Features
### Asynchronous I/O
PostgreSQL 18 introduces AIO for concurrent read operations. Benchmarks show up to 3x improvement for sequential scans.
#### io_method Options
| Method | Description | Best For |
|--------|-------------|----------|
| `sync` | PostgreSQL 17 behavior | Compatibility |
| `worker` | Background workers (default) | Most workloads |
| `io_uring` | Linux kernel 5.1+ | Cold cache workloads |
#### Configuration
```sql
-- Check current settings
SHOW io_method;
SHOW io_workers;
SHOW effective_io_concurrency;
SHOW maintenance_io_concurrency;
-- Recommended production settings
ALTER SYSTEM SET io_method = 'worker';
ALTER SYSTEM SET io_workers = 12; -- ~1/4 of CPU cores
ALTER SYSTEM SET effective_io_concurrency = 32;
ALTER SYSTEM SET maintenance_io_concurrency = 16;
```
**Supported operations:** Sequential scans, bitmap heap scans, VACUUM.
---
### Index Skip Scan
B-tree indexes now support skip scan for queries that don't specify leading columns.
```sql
-- Index on (region, status, created_at)
CREATE INDEX orders_region_status_date ON orders(region, status, created_at);
-- This query now uses skip scan (previously full table scan)
SELECT * FROM orders WHERE status = 'pending';
-- ~40% faster without changing SQL
```
---
### UUIDv7 Support
Timestamp-ordered UUIDs for better index locality:
```sql
SELECT uuidv7();
-- Returns: 019470a8-1234-7abc-8def-012345678901
```
**Advantages over UUIDv4:**
- Chronologically sortable
- Better B-tree index performance
- Reduced index fragmentation
- Time-based partitioning friendly
**In Drizzle:**
```typescript
id: uuid('id').primaryKey().default(sql`uuidv7()`),
```
---
### Virtual Generated Columns
Virtual columns compute values at read time (not stored on disk). In PG18,
`VIRTUAL` is the default when neither keyword is given:
```sql
CREATE TABLE products (
price numeric NOT NULL,
tax_rate numeric NOT NULL,
-- Stored (computed at write, stored on disk)
total_price numeric GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED,
-- Virtual (computed at read, not stored) — PG18 default
display_price text GENERATED ALWAYS AS (price::text || ' USD') VIRTUAL
);
```
**Notes:** Virtual generated columns cannot be indexed. Drizzle's
`generatedAlwaysAs()` only emits the `STORED` form for Postgres — define virtual
columns in a custom migration (see [SCHEMA.md](SCHEMA.md#generated-columns)).
---
### Temporal Constraints
`WITHOUT OVERLAPS` for temporal database patterns:
```sql
CREATE TABLE room_bookings (
room_id int,
booking_period tstzrange,
PRIMARY KEY (room_id, booking_period WITHOUT OVERLAPS)
);
-- Prevents overlapping bookings for the same room
INSERT INTO room_bookings VALUES (1, '[2024-01-01, 2024-01-05)');
INSERT INTO room_bookings VALUES (1, '[2024-01-03, 2024-01-07)'); -- Error!
```
---
### RETURNING Enhancements
Access both old and new values in DML:
```sql
-- UPDATE with OLD/NEW access
UPDATE inventory
SET quantity = quantity - 10
WHERE product_id = 123
RETURNING OLD.quantity AS was, NEW.quantity AS now;
-- DELETE with OLD access
DELETE FROM audit_log
WHERE created_at < now() - interval '90 days'
RETURNING OLD.*;
-- MERGE with RETURNING (MERGE RETURNING is PG17+; OLD/NEW is PG18+)
MERGE INTO products t
USING staging s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET price = s.price
WHEN NOT MATCHED THEN INSERT VALUES (s.*)
RETURNING *;
```
---
### Data Checksums by Default
PostgreSQL 18 enables data checksums by default for new clusters, protecting against silent data corruption.
```sql
SHOW data_checksums; -- on
```
---
## Memory Configuration
### shared_buffers
PostgreSQL's main memory cache. Set to ~25% of total RAM.
```sql
-- For 32GB RAM server
ALTER SYSTEM SET shared_buffers = '8GB';
```
### work_mem
Memory for sort and hash operations per query.
| Workload | Recommendation |
|----------|----------------|
| OLTP | 4-16 MB |
| OLAP | 64-256 MB |
| Mixed | 16-64 MB |
```sql
-- Set globally
ALTER SYSTEM SET work_mem = '32MB';
-- Or per-session for large queries
SET work_mem = '256MB';
```
**Warning:** Total memory = `work_mem × max_connections × operations_per_query`
### maintenance_work_mem
Memory for VACUUM, CREATE INDEX, and maintenance operations:
```sql
ALTER SYSTEM SET maintenance_work_mem = '1GB';
```
### effective_cache_size
Hint to planner about OS cache. Set to 50-75% of total RAM:
```sql
-- For 32GB RAM
ALTER SYSTEM SET effective_cache_size = '20GB';
```
---
## Row-Level Security (RLS)
### Enable RLS
```sql
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
-- Force owner to also follow RLS (optional)
ALTER TABLE documents FORCE ROW LEVEL SECURITY;
```
### Policy Types
| Type | Behavior |
|------|----------|
| PERMISSIVE (default) | Any matching policy grants access (OR) |
| RESTRICTIVE | All policies must pass (AND) |
### Multi-Tenant Pattern
```sql
-- Set tenant context per connection/transaction
SET app.current_tenant_id = 'tenant-123';
-- Create policy
CREATE POLICY tenant_isolation ON documents
FOR ALL
TO application_role
USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);
```
### Command-Specific Policies
```sql
-- SELECT only
CREATE POLICY select_own ON documents
FOR SELECT
USING (owner_id = current_user_id());
-- INSERT only
CREATE POLICY insert_own ON documents
FOR INSERT
WITH CHECK (owner_id = current_user_id());
-- UPDATE (both USING and WITH CHECK)
CREATE POLICY update_own ON documents
FOR UPDATE
USING (owner_id = current_user_id())
WITH CHECK (owner_id = current_user_id());
-- DELETE
CREATE POLICY delete_own ON documents
FOR DELETE
USING (owner_id = current_user_id());
```
### Defining Policies in Drizzle
Drizzle (0.36+) can manage roles and policies in the schema so `drizzle-kit
generate` emits the `CREATE POLICY` DDL. Defining any `pgPolicy` on a table
enables RLS for it automatically; use `.enableRLS()` for a table with RLS but
no policies (default-deny):
```typescript
import { sql } from 'drizzle-orm';
import { pgTable, pgPolicy, pgRole, uuid, text } from 'drizzle-orm/pg-core';
export const appUser = pgRole('app_user');
export const documents = pgTable('documents', {
id: uuid('id').primaryKey().defaultRandom(),
tenantId: uuid('tenant_id').notNull(),
body: text('body'),
}, (table) => [
pgPolicy('tenant_isolation', {
for: 'all',
to: appUser,
using: sql`${table.tenantId} = current_setting('app.current_tenant_id')::uuid`,
withCheck: sql`${table.tenantId} = current_setting('app.current_tenant_id')::uuid`,
}),
]);
// RLS enabled, zero policies => nothing visible:
export const audit = pgTable('audit', { id: uuid('id').primaryKey() }).enableRLS();
```
For Neon there is a higher-level wrapper:
`import { crudPolicy, authenticatedRole } from 'drizzle-orm/neon'` —
`crudPolicy({ role, read, modify })` expands to the four select/insert/update/delete
policies. Supabase helpers live in `drizzle-orm/supabase`.
### Setting RLS Context at Runtime
```typescript
// Use SET LOCAL inside a transaction: it scopes the setting to that
// transaction, which is required with pooled connections (a plain SET
// leaks onto whichever client reuses the connection)
await db.transaction(async (tx) => {
await tx.execute(
sql`SELECT set_config('app.current_tenant_id', ${tenantId}, true)`,
);
// Queries now filtered by RLS
const docs = await tx.select().from(documents);
});
```
Remember RLS applies to the connecting role — superusers and table owners bypass
it unless `FORCE ROW LEVEL SECURITY` is set, so connect as a non-owner app role.
---
## Table Partitioning
### When to Partition
- Tables > 100GB
- Clear partition key (dates, tenant IDs)
- Queries frequently filter on partition key
- Need to archive/drop old data efficiently
### Range Partitioning (Time-Series)
```sql
CREATE TABLE events (
id uuid PRIMARY KEY DEFAULT uuidv7(),
event_type text NOT NULL,
data jsonb,
created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);
-- Create partitions
CREATE TABLE events_2025_01 PARTITION OF events
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
```
### List Partitioning (Categories)
```sql
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT uuidv7(),
region text NOT NULL,
total numeric
) PARTITION BY LIST (region);
CREATE TABLE orders_na PARTITION OF orders
FOR VALUES IN ('US', 'CA', 'MX');
CREATE TABLE orders_eu PARTITION OF orders
FOR VALUES IN ('UK', 'DE', 'FR');
```
### Hash Partitioning (Even Distribution)
```sql
CREATE TABLE user_events (
id uuid PRIMARY KEY DEFAULT uuidv7(),
user_id uuid NOT NULL,
data jsonb
) PARTITION BY HASH (user_id);
CREATE TABLE user_events_0 PARTITION OF user_events
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_events_1 PARTITION OF user_events
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- etc.
```
### Partition Management
```sql
-- Detach old partition (fast, no lock)
ALTER TABLE events DETACH PARTITION events_2024_01 CONCURRENTLY;
-- Drop detached partition
DROP TABLE events_2024_01;
-- Attach new partition
ALTER TABLE events ATTACH PARTITION events_2025_03
FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');
```
---
## JSONB Operations
### Operators
| Operator | Description | Example |
|----------|-------------|---------|
| `->` | Get JSON object field | `data->'name'` |
| `->>` | Get JSON field as text | `data->>'name'` |
| `#>` | Get nested field | `data#>'{address,city}'` |
| `#>>` | Get nested field as text | `data#>>'{address,city}'` |
| `@>` | Contains | `data @> '{"active":true}'` |
| `<@` | Contained by | `'{"a":1}' <@ data` |
| `?` | Key exists | `data ? 'name'` |
| `?\|` | Any key exists | `data ?\| array['a','b']` |
| `?&` | All keys exist | `data ?& array['a','b']` |
### JSONB Functions
```sql
-- Build JSON
SELECT jsonb_build_object('name', 'John', 'age', 30);
-- Aggregate to array
SELECT jsonb_agg(row_to_json(users)) FROM users;
-- Extract keys
SELECT jsonb_object_keys(data) FROM events;
-- Update nested value
UPDATE users
SET data = jsonb_set(data, '{preferences,theme}', '"dark"')
WHERE id = 1;
-- Remove key
UPDATE users
SET data = data - 'deprecated_field'
WHERE id = 1;
```
### JSONB Path Queries (SQL/JSON)
```sql
-- JSONPath query
SELECT * FROM events
WHERE data @? '$.items[*] ? (@.price > 100)';
-- Extract with path
SELECT jsonb_path_query(data, '$.items[*].name') FROM orders;
```
---
## Full-Text Search
### Basic Setup (Generated Column — preferred)
Since PG12 the recommended approach is a **stored generated column**, not a
trigger (simpler, can't drift out of sync). Note it must be `STORED` — tsvector
columns need to be indexable:
```sql
ALTER TABLE posts ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(content, '')), 'B')
) STORED;
CREATE INDEX posts_search_idx ON posts USING gin(search_vector);
```
Use a trigger instead only when the vector needs data from other tables or
session state (generated columns can only reference the same row).
### Querying
```sql
-- Basic search
SELECT * FROM posts
WHERE search_vector @@ plainto_tsquery('english', 'database optimization');
-- Ranked results
SELECT *, ts_rank(search_vector, query) AS rank
FROM posts, plainto_tsquery('english', 'database') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;
-- Headline (highlighted snippets)
SELECT ts_headline('english', content, query)
FROM posts, plainto_tsquery('english', 'database') AS query
WHERE search_vector @@ query;
```
### In Drizzle
Drizzle has no built-in `tsvector` type — declare one with `customType`, define
the column as generated, and index it with GIN:
```typescript
import { SQL, sql } from 'drizzle-orm';
import { customType, index, pgTable, text, uuid } from 'drizzle-orm/pg-core';
const tsvector = customType<{ data: string }>({
dataType() { return 'tsvector'; },
});
export const posts = pgTable('posts', {
id: uuid('id').primaryKey().defaultRandom(),
title: text('title').notNull(),
content: text('content').notNull(),
searchVector: tsvector('search_vector').generatedAlwaysAs(
(): SQL => sql`to_tsvector('english', ${posts.title} || ' ' || ${posts.content})`,
),
}, (table) => [
index('posts_search_idx').using('gin', table.searchVector),
]);
// Query
const searchResults = await db
.select()
.from(posts)
.where(sql`${posts.searchVector} @@ plainto_tsquery('english', ${searchTerm})`)
.orderBy(sql`ts_rank(${posts.searchVector}, plainto_tsquery('english', ${searchTerm})) DESC`);
```
---
## Useful System Views
### Connection Info
```sql
-- Active connections
SELECT * FROM pg_stat_activity;
-- Connection count by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
```
### Table Statistics
```sql
-- Table sizes
SELECT
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;
-- Row counts and dead tuples
SELECT
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum
FROM pg_stat_user_tables;
```
### Index Usage
```sql
-- Index usage statistics
SELECT
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- Unused indexes
SELECT
indexrelname,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
```
### Lock Monitoring
```sql
-- Current locks
SELECT
pg_locks.pid,
pg_class.relname,
pg_locks.mode,
pg_locks.granted
FROM pg_locks
JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE pg_class.relkind = 'r';
-- Blocking queries
SELECT
blocked.pid AS blocked_pid,
blocking.pid AS blocking_pid,
blocked.query AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
AND blocked_locks.relation = blocking_locks.relation
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;
```
---
## Maintenance
### Autovacuum Tuning
```sql
-- Global settings
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1; -- default 0.2
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05; -- default 0.1
-- Per-table for high-write tables
ALTER TABLE events SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_analyze_scale_factor = 0.005
);
```
### Reindexing
```sql
-- Rebuild without locking (CONCURRENTLY)
REINDEX INDEX CONCURRENTLY orders_user_idx;
-- Rebuild all indexes on table
REINDEX TABLE CONCURRENTLY orders;
```
### Checkpoints
```sql
-- Reduce checkpoint frequency for lower I/O
ALTER SYSTEM SET checkpoint_timeout = '15min'; -- default 5min
ALTER SYSTEM SET max_wal_size = '4GB'; -- default 1GB
```
### Statistics
```sql
-- Update statistics for a table
ANALYZE orders;
-- Update all statistics
ANALYZE;
-- Check when last analyzed
SELECT relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables;
```
references/QUERIES.md
# Drizzle Query Patterns
Comprehensive reference for querying PostgreSQL with Drizzle ORM using the
SQL-like API (`db.select()` etc. — identical in 0.x and v1.0). For `db.query.*`
relational queries see [RELATIONS.md](RELATIONS.md).
## Contents
- [Query Operators](#query-operators)
- [Select Queries](#select-queries)
- [Ordering & Pagination](#ordering--pagination)
- [Joins](#joins)
- [Aggregations](#aggregations)
- [Subqueries](#subqueries)
- [Insert Operations](#insert-operations)
- [Update Operations](#update-operations)
- [Delete Operations](#delete-operations)
- [Raw SQL](#raw-sql)
- [Prepared Statements](#prepared-statements)
- [Transactions](#transactions)
---
## Query Operators
### Imports
```typescript
import {
eq, // =
ne, // <>
gt, // >
gte, // >=
lt, // <
lte, // <=
like, // LIKE (case-sensitive)
ilike, // ILIKE (case-insensitive)
notLike,
notIlike,
inArray, // IN
notInArray, // NOT IN
isNull,
isNotNull,
between,
notBetween,
and,
or,
not,
exists,
notExists,
arrayContains,
arrayContained,
arrayOverlaps,
sql,
} from 'drizzle-orm';
```
---
## Select Queries
### Basic Select
```typescript
// All columns
const allUsers = await db.select().from(users);
// Specific columns
const emails = await db.select({
id: users.id,
email: users.email
}).from(users);
// With alias
const result = await db.select({
identifier: users.id,
mail: users.email,
}).from(users);
```
### Where Clause
```typescript
// Single condition
const user = await db
.select()
.from(users)
.where(eq(users.id, userId));
// Multiple conditions (AND)
const activeAdmins = await db
.select()
.from(users)
.where(and(
eq(users.status, 'active'),
eq(users.role, 'admin'),
));
// OR conditions
const flaggedUsers = await db
.select()
.from(users)
.where(or(
eq(users.status, 'suspended'),
gt(users.warningCount, 3),
));
// Complex nested conditions
const result = await db
.select()
.from(users)
.where(and(
eq(users.status, 'active'),
or(
eq(users.role, 'admin'),
gt(users.score, 100),
),
));
```
### Comparison Operators
```typescript
// Equality
.where(eq(users.status, 'active'))
// Not equal
.where(ne(users.status, 'deleted'))
// Greater than / less than
.where(gt(users.age, 18))
.where(gte(users.age, 18))
.where(lt(users.age, 65))
.where(lte(users.age, 65))
// Between
.where(between(users.age, 18, 65))
.where(notBetween(products.price, 0, 10))
// Null checks
.where(isNull(users.deletedAt))
.where(isNotNull(users.verifiedAt))
// IN / NOT IN
.where(inArray(users.status, ['active', 'pending']))
.where(notInArray(users.role, ['banned', 'suspended']))
```
### Pattern Matching
```typescript
// Case-sensitive LIKE
.where(like(users.name, 'John%')) // Starts with
.where(like(users.name, '%Smith')) // Ends with
.where(like(users.name, '%John%')) // Contains
// Case-insensitive ILIKE
.where(ilike(users.email, '%@gmail.com'))
// Negated
.where(notLike(users.name, 'Test%'))
.where(notIlike(users.email, '%spam%'))
```
### Conditional Filters
Build dynamic queries by passing `undefined` to skip conditions:
```typescript
interface Filters {
search?: string;
categoryId?: string;
minPrice?: number;
maxPrice?: number;
}
async function getPosts(filters: Filters) {
return db
.select()
.from(posts)
.where(and(
eq(posts.published, true),
filters.search
? ilike(posts.title, `%${filters.search}%`)
: undefined,
filters.categoryId
? eq(posts.categoryId, filters.categoryId)
: undefined,
filters.minPrice !== undefined // not truthiness: 0 is a valid price
? gte(posts.price, filters.minPrice)
: undefined,
filters.maxPrice !== undefined
? lte(posts.price, filters.maxPrice)
: undefined,
));
}
```
`and()`/`or()` ignore `undefined` arguments, which is what makes this pattern
work. An empty `and()` is `undefined`, so `.where(undefined)` returns all rows.
---
## Ordering & Pagination
### Order By
```typescript
import { asc, desc } from 'drizzle-orm';
// Single column
const newest = await db
.select()
.from(posts)
.orderBy(desc(posts.createdAt));
// Multiple columns
const sorted = await db
.select()
.from(users)
.orderBy(asc(users.lastName), asc(users.firstName));
// Nulls handling
.orderBy(sql`${users.name} NULLS LAST`)
```
### Limit & Offset
```typescript
// Basic pagination
const page1 = await db
.select()
.from(posts)
.orderBy(desc(posts.createdAt))
.limit(20)
.offset(0);
// Page helper
async function getPage(page: number, pageSize: number = 20) {
return db
.select()
.from(posts)
.orderBy(desc(posts.createdAt))
.limit(pageSize)
.offset((page - 1) * pageSize);
}
```
### Cursor-Based Pagination (Better Performance)
```typescript
async function getPostsAfter(cursor?: string, limit = 20) {
return db
.select()
.from(posts)
.where(cursor ? lt(posts.id, cursor) : undefined)
.orderBy(desc(posts.id))
.limit(limit);
}
```
---
## Joins
### Left Join
```typescript
const usersWithPosts = await db
.select()
.from(users)
.leftJoin(posts, eq(posts.authorId, users.id));
// Result type: { users: User, posts: Post | null }[]
```
### Inner Join
```typescript
const usersWithPosts = await db
.select()
.from(users)
.innerJoin(posts, eq(posts.authorId, users.id));
// Only users who have posts
```
### Right Join
```typescript
const postsWithUsers = await db
.select()
.from(posts)
.rightJoin(users, eq(posts.authorId, users.id));
```
### Full Join
```typescript
const all = await db
.select()
.from(users)
.fullJoin(posts, eq(posts.authorId, users.id));
```
### Multiple Joins
```typescript
const fullData = await db
.select({
order: orders,
user: users,
product: products,
})
.from(orders)
.leftJoin(users, eq(orders.userId, users.id))
.leftJoin(products, eq(orders.productId, products.id));
```
### Join with Selected Columns
```typescript
const result = await db
.select({
userName: users.name,
userEmail: users.email,
postTitle: posts.title,
postDate: posts.createdAt,
})
.from(users)
.innerJoin(posts, eq(posts.authorId, users.id));
```
---
## Aggregations
### Imports
```typescript
import { count, sum, avg, min, max, countDistinct } from 'drizzle-orm';
```
### Basic Aggregates
```typescript
// Count all rows — shorthand
const total = await db.$count(users); // number
const active = await db.$count(users, eq(users.status, 'active'));
// Count all rows — explicit
const [{ total }] = await db
.select({ total: count() })
.from(users);
// Count with condition
const [{ activeCount }] = await db
.select({ activeCount: count() })
.from(users)
.where(eq(users.status, 'active'));
// Count distinct
const [{ uniqueAuthors }] = await db
.select({ uniqueAuthors: countDistinct(posts.authorId) })
.from(posts);
// Sum
const [{ totalRevenue }] = await db
.select({ totalRevenue: sum(orders.amount) })
.from(orders);
// Average
const [{ avgPrice }] = await db
.select({ avgPrice: avg(products.price) })
.from(products);
// Min / Max
const [{ cheapest, expensive }] = await db
.select({
cheapest: min(products.price),
expensive: max(products.price),
})
.from(products);
```
### Group By
```typescript
const postsByAuthor = await db
.select({
authorId: posts.authorId,
postCount: count(),
totalViews: sum(posts.views),
})
.from(posts)
.groupBy(posts.authorId);
```
### Having
```typescript
const prolificAuthors = await db
.select({
authorId: posts.authorId,
postCount: count(),
})
.from(posts)
.groupBy(posts.authorId)
.having(gt(count(), 10));
```
### Group By with Join
```typescript
const authorStats = await db
.select({
authorName: users.name,
postCount: count(posts.id),
totalViews: sum(posts.views),
})
.from(users)
.leftJoin(posts, eq(posts.authorId, users.id))
.groupBy(users.id, users.name);
```
---
## Subqueries
### Subquery in FROM
```typescript
const subquery = db
.select({
authorId: posts.authorId,
postCount: sql<number>`count(*)`.as('post_count'),
})
.from(posts)
.groupBy(posts.authorId)
.as('author_stats');
const usersWithStats = await db
.select({
user: users,
postCount: subquery.postCount,
})
.from(users)
.leftJoin(subquery, eq(users.id, subquery.authorId));
```
### Subquery in WHERE (EXISTS)
```typescript
// Users who have at least one post
const usersWithPosts = await db
.select()
.from(users)
.where(
exists(
db.select().from(posts).where(eq(posts.authorId, users.id))
)
);
// Users who have NO posts
const usersWithoutPosts = await db
.select()
.from(users)
.where(
notExists(
db.select().from(posts).where(eq(posts.authorId, users.id))
)
);
```
### Correlated Scalar Subquery
Self-referencing correlations need a table alias — comparing `posts.authorId`
to itself is always true:
```typescript
import { alias } from 'drizzle-orm/pg-core';
const p = alias(posts, 'p');
const postsWithAuthorCount = await db
.select({
post: posts,
authorPostCount: sql<number>`(
SELECT count(*) FROM ${p} WHERE ${p.authorId} = ${posts.authorId}
)`.as('author_post_count'),
})
.from(posts);
```
---
## Insert Operations
### Single Insert
```typescript
const [newUser] = await db
.insert(users)
.values({
email: 'user@example.com',
name: 'John Doe',
})
.returning();
```
### Multiple Insert
```typescript
const newUsers = await db
.insert(users)
.values([
{ email: 'user1@example.com', name: 'User 1' },
{ email: 'user2@example.com', name: 'User 2' },
{ email: 'user3@example.com', name: 'User 3' },
])
.returning();
```
### Upsert (On Conflict)
```typescript
// Update on conflict
await db
.insert(users)
.values({ email: 'user@example.com', name: 'John' })
.onConflictDoUpdate({
target: users.email,
set: {
name: 'John Updated',
updatedAt: new Date(),
},
});
// Ignore on conflict
await db
.insert(users)
.values({ email: 'user@example.com', name: 'John' })
.onConflictDoNothing();
// Composite key conflict
await db
.insert(usersToGroups)
.values({ userId, groupId })
.onConflictDoNothing({
target: [usersToGroups.userId, usersToGroups.groupId],
});
```
### Insert from Select
```typescript
await db
.insert(archivedPosts)
.select()
.from(posts)
.where(lt(posts.createdAt, oneYearAgo));
```
---
## Update Operations
### Basic Update
```typescript
await db
.update(users)
.set({ status: 'active' })
.where(eq(users.id, userId));
```
### Update with Returning
```typescript
const [updated] = await db
.update(users)
.set({
status: 'active',
updatedAt: new Date(),
})
.where(eq(users.id, userId))
.returning();
```
### Increment/Decrement
```typescript
// Increment
await db
.update(posts)
.set({ views: sql`${posts.views} + 1` })
.where(eq(posts.id, postId));
// Decrement with floor
await db
.update(products)
.set({ stock: sql`GREATEST(${products.stock} - 1, 0)` })
.where(eq(products.id, productId));
```
### Conditional Update
```typescript
await db
.update(users)
.set({
status: sql`CASE WHEN ${users.score} > 100 THEN 'gold' ELSE 'silver' END`,
})
.where(eq(users.role, 'member'));
```
---
## Delete Operations
### Basic Delete
```typescript
await db
.delete(users)
.where(eq(users.id, userId));
```
### Delete with Returning
```typescript
const [deleted] = await db
.delete(users)
.where(eq(users.id, userId))
.returning();
```
### Soft Delete
```typescript
await db
.update(users)
.set({ deletedAt: new Date() })
.where(eq(users.id, userId));
```
### Delete with Subquery
```typescript
// Delete inactive users who have no posts
await db
.delete(users)
.where(and(
eq(users.status, 'inactive'),
notExists(
db.select().from(posts).where(eq(posts.authorId, users.id))
),
));
```
---
## Raw SQL
### SQL Template
```typescript
import { sql } from 'drizzle-orm';
// In select
const result = await db
.select({
id: users.id,
fullName: sql<string>`${users.firstName} || ' ' || ${users.lastName}`,
})
.from(users);
// In where
.where(sql`${users.email} ~* ${pattern}`) // PostgreSQL regex
// Typed raw query
const rows = await db.execute<{ id: string; name: string }>(
sql`SELECT id, name FROM users WHERE status = 'active'`
);
```
Note on `db.execute()` result shape: with postgres.js the result is the row
array itself; with node-postgres it's a `pg` result object — read `result.rows`.
### SQL Operators
```typescript
// JSON operators
.where(sql`${events.data}->>'type' = 'purchase'`)
.where(sql`${events.data} @> '{"status": "active"}'::jsonb`)
// Array operators
.where(sql`${posts.tags} @> ARRAY['typescript']`)
// Full-text search
.where(sql`to_tsvector('english', ${posts.content}) @@ plainto_tsquery('english', ${searchTerm})`)
```
---
## Prepared Statements
Improve performance by preparing queries once. Caveat: named server-side
prepared statements break behind transaction-mode poolers (PgBouncer < 1.21,
Supavisor) — see
[PERFORMANCE.md](PERFORMANCE.md#transaction-pooling-limitations).
```typescript
// Prepare
const getUserById = db
.select()
.from(users)
.where(eq(users.id, sql.placeholder('id')))
.prepare('get_user_by_id');
// Execute multiple times
const user1 = await getUserById.execute({ id: 'uuid-1' });
const user2 = await getUserById.execute({ id: 'uuid-2' });
// Prepared insert
const createUser = db
.insert(users)
.values({
email: sql.placeholder('email'),
name: sql.placeholder('name'),
})
.returning()
.prepare('create_user');
const newUser = await createUser.execute({
email: 'user@example.com',
name: 'John',
});
```
---
## Transactions
### Basic Transaction
```typescript
const result = await db.transaction(async (tx) => {
const [user] = await tx.insert(users).values({ email, name }).returning();
await tx.insert(profiles).values({ userId: user.id, bio: '' });
return user;
});
```
Use `tx` for every statement inside the callback. A query on `db` runs on a
different connection outside the transaction and will not roll back.
### Nested Transactions (Savepoints)
```typescript
await db.transaction(async (tx) => {
await tx.insert(users).values({ ... });
try {
await tx.transaction(async (tx2) => {
// Creates savepoint
await tx2.insert(riskyTable).values({ ... });
// If this throws, only tx2 is rolled back
});
} catch (e) {
// Handle savepoint rollback
}
// Outer transaction continues
await tx.insert(logs).values({ ... });
});
```
### Rollback
```typescript
await db.transaction(async (tx) => {
const [user] = await tx.insert(users).values({ ... }).returning();
const balance = await checkBalance(user.id);
if (balance < 0) {
tx.rollback(); // Throws to abort entire transaction
}
await tx.insert(orders).values({ userId: user.id, ... });
});
```
### Transaction Isolation
```typescript
await db.transaction(async (tx) => {
// ...
}, {
isolationLevel: 'serializable', // read committed, repeatable read, serializable
accessMode: 'read write', // read only, read write
});
```
references/RELATIONS.md
# Drizzle Relations & Relational Queries
Comprehensive reference for defining relations and using the relational queries API.
## Contents
- [Overview](#overview)
- [Defining Relations](#defining-relations)
- [One-to-Many](#one-to-many)
- [One-to-One](#one-to-one)
- [Many-to-Many](#many-to-many)
- [Self-Referential](#self-referential)
- [Relational Queries API](#relational-queries-api)
- [Complex Examples](#complex-examples)
- [Type Inference](#type-inference)
- [Relations vs Joins](#relations-vs-joins)
- [Relational Queries v2 (drizzle-orm v1.0)](#relational-queries-v2-drizzle-orm-v10)
---
## Overview
Drizzle has two query APIs:
| API | Use Case | N+1 Safe |
|-----|----------|----------|
| **SQL-like** (`db.select()...`) | Complex queries, joins, aggregations | Manual |
| **Relational** (`db.query...`) | Nested data, simple CRUD | Yes |
Relations are **application-level** (not database constraints). They enable the
relational queries API. Always define both the FK (`.references()` in the table)
and the relation — one does not imply the other.
**Version note:** everything up to the final section uses the **stable 0.x**
`relations()` API (npm `latest`). drizzle-orm v1.0 (beta/RC — and the syntax shown
on orm.drizzle.team's main docs pages) replaces it with `defineRelations()`; see
[Relational Queries v2](#relational-queries-v2-drizzle-orm-v10). Never mix the two
APIs in one project.
---
## Defining Relations
### Imports
```typescript
import { relations } from 'drizzle-orm';
import { pgTable, uuid, text, timestamp, primaryKey } from 'drizzle-orm/pg-core';
```
---
## One-to-Many
A user has many posts. A post belongs to one user.
```typescript
// Tables
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
name: text('name').notNull(),
});
export const posts = pgTable('posts', {
id: uuid('id').primaryKey().defaultRandom(),
title: text('title').notNull(),
authorId: uuid('author_id').notNull().references(() => users.id),
});
// Relations
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 Examples
```typescript
// Get user with all their posts
const userWithPosts = await db.query.users.findFirst({
where: eq(users.id, userId),
with: { posts: true },
});
// Get post with author
const postWithAuthor = await db.query.posts.findFirst({
where: eq(posts.id, postId),
with: { author: true },
});
```
---
## One-to-One
A user has one profile. A profile belongs to one user.
```typescript
// Tables
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
email: text('email').notNull(),
});
export const profiles = pgTable('profiles', {
id: uuid('id').primaryKey().defaultRandom(),
userId: uuid('user_id').notNull().unique().references(() => users.id),
bio: text('bio'),
avatarUrl: text('avatar_url'),
});
// Relations
export const usersRelations = relations(users, ({ one }) => ({
// No config on this side: the FK lives on profiles, so Drizzle
// infers the join from profilesRelations below
profile: one(profiles),
}));
export const profilesRelations = relations(profiles, ({ one }) => ({
user: one(users, {
fields: [profiles.userId],
references: [users.id],
}),
}));
```
### Query Examples
```typescript
// Get user with profile
const userWithProfile = await db.query.users.findFirst({
where: eq(users.id, userId),
with: { profile: true },
});
// Get profile with user
const profileWithUser = await db.query.profiles.findFirst({
where: eq(profiles.userId, userId),
with: { user: true },
});
```
---
## Many-to-Many
Users belong to many groups. Groups have many users.
```typescript
// Tables
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
name: text('name').notNull(),
});
export const groups = pgTable('groups', {
id: uuid('id').primaryKey().defaultRandom(),
name: text('name').notNull(),
});
// Junction table
export const usersToGroups = pgTable('users_to_groups', {
userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
groupId: uuid('group_id').notNull().references(() => groups.id, { onDelete: 'cascade' }),
joinedAt: timestamp('joined_at').notNull().defaultNow(),
role: text('role').notNull().default('member'),
}, (table) => [
primaryKey({ columns: [table.userId, table.groupId] }),
]);
// Relations
export const usersRelations = relations(users, ({ many }) => ({
usersToGroups: many(usersToGroups),
}));
export const groupsRelations = relations(groups, ({ 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],
}),
}));
```
### Query Examples
```typescript
// Get user with all groups
const userWithGroups = await db.query.users.findFirst({
where: eq(users.id, userId),
with: {
usersToGroups: {
with: { group: true },
},
},
});
// Flatten the result
const groups = userWithGroups?.usersToGroups.map(utg => ({
...utg.group,
joinedAt: utg.joinedAt,
role: utg.role,
}));
// Get group with all members
const groupWithMembers = await db.query.groups.findFirst({
where: eq(groups.id, groupId),
with: {
usersToGroups: {
with: { user: true },
},
},
});
```
---
## Self-Referential
A category can have a parent category and child categories.
```typescript
import { AnyPgColumn } from 'drizzle-orm/pg-core';
export const categories = pgTable('categories', {
id: uuid('id').primaryKey().defaultRandom(),
name: text('name').notNull(),
parentId: uuid('parent_id').references((): AnyPgColumn => categories.id),
});
export const categoriesRelations = relations(categories, ({ one, many }) => ({
parent: one(categories, {
fields: [categories.parentId],
references: [categories.id],
relationName: 'parent',
}),
children: many(categories, {
relationName: 'parent',
}),
}));
```
### Query Examples
```typescript
// Get category with parent and children
const category = await db.query.categories.findFirst({
where: eq(categories.id, categoryId),
with: {
parent: true,
children: true,
},
});
// Get full tree (recursive CTE needed for deep trees)
const rootCategories = await db.query.categories.findMany({
where: isNull(categories.parentId),
with: {
children: {
with: {
children: true, // 2 levels deep
},
},
},
});
```
---
## Relational Queries API
### Setup
```typescript
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema }); // Pass schema!
```
### findMany
```typescript
// All users
const allUsers = await db.query.users.findMany();
// With filter
const activeUsers = await db.query.users.findMany({
where: eq(users.status, 'active'),
});
// With ordering
const sortedUsers = await db.query.users.findMany({
orderBy: [desc(users.createdAt)],
});
// With pagination
const page = await db.query.users.findMany({
limit: 20,
offset: 40,
});
```
### findFirst
```typescript
// First matching
const user = await db.query.users.findFirst({
where: eq(users.email, email),
});
// Returns undefined if not found
if (!user) {
throw new NotFoundError();
}
```
### With Relations
```typescript
// Single relation
const userWithPosts = await db.query.users.findFirst({
where: eq(users.id, userId),
with: { posts: true },
});
// Multiple relations
const userWithAll = await db.query.users.findFirst({
where: eq(users.id, userId),
with: {
posts: true,
profile: true,
usersToGroups: {
with: { group: true },
},
},
});
// Nested relations
const postWithAll = await db.query.posts.findFirst({
where: eq(posts.id, postId),
with: {
author: {
with: { profile: true },
},
comments: {
with: { author: true },
},
},
});
```
### Filtering Relations
```typescript
const userWithRecentPosts = await db.query.users.findFirst({
where: eq(users.id, userId),
with: {
posts: {
where: gt(posts.createdAt, oneWeekAgo),
orderBy: [desc(posts.createdAt)],
limit: 10,
},
},
});
```
### Selecting Columns
```typescript
// Select specific columns
const userBasic = await db.query.users.findFirst({
columns: {
id: true,
email: true,
// name: false (excluded by default when using columns)
},
});
// Exclude columns
const userWithoutPassword = await db.query.users.findFirst({
columns: {
password: false,
},
});
// Select columns on relations
const userWithPostTitles = await db.query.users.findFirst({
columns: { id: true, name: true },
with: {
posts: {
columns: { id: true, title: true },
},
},
});
```
### Custom Extras
```typescript
// Add computed fields
const usersWithPostCount = await db.query.users.findMany({
extras: {
postCount: sql<number>`(
SELECT count(*) FROM posts WHERE posts.author_id = users.id
)`.as('post_count'),
},
});
```
---
## Complex Examples
### Blog with Full Relations
```typescript
// Schema
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
name: text('name').notNull(),
email: text('email').notNull().unique(),
});
export const posts = pgTable('posts', {
id: uuid('id').primaryKey().defaultRandom(),
title: text('title').notNull(),
content: text('content').notNull(),
authorId: uuid('author_id').notNull().references(() => users.id),
createdAt: timestamp('created_at').notNull().defaultNow(),
});
export const comments = pgTable('comments', {
id: uuid('id').primaryKey().defaultRandom(),
content: text('content').notNull(),
postId: uuid('post_id').notNull().references(() => posts.id),
authorId: uuid('author_id').notNull().references(() => users.id),
createdAt: timestamp('created_at').notNull().defaultNow(),
});
export const likes = pgTable('likes', {
userId: uuid('user_id').notNull().references(() => users.id),
postId: uuid('post_id').notNull().references(() => posts.id),
}, (table) => [
primaryKey({ columns: [table.userId, table.postId] }),
]);
// Relations
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts),
comments: many(comments),
likes: many(likes),
}));
export const postsRelations = relations(posts, ({ one, many }) => ({
author: one(users, {
fields: [posts.authorId],
references: [users.id],
}),
comments: many(comments),
likes: many(likes),
}));
export const commentsRelations = relations(comments, ({ one }) => ({
post: one(posts, {
fields: [comments.postId],
references: [posts.id],
}),
author: one(users, {
fields: [comments.authorId],
references: [users.id],
}),
}));
export const likesRelations = relations(likes, ({ one }) => ({
user: one(users, {
fields: [likes.userId],
references: [users.id],
}),
post: one(posts, {
fields: [likes.postId],
references: [posts.id],
}),
}));
```
### Query Full Post
```typescript
const fullPost = await db.query.posts.findFirst({
where: eq(posts.id, postId),
with: {
author: {
columns: { id: true, name: true },
},
comments: {
orderBy: [desc(comments.createdAt)],
with: {
author: {
columns: { id: true, name: true },
},
},
},
likes: {
with: {
user: {
columns: { id: true, name: true },
},
},
},
},
});
// Result structure:
// {
// id, title, content, authorId, createdAt,
// author: { id, name },
// comments: [{ id, content, createdAt, author: { id, name } }],
// likes: [{ userId, postId, user: { id, name } }],
// }
```
### Feed Query
```typescript
const feed = await db.query.posts.findMany({
where: eq(posts.published, true),
orderBy: [desc(posts.createdAt)],
limit: 20,
columns: {
id: true,
title: true,
createdAt: true,
},
with: {
author: {
columns: { id: true, name: true },
},
},
extras: {
commentCount: sql<number>`(
SELECT count(*) FROM comments WHERE comments.post_id = posts.id
)`.as('comment_count'),
likeCount: sql<number>`(
SELECT count(*) FROM likes WHERE likes.post_id = posts.id
)`.as('like_count'),
},
});
```
---
## Type Inference
### Basic Types
```typescript
import type { InferSelectModel, InferInsertModel } from 'drizzle-orm';
type User = InferSelectModel<typeof users>;
type NewUser = InferInsertModel<typeof users>;
```
### Query Result Types
```typescript
// Type from a specific query result
type UserWithPosts = Awaited<ReturnType<typeof db.query.users.findFirst<{
with: { posts: true };
}>>>;
// Or infer from actual query
const getUser = async (id: string) => {
return db.query.users.findFirst({
where: eq(users.id, id),
with: { posts: true },
});
};
type UserWithPosts = NonNullable<Awaited<ReturnType<typeof getUser>>>;
```
### Partial Select Types
```typescript
const result = await db
.select({
id: users.id,
email: users.email,
})
.from(users);
type UserBasic = typeof result[number];
// { id: string; email: string }
```
---
## Relations vs Joins
### When to Use Relations (Relational Queries)
- Simple CRUD operations
- Fetching nested/hierarchical data
- When you want automatic N+1 prevention
- When the result should be nested objects
### When to Use Joins (SQL-like Queries)
- Complex aggregations
- Filtering based on related data
- Custom column selection across tables
- Performance-critical queries with specific needs
### Example Comparison
```typescript
// Relational - nested result
const userWithPosts = await db.query.users.findFirst({
where: eq(users.id, userId),
with: { posts: true },
});
// { id, name, posts: [{ id, title }, ...] }
// Join - flat result
const userWithPosts = await db
.select()
.from(users)
.leftJoin(posts, eq(posts.authorId, users.id))
.where(eq(users.id, userId));
// [{ users: { id, name }, posts: { id, title } | null }, ...]
```
---
## Relational Queries v2 (drizzle-orm v1.0)
drizzle-orm v1.0 (in beta/RC as of mid-2026; check `package.json`) removes the
`relations()` API above and replaces it with **RQB v2**. If the project depends on
`drizzle-orm@1.0.0-beta.*` / `1.0.0-rc.*` / `1.x`, use this section instead.
Summary of what changed:
| v1 (stable 0.x) | v2 (drizzle-orm 1.0) |
|-----------------|----------------------|
| `relations(table, ...)` per table | One `defineRelations(schema, (r) => ...)` for the whole schema |
| `drizzle(client, { schema })` | `drizzle(client, { relations })` |
| `fields` / `references` | `from` / `to` (single column or array) |
| `relationName: 'x'` for disambiguation | `alias: 'x'` |
| Many-to-many via explicit junction nesting | `.through()` — junction handled automatically |
| `where: eq(users.id, 1)` or callback | Object filters: `where: { id: 1 }` |
| `orderBy: [desc(users.createdAt)]` or callback | `orderBy: { createdAt: 'desc' }` |
| Cannot filter parents by related rows | Can: `where: { posts: { title: { like: 'M%' } } }` |
### Defining Relations (v2)
```typescript
// relations.ts
import { defineRelations } from 'drizzle-orm';
import * as schema from './schema';
export const relations = defineRelations(schema, (r) => ({
users: {
posts: r.many.posts(), // inferred from posts.author
// Many-to-many through a junction table:
groups: r.many.groups({
from: r.users.id.through(r.usersToGroups.userId),
to: r.groups.id.through(r.usersToGroups.groupId),
}),
},
posts: {
author: r.one.users({
from: r.posts.authorId,
to: r.users.id,
optional: false, // author is non-nullable in the result type
}),
},
}));
```
### Initialization (v2)
```typescript
import { drizzle } from 'drizzle-orm/node-postgres';
import { relations } from './relations';
export const db = drizzle(process.env.DATABASE_URL!, { relations });
```
### Querying (v2)
```typescript
// Object-style filters — no eq()/and() imports needed for db.query
const usersWithPosts = await db.query.users.findMany({
where: {
AND: [
{ OR: [{ id: { gt: 10 } }, { name: { like: 'John%' } }] },
{ age: 15 },
],
},
orderBy: { createdAt: 'desc' },
with: {
posts: {
where: { published: true },
limit: 10,
offset: 5, // offset on nested relations is new in v2
},
},
});
// Filter parents by related rows (impossible in v1)
const authorsOfMPosts = await db.query.users.findMany({
where: { posts: { title: { like: 'M%' } } },
});
// Many-to-many reads through the junction transparently
const usersWithGroups = await db.query.users.findMany({
with: { groups: true }, // no usersToGroups nesting needed
});
```
The SQL-like API (`db.select()`, `db.insert()`, operators like `eq`/`and`) is
unchanged in v1.0 — only relations and `db.query.*` filters changed. Migration
guide: https://orm.drizzle.team/docs/relations-v1-v2 and
https://orm.drizzle.team/docs/v0-v1-changes
references/SCHEMA.md
# Drizzle Schema Definition
Comprehensive reference for defining PostgreSQL schemas with Drizzle ORM
(stable 0.x syntax — see [RELATIONS.md](RELATIONS.md) for the v1.0 changes,
which affect relations/queries but not the column/constraint syntax below).
## Contents
- [Column Types](#column-types)
- [Primary Keys](#primary-keys)
- [String Types](#string-types)
- [Numeric Types](#numeric-types)
- [Date/Time Types](#datetime-types)
- [JSON/JSONB](#jsonjsonb)
- [Enums](#enums)
- [Arrays](#arrays)
- [Constraints](#constraints)
- [Foreign Keys](#foreign-keys)
- [Indexes](#indexes)
- [Composite Primary Key](#composite-primary-key)
- [Timestamps Pattern](#timestamps-pattern)
- [Soft Delete Pattern](#soft-delete-pattern)
- [Multi-Tenant Pattern](#multi-tenant-pattern)
- [Generated Columns](#generated-columns)
- [Schema Organization](#schema-organization)
---
## Column Types
### Imports
```typescript
import {
pgTable,
uuid,
text,
varchar,
char,
integer,
smallint,
bigint,
serial,
smallserial,
bigserial,
boolean,
timestamp,
date,
time,
interval,
numeric,
decimal,
real,
doublePrecision,
json,
jsonb,
pgEnum,
index,
uniqueIndex,
primaryKey,
foreignKey,
check,
} from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
```
---
## Primary Keys
### UUID (Recommended)
```typescript
// UUIDv4 - random
id: uuid('id').primaryKey().defaultRandom(),
// UUIDv7 - timestamp-ordered (PostgreSQL 18+, better index performance)
id: uuid('id').primaryKey().default(sql`uuidv7()`),
```
### Identity (Preferred over Serial for Integer PKs)
PostgreSQL recommends identity columns over `serial`: they are SQL-standard,
own their sequence (dropped with the column), and `GENERATED ALWAYS` prevents
accidental manual inserts into the ID column.
```typescript
// GENERATED ALWAYS AS IDENTITY
id: integer('id').primaryKey().generatedAlwaysAsIdentity(),
// GENERATED BY DEFAULT AS IDENTITY (allows manual override)
id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
// With sequence options
id: integer('id').primaryKey().generatedAlwaysAsIdentity({
startWith: 1000,
increment: 1,
minValue: 1,
maxValue: 2147483647,
cache: 100,
}),
```
### Serial (Legacy — avoid in new schemas)
```typescript
id: serial('id').primaryKey(), // 4 bytes, 1 to 2,147,483,647
id: bigserial('id').primaryKey(), // 8 bytes, 1 to 9,223,372,036,854,775,807
id: smallserial('id').primaryKey(), // 2 bytes, 1 to 32,767
```
---
## String Types
In PostgreSQL, `text` and `varchar` have identical performance — use `text`
unless you want the database to enforce a maximum length.
```typescript
// Unlimited length (most common)
name: text('name').notNull(),
// Variable length with limit
email: varchar('email', { length: 255 }).notNull(),
// Fixed length (padded with spaces)
countryCode: char('country_code', { length: 2 }),
// With default
status: text('status').notNull().default('pending'),
```
---
## Numeric Types
```typescript
// Integers
age: integer('age'), // 4 bytes, -2B to 2B
count: smallint('count'), // 2 bytes, -32K to 32K
bigNumber: bigint('big_number', { mode: 'number' }), // JS number
bigNumberStr: bigint('big_number', { mode: 'bigint' }), // JS BigInt
// Floating point (approximate)
score: real('score'), // 4 bytes, 6 decimal precision
amount: doublePrecision('amount'), // 8 bytes, 15 decimal precision
// Exact numeric (use for money!)
price: numeric('price', { precision: 10, scale: 2 }), // 12345678.90
total: decimal('total', { precision: 19, scale: 4 }), // alias for numeric
```
---
## Date/Time Types
```typescript
// Timestamp with timezone (RECOMMENDED)
createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
// Timestamp without timezone
localTime: timestamp('local_time', { withTimezone: false }),
// Timestamp modes
tsDate: timestamp('ts', { mode: 'date' }), // JavaScript Date (default)
tsString: timestamp('ts', { mode: 'string' }), // ISO string
tsNumber: timestamp('ts', { mode: 'number' }), // Unix timestamp
// Precision (0-6 microseconds)
precise: timestamp('precise', { precision: 6, withTimezone: true }),
// Date only
birthDate: date('birth_date'),
birthDateString: date('birth_date', { mode: 'string' }), // 'YYYY-MM-DD'
// Time only
openTime: time('open_time'),
openTimeWithTz: time('open_time', { withTimezone: true }),
// Interval
duration: interval('duration'),
```
---
## Boolean
```typescript
isActive: boolean('is_active').notNull().default(true),
verified: boolean('verified').default(false),
```
---
## JSON/JSONB
JSONB is preferred (binary format, indexable, faster queries).
```typescript
// Basic JSONB
data: jsonb('data'),
// Typed JSONB
settings: jsonb('settings').$type<{
theme: 'light' | 'dark';
notifications: boolean;
language: string;
}>(),
// With default
config: jsonb('config').$type<Record<string, unknown>>().default({}),
// JSON (text format, preserves whitespace/order)
rawData: json('raw_data'),
```
### Querying JSONB
```typescript
import { sql } from 'drizzle-orm';
// Access nested field
.where(sql`${events.data}->>'type' = 'purchase'`)
// Containment (@>)
.where(sql`${events.data} @> '{"status": "active"}'`)
// Key existence
.where(sql`${events.data} ? 'error_code'`)
```
---
## Enums
### PostgreSQL Enum
```typescript
// Define enum type
export const statusEnum = pgEnum('status', ['pending', 'active', 'archived']);
export const roleEnum = pgEnum('user_role', ['admin', 'user', 'guest']);
// Use in table
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
status: statusEnum('status').notNull().default('pending'),
role: roleEnum('role').notNull().default('user'),
});
```
### TypeScript Enum (Alternative)
```typescript
// Check constraint instead of pg enum (easier to modify)
export const users = pgTable('users', {
status: text('status', { enum: ['pending', 'active', 'archived'] }).notNull(),
});
```
---
## Arrays
```typescript
// Text array
tags: text('tags').array(),
// Integer array
scores: integer('scores').array(),
// Array with default
categories: text('categories').array().default([]),
// Querying arrays
import { arrayContains, arrayContained, arrayOverlaps } from 'drizzle-orm';
.where(arrayContains(posts.tags, ['typescript', 'drizzle']))
.where(arrayOverlaps(posts.tags, ['react', 'vue']))
```
---
## Constraints
### Not Null & Default
```typescript
email: text('email').notNull(),
status: text('status').notNull().default('active'),
createdAt: timestamp('created_at').notNull().defaultNow(),
```
### Unique
```typescript
// Column-level unique
email: text('email').notNull().unique(),
// Table-level unique (composite)
}, (table) => [
uniqueIndex('users_email_tenant_idx').on(table.email, table.tenantId),
]);
```
### Check Constraints
```typescript
export const products = pgTable('products', {
price: numeric('price', { precision: 10, scale: 2 }).notNull(),
quantity: integer('quantity').notNull(),
}, (table) => [
check('price_positive', sql`${table.price} > 0`),
check('quantity_non_negative', sql`${table.quantity} >= 0`),
]);
```
---
## Foreign Keys
### Inline Reference
```typescript
export const posts = pgTable('posts', {
id: uuid('id').primaryKey().defaultRandom(),
authorId: uuid('author_id')
.notNull()
.references(() => users.id),
});
```
### With Actions
```typescript
authorId: uuid('author_id')
.notNull()
.references(() => users.id, {
onDelete: 'cascade', // CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION
onUpdate: 'cascade',
}),
```
### Self-Referential
```typescript
import { AnyPgColumn } from 'drizzle-orm/pg-core';
export const categories = pgTable('categories', {
id: uuid('id').primaryKey().defaultRandom(),
name: text('name').notNull(),
parentId: uuid('parent_id').references((): AnyPgColumn => categories.id),
});
```
### Composite Foreign Key
```typescript
export const orderItems = pgTable('order_items', {
orderId: uuid('order_id').notNull(),
productId: uuid('product_id').notNull(),
quantity: integer('quantity').notNull(),
}, (table) => [
foreignKey({
columns: [table.orderId, table.productId],
foreignColumns: [orders.id, products.id],
}),
]);
```
---
## Indexes
### Single Column
```typescript
}, (table) => [
index('users_email_idx').on(table.email),
]);
```
### Composite Index
```typescript
}, (table) => [
index('orders_user_date_idx').on(table.userId, table.createdAt),
]);
```
### Unique Index
```typescript
}, (table) => [
uniqueIndex('users_email_unique').on(table.email),
]);
```
### Partial Index
```typescript
}, (table) => [
index('active_users_idx')
.on(table.email)
.where(sql`deleted_at IS NULL`),
]);
```
### Expression Index
```typescript
}, (table) => [
index('users_email_lower_idx').on(sql`lower(${table.email})`),
]);
```
### Index Types
Non-btree methods use `.using(method, ...columns)` — the method comes first,
columns/expressions after (there is no `.on(col).using(method)` chaining).
```typescript
// B-tree (default)
index('idx').on(table.column),
// Hash (equality only)
index('idx').using('hash', table.column),
// GIN (arrays, JSONB, full-text)
index('idx').using('gin', table.data),
// GIN with operator class (smaller/faster for JSONB containment-only)
index('idx').using('gin', table.data.op('jsonb_path_ops')),
// GiST (geometric, range, exclusion)
index('idx').using('gist', table.location),
// GIN over an expression (full-text without a stored tsvector column)
index('idx').using('gin', sql`to_tsvector('english', ${table.title})`),
```
---
## Composite Primary Key
```typescript
import { primaryKey } from 'drizzle-orm/pg-core';
export const usersToGroups = pgTable('users_to_groups', {
userId: uuid('user_id').notNull().references(() => users.id),
groupId: uuid('group_id').notNull().references(() => groups.id),
joinedAt: timestamp('joined_at').notNull().defaultNow(),
}, (table) => [
primaryKey({ columns: [table.userId, table.groupId] }),
]);
```
---
## Timestamps Pattern
### Reusable Timestamps
```typescript
const timestamps = {
createdAt: timestamp('created_at', { withTimezone: true })
.notNull()
.defaultNow(),
updatedAt: timestamp('updated_at', { withTimezone: true })
.notNull()
.defaultNow()
.$onUpdate(() => new Date()),
};
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
email: text('email').notNull(),
...timestamps,
});
export const posts = pgTable('posts', {
id: uuid('id').primaryKey().defaultRandom(),
title: text('title').notNull(),
...timestamps,
});
```
---
## Soft Delete Pattern
```typescript
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
email: text('email').notNull(),
deletedAt: timestamp('deleted_at', { withTimezone: true }),
...timestamps,
}, (table) => [
// Partial index for active users only
index('active_users_email_idx')
.on(table.email)
.where(sql`deleted_at IS NULL`),
]);
// Query active users
import { isNull } from 'drizzle-orm';
const activeUsers = await db
.select()
.from(users)
.where(isNull(users.deletedAt));
```
---
## Multi-Tenant Pattern
```typescript
export const tenants = pgTable('tenants', {
id: uuid('id').primaryKey().defaultRandom(),
name: text('name').notNull(),
});
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
tenantId: uuid('tenant_id').notNull().references(() => tenants.id),
email: text('email').notNull(),
}, (table) => [
// Unique email per tenant
uniqueIndex('users_tenant_email_idx').on(table.tenantId, table.email),
// Index for tenant queries
index('users_tenant_idx').on(table.tenantId),
]);
```
---
## Generated Columns
### Stored (Computed at Write)
Drizzle's `generatedAlwaysAs()` emits `GENERATED ALWAYS AS (...) STORED` for
PostgreSQL. Reference sibling columns via a `(): SQL =>` thunk so the table can
refer to itself:
```typescript
import { SQL, sql } from 'drizzle-orm';
export const products = pgTable('products', {
id: uuid('id').primaryKey().defaultRandom(),
price: numeric('price', { precision: 10, scale: 2 }).notNull(),
taxRate: numeric('tax_rate', { precision: 5, scale: 4 }).notNull(),
totalPrice: numeric('total_price', { precision: 10, scale: 2 })
.generatedAlwaysAs((): SQL => sql`${products.price} * (1 + ${products.taxRate})`),
});
```
A common use is a `tsvector` column for full-text search — see
[POSTGRES.md](POSTGRES.md#full-text-search).
### Virtual (PostgreSQL 18+, Computed at Read)
PostgreSQL 18 adds `VIRTUAL` generated columns (computed at read, not stored,
cannot be indexed). Drizzle's pg-core only generates the `STORED` form — to use
virtual columns, write the DDL in a custom migration
(`drizzle-kit generate --custom`):
```sql
ALTER TABLE products
ADD COLUMN display_price text GENERATED ALWAYS AS (price::text || ' USD') VIRTUAL;
```
---
## Schema Organization
### Single File (Small Projects)
```
src/db/
schema.ts # All tables, relations, types
index.ts # Database connection
```
### Multi-File (Large Projects)
```
src/db/
schema/
index.ts # Re-exports all
users.ts # User table + relations
posts.ts # Post table + relations
comments.ts # Comment table + relations
index.ts # Database connection
```
```typescript
// schema/users.ts
export const users = pgTable('users', { ... });
export const usersRelations = relations(users, ({ many }) => ({ ... }));
// schema/index.ts
export * from './users';
export * from './posts';
export * from './comments';
```
SKILL.md
---
name: postgres-drizzle
description: Proactively apply when creating APIs, backends, or data models. Triggers on PostgreSQL, Postgres, Drizzle, drizzle-orm, drizzle-kit, database, schema, pgTable, tables, columns, indexes, queries, migrations, ORM, relations, relational queries, joins, transactions, SQL, connection pooling, PgBouncer, N+1, JSONB, RLS, full-text search, partitioning. Use when writing database schemas, queries, migrations, connection setup, or any database-related code. PostgreSQL and Drizzle ORM best practices.
---
# PostgreSQL + Drizzle ORM
Type-safe database applications with PostgreSQL 17/18 and Drizzle ORM.
## Version Check (do this first)
Drizzle's API changed significantly between the stable 0.x line and v1.0. Check
`package.json` before writing code, because the two relations APIs are incompatible
and must not be mixed:
| `drizzle-orm` version | Relations API | Query filters |
|-----------------------|---------------|---------------|
| `^0.x` (npm `latest`) | `relations()` per table, `drizzle(client, { schema })` | `where: eq(users.id, id)` |
| `1.0.0-beta.*` / `1.0.0-rc.*` | `defineRelations()` once for all tables, `drizzle(client, { relations })` | `where: { id: userId }` (object style) |
The official docs site (orm.drizzle.team) documents v1.0 syntax on its main pages.
This skill defaults to **stable 0.x** syntax; for v1.0 projects read
[references/RELATIONS.md](references/RELATIONS.md) § "Relational Queries v2".
Signals a project is on v1.0: `defineRelations` imports, object-style `where`,
`r.many.posts()` in relations, `from`/`to` keys instead of `fields`/`references`.
## Essential Commands
```bash
npx drizzle-kit generate # Generate SQL migration from schema changes
npx drizzle-kit migrate # Apply pending migrations
npx drizzle-kit push # Push schema directly (dev/prototyping only)
npx drizzle-kit pull # Introspect existing DB into a schema file
npx drizzle-kit studio # Open database browser
npx drizzle-kit check # Detect migration collisions (race conditions)
```
## Quick Decision Trees
### "How do I model this relationship?"
```
Relationship type?
├─ One-to-many (user has posts) → FK on "many" side + relations()
├─ Many-to-many (posts have tags) → Junction table with composite PK + relations()
├─ One-to-one (user has profile) → FK with unique constraint
└─ Self-referential (comments) → FK to same table (type the ref as AnyPgColumn)
```
### "Why is my query slow?"
```
Slow query?
├─ Missing index on WHERE/JOIN columns → Add index (Postgres does NOT auto-index FKs)
├─ Query per row in a loop (N+1) → Use relational queries (`with:`) or a join
├─ Full table scan → EXPLAIN (ANALYZE, BUFFERS), add index
├─ Large OFFSET pagination → Switch to cursor/keyset pagination
└─ Connection overhead per request → Pool connections (pg Pool / postgres.js / PgBouncer)
```
### "Which drizzle-kit command?"
```
What do I need?
├─ Schema changed, need versioned SQL → drizzle-kit generate, review SQL, then migrate
├─ Apply migrations (CI, prod) → drizzle-kit migrate (or migrate() in code)
├─ Quick local iteration, throwaway DB → drizzle-kit push
├─ Adopt Drizzle on an existing DB → drizzle-kit pull
└─ Hand-written SQL (triggers, backfill)→ drizzle-kit generate --custom
```
## Connection Setup
```typescript
// node-postgres — pass a URL and Drizzle creates a Pool for you
import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema';
export const db = drizzle(process.env.DATABASE_URL!, { schema });
```
```typescript
// postgres.js — built-in pooling; set prepare: false behind a
// transaction-mode pooler (PgBouncer/Supavisor) unless it supports prepared statements
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
const client = postgres(process.env.DATABASE_URL!, { max: 20 });
export const db = drizzle(client, { schema });
```
Passing `schema` is what enables `db.query.*` relational queries — forgetting it is
the most common cause of "Property 'users' does not exist on type ...".
Optional: `drizzle(url, { schema, casing: 'snake_case' })` maps camelCase TS keys to
snake_case columns so you can write `pgTable('users', { createdAt: timestamp() })`
without repeating column names. Set the same `casing` in `drizzle.config.ts`.
## Schema Patterns
### Basic Table with Timestamps
```typescript
import { pgTable, uuid, varchar, timestamp } from 'drizzle-orm/pg-core';
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
email: varchar('email', { length: 255 }).notNull().unique(),
createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
updatedAt: timestamp('updated_at', { withTimezone: true })
.defaultNow()
.notNull()
.$onUpdate(() => new Date()),
});
```
Prefer `timestamp(..., { withTimezone: true })` (timestamptz) — naive timestamps
cause silent timezone bugs. For integer PKs, prefer
`integer().primaryKey().generatedAlwaysAsIdentity()` over `serial()` (the
PostgreSQL-recommended form; serial is legacy).
### Foreign Key with Index
```typescript
import { index } from 'drizzle-orm/pg-core';
export const posts = pgTable('posts', {
id: uuid('id').primaryKey().defaultRandom(),
userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
title: varchar('title', { length: 255 }).notNull(),
}, (table) => [
// Postgres creates NO index for FK columns — add one or JOINs/cascades scan
index('posts_user_id_idx').on(table.userId),
]);
```
The third `pgTable` argument returns an **array** (the older object form is deprecated).
### Relations (stable 0.x API)
```typescript
import { relations } from 'drizzle-orm';
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts),
}));
export const postsRelations = relations(posts, ({ one }) => ({
author: one(users, { fields: [posts.userId], references: [users.id] }),
}));
```
`relations()` is application-level metadata for `db.query.*` — it does not create
FK constraints. Define both (`.references()` for the DB, `relations()` for queries).
## Query Patterns
```typescript
import { eq } from 'drizzle-orm';
// Relational query — nested data in one round trip, no N+1
const usersWithPosts = await db.query.users.findMany({
with: { posts: true },
});
// SQL-like query — filters, joins, aggregations
const activeUsers = await db
.select()
.from(users)
.where(eq(users.status, 'active'));
// Transaction — all statements commit or roll back together
await db.transaction(async (tx) => {
const [user] = await tx.insert(users).values({ email }).returning();
await tx.insert(profiles).values({ userId: user.id });
});
```
Inside a transaction, always use `tx`, not `db` — queries on `db` escape the
transaction and won't roll back.
## Performance Checklist
| Priority | Check | Impact |
|----------|-------|--------|
| CRITICAL | Index all foreign keys | Prevents full scans on JOINs and cascaded deletes |
| CRITICAL | Use relational queries or joins for nested data | Avoids N+1 |
| HIGH | Connection pooling in production | Each PG connection costs ~MBs of RAM |
| HIGH | `EXPLAIN (ANALYZE, BUFFERS)` slow queries | Identifies missing indexes |
| MEDIUM | Partial indexes for filtered subsets | Smaller, faster indexes |
| MEDIUM | UUIDv7 (`uuidv7()`, PG18+) or identity for PKs | Better index locality than UUIDv4 |
## Anti-Patterns
| Anti-Pattern | Problem | Fix |
|--------------|---------|-----|
| No FK index | Slow JOINs, slow cascades | Add index on every FK column |
| N+1 in loops | Query per row | `with:` relational queries or a join |
| One connection per request | Connection storms, RAM exhaustion | pg `Pool` / postgres.js `max` / PgBouncer |
| `push` in prod | No history, data-loss prompts | `generate` + `migrate` |
| Mixing 0.x `relations()` with v1.0 `defineRelations` | Type errors, broken `db.query` | Pick one per project (see Version Check) |
| Storing JSON as `text` | No validation, no indexing | `jsonb()` column + GIN index |
| `timestamp` without timezone | Silent TZ bugs | `{ withTimezone: true }` |
| Editing applied migration files | Checksum mismatch, drift | New migration (`generate` / `generate --custom`) |
## Reference Documentation
| Read this | When you are... |
|-----------|-----------------|
| [references/SCHEMA.md](references/SCHEMA.md) | Defining tables: column types, constraints, indexes, enums, generated columns |
| [references/QUERIES.md](references/QUERIES.md) | Writing selects, inserts, upserts, transactions, prepared statements |
| [references/RELATIONS.md](references/RELATIONS.md) | Modeling relations or using `db.query.*` — includes the v1.0 RQB v2 API |
| [references/MIGRATIONS.md](references/MIGRATIONS.md) | Configuring drizzle-kit, generating/applying migrations, custom SQL |
| [references/POSTGRES.md](references/POSTGRES.md) | Using PG17/18 features, RLS, partitioning, JSONB ops, full-text search |
| [references/PERFORMANCE.md](references/PERFORMANCE.md) | Indexing strategy, EXPLAIN, pooling, pagination, bulk operations |
| [references/CHEATSHEET.md](references/CHEATSHEET.md) | Needing a compact syntax reminder for any of the above |
## Resources
- Drizzle ORM docs: https://orm.drizzle.team (documents v1.0 syntax; see Version Check)
- Drizzle GitHub: https://github.com/drizzle-team/drizzle-orm
- PostgreSQL docs: https://www.postgresql.org/docs/current/
- Row-Level Security: https://www.postgresql.org/docs/current/ddl-rowsecurity.html
- Index types: https://www.postgresql.org/docs/current/indexes-types.html