Skip to content

Database Migrations

Migrations are numbered SQL files in packages/db/migrations/. They're applied in lexicographic order by pnpm db:migrate. Already-applied migrations are tracked in a _migrations table.

Running migrations

Bare-metal

pnpm db:migrate

This runs from services/api against the URL in DATABASE_URL.

Docker

docker compose -f docker/docker-compose.yml run --rm migrate

The migrate service is a small, dedicated container that runs once and exits. It's auto-run on first up; rerun manually after pulling new code.

Idempotency

The runner is idempotent — migrations already in _migrations are skipped. Safe to run on every deploy.

Adding a new migration

  1. Pick the next number higher than the current highest in the directory.
ls packages/db/migrations | sort -V | tail -1
# 050_mode_tool_config.sql → next is 051_*
  1. Create the file:
touch packages/db/migrations/051_my_change.sql
  1. Write SQL that's idempotent when possible:
CREATE TABLE IF NOT EXISTS my_new_table (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_my_new_table_created
  ON my_new_table (created_at);

  1. Add corresponding query helpers in packages/db/src/queries/ and any types in types-core.ts.

  2. Run locally:

pnpm db:migrate
  1. Commit the migration and the helpers in the same PR.

Rolling back

Doable doesn't ship automatic down migrations — they're error-prone in production. Roll back manually:

  1. Write a follow-up migration (e.g. 052_rollback_my_change.sql) that undoes the change.
  2. Or, restore from your most recent backup if the change was destructive.

For destructive changes (DROP TABLE / column), always:

  • Make sure no in-flight code reads the dropped object.
  • Take a backup immediately before.
  • Roll out in two steps: stop reading → run migration.

Naming conventions

  • Lower-case, underscore-separated descriptive name.
  • Number padded to 3 digits.
  • Group related changes into a single file when possible.

Examples from the existing tree:

  • 004_analytics.sql
  • 017_teams.sql
  • 024_git_versioning.sql
  • 050_mode_tool_config.sql

Multi-step migrations (zero-downtime)

For changes that would lock heavily on a large table:

  1. Migration A: add the new column / table, dual-write in app code.
  2. Migration B (next deploy): backfill existing rows.
  3. Migration C (later): switch reads to the new column, drop the old one.

This is the standard expand → migrate → contract pattern. Doable's tables are small enough that most of the time you can do it in a single migration without locking issues — but for chat_messages, activity_events, or analytics tables in busy instances, plan ahead.

Testing migrations

Spin up a throwaway database:

docker run --rm -d -p 5433:5432 -e POSTGRES_PASSWORD=test --name pgtest pgvector/pgvector:pg16
DATABASE_URL=postgres://postgres:test@localhost:5433/postgres pnpm db:migrate
docker rm -f pgtest

For more involved checks, snapshot a production backup (anonymized) to staging and run the full migration set.

Where the runner lives

services/api/src/db/migrate.ts — small, ~100 lines. Reads files from packages/db/migrations, compares to _migrations, runs missing ones in a transaction, records each.

Using a different schema name

By default everything is in public. If you want a dedicated doable schema:

-- Run once manually
CREATE SCHEMA doable;
ALTER ROLE doable SET search_path = doable, public;

Then re-run pnpm db:migrate. Migrations don't qualify table names, so the role's search_path controls placement.