Database Migrations

Drizzle-powered schema migrations with checksums, rollback, drift detection, and conflict resolution — never lose a schema change again.

Quick Start

# 1. Edit your Drizzle schema
#    src/db/schema/users.ts

# 2. Generate a migration SQL file
nself db migrate generate --name add_documents_table

# 3. Review the generated SQL
cat migrations/0003_add_documents_table.sql

# 4. Apply to database
nself db migrate up

# 5. Verify
nself db migrate status

Architecture Overview

Migration pipeline:

  src/db/schema/*.ts           (Drizzle schema — source of truth)
         |
         v (nself db migrate generate)
  migrations/
    0001_initial.sql
    0002_add_users_source_id.sql
    0003_add_documents.sql
    meta/
      _journal.json            (migration order + checksums)
         |
         v (nself db migrate up)
  np_migrations table          (tracks applied migrations)
         |
         v
  PostgreSQL (live schema)

nself db migrate — Command Reference

Drizzle Schema Setup

// src/db/schema/index.ts — barrel export
export * from './users'
export * from './documents'
export * from './tenants'

// src/db/index.ts — database connection
import { drizzle } from 'drizzle-orm/node-postgres'
import { Pool } from 'pg'
import * as schema from './schema'

const pool = new Pool({
  host:     process.env.POSTGRES_HOST,
  port:     Number(process.env.POSTGRES_PORT),
  database: process.env.POSTGRES_DB,
  user:     process.env.POSTGRES_USER,
  password: process.env.POSTGRES_PASSWORD,
  ssl:      process.env.NODE_ENV === 'production',
})

export const db = drizzle(pool, { schema })

// drizzle.config.ts (project root)
{
  "schema": "./src/db/schema/*.ts",
  "out": "./migrations",
  "driver": "pg",
  "dbCredentials": {
    "connectionString": "postgresql://nself:password@localhost:5432/nself"
  }
}

Generating Migrations

# Generate from schema changes
nself db migrate generate --name add_documents_table

# What this produces:
# migrations/0003_add_documents_table.sql
# migrations/meta/_journal.json (updated)
# migrations/meta/0003_snapshot.json (schema snapshot)

Review the generated SQL before applying:

-- migrations/0003_add_documents_table.sql
-- Generated by nself db migrate generate

CREATE TABLE "np_documents" (
  "id"                 uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "source_account_id"  text DEFAULT 'primary' NOT NULL,
  "tenant_id"          uuid,
  "user_id"            uuid NOT NULL,
  "title"              text NOT NULL,
  "body"               text,
  "created_at"         timestamptz DEFAULT now() NOT NULL,
  "updated_at"         timestamptz DEFAULT now() NOT NULL,
  "deleted_at"         timestamptz,
  CONSTRAINT "np_documents_user_id_np_users_id_fk"
    FOREIGN KEY ("user_id") REFERENCES "np_users"("id") ON DELETE RESTRICT
);

CREATE INDEX "idx_np_documents_user" ON "np_documents" ("user_id");
CREATE INDEX "idx_np_documents_source_active"
  ON "np_documents" ("source_account_id","user_id")
  WHERE deleted_at IS NULL;

CREATE TRIGGER "np_documents_updated_at"
  BEFORE UPDATE ON "np_documents"
  FOR EACH ROW EXECUTE FUNCTION np_set_updated_at();

Applying Migrations

# Apply all pending migrations (idempotent)
nself db migrate up

# Output:
# Applying 0003_add_documents_table.sql ... done (42ms)
# Applied 1 migration. Database is now at version 0003.

# Apply up to a specific version
nself db migrate up --to 0003

# Dry run (show what would be applied without running)
nself db migrate up --dry-run

# Apply in production (requires explicit flag)
nself db migrate up --env prod

Rolling Back

nSelf generates a .down.sql file alongside every migration. Rollback replays the down file.

# Roll back the last migration
nself db migrate down

# Roll back to a specific version
nself db migrate down --to 0001

# Dry run rollback
nself db migrate down --dry-run

-- migrations/0003_add_documents_table.down.sql
-- Generated automatically by nself db migrate generate

DROP TABLE IF EXISTS "np_documents";

Checking Status

nself db migrate status

# Output:
# Migration status for env: dev
#
# Applied:
#   [x] 0001_initial.sql             2026-05-01 10:00:00
#   [x] 0002_add_source_account_id   2026-05-03 14:22:17
#   [x] 0003_add_documents_table     2026-05-07 09:11:03
#
# Pending:
#   [ ] 0004_add_fts_index           (not yet applied)
#
# Database is behind schema by 1 migration.

Verifying Checksums

nSelf stores a SHA-256 checksum of each migration file when it is applied. If someone edits an applied migration file (a common mistake), verify-checksums catches it.

nself db migrate verify-checksums

# All good:
# Verifying 3 applied migrations...
# [x] 0001_initial.sql             OK
# [x] 0002_add_source_account_id   OK
# [x] 0003_add_documents_table     OK
# All checksums match.

# Tampered migration:
# [!] 0002_add_source_account_id   CHECKSUM MISMATCH
#     Expected: sha256:abc123...
#     Got:      sha256:def456...
#
# ERROR: Applied migration has been modified. This is a schema integrity violation.
# To resolve: restore the original file, or create a new migration for the change.

Run this check on every deployment via nself doctor.

Drift Detection

Schema drift occurs when someone modifies the database directly (via psql, Hasura console DDL, etc.) without creating a migration.

# Show diff between current DB and Drizzle schema
nself db migrate diff

# Output:
# Schema drift detected:
#
# Missing in DB (in schema, not in DB):
#   Column: np_documents.fts_vector (tsvector)
#   Index:  idx_np_documents_fts
#
# Extra in DB (in DB, not in schema):
#   Table: temp_import_2026_05_07
#
# To fix: generate a migration or drop the extra table.

# Auto-generate a migration to fix drift
nself db migrate generate --name fix_drift_from_$(date +%Y%m%d)

Conflict Resolution

When two developers generate migrations simultaneously, their migration numbers may conflict.

# Scenario: two developers both generated 0004_*.sql

# Check for conflicts
nself db migrate status --check-conflicts

# Output:
# CONFLICT detected:
#   0004_add_fts_index.sql       (developer-A, hash: abc123)
#   0004_add_notifications.sql   (developer-B, hash: def456)
#
# These migrations have the same sequence number but different content.

# Resolution: renumber one of them
nself db migrate renumber 0004_add_notifications.sql --to 0005

# Then rebase journal
nself db migrate journal rebase

Seeding Data

# Run seed files (separate from migrations)
nself db seed                              # All seed files
nself db seed --file seeds/001_roles.sql  # Specific file

# Apply Hasura-format seed
nself db hasura seed apply --file 008_owner_license.sql

# Generate mock data (dev only)
nself db mock --table np_users --count 100
nself db mock --table np_documents --count 1000

// seeds/001_roles.ts — TypeScript seed file
import { db } from '../src/db'
import { npRoles } from '../src/db/schema'

async function seed() {
  await db.insert(npRoles).values([
    { id: 'admin',  name: 'Administrator', permissions: ['*'] },
    { id: 'user',   name: 'User',          permissions: ['read', 'write'] },
    { id: 'viewer', name: 'Viewer',        permissions: ['read'] },
  ]).onConflictDoNothing()

  console.log('Seeded roles.')
}

seed().catch(console.error).finally(() => process.exit())

Multi-Tenant Migrations

# Apply migration across all Cloud tenants (schema-per-tenant mode)
nself db migrate up --all-tenants

# Apply to one tenant only
nself db migrate up --tenant acme-corp

# Status across all tenants
nself db migrate status --all-tenants

CI/CD Integration

# .github/workflows/migrate.yml
name: Database Migrations

on:
  push:
    branches: [main]
    paths: ['migrations/**', 'src/db/schema/**']

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Verify checksums (no tampered migrations)
        run: nself db migrate verify-checksums --env staging

      - name: Apply migrations to staging
        run: nself db migrate up --env staging

      - name: Check for drift
        run: nself db migrate diff --env staging --fail-on-drift

      - name: Run schema tests
        run: pnpm test:schema

Best Practices

• Never edit an applied migration file — create a new migration instead. verify-checksums will catch any tampering.
• Review generated SQL before applying — Drizzle's diff may not always produce the safest migration for large tables. Add CONCURRENTLY to index creation manually for production.
• Use CREATE INDEX CONCURRENTLY for large tables — standard CREATE INDEX locks the table. Add this manually to generated migrations for any table over 1M rows.
• Run verify-checksums on every deploy — include it in CI and nself doctor.
• Never use nself db migrate push in production — this bypasses migration tracking entirely.
• Keep migrations small and focused — one table per migration, or one set of related changes. Easier to review, easier to roll back.
• Take a backup before rolling back in production — down migrations drop data.
• Use --dry-run before production applies — verify the SQL that will be run.

Related

• Schema Design — np_* conventions, Drizzle schema patterns
• Database Management — Backup, restore, seeds, diff
• PostgreSQL — Extensions, RLS, indexes
• Multi-Tenancy — Migrations across tenant schemas