Postgres Drizzle

PostgreSQL + Drizzle ORM

Type-safe database applications with PostgreSQL 18 and Drizzle ORM.

Essential Commands

npx drizzle-kit generate   # Generate migration from schema changes
npx drizzle-kit migrate    # Apply pending migrations
npx drizzle-kit push       # Push schema directly (dev only!)
npx drizzle-kit studio     # Open database browser

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 + relations()
├─ One-to-one (user has profile)    → FK with unique constraint
└─ Self-referential (comments)      → FK to same table

"Why is my query slow?"

Slow query?
├─ Missing index on WHERE/JOIN columns  → Add index
├─ N+1 queries in loop                  → Use relational queries API
├─ Full table scan                      → EXPLAIN ANALYZE, add index
├─ Large result set                     → Add pagination (limit/offset)
└─ Connection overhead                  → Enable connection pooling

"Which drizzle-kit command?"

What do I need?
├─ Schema changed, need SQL migration   → drizzle-kit generate
├─ Apply migrations to database         → drizzle-kit migrate
├─ Quick dev iteration (no migration)   → drizzle-kit push
└─ Browse/edit data visually            → drizzle-kit studio

Directory Structure

src/db/
├── schema/
│   ├── index.ts          # Re-export all tables
│   ├── users.ts          # Table + relations
│   └── posts.ts          # Table + relations
├── db.ts                 # Connection with pooling
└── migrate.ts            # Migration runner
drizzle/
└── migrations/           # Generated SQL files
drizzle.config.ts         # drizzle-kit config

Schema Patterns

Basic Table with Timestamps

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

Foreign Key with Index

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull().references(() => users.id),
  title: varchar('title', { length: 255 }).notNull(),
}, (table) => [
  index('posts_user_id_idx').on(table.userId), // ALWAYS index FKs
]);

Relations

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.userId], references: [users.id] }),
}));

Query Patterns

Relational Query (Avoid N+1)

// ✓ Single query with nested data
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
});

Filtered Query

const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.status, 'active'));

Transaction

await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ email }).returning();
  await tx.insert(profiles).values({ userId: user.id });
});

Performance Checklist

Anti-Patterns (CRITICAL)

Reference Documentation

Resources

Drizzle ORM

• Official Documentation: https://orm.drizzle.team
• GitHub Repository: https://github.com/drizzle-team/drizzle-orm
• Drizzle Kit (Migrations): https://orm.drizzle.team/kit-docs/overview

PostgreSQL

• Official Documentation: https://www.postgresql.org/docs/
• SQL Commands Reference: https://www.postgresql.org/docs/current/sql-commands.html
• Performance Tips: https://www.postgresql.org/docs/current/performance-tips.html
• Index Types: https://www.postgresql.org/docs/current/indexes-types.html
• JSON Functions: https://www.postgresql.org/docs/current/functions-json.html
• Row Level Security: https://www.postgresql.org/docs/current/ddl-rowsecurity.html

postgres-drizzle

PostgreSQL and Drizzle ORM best practices. This skill activates automatically when writing database schemas, queries, migrations, or any database-related code.

Topics Covered

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 (concise overview)
• Reference Files:
• SCHEMA.md - Column types, constraints, indexes
• QUERIES.md - Query patterns and operators
• RELATIONS.md - Relations API and relational queries
• MIGRATIONS.md - drizzle-kit workflows
• POSTGRES.md - PostgreSQL 18 features
• PERFORMANCE.md - Optimization and pooling
• CHEATSHEET.md - Quick reference

Quick Start

import { pgTable, uuid, text, timestamp, index } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

// Schema
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
}, (table) => [
  index('users_email_idx').on(table.email),
]);

// Connection
const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema: { users } });

// 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/18/

Drizzle + PostgreSQL Quick Reference

---

Schema Definition

Column Types

import { pgTable, uuid, text, varchar, integer, bigint, boolean,
  timestamp, date, numeric, json, jsonb, pgEnum, serial } 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

email: text('email').notNull().unique(),
status: text('status').notNull().default('pending'),
price: numeric('price').check(sql`price > 0`),

// Foreign Key
authorId: uuid('author_id').references(() => users.id, { onDelete: 'cascade' }),

Indexes

}, (table) => [
  index('idx_name').on(table.column),                    // B-tree
  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
]);

Enums

export const statusEnum = pgEnum('status', ['pending', 'active', 'archived']);
status: statusEnum('status').default('pending'),

---

Relations

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

import type { InferSelectModel, InferInsertModel } from 'drizzle-orm';

type User = InferSelectModel<typeof users>;
type NewUser = InferInsertModel<typeof users>;

---

Query Operators

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

// 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

// 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

// 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

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

await db.delete(users).where(eq(users.id, id));

const [deleted] = await db.delete(users)
  .where(eq(users.id, id))
  .returning();

---

Transactions

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

import { count, sum, avg, min, max } from 'drizzle-orm';

// 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

const getUser = db.select().from(users)
  .where(eq(users.id, sql.placeholder('id')))
  .prepare('get_user');

const user = await getUser.execute({ id });

---

drizzle-kit Commands

npx drizzle-kit generate   # Generate migration from schema
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

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)

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

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export const db = drizzle(pool, { schema });

---

Error Codes

---

PostgreSQL 18 Features

---

Quick Tips

1. Use UUIDv7 over UUIDv4 for better index performance 2. Use relational queries to avoid N+1 3. Add indexes on foreign keys and frequently filtered columns 4. Use partial indexes for filtered subsets 5. Use prepared statements for repeated queries 6. Set `shared_buffers` to 25% of RAM 7. Use `EXPLAIN ANALYZE` to debug slow queries 8. Use transactions for related operations 9. Use connection pooling in production 10. Run `generate` not `push` for production migrations

Drizzle Migrations

Comprehensive reference for managing database migrations with drizzle-kit.

---

Configuration

drizzle.config.ts

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: verbose logging
  verbose: true,

  // Optional: strict mode
  strict: true,
});

Multiple Schema Files

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

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.

npx drizzle-kit generate

Output:

drizzle/
  0000_initial.sql
  0001_add_posts_table.sql
  meta/
    0000_snapshot.json
    0001_snapshot.json
    _journal.json

migrate

Apply pending migrations to the database.

npx drizzle-kit migrate

push

Push schema directly to database (no migration files).

npx drizzle-kit push

Use cases:

• Rapid prototyping
• Local development
• Schema experimentation

pull

Introspect existing database and generate schema.

npx drizzle-kit pull

Use cases:

• Adopting Drizzle on existing project
• Syncing schema from production
• Reverse engineering

check

Verify migration integrity.

npx drizzle-kit check

studio

Launch Drizzle Studio (database browser).

npx drizzle-kit studio

---

Migration Workflow

Development Workflow

# 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

// 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);

# Run before app starts
node -r tsx src/db/migrate.ts

Option 2: CI/CD Migration

# .github/workflows/deploy.yml
- name: Run migrations
  run: npx drizzle-kit migrate
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

Option 3: Application Startup

// 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

Transitioning from Push to Migrate

# 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

// 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:

ALTER TABLE "users" ADD COLUMN "name" text;

Adding a Required Column

// Add with default for existing rows
name: text('name').notNull().default('Unknown'),

Generated SQL:

ALTER TABLE "users" ADD COLUMN "name" text NOT NULL DEFAULT 'Unknown';

Renaming a Column

Warning: Drizzle may generate DROP + ADD instead of RENAME.

-- Manual migration
ALTER TABLE "users" RENAME COLUMN "name" TO "full_name";

Adding an Index

export const users = pgTable('users', {
  // ...
}, (table) => [
  index('users_email_idx').on(table.email),  // New index
]);

Generated SQL:

CREATE INDEX "users_email_idx" ON "users" ("email");

Adding a Foreign Key

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  authorId: uuid('author_id')
    .notNull()
    .references(() => users.id),  // New FK
});

Generated SQL:

ALTER TABLE "posts"
ADD CONSTRAINT "posts_author_id_users_id_fk"
FOREIGN KEY ("author_id") REFERENCES "users"("id");

Creating a New Table

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:

DROP TABLE "old_table";

---

Custom Migrations

Adding Custom SQL

Create a migration file manually:

-- drizzle/0005_custom_migration.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

-- drizzle/0006_migrate_data.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 migrations in __drizzle_migrations table:

SELECT * FROM __drizzle_migrations;

---

Rollback Strategies

Drizzle doesn't generate automatic rollbacks. Strategies:

Manual Rollback Script

-- 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:

// 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:

npx drizzle-kit generate
cat drizzle/0001_*.sql

2. Test Migrations

# 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:

BEGIN;
-- Migration statements
COMMIT;

5. Handle Downtime

For zero-downtime deployments:

-- Create index concurrently (no lock)
CREATE INDEX CONCURRENTLY users_email_idx ON users(email);

6. Version Control

# .gitignore
# Don't ignore migrations!
# drizzle/  <- Include this in version control

7. CI Validation

# Validate schema matches migrations
- name: Check migrations
  run: |
    npx drizzle-kit generate
    git diff --exit-code drizzle/

---

Troubleshooting

"Migration already applied"

# Check migration status
SELECT * FROM __drizzle_migrations;

# If needed, manually mark as applied
INSERT INTO __drizzle_migrations (hash, created_at)
VALUES ('migration_hash', NOW());

"Schema out of sync"

# 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:

-- Find dependent objects
SELECT * FROM pg_depend WHERE refobjid = 'table_name'::regclass;

Concurrent Migration Issues

Use advisory locks:

await db.execute(sql`SELECT pg_advisory_lock(12345)`);
await migrate(db, { migrationsFolder: './drizzle' });
await db.execute(sql`SELECT pg_advisory_unlock(12345)`);

Performance Optimization

Comprehensive reference for PostgreSQL and Drizzle ORM performance optimization.

---

Indexing Strategies

B-Tree Indexes (Default)

Best for: equality, range queries, sorting, LIKE with left anchor.

-- 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:

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:

-- 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:

}, (table) => [
  index('active_users_idx')
    .on(table.email)
    .where(sql`deleted_at IS NULL`),
]);

Covering Indexes (INCLUDE)

Include columns for index-only scans:

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

-- 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);

Expression Indexes

Index computed values:

-- 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.

-- 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

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = '123' AND status = 'pending';

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

-- 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

// 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' });

Avoid N+1 Queries

Bad (N+1):

const posts = await db.select().from(posts);
for (const post of posts) {
  const author = await db
    .select()
    .from(users)
    .where(eq(users.id, post.authorId));
  // N+1 queries!
}

Good (Relational Query):

const posts = await db.query.posts.findMany({
  with: { author: true },
});
// Single query with JOIN

Good (Manual Join):

const posts = await db
  .select()
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id));

Select Only Needed Columns

// Bad - selects all columns
const users = await db.select().from(users);

// Good - selects only needed columns
const users = await db
  .select({ id: users.id, email: users.email })
  .from(users);

// With relational queries
const users = await db.query.users.findMany({
  columns: { id: true, email: true },
});

Batch Operations

// 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

// 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

[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

Transaction Pooling Limitations

• No SET SESSION (use SET LOCAL)
• No PREPARE without config
• Temp tables must be created/dropped in same transaction

Drizzle with postgres.js

postgres.js has built-in connection pooling:

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
});

Drizzle with node-postgres Pool

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

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

// 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)

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)

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)

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

// 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

// 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

await db
  .insert(products)
  .values(products)
  .onConflictDoUpdate({
    target: products.sku,
    set: {
      price: sql`excluded.price`,
      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

-- Enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- 1 second

pg_stat_statements

-- 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

-- 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;

PostgreSQL 18 Features & Configuration

Comprehensive reference for PostgreSQL 18 features, configuration, and best practices.

---

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

Configuration

-- 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.

-- 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:

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:

id: uuid('id').primaryKey().default(sql`uuidv7()`),

---

Virtual Generated Columns

Virtual columns compute values at read time (not stored on disk):

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)
  display_price text GENERATED ALWAYS AS (price::text || ' USD')
);

Note: Virtual generated columns cannot be indexed.

---

Temporal Constraints

WITHOUT OVERLAPS for temporal database patterns:

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:

-- 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 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.

SHOW data_checksums;  -- on

---

Memory Configuration

shared_buffers

PostgreSQL's main memory cache. Set to ~25% of total RAM.

-- For 32GB RAM server
ALTER SYSTEM SET shared_buffers = '8GB';

work_mem

Memory for sort and hash operations per query.

-- 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:

ALTER SYSTEM SET maintenance_work_mem = '1GB';

effective_cache_size

Hint to planner about OS cache. Set to 50-75% of total RAM:

-- For 32GB RAM
ALTER SYSTEM SET effective_cache_size = '20GB';

---

Row-Level Security (RLS)

Enable RLS

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Force owner to also follow RLS (optional)
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

Policy Types

Multi-Tenant Pattern

-- 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

-- 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());

Using with Drizzle

// Set tenant context before queries
await db.execute(sql`SET app.current_tenant_id = ${tenantId}`);

// Or use transaction
await db.transaction(async (tx) => {
  await tx.execute(sql`SET LOCAL app.current_tenant_id = ${tenantId}`);
  // Queries now filtered by RLS
  const docs = await tx.select().from(documents);
});

---

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)

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)

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)

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

-- 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

JSONB Functions

-- 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)

-- 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

-- Add search column
ALTER TABLE posts ADD COLUMN search_vector tsvector;

-- Create GIN index
CREATE INDEX posts_search_idx ON posts USING gin(search_vector);

-- Create trigger to update vector
CREATE FUNCTION posts_search_trigger() RETURNS trigger AS $$
BEGIN
  NEW.search_vector := to_tsvector('english',
    coalesce(NEW.title, '') || ' ' || coalesce(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();

Querying

-- 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

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

-- Active connections
SELECT * FROM pg_stat_activity;

-- Connection count by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

Table Statistics

-- 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

-- 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

-- 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

-- 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

-- Rebuild without locking (CONCURRENTLY)
REINDEX INDEX CONCURRENTLY orders_user_idx;

-- Rebuild all indexes on table
REINDEX TABLE CONCURRENTLY orders;

Checkpoints

-- 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

-- 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;

Drizzle Query Patterns

Comprehensive reference for querying PostgreSQL with Drizzle ORM.

---

Query Operators

Imports

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

// 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

// 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

// 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

// 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:

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
        ? gte(posts.price, filters.minPrice)
        : undefined,
      filters.maxPrice
        ? lte(posts.price, filters.maxPrice)
        : undefined,
    ));
}

---

Ordering & Pagination

Order By

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

// 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)

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

const usersWithPosts = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id));

// Result type: { users: User, posts: Post | null }[]

Inner Join

const usersWithPosts = await db
  .select()
  .from(users)
  .innerJoin(posts, eq(posts.authorId, users.id));

// Only users who have posts

Right Join

const postsWithUsers = await db
  .select()
  .from(posts)
  .rightJoin(users, eq(posts.authorId, users.id));

Full Join

const all = await db
  .select()
  .from(users)
  .fullJoin(posts, eq(posts.authorId, users.id));

Multiple Joins

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

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

import { count, sum, avg, min, max, countDistinct } from 'drizzle-orm';

Basic Aggregates

// Count all rows
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

const postsByAuthor = await db
  .select({
    authorId: posts.authorId,
    postCount: count(),
    totalViews: sum(posts.views),
  })
  .from(posts)
  .groupBy(posts.authorId);

Having

const prolificAuthors = await db
  .select({
    authorId: posts.authorId,
    postCount: count(),
  })
  .from(posts)
  .groupBy(posts.authorId)
  .having(gt(count(), 10));

Group By with Join

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

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)

// 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))
    )
  );

Scalar Subquery

const postsWithAuthorCount = await db
  .select({
    post: posts,
    authorPostCount: db
      .select({ count: count() })
      .from(posts)
      .where(eq(posts.authorId, posts.authorId)),
  })
  .from(posts);

---

Insert Operations

Single Insert

const [newUser] = await db
  .insert(users)
  .values({
    email: 'user@example.com',
    name: 'John Doe',
  })
  .returning();

Multiple Insert

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)

// 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

await db
  .insert(archivedPosts)
  .select()
  .from(posts)
  .where(lt(posts.createdAt, oneYearAgo));

---

Update Operations

Basic Update

await db
  .update(users)
  .set({ status: 'active' })
  .where(eq(users.id, userId));

Update with Returning

const [updated] = await db
  .update(users)
  .set({
    status: 'active',
    updatedAt: new Date(),
  })
  .where(eq(users.id, userId))
  .returning();

Increment/Decrement

// 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

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

await db
  .delete(users)
  .where(eq(users.id, userId));

Delete with Returning

const [deleted] = await db
  .delete(users)
  .where(eq(users.id, userId))
  .returning();

Soft Delete

await db
  .update(users)
  .set({ deletedAt: new Date() })
  .where(eq(users.id, userId));

Delete with Subquery

// 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

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 users = await db.execute<{ id: string; name: string }>(
  sql`SELECT id, name FROM users WHERE status = 'active'`
);

SQL Operators

// 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:

// 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

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;
});

Nested Transactions (Savepoints)

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

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

await db.transaction(async (tx) => {
  // ...
}, {
  isolationLevel: 'serializable',  // read committed, repeatable read, serializable
  accessMode: 'read write',        // read only, read write
});

Drizzle Relations & Relational Queries

Comprehensive reference for defining relations and using the relational queries API.

---

Overview

Drizzle has two query APIs:

Relations are application-level (not database constraints). They enable the relational queries API.

---

Defining Relations

Imports

import { relations } from 'drizzle-orm';
import { pgTable, uuid, text, timestamp, integer } from 'drizzle-orm/pg-core';

---

One-to-Many

A user has many posts. A post belongs to one user.

// 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

// 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.

// 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 }) => ({
  profile: one(profiles),
}));

export const profilesRelations = relations(profiles, ({ one }) => ({
  user: one(users, {
    fields: [profiles.userId],
    references: [users.id],
  }),
}));

Query Examples

// 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.

// 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

// 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.

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

// 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

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

// 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

// 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

// 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

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

// 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

// 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

// 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

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

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

import type { InferSelectModel, InferInsertModel } from 'drizzle-orm';

type User = InferSelectModel<typeof users>;
type NewUser = InferInsertModel<typeof users>;

Query Result Types

// 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

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

// 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 }, ...]

Drizzle Schema Definition

Comprehensive reference for defining PostgreSQL schemas with Drizzle ORM.

---

Column Types

Imports

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)

// UUIDv4 - random
id: uuid('id').primaryKey().defaultRandom(),

// UUIDv7 - timestamp-ordered (PostgreSQL 18+, better index performance)
id: uuid('id').primaryKey().default(sql`uuidv7()`),

Identity (PostgreSQL Preferred over Serial)

// 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)

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

// 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

// 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

// 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

isActive: boolean('is_active').notNull().default(true),
verified: boolean('verified').default(false),

---

JSON/JSONB

JSONB is preferred (binary format, indexable, faster queries).

// 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

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

// 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)

// Check constraint instead of pg enum (easier to modify)
export const users = pgTable('users', {
  status: text('status', { enum: ['pending', 'active', 'archived'] }).notNull(),
});

---

Arrays

// 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

email: text('email').notNull(),
status: text('status').notNull().default('active'),
createdAt: timestamp('created_at').notNull().defaultNow(),

Unique

// 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

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

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  authorId: uuid('author_id')
    .notNull()
    .references(() => users.id),
});

With Actions

authorId: uuid('author_id')
  .notNull()
  .references(() => users.id, {
    onDelete: 'cascade',    // CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION
    onUpdate: 'cascade',
  }),

Self-Referential

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

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

}, (table) => [
  index('users_email_idx').on(table.email),
]);

Composite Index

}, (table) => [
  index('orders_user_date_idx').on(table.userId, table.createdAt),
]);

Unique Index

}, (table) => [
  uniqueIndex('users_email_unique').on(table.email),
]);

Partial Index

}, (table) => [
  index('active_users_idx')
    .on(table.email)
    .where(sql`deleted_at IS NULL`),
]);

Expression Index

}, (table) => [
  index('users_email_lower_idx').on(sql`lower(${table.email})`),
]);

Index Types

// B-tree (default)
index('idx').on(table.column),

// Hash (equality only)
index('idx').on(table.column).using('hash'),

// GIN (arrays, JSONB, full-text)
index('idx').on(table.data).using('gin'),

// GiST (geometric, full-text, range)
index('idx').on(table.location).using('gist'),

---

Composite Primary Key

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

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

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

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)

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`price * (1 + tax_rate)`),
});

Virtual (PostgreSQL 18+, Computed at Read)

// Virtual columns are not stored on disk
displayPrice: text('display_price')
  .generatedAlwaysAs(sql`price::text || ' USD'`),

---

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

// 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';