Drizzle ORM + PostgreSQL: The Complete Production Guide

I've used Prisma, TypeORM, Kysely, and raw pg over the past few years. Drizzle is the first ORM where I genuinely enjoy writing database code. It's not because it's trendy - it's because it gets out of your way and lets SQL be SQL, while giving you the type safety you actually want.

This is everything I've learned running Drizzle + PostgreSQL in production across multiple services. No fluff, no "hello world" - just the patterns, the gotchas, and the stuff nobody tells you.

Why Drizzle

Before diving in, let's be honest about why you'd pick Drizzle over the alternatives.

vs Prisma: Prisma generates a client at build time that hides SQL from you. That's great until you need a CTE, a window function, or a query that doesn't fit Prisma's mental model. Drizzle lets you write actual SQL-like queries that compile to real SQL. You own the query. Prisma also struggles with connection pooling in serverless - Drizzle works with any standard PostgreSQL driver.

vs TypeORM: TypeORM is decorator-heavy, opinionated, and its TypeScript support is inconsistent. Drizzle is purely functional, tree-shakeable, and has first-class TypeScript inference. No decorators, no magic, no runtime reflection.

vs Kysely: Kysely is a query builder, not an ORM. It's excellent for type-safe queries but doesn't handle schema definitions, migrations, or relation loading. Drizzle gives you schema + query builder + migrations + a Studio UI. It's Kysely with batteries included.

The pitch: Drizzle is a TypeScript ORM that looks like SQL, performs like a query builder, and gives you a full schema management toolkit. It supports PostgreSQL, MySQL, and SQLite, but PostgreSQL support is the most mature.

Project Setup

Installing Dependencies

npm install drizzle-orm postgres
npm install -D drizzle-kit

postgres is the driver. Drizzle supports multiple PostgreSQL drivers - postgres (Postgres.js), pg (node-postgres), @neondatabase/serverless, @vercel/postgres, and @electric-sql/pglite. Pick based on your runtime:

Driver	Best For
`postgres`	Node.js, general purpose, fastest
`@neondatabase/serverless`	Neon, Vercel Postgres, serverless
`@vercel/postgres`	Vercel managed Postgres
`pg`	Legacy projects, connection pooling with pgBouncer
`@electric-sql/pglite`	Embedded PostgreSQL, testing, edge

This guide uses postgres (Postgres.js) since it's the most common for production Node.js services.

Directory Structure

I keep a dedicated src/db directory for all database code:

src/
  db/
    index.ts          # Client instantiation
    schema.ts         # All table definitions
    relations.ts      # Relation definitions
    migrate.ts        # Migration runner (if needed)
    seed.ts           # Seed script
  drizzle/
    migrate.sql       # Generated SQL migrations
    meta/
      _journal.json   # Migration journal

Database Client

// src/db/index.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

const connectionString = process.env.DATABASE_URL!;

// postgres() returns a sql tagged template + connection pool
const client = postgres(connectionString, {
  max: 20,               // Connection pool size
  idle_timeout: 20,      // Close idle connections after 20s
  connect_timeout: 10,   // Fail connection attempts after 10s
  prepare: false,        // Disable prepared statements if using PgBouncer
});

export const db = drizzle(client, { schema });
export type Database = typeof db;

Key decisions here:

max: 20 - This is your connection pool ceiling. For most services, 20 is plenty. If you're running a high-concurrency API, bump to 50 but watch your PostgreSQL max_connections setting (usually 100 for managed services).
prepare: false - If you're behind PgBouncer in transaction mode, prepared statements break silently. Always set this to false if you're using connection pooling at the database level.
schema - Passing your schema to drizzle() enables the relational query API and gives you full type inference on db.query.*.

Schema Design

This is where Drizzle really shines. The schema file is the single source of truth for your database structure.

Basic Tables

// src/db/schema.ts
import {
  pgTable,
  text,
  varchar,
  integer,
  bigint,
  boolean,
  timestamp,
  uuid,
  jsonb,
  real,
  doublePrecision,
  pgEnum,
  index,
  uniqueIndex,
  unique,
  primaryKey,
} from 'drizzle-orm/pg-core';

export const userRoleEnum = pgEnum('user_role', ['admin', 'editor', 'viewer']);
export const planEnum = pgEnum('plan', ['free', 'pro', 'enterprise']);

export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: varchar('name', { length: 255 }).notNull(),
  role: userRoleEnum('role').notNull().default('viewer'),
  plan: planEnum('plan').notNull().default('free'),
  metadata: jsonb('metadata').$type<Record<string, unknown>>().default({}),
  emailVerified: boolean('email_verified').notNull().default(false),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (table) => [
  index('users_email_idx').on(table.email),
  index('users_role_idx').on(table.role),
  uniqueIndex('users_email_unique_idx').on(table.email),
]);

Notes on PostgreSQL types:

uuid with defaultRandom() generates UUIDs client-side. If you want the database to generate them, use uuid('id').primaryKey().defaultRandom() and let PostgreSQL's gen_random_uuid() handle it - but Drizzle's approach is fine and avoids an extra round trip.
jsonb is almost always the right choice over json. It's stored in binary format, supports indexing, and is faster to query.
$type<Record<string, unknown>>() gives you TypeScript type safety on JSON columns. Without it, the column is typed as unknown.
Always use withTimezone: true on timestamps. Without it, PostgreSQL strips timezone info and you'll have a bad time with daylight saving transitions.

Composite Keys and Junction Tables

For many-to-many relationships:

export const teamMembers = pgTable('team_members', {
  teamId: uuid('team_id').notNull().references(() => teams.id, { onDelete: 'cascade' }),
  userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  role: varchar('role', { length: 50 }).notNull().default('member'),
  joinedAt: timestamp('joined_at', { withTimezone: true }).notNull().defaultNow(),
}, (table) => [
  primaryKey({ columns: [table.teamId, table.userId] }),
  index('team_members_user_idx').on(table.userId),
]);

Soft Deletes

Drizzle doesn't have built-in soft deletes, but here's the pattern I use:

export const posts = pgTable('posts', {
  id: uuid('id').defaultRandom().primaryKey(),
  title: varchar('title', { length: 500 }).notNull(),
  body: text('body').notNull(),
  published: boolean('published').notNull().default(false),
  deletedAt: timestamp('deleted_at', { withTimezone: true }),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (table) => [
  index('posts_published_idx').on(table.published),
  // Partial index - only indexes non-deleted rows. Huge performance win.
  index('posts_active_idx').on(table.createdAt).where(sql`${table.deletedAt} IS NULL`),
]);

That partial index is critical. Without it, every query with WHERE deletedAt IS NULL scans the entire table including soft-deleted rows.

Views and Materialized Views

import { pgView, pgMaterializedView } from 'drizzle-orm/pg-core';

export const activeUsersView = pgView('active_users_view').as((qb) =>
  qb.select().from(users).where(eq(users.emailVerified, true))
);

export const userStatsMaterialized = pgMaterializedView('user_stats_mat')
  .as((qb) =>
    qb
      .select({
        userId: users.id,
        userName: users.name,
        postCount: count(posts.id).as('post_count'),
        lastPostAt: max(posts.createdAt).as('last_post_at'),
      })
      .from(users)
      .leftJoin(posts, eq(users.id, posts.authorId))
      .groupBy(users.id, users.name)
  );

To refresh a materialized view:

REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats_mat;

The CONCURRENTLY flag lets you read from the view while it refreshes. Requires a unique index on the materialized view.

Relations

Drizzle has two relation systems: the legacy relations() function and the newer foreignKey() constraints in the table definition. Use relations() - it's more flexible and powers the relational query API.

// src/db/relations.ts
import { relations } from 'drizzle-orm';
import { users, posts, comments, teamMembers, teams } from './schema';

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

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

export const commentsRelations = relations(comments, ({ one }) => ({
  author: one(users, {
    fields: [comments.authorId],
    references: [users.id],
  }),
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id],
  }),
}));

export const teamsRelations = relations(teams, ({ many }) => ({
  members: many(teamMembers),
}));

export const teamMembersRelations = relations(teamMembers, ({ one }) => ({
  team: one(teams, {
    fields: [teamMembers.teamId],
    references: [teams.id],
  }),
  user: one(users, {
    fields: [teamMembers.userId],
    references: [users.id],
  }),
}));

Import these relation files in your schema index so they get picked up:

// src/db/schema.ts (bottom of file)
export * from './relations';

Querying

Select

// Simple select
const allUsers = await db.select().from(users);

// With where
const admins = await db.select().from(users).where(eq(users.role, 'admin'));

// With ordering and limits
const recentPosts = await db
  .select()
  .from(posts)
  .orderBy(desc(posts.createdAt))
  .limit(10);

// Select specific columns
const userEmails = await db
  .select({ id: users.id, email: users.email })
  .from(users);

// With partial select and aliasing
const postSummaries = await db
  .select({
    id: posts.id,
    title: posts.title,
    authorName: users.name,
    commentCount: count(comments.id),
  })
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))
  .leftJoin(comments, eq(posts.id, comments.postId))
  .groupBy(posts.id, posts.title, users.name);

Filtering

Drizzle uses functions, not objects, for filtering. This is more verbose but gives you complete control over SQL operators:

import { eq, ne, gt, lt, gte, lte, like, ilike, inArray, isNull, isNotNull, and, or, not, sql } from 'drizzle-orm';

// Exact match
.where(eq(users.role, 'admin'))

// Multiple conditions
.where(and(
  eq(posts.published, true),
  gt(posts.createdAt, new Date('2026-01-01')),
  ilike(posts.title, '%typescript%'),
))

// OR conditions
.where(or(
  eq(users.role, 'admin'),
  eq(users.role, 'editor'),
))

// IN clause
.where(inArray(users.plan, ['pro', 'enterprise']))

// NULL checks
.where(isNull(posts.deletedAt))
.where(isNotNull(users.emailVerified))

// Raw SQL for complex expressions
.where(sql`${posts.metadata} @> ${JSON.stringify({ featured: true })}`)

// NOT
.where(not(eq(users.emailVerified, false)))

Insert

// Single insert
const [newUser] = await db
  .insert(users)
  .values({
    email: '[email protected]',
    name: 'Zara',
    role: 'admin',
  })
  .returning();  // Returns the inserted row

// Batch insert
const newPosts = await db
  .insert(posts)
  .values([
    { title: 'Post 1', body: 'Body 1', authorId: userId },
    { title: 'Post 2', body: 'Body 2', authorId: userId },
    { title: 'Post 3', body: 'Body 3', authorId: userId },
  ])
  .returning();

// Upsert (insert or update on conflict)
await db
  .insert(users)
  .values({ email: '[email protected]', name: 'Zara' })
  .onConflictDoUpdate({
    target: users.email,
    set: {
      name: 'Zara Updated',
      updatedAt: new Date(),
    },
  });

// Upsert - ignore on conflict
await db
  .insert(users)
  .values({ email: '[email protected]', name: 'Zara' })
  .onConflictDoNothing();

Update

// Simple update
await db
  .update(users)
  .set({ name: 'New Name' })
  .where(eq(users.id, userId));

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

// Conditional updates using SQL expressions
await db
  .update(posts)
  .set({
    viewCount: sql`${posts.viewCount} + 1`,
  })
  .where(eq(posts.id, postId));

Delete

// Hard delete
await db.delete(posts).where(eq(posts.id, postId));

// Soft delete (the pattern I prefer)
await db
  .update(posts)
  .set({ deletedAt: new Date() })
  .where(eq(posts.id, postId));

Joins

Drizzle supports leftJoin, rightJoin, innerJoin, and fullJoin:

const postsWithAuthors = await db
  .select({
    postId: posts.id,
    postTitle: posts.title,
    authorName: users.name,
    authorEmail: users.email,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id));

// Multiple joins
const commentedPosts = await db
  .select({
    postTitle: posts.title,
    authorName: users.name,
    commentBody: comments.body,
    commenterName: sql<string>`commenter.name`,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .innerJoin(comments, eq(posts.id, comments.postId))
  .innerJoin(
    sql`users AS commenter`,
    eq(comments.authorId, sql`commenter.id`)
  );

Aggregations

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

const stats = await db
  .select({
    totalPosts: count(posts.id),
    totalViews: sum(posts.viewCount),
    avgViews: avg(posts.viewCount),
    latestPost: max(posts.createdAt),
    earliestPost: min(posts.createdAt),
  })
  .from(posts)
  .where(eq(posts.published, true));

// Group by
const postsPerUser = await db
  .select({
    authorId: users.id,
    authorName: users.name,
    postCount: count(posts.id),
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.id, users.name)
  .having(sql`${count(posts.id)} > 5`);

Subqueries

// Subquery in select
const usersWithPostCount = await db
  .select({
    id: users.id,
    name: users.name,
    postCount: db
      .select({ count: count() })
      .from(posts)
      .where(eq(posts.authorId, users.id))
      .as('post_count'),
  })
  .from(users);

// Subquery in where
const activeAuthors = await db
  .select()
  .from(users)
  .where(
    inArray(
      users.id,
      db.select({ authorId: posts.authorId }).from(posts).where(eq(posts.published, true))
    )
  );

// CTE (Common Table Expression)
const recentPostsCte = db.$with('recent_posts').as(
  db.select().from(posts).where(gt(posts.createdAt, new Date('2026-01-01')))
);

const result = await db
  .with(recentPostsCte)
  .select()
  .from(recentPostsCte)
  .innerJoin(users, eq(recentPostsCte.authorId, users.id));

Relational Queries (The Query API)

This is Drizzle's answer to Prisma's include. It uses the relations() definitions to auto-join related tables:

// One-to-many: Get users with their posts
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true,
  },
});

// Deep nesting
const usersFull = await db.query.users.findMany({
  with: {
    posts: {
      with: {
        comments: true,
      },
      orderBy: desc(posts.createdAt),
      limit: 5,
    },
    teamMemberships: {
      with: {
        team: true,
      },
    },
  },
});

// With filters on related tables
const usersWithRecentPosts = await db.query.users.findMany({
  with: {
    posts: {
      where: and(
        eq(posts.published, true),
        gt(posts.createdAt, new Date('2026-01-01')),
      ),
      orderBy: desc(posts.createdAt),
      limit: 3,
    },
  },
});

// Find first with relations
const user = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    posts: {
      columns: {
        id: true,
        title: true,
        createdAt: true,
      },
      orderBy: desc(posts.createdAt),
    },
  },
});

// With column selection (omit sensitive fields)
const safeUsers = await db.query.users.findMany({
  columns: {
    id: true,
    name: true,
    email: true,
    // role and metadata are excluded
  },
});

When to use the Query API vs select:

Use the Query API when you want nested relations loaded automatically. It's convenient for API responses where you need related data.
Use select with joins when you need complex filtering, aggregations, or precise control over the SQL. The Query API can't do GROUP BY, window functions, or CTEs.

Transactions

Drizzle uses PostgreSQL's native transactions with savepoint support:

// Basic transaction
await db.transaction(async (tx) => {
  const [user] = await tx
    .insert(users)
    .values({ email: '[email protected]', name: 'Zara' })
    .returning();

  await tx.insert(posts).values({
    title: 'My First Post',
    body: 'Hello world',
    authorId: user.id,
  });
});

If any query inside the transaction throws, the entire transaction rolls back automatically.

Nested Transactions (Savepoints)

await db.transaction(async (tx) => {
  await tx.insert(users).values({ email: '[email protected]', name: 'A' });

  // Nested transaction creates a savepoint
  await tx.transaction(async (nestedTx) => {
    await nestedTx.insert(users).values({ email: '[email protected]', name: 'B' });
    // If this fails, only the nested transaction rolls back
    // The outer transaction continues
  });

  // This runs even if the nested transaction failed
  await tx.insert(users).values({ email: '[email protected]', name: 'C' });
});

Transaction Isolation Levels

// Serializable (default in PostgreSQL, strongest isolation)
await db.transaction(async (tx) => {
  // ...
}, { isolationLevel: 'serializable' });

// Read committed
await db.transaction(async (tx) => {
  // ...
}, { isolationLevel: 'read committed' });

// Repeatable read
await db.transaction(async (tx) => {
  // ...
}, { isolationLevel: 'repeatable read' });

Practical advice: Use serializable for financial operations, inventory management, or anything where phantom reads or write skew can cause data corruption. Use read committed (PostgreSQL's default) for everything else. Drizzle defaults to whatever PostgreSQL defaults to, which is read committed.

Prepared Statements

Prepared statements parse the SQL once and reuse the plan. For queries that run thousands of times per second, this is a measurable performance win:

// Prepared statement
const findByEmail = db
  .select()
  .from(users)
  .where(eq(users.email, sql.placeholder('email')))
  .prepare('find_user_by_email');

// Execute with parameters
const user = await findByEmail.execute({ email: '[email protected]' });

When to use prepared statements:

Queries executed in hot loops (100+ times per second)
Parameterized queries with the same structure but different values
When you need a specific query plan cached by PostgreSQL

When NOT to use them:

Ad-hoc queries
Queries that run less than 10 times per second
Behind PgBouncer in transaction mode (prepared statements break)

Raw SQL Escape Hatch

Drizzle gives you full access to raw SQL when you need it:

import { sql } from 'drizzle-orm';

// Raw SQL query
const result = await db.execute(sql`
  SELECT
    u.id,
    u.name,
    COUNT(p.id) as post_count,
    RANK() OVER (ORDER BY COUNT(p.id) DESC) as rank
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id
  GROUP BY u.id, u.name
  HAVING COUNT(p.id) > 0
  ORDER BY rank
  LIMIT 10
`);

// Parameterized raw query (prevent SQL injection)
const result2 = await db.execute(sql`
  SELECT * FROM users
  WHERE email = ${userEmail}
  AND created_at > ${new Date('2026-01-01')}
`);

// Raw SQL in a select context
const data = await db.execute<{
  userId: string;
  postCount: number;
}>(sql`
  SELECT
    author_id as "userId",
    COUNT(*)::int as "postCount"
  FROM posts
  GROUP BY author_id
`);

Always use Drizzle's SQL template literals for raw queries. Never concatenate strings. The template literal approach handles parameterization automatically.

Migrations with Drizzle Kit

Configuration

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './src/drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Commands

# Generate a migration from schema changes
npx drizzle-kit generate

# Run pending migrations
npx drizzle-kit migrate

# Open Drizzle Studio (browser UI)
npx drizzle-kit studio

# Push schema directly to database (dev only)
npx drizzle-kit push

# Pull existing database schema into code
npx drizzle-kit pull

Migration Workflow

I use this workflow in production:

npx drizzle-kit generate - Generates a timestamped SQL file in src/drizzle/
Review the generated SQL - Always read the migration before applying it
npx drizzle-kit migrate - Applies pending migrations

The migration journal (_journal.json) tracks which migrations have been applied. Drizzle keeps this in the drizzle/ directory, not in the database itself. For production, you need to run migrations as part of your deployment pipeline.

Programmatic Migration Runner

// src/db/migrate.ts
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { db } from './index';

async function runMigrations() {
  console.log('Running migrations...');
  await migrate(db, { migrationsFolder: './src/drizzle' });
  console.log('Migrations complete.');
}

runMigrations().catch((err) => {
  console.error('Migration failed:', err);
  process.exit(1);
});

Add to package.json:

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "tsx src/db/migrate.ts",
    "db:studio": "drizzle-kit studio",
    "db:push": "drizzle-kit push",
    "db:pull": "drizzle-kit pull"
  }
}

Performance Patterns

Indexing Strategy

import { index } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  // ... columns
}, (table) => [
  // B-tree index (default) - good for equality and range queries
  index('posts_author_idx').on(table.authorId),

  // Composite index - for queries that filter on multiple columns
  index('posts_author_published_idx').on(table.authorId, table.published),

  // Partial index - only indexes rows matching the condition
  // Massive win for soft deletes
  index('posts_active_idx').on(table.createdAt)
    .where(sql`${table.deletedAt} IS NULL`),

  // GIN index for JSONB columns
  // Allows @>, ?, ?|, ?& operators
  index('posts_metadata_idx')
    .using('gin', table.metadata),

  // Full-text search index
  index('posts_search_idx')
    .using('gin', sql`to_tsvector('english', ${table.title} || ' ' || ${table.body})`),
]);

Index decision framework:

Equality lookups (WHERE x = 'value'): B-tree (default)
Range queries (WHERE x > 5 AND x < 10): B-tree (default)
JSONB contains (WHERE data @> '{"key": "value"}'): GIN
Full-text search (WHERE to_tsvector(...) @@ to_tsquery(...)): GIN
Array contains (WHERE tags @> ARRAY['a', 'b']): GIN
Pattern matching (WHERE name ILIKE '%pattern%'): pg_trgm + GIN
Geographic data: GiST

Connection Pooling in Production

Drizzle itself doesn't pool connections - your driver does. Postgres.js handles pooling internally. For serverless environments, use PgBouncer or a managed pooler:

// Production: use connection string with pooler
const client = postgres(process.env.DATABASE_URL!, {
  max: 20,                // Max connections in pool
  idle_timeout: 20,       // Close idle connections after 20s
  max_lifetime: 1800,     // Recycle connections every 30 min
  connect_timeout: 5,     // Fail fast on connection issues
  prepare: false,         // Disable for PgBouncer
  onnotice: (notice) => {
    // Log PostgreSQL notices in development
    if (process.env.NODE_ENV === 'development') {
      console.warn('PG notice:', notice.message);
    }
  },
});

Query Performance Monitoring

import { sql } from 'drizzle-orm';

// Enable pg_stat_statements extension
// CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

// Find slow queries
const slowQueries = await db.execute(sql`
  SELECT
    query,
    calls,
    mean_exec_time,
    total_exec_time
  FROM pg_stat_statements
  ORDER BY mean_exec_time DESC
  LIMIT 10
`);

// Analyze a specific query
const plan = await db.execute(sql`
  EXPLAIN ANALYZE
  SELECT * FROM posts WHERE author_id = 'some-uuid'
`);

Batch Operations

For bulk inserts, use partitioned batches:

async function bulkInsert(items: typeof posts.$inferInsert[]) {
  // PostgreSQL has a limit on parameters per query (65535)
  // For a table with 10 columns, that's ~6500 rows per batch
  const BATCH_SIZE = 5000;

  for (let i = 0; i < items.length; i += BATCH_SIZE) {
    const batch = items.slice(i, i + BATCH_SIZE);
    await db.insert(posts).values(batch);
  }
}

Testing with Drizzle

In-Memory Database for Unit Tests

Use @electric-sql/pglite for a real PostgreSQL instance in memory:

// tests/setup.ts
import { PGlite } from '@electric-sql/pglite';
import { drizzle } from 'drizzle-orm/pglite';
import { migrate } from 'drizzle-orm/pglite/migrator';
import { sql } from 'drizzle-orm';

let pglite: PGlite;
let db: ReturnType<typeof drizzle>;

export async function setupTestDb() {
  pglite = new PGlite();  // In-memory PostgreSQL
  await pglite.waitReady;

  db = drizzle(pglite);
  await migrate(db, { migrationsFolder: './src/drizzle' });

  return db;
}

export async function teardownTestDb() {
  await pglite.close();
}

export async function clearDatabase() {
  const tables = await db.execute(sql`
    SELECT tablename FROM pg_tables
    WHERE schemaname = 'public'
  `);

  for (const table of tables.rows) {
    await db.execute(sql`TRUNCATE TABLE ${sql.identifier(table.tablename as string)} CASCADE`);
  }
}

Test Example

// tests/users.test.ts
import { describe, it, expect, beforeEach, beforeAll, afterAll } from 'vitest';
import { setupTestDb, teardownTestDb, clearDatabase } from './setup';
import { users } from '../src/db/schema';
import { eq } from 'drizzle-orm';

describe('Users', () => {
  let db: Awaited<ReturnType<typeof setupTestDb>>;

  beforeAll(async () => {
    db = await setupTestDb();
  });

  afterAll(async () => {
    await teardownTestDb();
  });

  beforeEach(async () => {
    await clearDatabase();
  });

  it('should create a user', async () => {
    const [user] = await db
      .insert(users)
      .values({
        email: '[email protected]',
        name: 'Test User',
      })
      .returning();

    expect(user).toBeDefined();
    expect(user.email).toBe('[email protected]');
  });

  it('should find user by email', async () => {
    await db.insert(users).values({
      email: '[email protected]',
      name: 'Find Me',
    });

    const [found] = await db
      .select()
      .from(users)
      .where(eq(users.email, '[email protected]'));

    expect(found).toBeDefined();
    expect(found.name).toBe('Find Me');
  });
});

Edge Runtime Compatibility

Drizzle works on the edge because it's just SQL - no binary protocol dependencies. Use the appropriate driver:

// Edge runtime (Cloudflare Workers, Vercel Edge, etc.)
import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';

const sql = neon(process.env.DATABASE_URL!);
const db = drizzle(sql);

The query API, type inference, and schema work identically on edge. You lose nothing except prepared statement optimization (which doesn't apply to edge anyway).

Common Gotchas and Production Tips

1. Schema File Must Export Everything

// Good - single file
export * from './users-schema';
export * from './posts-schema';

// Good - single schema.ts
// Just export all tables and enums

// Bad - forgetting to export
// drizzle-kit won't see your tables

2. Type Inference for Insert vs Select

// Infer the select type (what comes back from the database)
export type User = typeof users.$inferSelect;

// Infer the insert type (what you pass in, without defaults)
export type NewUser = typeof users.$inferInsert;

3. `returning()` Only Works with PostgreSQL

It's not portable to MySQL or SQLite. If you ever switch databases, you'll need to handle this.

4. Timestamp Handling

PostgreSQL timestamp with time zone stores UTC internally. Drizzle returns JavaScript Date objects. If you're doing date comparisons:

// This works - Drizzle handles the conversion
.where(gt(posts.createdAt, new Date('2026-01-01')))

// For exact day queries, use SQL to truncate timezone
.where(sql`DATE(${posts.createdAt}) = '2026-01-01'::date`)

5. JSONB Queries

// Containment check
.where(sql`${posts.metadata} @> ${JSON.stringify({ featured: true })}`)

// Key exists
.where(sql`${posts.metadata} ? 'featured'`)

// Any of these keys exist
.where(sql`${posts.metadata} ?| ${['featured', 'pinned']}`)

// All of these keys exist
.where(sql`${posts.metadata} ?& ${['featured', 'pinned']}`)

// Access nested values
.select({
  featured: sql<boolean>`${posts.metadata}->>'featured'`,
  viewCount: sql<number>`(${posts.metadata}->>'viewCount')::int`,
})

6. Don't Over-Index

Every index slows down writes. Profile your actual query patterns before adding indexes. Use EXPLAIN ANALYZE to verify that PostgreSQL is actually using your indexes:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM posts WHERE author_id = 'some-uuid' AND published = true;

Look for Index Scan or Index Only Scan in the output. If you see Seq Scan, your index isn't being used (wrong column order, missing composite index, or the planner thinks a sequential scan is faster for small tables).

7. Schema Push for Development Only

# This directly modifies your database without generating a migration file
npx drizzle-kit push

Use push during local development to iterate quickly. Always use generate + migrate for staging and production. Without migration files, you can't track schema changes or reproduce them in CI/CD.

8. Parallel Migrations

In production, if you have multiple instances deploying simultaneously, they'll all try to run migrations at the same time. Use a lock:

import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { sql } from 'drizzle-orm';

async function safeMigrate() {
  // Advisory lock ensures only one migration runs at a time
  const lockResult = await db.execute(sql`SELECT pg_advisory_lock(12345)`);
  const locked = lockResult.rows[0]?.pg_advisory_lock;

  if (!locked) {
    throw new Error('Could not acquire migration lock');
  }

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

Drizzle Studio

Drizzle Kit includes a browser-based GUI for viewing and editing your data:

npx drizzle-kit studio

It opens at https://local.drizzle.studio and lets you:

Browse all tables and their data
Filter and sort rows
Insert, update, and delete records
View the generated SQL for each query
Inspect schema definitions

It's not a replacement for proper database tooling (DBeaver, pgAdmin, TablePlus), but it's great for quick inspection during development.

Putting It All Together

Here's a real-world service pattern I use:

// src/services/post-service.ts
import { db } from '../db';
import { posts, users, comments } from '../db/schema';
import { eq, and, desc, sql } from 'drizzle-orm';

export class PostService {
  // Get a post with author info and comment count
  async getPostById(id: string) {
    return db.query.posts.findFirst({
      where: eq(posts.id, id),
      with: {
        author: {
          columns: { id: true, name: true, email: true },
        },
        comments: {
          columns: { id: true, body: true, createdAt: true },
          with: {
            author: {
              columns: { id: true, name: true },
            },
          },
          orderBy: desc(comments.createdAt),
          limit: 20,
        },
      },
    });
  }

  // Create a post inside a transaction
  async createPost(authorId: string, data: { title: string; body: string }) {
    return db.transaction(async (tx) => {
      const [author] = await tx
        .select()
        .from(users)
        .where(eq(users.id, authorId));

      if (!author) {
        throw new Error('Author not found');
      }

      const [post] = await tx
        .insert(posts)
        .values({
          ...data,
          authorId,
        })
        .returning();

      return post;
    });
  }

  // Paginated feed with cursor-based pagination
  async getFeed(cursor?: string, limit = 20) {
    const query = db
      .select({
        id: posts.id,
        title: posts.title,
        body: posts.body,
        createdAt: posts.createdAt,
        authorName: users.name,
      })
      .from(posts)
      .innerJoin(users, eq(posts.authorId, users.id))
      .where(
        and(
          eq(posts.published, true),
          sql`${posts.deletedAt} IS NULL`,
          cursor ? sql`${posts.createdAt} < ${new Date(cursor)}` : sql`true`,
        ),
      )
      .orderBy(desc(posts.createdAt))
      .limit(limit + 1); // Fetch one extra to determine if there's a next page

    const results = await query;
    const hasNextPage = results.length > limit;
    const items = hasNextPage ? results.slice(0, limit) : results;

    return {
      items,
      nextCursor: hasNextPage
        ? items[items.length - 1].createdAt.toISOString()
        : null,
    };
  }
}

export const postService = new PostService();

Senior-Level Patterns You'll Actually Need

These are the patterns I reach for when things get serious. The basics above keep you afloat; this is what keeps you from burning down the database in production.

Locking: Pessimistic vs Optimistic

Pessimistic locking locks a row while you work with it - no one else can modify it until you commit:

import { sql } from 'drizzle-orm';

// SELECT ... FOR UPDATE - take a row lock before reading/updating
async function transferMoney(tx: PostgresTransaction, fromId: string, toId: string, amount: number) {
  // FOR UPDATE locks both rows so a concurrent transfer can't double-spend
  const [from] = await tx
    .select()
    .from(accounts)
    .where(inArray(accounts.id, [fromId, toId]))
    .for('update');   // .for('update') => SELECT ... FOR UPDATE

  if (from.balance < amount) {
    throw new Error('Insufficient funds');
  }

  await tx
    .update(accounts)
    .set({ balance: sql`${accounts.balance} - ${amount}` })
    .where(eq(accounts.id, fromId));

  await tx
    .update(accounts)
    .set({ balance: sql`${accounts.balance} + ${amount}` })
    .where(eq(accounts.id, toId));
}

Optimistic locking uses a version column and fails on conflict instead of waiting:

// schema.ts - add a version column
export const documents = pgTable('documents', {
  id: uuid('id').defaultRandom().primaryKey(),
  content: text('content').notNull(),
  version: integer('version').notNull().default(1),
});

// update only succeeds if the version matches what the client read
async function saveDocument(id: string, content: string, expectedVersion: number) {
  const result = await db
    .update(documents)
    .set({ content, version: sql`${documents.version} + 1` })
    .where(and(eq(documents.id, id), eq(documents.version, expectedVersion)))
    .returning();

  if (result.length === 0) {
    throw new ConflictError(`Document was modified by someone else`);
  }
  return result[0];
}

When to use which: Use pessimistic locking for short, hot critical sections (money movement, seat booking) where conflicts are rare. Use optimistic locking for long-lived edits (documents, configs) where holding a row lock would block everyone else.

Automatic `updated_at` with `onUpdate`

Stop setting updatedAt by hand - you will forget it in a hot path eventually:

export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: varchar('name', { length: 255 }).notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .notNull()
    .defaultNow()
    .$onUpdate(() => new Date()),   // auto-bumps on every UPDATE
});

The .$onUpdate() callback fires automatically for the update and update returning queries. Pair it with .defaultNow() and you never touch this column manually again.

DB-level validation: You can't trust compiled types in prod

TypeScript types compile away. If a string sneaks into an integer column at 2am, the error should come from PostgreSQL, not a runtime crash downstream. Enforce at the database with CHECK constraints, not just your types:

import { check, sql } from 'drizzle-orm/pg-core';

export const orders = pgTable('orders', {
  id: uuid('id').defaultRandom().primaryKey(),
  quantity: integer('quantity').notNull(),
  status: orderStatusEnum('status').notNull(),
  email: varchar('email', { length: 320 }),
}, (table) => [
  // CHECK constraint - PostgreSQL rejects invalid rows at the DB level
  check('orders_qty_positive', sql`${table.quantity} > 0`),
  check('orders_status_valid', sql`${table.status} IN ('pending', 'paid', 'shipped', 'cancelled')`),
  check('orders_email_format', sql`${table.email} ~* '^[^@\\s]+@[^@\\s]+\\.[^@\\s]+$'`),
]);

Now even a raw SQL insert from a rogue script gets rejected. Types protect your TypeScript paths; constraints protect your data.

Batch Querying: Avoid N+1 Like It's a Disease

The Query API makes N+1 deceptively easy to write. One findMany with a with per row is technically one query - but if you loop and call it per item, you get N+1. Batch it:

// BAD - one query per post (N+1)
for (const postId of postIds) {
  const p = await db.query.posts.findFirst({ where: eq(posts.id, postId) });
}

// GOOD - single query for all posts
const posts = await db.query.posts.findMany({
  where: inArray(posts.id, postIds),
});

// GOOD - single query pulling all related authors at once
const postsWithAuthors = await db.query.posts.findMany({
  where: inArray(posts.id, postIds),
  with: { author: true },
});

Backpressure, Timeouts, and Fail-Fast

Nothing kills a service faster than a saturated pool with requests queuing forever. Set hard timeouts everywhere and fail fast:

const client = postgres(process.env.DATABASE_URL!, {
  max: 20,
  connect_timeout: 5,      // fail if can't connect in 5s
  idle_timeout: 20,
  max_lifetime: 1800,
  onnotice: () => {},
  onError: (err) => {       // log pool-level errors, don't swallow them
    console.error('Postgres pool error:', err);
  },
});

And always enforce a per-call timeout so a hung query can't hold a pool slot hostage:

import { sql } from 'drizzle-orm';

// PostgreSQL statement_timeout - hard fails a query that runs too long
await db.execute(sql`SET statement_timeout = '5s'`);

// Or guard at the app level for a single query
async function withQueryTimeout<T>(fn: () => Promise<T>, ms = 5000): Promise<T> {
  const timer = new Promise<never>((_, rej) =>
    setTimeout(() => rej(new Error(`Query timed out after ${ms}ms`)), ms)
  );
  return Promise.race([fn(), timer]);
}

const posts = await withQueryTimeout(() =>
  db.select().from(posts).orderBy(desc(posts.createdAt)).limit(20)
);

Idempotency: Duplicate Writes Are a Fact of Life

Webhooks, retries, and double-clicks all produce duplicate requests. Make your writes idempotent with a deduplication key:

// schema.ts
export const payments = pgTable('payments', {
  id: uuid('id').defaultRandom().primaryKey(),
  orderId: uuid('order_id').notNull().references(() => orders.id),
  // unique external id - same request retried produces the same row
  providerRef: varchar('provider_ref', { length: 255 }).notNull().unique(),
  amountCents: integer('amount_cents').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});

// handler - retry-safe: second call with same ref is a no-op
async function recordPayment(payment: NewPayment) {
  return db
    .insert(payments)
    .values(payment)
    .onConflictDoNothing({ target: payments.providerRef });
}

Row-Level Security (RLS) for Multi-Tenancy

If you serve multiple tenants, filter by tenantId everywhere, or trust PostgreSQL to do it for you. RLS means you can never accidentally leak a tenant's rows, even with a buggy query:

export const tenantData = pgTable('tenant_data', {
  id: uuid('id').defaultRandom().primaryKey(),
  tenantId: uuid('tenant_id').notNull(),
  value: text('value').notNull(),
});

// Enable RLS on the table
// ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY;
// CREATE POLICY tenant_isolation ON tenant_data
//   USING (tenant_id = current_setting('app.tenant_id')::uuid);

Then set the tenant context at the start of each request:

async function setTenantContext(tenantId: string) {
  await db.execute(sql`SET app.tenant_id = ${tenantId}`);
}

Now even if someone forgets the where, PostgreSQL returns nothing for other tenants. Defense in depth.

Retry Logic Worth Having

Transient failures (deadlocks, connection drops) are normal in production. Don't crash on them - retry a small, sane number of times with backoff:

async function withRetry<T>(fn: () => Promise<T>, retries = 3): Promise<T> {
  let lastErr: unknown;
  for (let attempt = 0; attempt < retries; attempt++) {
    try {
      return await fn();
    } catch (err) {
      lastErr = err;
      if (attempt < retries - 1) {
        await new Promise((r) => setTimeout(r, 100 * 2 ** attempt)); // 100ms, 200ms, 400ms
      }
    }
  }
  throw lastErr;
}

// Deadlock? Retry. Note: PostgreSQL immediately aborts the deadlocked transaction,
// so wrap the WHOLE transaction call in withRetry, not individual statements.
const result = await withRetry(() =>
  db.transaction(async (tx) => { /* ... money move ... */ })
);

Partitioning Large Tables

When a table hits tens of millions of rows and slow aggregate queries, partition it. Drizzle supports declarative partitions:

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

export const events = pgTable('events', {
  id: integer('id').notNull(),
  occurredAt: timestamp('occurred_at', { withTimezone: true }).notNull(),
  payload: jsonb('payload').notNull(),
}, (table) => [
  // Must include the partition key in the PK
  // Each partition is a separate table trimming index size & vacuum cost
]);

// Range partition by month:
// CREATE TABLE events PARTITION BY RANGE (occurred_at);
// CREATE TABLE events_2026_09 PARTITION OF events
//   FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

Queries filter on occurredAt and PostgreSQL prunes to only the relevant partitions. Drop old months instantly with DROP TABLE instead of an expensive DELETE.

Command Palette