ADR-004: ORM & Data Access Layer — Drizzle ORM

Date: 2026-04-09 | Amended: 2026-04-10 (v2.0 D1 adapter)

Status: Accepted (amended)

Deciders: Platform Engineering Lead, Backend Lead

CODITECT Classification: Architecture Decision Record · A6

Context

We need a data access layer for the relational database. The schema is relational and well-defined (polls, slots, responses, slot_responses). We need type-safe queries, migration management, and good developer ergonomics without sacrificing visibility into generated SQL (important for query optimisation and auditability in regulated contexts).

v2.0 amendment: The database changed from PostgreSQL to Cloudflare D1 (SQLite) per ADR-006. Drizzle ORM's decision stands — only the adapter changes from `drizzle-orm/postgres-js` to `drizzle-orm/d1`. The `drizzle.config.ts` dialect changes from `postgresql` to `sqlite`, and the driver to `d1-http`. All other benefits (SQL visibility, plain SQL migrations, type safety) are preserved.

Options evaluated:

Key requirements for our context:

• Full visibility into SQL for performance reviews and compliance audit trails
• Type safety from schema definition through to query results — no runtime surprises
• Lightweight — we don't want a binary sidecar (Prisma's query engine) in the Docker image
• Migrations stored as plain SQL files — reviewable in PRs, appliable by DBA if needed

Decision

Adopt Drizzle ORM as the data access layer.

• Schema defined once in src/db/schema.ts using Drizzle's TypeScript schema builder
• drizzle-kit generate produces plain SQL migration files — committed to src/db/migrations/
• drizzle-kit migrate applies migrations — can also be run by a DBA directly against the SQL files
• Queries written using Drizzle's type-safe query builder — SQL-like syntax, no magic, full TypeScript inference on results
• No binary sidecar — Drizzle runs entirely in the Node.js process

Consequences

Positive:

• Generated SQL is readable and reviewable in PRs — critical for compliance audit trail
• Zero binary sidecar — Docker image is smaller and simpler to reason about
• TypeScript types flow from schema definition to query results without code generation step at build time (unlike Prisma which requires prisma generate)
• Migration files are plain SQL — a DBA can review, modify, or apply them without Drizzle installed
• Active development cadence; PostgreSQL support is first-class

Negative:

• Drizzle ORM is newer than Prisma — less Stack Overflow coverage for edge cases; team may hit undocumented behaviours
• No built-in seeding tool — seed scripts must be written manually (acceptable for this schema)
• drizzle-kit studio (visual DB browser) is a convenience tool only — do not expose in production environments
• Relations API (for eager loading) is less ergonomic than Prisma's include — complex join queries may require raw SQL via db.execute(sql\...\)

Alternatives Rejected

Prisma: Binary query engine adds ~30MB to the Docker image and complicates multi-architecture builds (ARM vs. AMD64). The generated SQL is not visible by default, making query review harder. Deferred to future consideration if Prisma removes the binary engine dependency (in progress as of 2026 with Prisma 6 Rust rewrite).

Raw pg: Maximum control but every query requires manual TypeScript type annotations. Boilerplate cost is not justified for a schema this size. No migration tooling included.

Kysely: Excellent query builder but no built-in migration management — would require pairing with a separate migration tool (e.g., node-pg-migrate), adding complexity.

Review Trigger

Revisit if Drizzle ORM introduces a breaking change in a major version that requires significant migration of existing query code, or if Prisma completes its binary-free query engine and the ecosystem gap closes.