Guide

Drizzle fundamentals explained

Drizzle ORM is a lightweight, SQL-forward TypeScript ORM for Node.js, Bun, and edge runtimes. Instead of a proprietary schema DSL and a heavy generated client, you define tables as TypeScript objects, write queries that look like SQL composed in code, and let the compiler infer row shapes. Teams pick Drizzle when they want zero runtime magic, small bundle sizes on Hono Workers, and explicit control over every JOIN and WHERE clause. The tradeoffs are real: you type more than with Prisma’s nested writes, relation APIs are younger than Prisma’s, and you still own connection pooling and index design on PostgreSQL. This guide covers Drizzle table schemas, the query builder and relational API, drizzle-kit migrations, transactions, serverless pooling, pairing with Zod and TypeScript, a Harbor Fleet shipment API worked example, an ORM decision table, common pitfalls, and a production checklist.

What Drizzle is

Drizzle splits into two packages most teams install together:

drizzle-orm — the runtime query layer: connect to Postgres, MySQL, SQLite, or Turso; run selects, inserts, updates, and relational queries with full type inference.
drizzle-kit — the CLI for migrations (generate, migrate, push), schema introspection (pull), and studio browsing.

Drizzle’s philosophy is SQL you can read. A db.select().from(users).where(eq(users.email, email)) call maps closely to the SQL it emits. There is no opaque query engine translating a graph API into surprise SELECT * storms. That transparency helps when you optimize slow endpoints with database indexes and EXPLAIN ANALYZE.

Drizzle is not a database server. It is a thin client over your existing Postgres or SQLite instance. You still configure backups, replication, and pool limits on the engine underneath.

Defining schemas in TypeScript

Tables are plain TypeScript constants built with dialect helpers. A Postgres shipment table might look like:

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

export const shipments = pgTable('shipments', {
  id: uuid('id').primaryKey().defaultRandom(),
  trackingCode: text('tracking_code').notNull().unique(),
  status: text('status').notNull().default('pending'),
  weightGrams: integer('weight_grams').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
})

Column builders mirror SQL types: text, integer, bigint, boolean, timestamp, jsonb, numeric. Modifiers include .notNull(), .unique(), .default(), and .references(() => otherTable.id) for foreign keys.

Inferring types

Drizzle exports inference helpers so API layers share one source of truth:

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

export type Shipment = InferSelectModel<typeof shipments>
export type NewShipment = InferInsertModel<typeof shipments>

Pair with drizzle-zod to generate Zod schemas from table definitions for request validation — the insert schema omits auto-generated columns like id and createdAt when configured correctly.

Indexes and enums

Declare compound indexes in the table callback: (table) => [index('shipments_status_idx').on(table.status, table.createdAt)]. Postgres enums use pgEnum('status', ['pending', 'in_transit', 'delivered']) then reference the enum type on the column. Keep schema files modular (schema/shipments.ts, schema/events.ts) and export a single schema object to drizzle-kit via drizzle.config.ts.

Connecting and querying

Create a database instance with the driver for your runtime:

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

Core query builder

Reads: db.select().from(shipments).where(eq(shipments.status, 'pending')). Project columns with .select({ id: shipments.id, code: shipments.trackingCode }). Ordering, limits, and offsets chain fluently. Inserts: db.insert(shipments).values({ trackingCode, weightGrams }).returning() — returning() on Postgres gives you the inserted row without a second round trip. Updates and deletes use .set() and .where() with the same filter operators: eq, and, or, inArray, isNull, like, between.

Relational queries

Define relations once with relations(), then use the RQB (relational query builder): db.query.shipments.findMany({ with: { events: true }, where: eq(shipments.status, 'in_transit') }). This eager-loads child rows in optimized queries — but inspect generated SQL during development; deep with trees can still over-fetch without careful columns selection on nested objects.

Raw SQL escape hatch

Use db.execute(sql`SELECT ...`) or sql.raw with bound parameters for window functions, CTEs, and database-specific features Drizzle does not model. Keep raw fragments in repository modules so the rest of the app stays typed.

Migrations with drizzle-kit

Configure drizzle.config.ts with schema path, migrations output folder, and dialect. Development workflow:

Edit TypeScript schema files.
Run npx drizzle-kit generate — diffs schema against the last snapshot and writes SQL files under drizzle/.
Review generated SQL in PRs — Drizzle is good but not infallible on complex renames.
Apply locally: npx drizzle-kit migrate (or your app calls migrate() on boot in dev).
Production CI: run drizzle-kit migrate before deploying new containers.

drizzle-kit push syncs schema directly without migration files — acceptable for solo prototypes, risky for production because teammates cannot replay history. drizzle-kit pull introspects an existing database into schema files when adopting Drizzle on a legacy DB; baseline migrations with drizzle-kit migrate after marking initial state.

drizzle-kit studio launches a local browser UI to browse tables — useful for debugging, never expose it publicly without authentication.

Relations and transactions

Relation definitions

Foreign keys live on table columns via .references(). Relation metadata for the query API is separate:

export const shipmentEvents = pgTable('shipment_events', {
  id: uuid('id').primaryKey().defaultRandom(),
  shipmentId: uuid('shipment_id').references(() => shipments.id).notNull(),
  eventType: text('event_type').notNull(),
  occurredAt: timestamp('occurred_at', { withTimezone: true }).defaultNow(),
})

export const shipmentsRelations = relations(shipments, ({ many }) => ({
  events: many(shipmentEvents),
}))

One-to-one uses one() with optional fields and references. Many-to-many typically uses an explicit join table with extra columns (role, quantity) rather than an implicit junction — Drizzle favors explicit schemas, which ages better when the join row carries data.

Transactions

Wrap multi-step logic in db.transaction(async (tx) => { ... }). All operations inside use tx instead of db. A shipment checkout might insert the row, insert an initial scan event, and decrement warehouse inventory in one unit. Postgres defaults to READ COMMITTED; for ledger-style invariants use tx.execute(sql`SELECT ... FOR UPDATE`) on contested rows before updating.

Serverless, edge, and connection pooling

Drizzle itself is tiny, but database drivers still open TCP connections. On long-lived Node servers, a singleton Pool per process is standard. On serverless (Vercel, Cloudflare Workers with Hyperdrive, AWS Lambda), unbounded pools exhaust Postgres max_connections.

Pooler in front of Postgres — PgBouncer, Neon, or Supabase pooler; point DATABASE_URL at the pooled endpoint.
Neon serverless driver — @neondatabase/serverless with drizzle-orm/neon-http for one-shot HTTP queries on edge (higher latency per query, no persistent pool).
Turso / libSQL — drizzle-orm/libsql for edge SQLite replicas when your data model fits embedded SQL.
Limit pool size — max: 1 on small Lambdas if you must pool locally; prefer external pooler instead.

Because Drizzle adds almost no overhead, the pooling story is identical to raw pg — you are not paying an extra managed proxy tax unless you choose one.

Worked example: Harbor Fleet shipment API

Harbor Fleet tracks container shipments on a Hono API deployed to Cloudflare Workers with Postgres via Hyperdrive. Their Drizzle schema defines shipments, shipment_events, and warehouses with a foreign key from events to shipments.

Creating a shipment in one transaction:

Validate input with a Zod schema derived from createInsertSchema(shipments) minus server-set fields.
tx.insert(shipments).values({ trackingCode, weightGrams }).returning() captures the new id.
tx.insert(shipmentEvents).values({ shipmentId, eventType: 'created' }) seeds the audit trail.
Return the row from tx.query.shipments.findFirst({ with: { events: true }, where: eq(shipments.id, id) }).

The public tracking endpoint uses a narrow select projecting only trackingCode, status, and latest event timestamp — no internal warehouse ids leak to anonymous callers. Admin dashboards use relational queries with with: { events: { orderBy: desc(shipmentEvents.occurredAt) } }. Monthly on-time delivery reports run as a raw SQL CTE because window functions over event sequences are clearer in SQL than in the builder. Migrations ship through GitHub Actions running drizzle-kit migrate against staging before production promotion.

ORM decision table

Choose Drizzle when…	Prefer Prisma when…	Prefer Kysely or sqlc when…
Bundle size and edge cold starts matter	Nested writes and relation CRUD dominate	Queries are mostly hand-written SQL
You want SQL-transparent query code	Team prefers declarative schema DSL	Compile-time safety from .sql files is enough
Zod-first validation from shared schema	MongoDB document model is required	Polyglot services share SQL, not TypeScript
Hono / Workers / Bun microservices	Prisma Migrate + Studio ecosystem is valued	ORM features add no value over thin SQL
You are comfortable reviewing migration SQL	Heavy codegen and Client API is acceptable	Analytics are 90%+ raw SQL already

Common pitfalls

Forgetting returning() — Postgres inserts without returning force a second select to get generated ids.
Mixing db and tx inside transactions — operations outside tx are not rolled back on failure.
Blind push to production — no auditable migration history; use generate + migrate for teams.
Deep relational with trees — inspect SQL; over-fetching columns negates Drizzle’s lean reputation.
No pooler on serverless — each isolate opening ten pg connections will take down small Postgres instances.
Schema drift between kit and runtime — deploy migrations before app code that expects new columns.
Renaming columns without SQL review — kit may emit drop+add instead of rename; hand-edit migration when data volume is large.
Skipping indexes declared only in comments — Drizzle indexes must be in schema or raw migration SQL, not README notes.

Practitioner checklist

Install drizzle-orm + drizzle-kit; add drizzle.config.ts pointing at schema and migrations folders.
Define tables with explicit references() and export InferSelectModel types for API responses.
Wire drizzle-zod insert/select schemas for HTTP handlers validated with Zod.
Create a singleton db module; never instantiate pools per request on servers.
Run drizzle-kit generate after schema changes; commit SQL to git.
Apply drizzle-kit migrate in CI before rolling new app versions.
Use db.transaction for multi-table writes; add FOR UPDATE on contested inventory rows.
Log slow queries; add indexes when EXPLAIN shows sequential scans on hot paths.
Configure PgBouncer or managed pooler before deploying to serverless at scale.
Document when endpoints use raw SQL so future contributors do not duplicate logic in the builder.

Key takeaways

Drizzle is a thin TypeScript ORM with SQL-transparent queries and minimal runtime overhead.
Schemas are TypeScript tables; types infer via InferSelectModel and pair cleanly with Zod.
drizzle-kit generates versioned SQL migrations; avoid push in production teams.
Relational queries eager-load children but still deserve SQL review on complex graphs.
Serverless requires external pooling or HTTP drivers — Drizzle’s small bundle does not remove connection limits.