Drizzle ORM

Schema Definition

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

export const roleEnum = pgEnum('role', ['admin', 'user']);

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  clerkId: text('clerk_id').unique().notNull(),
  email: varchar('email', { length: 255 }).unique().notNull(),
  name: varchar('name', { length: 100 }).notNull(),
  role: roleEnum('role').default('user').notNull(),
  settings: jsonb('settings').$type<{ theme: string }>(),
  isActive: boolean('is_active').default(true).notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
}, (table) => ({
  emailIdx: index('users_email_idx').on(table.email),
  createdAtIdx: index('users_created_at_idx').on(table.createdAt.desc()),
}));

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: text('title').notNull(),
  slug: text('slug').unique().notNull(),
  body: text('body').notNull(),
  authorId: uuid('author_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
  publishedAt: timestamp('published_at', { withTimezone: true }),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});

// Relations (for joins/queries — not FK constraints)
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));

// Type exports
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Post = typeof posts.$inferSelect;

Queries

import { db } from '@/db';
import { users, posts } from '@/db/schema';
import { eq, and, or, ilike, gt, gte, lt, lte, isNull, inArray, desc, asc, count, sql } from 'drizzle-orm';

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

// Filtered
const user = await db.select()
  .from(users)
  .where(eq(users.email, 'alice@example.com'))
  .limit(1);

// Multiple conditions
const results = await db.select()
  .from(users)
  .where(
    and(
      eq(users.role, 'user'),
      gte(users.createdAt, new Date('2024-01-01')),
      ilike(users.name, '%alice%')
    )
  )
  .orderBy(desc(users.createdAt))
  .limit(20)
  .offset(0);

// JOIN (relational query — uses relations)
const usersWithPosts = await db.query.users.findMany({
  with: { posts: { limit: 5, orderBy: [desc(posts.createdAt)] } },
  where: eq(users.isActive, true),
  limit: 20,
});

// JOIN (SQL style)
const result = await db
  .select({ user: users, postCount: count(posts.id) })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id))
  .groupBy(users.id)
  .orderBy(desc(count(posts.id)));

// Aggregates
const [{ total }] = await db.select({ total: count() }).from(users);

// INSERT
const [newUser] = await db.insert(users).values({
  clerkId: 'clerk_123',
  email: 'alice@example.com',
  name: 'Alice',
}).returning();

// INSERT multiple
await db.insert(posts).values([
  { title: 'Post 1', slug: 'post-1', body: '...', authorId: userId },
  { title: 'Post 2', slug: 'post-2', body: '...', authorId: userId },
]);

// Upsert
await db.insert(users)
  .values({ clerkId: 'clerk_123', email: 'a@b.com', name: 'Alice' })
  .onConflictDoUpdate({ target: users.clerkId, set: { name: 'Alice Updated' } });

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

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

// Transactions
await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ ... }).returning();
  await tx.insert(posts).values({ authorId: user.id, ... });
});

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! },
  verbose: true,
  strict: true,
});

# Commands
npx drizzle-kit generate    # generate migration SQL from schema diff
npx drizzle-kit migrate     # apply pending migrations
npx drizzle-kit push        # push schema directly (dev only, no migration files)
npx drizzle-kit studio      # open Drizzle Studio (GUI)
npx drizzle-kit check       # check for schema issues

Drizzle ORM: Schema Definition & Table Design

Drizzle is a TypeScript-first ORM that keeps you close to SQL while providing type safety. Your schema is the single source of truth for both the database and TypeScript types.

Setup

# Install Drizzle with your database driver
npm install drizzle-orm @neondatabase/serverless   # Neon / serverless PostgreSQL
npm install drizzle-orm postgres                    # Standard PostgreSQL
npm install drizzle-orm better-sqlite3              # SQLite
npm install drizzle-orm mysql2                      # MySQL

# Install drizzle-kit for migrations
npm install -D drizzle-kit

Database Client (Neon)

// src/db/client.ts
import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';
import * as schema from './schema';

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });

// For connection pooling (serverful environments)
import { Pool } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-serverless';

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

Defining Tables (PostgreSQL)

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

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  clerkId: text('clerk_id').unique().notNull(),
  email: text('email').unique().notNull(),
  name: text('name').notNull(),
  nickname: varchar('nickname', { length: 50 }).unique(),
  plan: text('plan', { enum: ['free', 'pro', 'enterprise'] }).default('free').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => [
  index('users_email_idx').on(table.email),
  index('users_clerk_id_idx').on(table.clerkId),
]);

export const folders = pgTable('folders', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  parentId: uuid('parent_id').references((): AnyPgColumn => folders.id, { onDelete: 'cascade' }),
  name: text('name').notNull(),
  slug: text('slug').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => [
  unique('folders_user_slug_unique').on(table.userId, table.slug),
]);

export const pages = pgTable('pages', {
  id: uuid('id').primaryKey().defaultRandom(),
  folderId: uuid('folder_id').notNull().references(() => folders.id, { onDelete: 'cascade' }),
  title: text('title').notNull(),
  slug: text('slug').notNull(),
  content: text('content'),
  status: text('status', { enum: ['to_learn', 'learning', 'proficient', 'expert'] }).default('to_learn'),
  tags: uuid('tags').array().default([]).notNull(),
  metadata: jsonb('metadata').$type<{ wordCount?: number; lastEdited?: string }>(),
  isImportant: boolean('is_important').default(false).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

Type Inference

// Infer TypeScript types from schema — no duplication needed
export type User = typeof users.$inferSelect;       // SELECT result type
export type NewUser = typeof users.$inferInsert;    // INSERT input type

export type Folder = typeof folders.$inferSelect;
export type NewFolder = typeof folders.$inferInsert;

export type Page = typeof pages.$inferSelect;
export type NewPage = typeof pages.$inferInsert;

// Partial types for updates
export type UpdatePage = Partial<Omit<NewPage, 'id' | 'folderId' | 'createdAt'>>;

Column Reference Types (self-referencing)

// Self-referencing table needs explicit type annotation
import type { AnyPgColumn } from 'drizzle-orm/pg-core';

export const categories = pgTable('categories', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  // Must annotate with (): AnyPgColumn for circular reference
  parentId: integer('parent_id').references((): AnyPgColumn => categories.id),
});

Column Types Reference

// Common PostgreSQL column types in Drizzle
text('col')                           // TEXT — variable length string
varchar('col', { length: 255 })      // VARCHAR(n)
char('col', { length: 10 })          // CHAR(n) — fixed length
integer('col')                        // INT4 — 32-bit integer
bigint('col', { mode: 'number' })    // INT8 — 64-bit (use mode:'bigint' for >2^53)
smallint('col')                       // INT2 — 16-bit integer
serial('col')                         // SERIAL — auto-increment int
boolean('col')                        // BOOLEAN
timestamp('col')                      // TIMESTAMP WITHOUT TIME ZONE
timestamp('col', { withTimezone: true }) // TIMESTAMPTZ
date('col')                          // DATE
numeric('col', { precision: 10, scale: 2 }) // NUMERIC — for money
real('col')                           // FLOAT4
doublePrecision('col')               // FLOAT8
uuid('col')                          // UUID
jsonb('col')                          // JSONB — binary JSON, indexable
json('col')                           // JSON — text JSON
vector('col', { dimensions: 1536 }) // pgvector extension

Drizzle: Queries — Select, Insert, Update, Delete

SELECT Queries

import { db } from '@/db';
import { users, folders, pages } from '@/db/schema';
import { eq, and, or, not, lt, gt, lte, gte, like, ilike,
         inArray, isNull, isNotNull, between, desc, asc, sql } from 'drizzle-orm';

// Select all rows
const allUsers = await db.select().from(users);

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

// Where clause
const user = await db.select()
  .from(users)
  .where(eq(users.clerkId, 'clerk_abc123'))
  .limit(1);

// Multiple conditions
const activePros = await db.select()
  .from(users)
  .where(and(
    eq(users.plan, 'pro'),
    isNotNull(users.nickname),
  ));

// OR conditions
const found = await db.select()
  .from(users)
  .where(or(
    eq(users.email, 'alice@example.com'),
    eq(users.nickname, 'alice'),
  ));

// Pattern matching (case-insensitive)
const searchResults = await db.select()
  .from(users)
  .where(ilike(users.name, '%alice%'));

// IN clause
const specificUsers = await db.select()
  .from(users)
  .where(inArray(users.id, ['uuid1', 'uuid2', 'uuid3']));

// Order, limit, offset
const page1 = await db.select()
  .from(pages)
  .where(eq(pages.folderId, folderId))
  .orderBy(desc(pages.updatedAt))
  .limit(20)
  .offset(0);

Aggregates & GroupBy

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

// Count
const [{ total }] = await db.select({ total: count() }).from(users);

// Count with condition
const [{ proCount }] = await db.select({ proCount: count() })
  .from(users)
  .where(eq(users.plan, 'pro'));

// Group by
const pagesByStatus = await db.select({
  status: pages.status,
  count: count(),
})
  .from(pages)
  .groupBy(pages.status);
// [{ status: 'to_learn', count: 42 }, { status: 'proficient', count: 8 }]

// Having (filter on aggregated value)
const activeFolders = await db.select({
  folderId: pages.folderId,
  pageCount: count(),
})
  .from(pages)
  .groupBy(pages.folderId)
  .having(gt(count(), 5));

INSERT

// Insert single row
const [newUser] = await db.insert(users)
  .values({
    clerkId: 'clerk_abc',
    email: 'alice@example.com',
    name: 'Alice',
    plan: 'free',
  })
  .returning(); // returns the full inserted row

// Insert multiple rows
await db.insert(folders).values([
  { userId: user.id, name: 'React', slug: 'react' },
  { userId: user.id, name: 'TypeScript', slug: 'typescript' },
  { userId: user.id, name: 'Node.js', slug: 'nodejs' },
]);

// Upsert — insert or update on conflict
await db.insert(users)
  .values({ clerkId: 'clerk_abc', email: 'alice@example.com', name: 'Alice' })
  .onConflictDoUpdate({
    target: users.clerkId,            // conflict on this column
    set: {
      name: sql`excluded.name`,      // use the new value
      updatedAt: new Date(),
    },
  });

// Insert or do nothing on conflict
await db.insert(users)
  .values(userData)
  .onConflictDoNothing();

UPDATE & DELETE

// Update with where clause — always include where!
const [updated] = await db.update(users)
  .set({
    name: 'Alice Smith',
    updatedAt: new Date(),
  })
  .where(eq(users.id, userId))
  .returning();

// Update with SQL expression
await db.update(pages)
  .set({ updatedAt: new Date() })
  .where(eq(pages.folderId, folderId));

// Delete
await db.delete(folders)
  .where(eq(folders.id, folderId));

// Delete and return deleted rows
const [deleted] = await db.delete(users)
  .where(eq(users.clerkId, clerkId))
  .returning();

// Bulk delete
await db.delete(pages)
  .where(inArray(pages.id, pageIds));

Dynamic Queries

import { SQL } from 'drizzle-orm';

// Build conditions dynamically
async function searchPages(opts: {
  folderId?: string;
  status?: string;
  query?: string;
}) {
  const conditions: SQL[] = [];

  if (opts.folderId) conditions.push(eq(pages.folderId, opts.folderId));
  if (opts.status) conditions.push(eq(pages.status, opts.status as any));
  if (opts.query) conditions.push(ilike(pages.title, `%${opts.query}%`));

  return db.select()
    .from(pages)
    .where(conditions.length > 0 ? and(...conditions) : undefined)
    .orderBy(desc(pages.updatedAt));
}

Drizzle: Relations, Joins & Transactions

Defining Relations

// src/db/schema.ts — define relations for the relational query API
import { relations } from 'drizzle-orm';

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

export const foldersRelations = relations(folders, ({ one, many }) => ({
  user: one(users, {
    fields: [folders.userId],
    references: [users.id],
  }),
  parent: one(folders, {
    fields: [folders.parentId],
    references: [folders.id],
    relationName: 'parentFolder',
  }),
  children: many(folders, { relationName: 'parentFolder' }),
  pages: many(pages),
}));

export const pagesRelations = relations(pages, ({ one }) => ({
  folder: one(folders, {
    fields: [pages.folderId],
    references: [folders.id],
  }),
}));

Relational Query API (with relations)

// Requires schema with relations passed to drizzle()
// const db = drizzle(sql, { schema });

// Get user with all their folders
const userWithFolders = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    folders: {
      orderBy: [asc(folders.name)],
    },
  },
});

// Nested relations
const folderWithPages = await db.query.folders.findFirst({
  where: eq(folders.id, folderId),
  with: {
    pages: {
      where: eq(pages.status, 'proficient'),
      orderBy: [desc(pages.updatedAt)],
      limit: 10,
      columns: {
        id: true,
        title: true,
        status: true,
        updatedAt: true,
        // Exclude heavy content field
        content: false,
      },
    },
    user: {
      columns: { id: true, name: true },
    },
  },
});

// Find many with relations
const allFolders = await db.query.folders.findMany({
  where: eq(folders.userId, userId),
  with: { pages: true },
});

Manual Joins

// Left join — includes rows without matching pages
const foldersWithPageCount = await db.select({
  folder: folders,
  pageCount: count(pages.id),
})
  .from(folders)
  .leftJoin(pages, eq(pages.folderId, folders.id))
  .where(eq(folders.userId, userId))
  .groupBy(folders.id)
  .orderBy(desc(count(pages.id)));

// Inner join — only rows with matching data
const pagesWithFolder = await db.select({
  page: pages,
  folderName: folders.name,
  folderSlug: folders.slug,
})
  .from(pages)
  .innerJoin(folders, eq(pages.folderId, folders.id))
  .where(eq(folders.userId, userId));

// Multiple joins
const fullData = await db.select()
  .from(pages)
  .innerJoin(folders, eq(pages.folderId, folders.id))
  .innerJoin(users, eq(folders.userId, users.id))
  .where(eq(users.clerkId, clerkId));

Transactions

// All operations in transaction are atomic
const result = await db.transaction(async (tx) => {
  // Create folder
  const [folder] = await tx.insert(folders)
    .values({ userId, name: 'New Project', slug: 'new-project' })
    .returning();

  // Create initial pages inside it
  const createdPages = await tx.insert(pages)
    .values([
      { folderId: folder.id, title: 'Overview', slug: 'overview' },
      { folderId: folder.id, title: 'Notes', slug: 'notes' },
    ])
    .returning();

  return { folder, pages: createdPages };
});

// Rollback by throwing inside the callback
const safeTransfer = await db.transaction(async (tx) => {
  const [source] = await tx.select().from(folders).where(eq(folders.id, sourceId)).for('update');

  if (!source) {
    tx.rollback(); // explicit rollback
    return;
  }

  await tx.update(folders).set({ parentId: targetId }).where(eq(folders.id, sourceId));
  return source;
});

Batch Operations

// Execute multiple queries in a single round-trip (Neon/SQLite)
const [userResult, foldersResult, pagesResult] = await db.batch([
  db.select().from(users).where(eq(users.id, userId)),
  db.select().from(folders).where(eq(folders.userId, userId)),
  db.select().from(pages).where(inArray(pages.folderId, folderIds)),
]);

Drizzle: Migrations & Database Management

drizzle.config.ts

// drizzle.config.ts (project root)
import { defineConfig } from 'drizzle-kit';

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

drizzle-kit Commands

# Generate SQL migration from schema changes
npx drizzle-kit generate
# Creates timestamped SQL file in ./src/db/migrations/

# Apply migrations to database
npx drizzle-kit migrate

# Push schema directly (dev only — skips migration files)
npx drizzle-kit push

# Open Drizzle Studio (visual DB browser in browser)
npx drizzle-kit studio

# Check for drift between schema and DB
npx drizzle-kit check

# Drop all tables and push fresh schema (dev only!)
npx drizzle-kit push --force

Migration Workflow

# Standard development workflow:
# 1. Edit src/db/schema.ts (add table, column, index)
# 2. Generate migration
npx drizzle-kit generate

# 3. Review the generated SQL in src/db/migrations/
cat src/db/migrations/0005_add_nickname_column.sql

# 4. Apply to local dev DB
npx drizzle-kit migrate

# 5. Commit both schema.ts and migration SQL file
git add src/db/schema.ts src/db/migrations/
git commit -m "feat: add nickname column to users"

# Production deployment:
# Run migrations as part of deploy script
# Never use 'push' in production — always use migrations

Running Migrations Programmatically

// src/db/migrate.ts — run via: npx tsx src/db/migrate.ts
import { drizzle } from 'drizzle-orm/neon-http';
import { migrate } from 'drizzle-orm/neon-http/migrator';
import { neon } from '@neondatabase/serverless';

async function runMigrations() {
  const sql = neon(process.env.DATABASE_URL!);
  const db = drizzle(sql);

  console.log('Running migrations...');
  await migrate(db, { migrationsFolder: './src/db/migrations' });
  console.log('Migrations completed');
}

runMigrations().catch(console.error);

// package.json scripts
{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "npx tsx src/db/migrate.ts",
    "db:migrate:prod": "NODE_ENV=production npx tsx src/db/migrate.ts",
    "db:push": "drizzle-kit push",
    "db:studio": "drizzle-kit studio"
  }
}

Seeding

// src/db/seed.ts
import { db } from './client';
import { users, folders, pages } from './schema';

async function seed() {
  console.log('Seeding database...');

  // Clean existing data (dev only!)
  await db.delete(pages);
  await db.delete(folders);
  await db.delete(users);

  // Insert seed data
  const [user] = await db.insert(users).values({
    clerkId: 'clerk_test_user',
    email: 'test@example.com',
    name: 'Test User',
    plan: 'pro',
  }).returning();

  const [reactFolder] = await db.insert(folders).values({
    userId: user.id,
    name: 'React',
    slug: 'react',
  }).returning();

  await db.insert(pages).values([
    { folderId: reactFolder.id, title: 'Hooks', slug: 'hooks', status: 'proficient' },
    { folderId: reactFolder.id, title: 'Context API', slug: 'context', status: 'to_learn' },
  ]);

  console.log('Seeding complete');
}

seed().catch(console.error);

Drizzle: Advanced Patterns & Type Safety

Drizzle with Zod (drizzle-zod)

import { createInsertSchema, createSelectSchema, createUpdateSchema } from 'drizzle-zod';
import { z } from 'zod';
import { pages } from '@/db/schema';

// Auto-generate Zod schemas from Drizzle table
const insertPageSchema = createInsertSchema(pages, {
  // Override or extend individual fields
  title: z.string().min(1).max(200),
  slug: z.string().regex(/^[a-z0-9-]+$/),
});

const selectPageSchema = createSelectSchema(pages);
const updatePageSchema = createUpdateSchema(pages).omit({ id: true, createdAt: true });

// Use in API route validation
export async function POST(req: Request) {
  const body = await req.json();
  const parsed = insertPageSchema.safeParse(body);
  if (!parsed.success) {
    return Response.json({ errors: parsed.error.format() }, { status: 400 });
  }
  const page = await db.insert(pages).values(parsed.data).returning();
  return Response.json(page[0], { status: 201 });
}

Prepared Statements

import { placeholder } from 'drizzle-orm';

// Prepare once, execute many times — better performance
const getUserById = db.select()
  .from(users)
  .where(eq(users.id, placeholder('id')))
  .prepare('get_user_by_id');

// Execute with type-safe parameters
const user = await getUserById.execute({ id: userId });

// Prepared statement with multiple params
const getFolderPages = db.select()
  .from(pages)
  .where(and(
    eq(pages.folderId, placeholder('folderId')),
    eq(pages.status, placeholder('status')),
  ))
  .orderBy(desc(pages.updatedAt))
  .prepare('get_folder_pages');

const learningPages = await getFolderPages.execute({
  folderId: folder.id,
  status: 'learning',
});

Raw SQL with sql Tag

import { sql } from 'drizzle-orm';

// Raw SQL expression in select
const result = await db.select({
  id: users.id,
  daysSinceCreation: sql<number>`EXTRACT(day FROM NOW() - ${users.createdAt})`,
  nameUpper: sql<string>`UPPER(${users.name})`,
}).from(users);

// Raw WHERE clause
const recent = await db.select()
  .from(users)
  .where(sql`${users.createdAt} > NOW() - INTERVAL '30 days'`);

// Full raw query (escape hatch)
const rawResult = await db.execute(
  sql`SELECT * FROM users WHERE id = ${userId}`
);

JSON/JSONB Columns with Type Safety

// Type your jsonb columns
type PageMetadata = {
  wordCount: number;
  lastEdited: string;
  aiGeneratedAt?: string;
};

export const pages = pgTable('pages', {
  // ...
  metadata: jsonb('metadata').$type<PageMetadata>(),
  // Now metadata is typed as PageMetadata | null
});

// Query jsonb field
const recentlyEdited = await db.select()
  .from(pages)
  .where(sql`${pages.metadata}->>'lastEdited' > ${cutoff}`);

// Update jsonb field
await db.update(pages)
  .set({ metadata: { wordCount: 500, lastEdited: new Date().toISOString() } })
  .where(eq(pages.id, pageId));

Subqueries & CTEs

// Subquery
const subquery = db.select({ userId: folders.userId })
  .from(folders)
  .where(eq(folders.id, folderId))
  .as('subq');

const user = await db.select()
  .from(users)
  .where(eq(users.id, sql`(${subquery})`));

// CTE (Common Table Expression) with $with
const latestPages = db.$with('latest_pages').as(
  db.select({
    folderId: pages.folderId,
    maxUpdated: sql<Date>`MAX(${pages.updatedAt})`.as('max_updated'),
  })
    .from(pages)
    .groupBy(pages.folderId)
);

const result = await db.with(latestPages)
  .select({
    folder: folders,
    lastUpdated: latestPages.maxUpdated,
  })
  .from(folders)
  .leftJoin(latestPages, eq(latestPages.folderId, folders.id));

Performance Tips

• Use .limit() everywhere — never fetch unbounded datasets

• Select only needed columns: .select({ id: users.id, name: users.name }) avoids fetching large columns (content, metadata)

• Add indexes for foreign keys and frequently queried columns via index() in table definition

• Prepared statements for repeated queries — parse plan is cached in PostgreSQL

• Use db.batch() to reduce round-trips (Neon HTTP or SQLite)

• Prefer relational API (db.query) for joins — Drizzle optimizes the SQL. Manual joins for complex aggregates.

• Use connection pooling in long-running servers (PgBouncer, Neon pooled endpoint, RDS Proxy)

• For large bulk operations: use db.insert().values(manyRows) — single SQL statement is much faster than loop