Savvi Studio

Database Migrations

Purpose: Schema evolution and migration patterns
Last Updated: 2024-11-26

Overview

Database migrations manage schema changes over time. The current system uses numbered SQL files that load on container initialization. A more sophisticated migration system is planned for the future.

For complete migration examples, see Migration Examples.

Current Migration System

How It Works

Container initialization:

  1. Dockerfile.postgres copies db/ to /docker-entrypoint-initdb.d/
  2. PostgreSQL loads all .sql files in numeric order
  3. Template database created with full schema
  4. Working database cloned from template

Key files:

  • db/*.sql - Schema definition files
  • src/lib/db/migrations.ts - Migration runner
  • Template database - For fast test database creation

Migration Runner

Location: src/lib/db/migrations.ts

interface MigrationResult {
  success: boolean;
  filesExecuted: number;
  errors: Error[];
  duration: number;
}

Used by: Test fixtures, template database creation, development database setup

Creating Migrations

Development Workflow:

  1. Add SQL file: touch db/316_graph_my_feature.sql
  2. Write migration: See Example 1 - Add Table Migration
  3. Apply migration: docker compose down -v && docker compose up -d postgres
  4. Run codegen: pnpm db:codegen (see Codegen System)
  5. Test migration: See Example 7 - Test Migration Success

Migration Patterns

For complete SQL examples, see Migration Examples.

Pattern 1: Add Table

See: Example 1

Create table with indexes and comments.

Pattern 2: Add Column

See: Example 2

Add column with default, update existing rows, then set NOT NULL.

Pattern 3: Add Function

See: Example 3

Create PostgreSQL function with proper comments.

Pattern 4: Modify Function

See: Example 4

Drop old version, create new version.

Pattern 5: Add Index

See: Example 5

Create regular, composite, or partial indexes.

Pattern 6: Data Migration

See: Example 6

Transform data with validation.

Rollback Strategies

Development Rollback

Reset database:

docker compose down -v
docker compose up -d postgres

Production Rollback

Option 1: Backup and restore

pg_dump -h prod -U user -d db > backup.sql
psql -h prod -U user -d db < backup.sql

Option 2: Reverse migration (write down migration manually)

Option 3: Point-in-time recovery (if supported)

Testing Migrations

Test Migration Success

test('migration completes successfully', async ({ migration }) => {
  expect(migration.success).toBe(true);
  expect(migration.errors).toHaveLength(0);
});

Test Schema Changes

test('migration adds table', async ({ newDbClient }) => {
  const result = await newDbClient.query(`
    SELECT EXISTS (
      SELECT FROM pg_tables
      WHERE schemaname = 'graph' AND tablename = 'my_feature'
    ) as exists
  `);
  expect(result.rows[0].exists).toBe(true);
});

Template Database Pattern

How it works: After loading all migrations, the database is renamed to savvi_studio_template and marked as a template. New databases are created from this template.

Benefits:

  • Fast test database creation (~10ms vs ~5s)
  • Consistent schema across tests
  • No need to re-run migrations for each test

Usage: Automatic via test fixtures

Known Issues

Issue 1: No Migration Tracking

Problem: No record of which migrations have run

Workaround: Use container rebuild (docker compose down -v && docker compose up -d)

Future: Migration tracking table

Issue 2: No Rollback Support

Problem: Must manually write reverse migrations

Workaround: Backup before migrations

Future: Auto-generate rollback from migration

Issue 3: All-or-Nothing Loading

Problem: Can't partially apply migrations

Workaround: Use transactions in SQL files

Future: Granular migration control

Planned Refactor

Future Migration System

Goals:

  • Track which migrations have run
  • Support up/down migrations
  • Enable partial migrations
  • Better error handling
  • Production-safe migrations

Proposed structure:

migrations/
├── 001_initial_schema.sql
├── 001_initial_schema_down.sql
├── 002_add_feature.sql
├── 002_add_feature_down.sql
└── migrations.json

Best Practices

✅ Do

  • Wrap migrations in transactions
  • Test migrations before production
  • Backup before migration
  • Document migration purpose
  • Include rollback plan
  • Verify data integrity after
  • Use idempotent operations when possible

❌ Don't

  • Modify existing migration files
  • Skip dependency checks
  • Make breaking changes without plan
  • Forget to run codegen after
  • Apply untested migrations to production
  • Mix schema and data changes

Safe Migration Checklist

  • Migration tested locally
  • Rollback plan documented
  • Backup created
  • Dependencies verified
  • Performance impact assessed
  • Codegen run and tested
  • Migration wrapped in transaction
  • Success verified

Emergency Procedures

If Migration Fails

  1. Stop application: docker compose stop app
  2. Assess damage: Check what was partially applied
  3. Restore from backup: psql -h prod -U user -d db < backup.sql
  4. Fix and retry: Fix migration, test thoroughly, re-apply

If Data Corrupted

  1. Stop writes: Set database to read-only mode if possible
  2. Assess extent: Check affected data
  3. Restore or repair: Restore from backup or write repair script

Quick Reference

Task Command
Reset database docker compose down -v && docker compose up -d
Apply migration psql ... -f db/NNN_migration.sql
Run codegen pnpm db:codegen
Backup database pg_dump -h ... > backup.sql

Next steps: See Migration Examples for complete SQL patterns, or Schema Organization for file organization.