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