Database migrations

Database migrations

The monorepo uses Drizzle ORM and Drizzle Kit for schema and migrations. All Kit commands (generate, migrate, push, studio) use one config (drizzle.config.cjs) and the same database URL as the server (one DB, e.g. in Docker).

How it works (Drizzle Kit)

Per the Kit overview:

• generate — Reads your Drizzle schema, diffs against previous snapshots, and writes new SQL migration files into packages/db/drizzle/ (and updates meta/_journal.json). No DB connection needed for the diff; DB URL is only used when you run migrate or push.
• migrate — Connects to the DB, reads the migration folder and journal, and runs any not-yet-applied SQL files, then records them in public.__drizzle_migrations.
• push — Connects to the DB and syncs your schema directly (no migration files). Good for quick iteration; for versioned deployments use generate → migrate.

All three use the same config (drizzle.config.cjs) and the same env (e.g. SERVER_POSTGRES_URL in packages/db/.env), so they target the same single database.

Commands (from repo root)

Command	What it does
pnpm db:generate	Generates a new migration file from schema changes in packages/db (run after editing schema).
pnpm db:migrate	Applies pending migration files to the database. Use in both dev and production.
pnpm db:push	Pushes the current schema directly to the DB without migration files. Dev-only; can cause drift.
pnpm db:drop	Drops the database (destructive).
pnpm db:seed	Seeds the database with initial data.
pnpm db:which	Prints which DB URL migrate would use (redacted). Use to verify it matches the server.

Environment

• Server reads SERVER_POSTGRES_URL from apps/server/.env when you run the app.
• Worker (apps/worker) reads SERVER_POSTGRES_URL from apps/worker/.env for pg-boss and DB access. Use the same URL as the server so the worker and API share the same database and job queue.
• Migrate reads SERVER_POSTGRES_URL (or DB_POSTGRES_URL) from packages/db/.env when you run pnpm db:migrate.
• These files can differ. For migrations to apply to the same DB the app uses, server and migrate URLs must match. For the worker to process jobs from the same queue as the server, worker and server must use the same SERVER_POSTGRES_URL.

Verify which DB migrate uses: run pnpm db:which and compare the printed URL with SERVER_POSTGRES_URL in apps/server/.env. They must match.

PostgreSQL note: The migrations table is in the public schema: public.__drizzle_migrations. To see applied migrations: SELECT * FROM public.__drizzle_migrations ORDER BY id;

Baseline policy (current starting point)

• packages/db/drizzle/0000_init.sql is the single baseline migration and includes the latest schema.
• If you need to reset migration history again in the future, create a new baseline in 0000_init.sql and reset drizzle/meta/_journal.json to a single 0000_init entry in the same PR.
• Never run db:generate directly in production. Generate in development, review SQL, commit, then deploy.

Recommended workflow

Development

1. Change schema in packages/db/src (e.g. add/change tables or columns).
2. Generate a migration:
   pnpm db:generate
   This creates a new .sql file under packages/db/drizzle/ and updates drizzle/meta/_journal.json.
3. Apply migrations:
   pnpm db:migrate
   This runs any pending migrations (including the one you just generated).
4. Optionally seed:
   pnpm db:seed
   if you need fresh seed data.

Use migrate (not only push) so that the same migration files can be used in production.

Production (GitHub workflow path)

1. Commit migration files (packages/db/drizzle/*.sql and packages/db/drizzle/meta/_journal.json) together with schema code.
2. Trigger .github/workflows/deploy.yml.
3. The workflow pulls the pinned db image for the same commit (db:sha-<commit-sha>) and runs: pnpm --filter=@repo/db migrate against production Postgres.
4. Migrations run before app rollout and fail the deploy on migration errors.
5. Do not run db:push or db:generate in production.

When to use which

• db:generate — After you change the Drizzle schema and want a new migration file.
• db:migrate — To apply pending migrations (dev and prod). Same DB as the app.
• db:push — Quick dev-only sync of schema to DB without migration files; avoid if you rely on versioned migrations.
• db:drop — Reset DB (destructive).
• db:seed — Load initial/reference data.

Troubleshooting

• “Only one row in __drizzle_migrations” / migrations don’t apply

  1. Different databases: Migrate uses packages/db/.env; the server uses apps/server/.env. If the URLs differ, migrate applied to one DB and the app is using another. Copy SERVER_POSTGRES_URL from apps/server/.env into packages/db/.env so they are identical, then run pnpm db:migrate again.
  2. Where to look: Applied migrations: SELECT * FROM public.__drizzle_migrations ORDER BY id; App tables (e.g. sef_invoice) are in public.

• “Only one row” when checking migrations
  Check public.__drizzle_migrations. Run pnpm db:which to see which DB migrate uses; it must match apps/server/.env.

• “column X does not exist”
  Ensure packages/db/.env and apps/server/.env use the same URL, then run pnpm db:migrate.