Skip to content

Migrations

How schema changes reach the database, how to check they did, and what to do when one fails. Companion to apps/server/drizzle/DEPLOY-NOTES.md, which lists the migrations that take heavy locks.

How it works

  • Migrations are Drizzle SQL files in apps/server/drizzle/NNNN_<tag>.sql (121 of them at 2026-09-17, 0000 … 0120), tracked in apps/server/drizzle/meta/_journal.json.
  • The runner is apps/server/src/db/migrate.ts (npm run db:migrate): drizzle-orm's migrator applies every journal entry not yet recorded in the database's __drizzle_migrations table — all pending files inside one transaction (drizzle's migrate() wraps the whole loop), then calls storage.ensureBucket() so object storage exists.
  • In Production the migrate compose service runs this once per deploy (restart: "no"); api and worker declare depends_on: migrate: service_completed_successfully, so the app never starts against a schema behind the code. A failed migration means the deploy stops with the previous containers still serving.

Writing one

  1. Change apps/server/src/db/schema.ts.
  2. Generate the SQL: npm run db:generate --workspace apps/server (drizzle-kit) — or write it by hand when you need something drizzle-kit does not emit (partial indexes, CREATE INDEX CONCURRENTLY, data backfills). Hand-written files must be appended to _journal.json by hand with the next idx, a when timestamp and the file tag; the runner ignores files the journal does not list.
  3. Never edit a migration that has been applied anywhere — its hash is recorded; write a new one.
  4. Run it locally against a scratch database (npm run db:migrate), then run the full server suite alone (npm run test --workspace @bimzone/server; global setup drops and recreates the test database, so nothing else may run against it at the same time).
  5. If the migration rewrites or index-builds a large table, add a section to drizzle/DEPLOY-NOTES.md saying what locks it takes and how long — the runner cannot run CONCURRENTLY inside its transaction, so such migrations need a maintenance window or an out-of-band apply (create the index concurrently by hand, then record the migration as applied).

Verifying in Production

  • Dokploy deployment log: Migrations applied. from the migrate service, then api starts.
  • Or query: SELECT id, hash, created_at FROM drizzle.__drizzle_migrations ORDER BY id DESC LIMIT 3; — the newest tag must be the one you shipped.
  • The API's own behaviour: a route that needs the new column answers 200, not a Postgres 42703.

When a migration fails

  1. Read the migrate container log — the runner prints Migration failed: and the Postgres error. The old api/worker are still running the previous code; users are not down.
  2. Typical causes: a hand-written file with a syntax error; a constraint that existing rows violate; a lock timeout on a populated table (see DEPLOY-NOTES).
  3. Fix forward: write a corrected migration and push. Because every pending file ran in one transaction, a failure rolled all of them back — the database is exactly as it was before the deploy, and none of the pending files is recorded as applied. Editing the failed file is acceptable only if it is unreleased anywhere else; otherwise add a new file.
  4. Data-destroying mistakes are the case for Backups & restore › PITR: restore to just before the deploy, then fix forward.

Rolling back code without rolling back schema

Migrations here are additive by convention (add column, add table, add index). Rolling the application back one commit after a successful migration is therefore safe; rolling the schema back is not something the runner does — if a migration must be undone, write the inverse as a new migration.

Local development

  • DATABASE_URL in apps/server/.env; npm run db:migrate from the repo root.
  • The test suite creates its own database from the same migrations on every run — a migration that breaks tests breaks the build before it can reach Production.