ADR-0008 ORM / query layer

Context

Postgres is our engine (ADR-0007). The question is how TypeScript code talks to it. Requirements:

• Full type safety — query results typed end to end without codegen gymnastics
• SQL transparency — we should be able to predict the SQL a query produces; no ORM magic surprising us in production
• Migrations as code — versioned, reviewable, runnable in CI and at deploy time
• Works on Bun (ADR-0035) with direct TCP to Postgres (ADR-0003)
• Plays with Zod (ADR-0005) — DB schemas and API validation schemas should not drift

Options considered

1. Drizzle ORM + Drizzle Kit

SQL-first TypeScript ORM. Schemas defined in TypeScript, thin query layer that maps directly to SQL, Drizzle Kit for migrations/introspection/studio.

Pros

• SQL-first: queries read like SQL and produce predictable SQL — no query-planner surprises
• Schema as TypeScript: no separate DSL, full inference for selects/inserts
• Lightweight: pure TS, no codegen step, fast startup, trivial to run on Bun and in distroless Docker images
• Drizzle Kit: migration generation from schema diffs, migrate at deploy, studio for browsing
• drizzle-orm/zod (the separate drizzle-zod package on 0.x) generates Zod schemas from table schemas — direct synergy with ADR-0005 (one source of truth, no drift)
• Postgres is Drizzle’s best-supported dialect (ADR-0007)

Cons

• Younger than Prisma; smaller ecosystem and fewer Stack Overflow answers
• v1 is still a release candidate (stable line is 0.45.x); v1 renames the validator packages and reworks the relational query API
• Relational query API has some rough edges on deeply nested cases
• Team must be comfortable reading SQL — a feature, but requires the skill

2. Prisma

The incumbent TypeScript ORM.

Pros

• Biggest ecosystem, most tutorials, mature tooling (Prisma Studio)
• Friendly schema DSL; approachable for ORM-first teams

Cons

• Separate schema DSL (schema.prisma) instead of TypeScript — prisma generate codegen step, types that lag the schema until regenerated
• (Prisma 7 dropped the Rust query engine by default — TypeScript query compiler plus driver adapters — so deploy weight is no longer a differentiator)
• Less SQL-transparent; generated queries can surprise at scale

3. Kysely

Pure type-safe SQL query builder.

Pros

• Excellent SQL transparency and type inference
• Tiny, dependency-free, run-anywhere

Cons

• Query builder only: no migrations, no schema management — we’d assemble that from extra tools
• More manual work for relations and inserts

4. Raw SQL (pg / postgres.js)

Pros: zero abstraction. Cons: no types without heavy manual effort, error-prone string SQL, no migration story.

Decision

Drizzle ORM v1 for queries and schema definition, Drizzle Kit v1 for migrations and tooling — the company-standard database access layer.

Concretely:

• New apps start on the v1 line, even while it is a release candidate — starting on 0.x would mean a guaranteed migration (package renames, relational query API rework) within months. Pin exact versions (no ^) while v1 is pre-release; upgrade deliberately
• Driver: Bun’s built-in Bun.SQL (ADR-0035), through drizzle-orm/bun-sql: one new SQL(env.DATABASE_URL) pool, wrapped by traceSql for a span per query (ADR-0015), handed to drizzle({ client }). No driver package, no native addon, nothing to compile. Drizzle ships this driver only on the v1 line, which we already use. (Amended 2026-11-12: this ADR first chose pg-native. Amended 2026-11-13: then pg, node-postgres’s pure-JS client; Bun’s built-in client replaces it.)
• Table schemas live in TypeScript (schema.ts) — the single source of truth
• Migrations are generated and applied with Drizzle Kit (drizzle-kit generate / migrate), committed to the repo, run in CI/deploy
• Zod schemas for API boundaries are derived with drizzle-orm/zod where shapes overlap (ADR-0005)
• Raw SQL via Drizzle’s sql template is welcome where it reads better than the query builder

The deciding factors: SQL transparency (we can always see and control what hits Postgres), TypeScript-native schemas with no DSL or codegen, a lightweight pure-TS ORM that fits Bun and Docker, first-party migrations, and the drizzle-orm/zod bridge keeping DB and API schemas from drifting. We accept the younger ecosystem, and the pre-release risk of v1 over a near-certain 0.x → v1 migration.

Consequences

Easier:

• One TypeScript file defines tables → types, migrations, and Zod schemas derive from it
• Predictable SQL: performance tuning happens in queries we can see, not in ORM internals
• Bun + Docker + Drizzle = fast startup, no codegen step in the build
• Drizzle Studio gives DB browsing without another tool
• The driver is part of the runtime: no driver package to update, no compiler or libpq on laptops, in CI, or in the image

Harder:

• Fewer copy-paste answers than Prisma; team reads Drizzle docs and SQL, not just tutorials
• Discipline required to keep raw sql usage readable and reviewed
• Running a release candidate: RC-to-RC upgrades may break, and most tutorials and answers online still show the 0.x API. Revisit when v1 goes stable
• Bun.SQL is younger than node-postgres, and Drizzle’s bun-sql driver lives only on the v1 release-candidate line (rc.4 fixed a db.select that rc.3 broke): upgrade Drizzle and Bun with the integration tests green (ADR-0014)
• No maintained OpenTelemetry instrumentation exists for Bun.SQL, so query spans come from our own traceSql wrapper until Bun traces queries itself (ADR-0015)

Given up: Prisma’s ecosystem gravity and polished onboarding, and node-postgres’s maturity and maintained query instrumentation. Accepted — SQL transparency and codegen-free TypeScript schemas matter more to us than tutorial volume, and a driver built into the runtime is one less dependency to keep current.