Database Migration

Migration Files

Migrations are split into two categories:

Create a Migration

Create a SQL file in api/src/migrations/, named by date:

-- api/src/migrations/20260416.sql
CREATE TABLE IF NOT EXISTS todos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  title VARCHAR(255) NOT NULL,
  completed BOOLEAN DEFAULT false,
  created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,
  updated_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT
);

Run Migrations

npm run migrate

How It Works

The migration script (libs/api-core/src/shared/scripts/migrate.ts):

1. Creates a migrations table (tracks executed migrations)
2. Reads all .sql files sorted by filename
3. Skips already executed ones, runs new ones
4. Core migrations run first, then business migrations

[core] Executed: 20251001.sql
[core] Skipped: 20260411.sql (already executed)
[user] Executed: 20260416.sql

Conventions

• Use CREATE TABLE IF NOT EXISTS to avoid duplicate creation
• Use ALTER TABLE ... ADD COLUMN IF NOT EXISTS to add columns
• Timestamps use BIGINT (Unix timestamp)
• IDs use UUID
• Multi-tenant tables must have a tenant_id column