Skip to content

ADR-0004: Versioned migrations with Drizzle

  • Status: Accepted
  • Date: 2026-11-12

In Ochema the database carries part of the security model: policies, column privileges, definer functions, triggers and partitions. All of it must be reproducible in every environment, reviewed as text and typed for the code that queries it.

How is the schema declared and changed?

  • No table, column or index exists outside a versioned migration.
  • Policies, privileges and functions live in the same history as the tables.
  • The API and the worker query with types derived from the schema.
  • The SQL that runs in production is the SQL that was reviewed.
  1. Drizzle ORM with SQL migrations generated by drizzle-kit, plus hand-written migrations for what the generator cannot express.
  2. Schema synchronization without migration files (drizzle-kit push). Rejected: migrations are applied only from files.
  3. Another ORM for Node.js with its own migration tool. Common market alternative, listed for comparison.

Chosen option: 1.

The tables are declared in packages/db/src/schema. drizzle-kit generate writes the SQL into packages/db/migrations. What the generator cannot express is written by hand as a custom migration: partitioned tables, FORCE ROW LEVEL SECURITY, column privileges, triggers and the definer functions. pnpm --filter @ochema/db migrate applies the pending files, connected as the owner role. The API and the worker connect as a different role that owns nothing.

Two implementation choices were recorded with the decision: the pg driver, and the stable 0.45 line of drizzle-orm instead of the 1.0 release candidate.

  • Good, because tables and security objects share one ordered history, and upgrade tests (packages/db/test/upgrade*.test.ts) run the migrations over existing data.
  • Good, because queries are typed from the same files that generate the tables.
  • Bad, because drizzle-orm applies all pending migrations in one transaction. CREATE INDEX CONCURRENTLY cannot be used inside a migration, so creating an index on a large table blocks writes to it while the index is built.
  • Bad, because hand-written objects are outside the generator snapshot. For the five partitioned tables the TypeScript declaration and the SQL are kept in step by hand.
  • Bad, because a column written only by a trigger needs explicit column privileges, which is easy to miss when a table gains a column.
  • packages/db/drizzle.config.ts: which schema files the generator reads.
  • packages/db/scripts/migrate.ts and packages/db/sql/bootstrap-roles.sql: applying migrations, and the roles and extensions created before the first one.
  • packages/db/migrations: the migration files.
  • Concept pages: Tenant isolation for the roles and the policies the migrations create, Testing for the upgrade tests.
  • Reference: Database tables.
  • Related: ADR-0002, ADR-0028.