Database Migrations

Strav uses a schema-driven migration system that automatically generates database migrations from your schema changes. Migrations are organized by domains and executed in batches with full rollback support.

How Migrations Work

Strav migrations follow a unique approach:

  1. Schema-first: Define your data models using schemas, not raw SQL
  2. Auto-generation: Migrations are automatically generated by comparing schemas with the current database
  3. Domain-scoped: Migrations can be scoped to specific domains (public, tenant, etc.)
  4. Batched execution: Migrations run in numbered batches for precise rollback control
  5. Transactional: Each migration runs in a transaction for atomic operations

CLI Commands

Generate Migration

Generate migrations from schema changes:

# Generate migration for default (public) domain
strav generate:migration --message "Add user authentication"

# Generate migration for specific domain
strav generate:migration --message "Add tenant settings" --scope tenant

# Available aliases
strav migration:generate -m "Migration message"
strav g:migration -m "Migration message"

Options:

  • -m, --message: Descriptive message for the migration
  • -s, --scope: Domain scope (public, tenant, factory, marketing, etc.)

Run Migrations

Execute all pending migrations:

# Run migrations for default domain
strav migrate

# Run migrations for specific domain
strav migrate --scope tenant

# Alternative command
strav migration:run -s public

Example output:

Running pending migrations...

Applied 3 migration(s) in batch 5:
  - 1704067200000
  - 1704153600000
  - 1704240000000

Rollback Migrations

Roll back migrations by batch:

# Rollback last batch
strav rollback

# Rollback specific batch
strav rollback --batch 5

# Rollback specific domain
strav rollback --scope tenant

# Alternative command
strav migration:rollback --batch 3

Example output:

Rolling back batch 5...

Rolled back 3 migration(s) from batch 5:
  - 1704240000000
  - 1704153600000
  - 1704067200000

Fresh Migration

Drop all tables and re-run migrations from scratch:

# Fresh migration for default domain
strav migration:fresh

# Fresh migration for specific domain
strav migration:fresh --scope tenant

Migration File Structure

Each migration creates a versioned directory with organized SQL files:

database/
└── migrations/
    └── public/                    // Domain scope
        └── 1704067200000/         // Timestamp version
            ├── manifest.json      // Migration metadata
            ├── enums/
            │   ├── up.sql        // Create/modify enums
            │   └── down.sql      // Rollback enums
            ├── tables/
            │   ├── users/
            │   │   ├── up.sql    // Create/modify users table
            │   │   └── down.sql  // Rollback users table
            │   └── profiles/
            │       ├── up.sql
            │       └── down.sql
            ├── constraints/
            │   ├── up.sql        // Add foreign keys, unique constraints
            │   └── down.sql      // Remove constraints
            └── indexes/
                ├── up.sql        // Create indexes
                └── down.sql      // Drop indexes

Migration Manifest

Each migration includes a manifest.json file with metadata and execution order:

{
  "version": "1704067200000",
  "message": "Add user authentication",
  "generatedAt": "2024-01-01T12:00:00.000Z",
  "summary": {
    "tablesToCreate": 2,
    "tablesToDrop": 0,
    "tablesToModify": 1,
    "enumsToCreate": 1,
    "enumsToModify": 0,
    "enumsToDrop": 0
  },
  "executionOrder": {
    "up": [
      "enums/up.sql",
      "tables/users/up.sql",
      "tables/profiles/up.sql",
      "constraints/up.sql",
      "indexes/up.sql"
    ],
    "down": [
      "indexes/down.sql",
      "constraints/down.sql",
      "tables/profiles/down.sql",
      "tables/users/down.sql",
      "enums/down.sql"
    ]
  }
}

Multi-Domain Migrations

Strav supports multiple database domains for complex applications:

Domain Purpose Migration Path Tracking Table
public Main application tables database/migrations/public/ _strav_migrations
tenant Multi-tenant specific tables database/migrations/tenant/ _strav_tenant_migrations
factory Data factory/seeding tables database/migrations/factory/ _strav_factory_migrations
marketing Marketing and analytics database/migrations/marketing/ _strav_marketing_migrations

Each domain has its own migration tracking table and can be managed independently.

Workflow Examples

Basic Migration Workflow

// 1. Update your schema
// database/schemas/user.ts
export default defineSchema('user', {
  archetype: Archetype.Entity,
  fields: {
    id: t.ulid().primaryKey(),
    email: t.string().email().unique(),
    password: t.string(),
    role: t.enum(['user', 'admin']).default('user'),
    // Added new field
    emailVerifiedAt: t.timestamptz().nullable(),
  },
})

# 2. Generate migration
strav generate:migration -m "Add email verification"
# ✓ Migration generated: 1704067200000

# 3. Review generated SQL files
cat database/migrations/public/1704067200000/tables/user/up.sql
# ALTER TABLE "user" ADD COLUMN "email_verified_at" TIMESTAMPTZ;

# 4. Run the migration
strav migrate
# ✓ Applied 1 migration(s) in batch 6

Multi-Domain Workflow

# 1. Check available domains
strav generate:migration --scope invalid_domain
# Error: Invalid domain: invalid_domain. Available domains: public, tenant, factory

# 2. Generate tenant-specific migration
strav generate:migration -m "Add tenant settings" -s tenant

# 3. Run tenant migrations
strav migrate -s tenant

# 4. Check migration status
strav migration:status -s tenant

Rollback Scenario

# 1. Something went wrong, rollback last batch
strav rollback
# ✓ Rolled back 2 migration(s) from batch 6

# 2. Fix the issue in your schemas

# 3. Generate new migration
strav generate:migration -m "Fix user table issues"

# 4. Run again
strav migrate
# ✓ Applied 1 migration(s) in batch 7

Migration Tracking

Strav tracks applied migrations in dedicated tracking tables:

-- Example tracking table structure
CREATE TABLE "_strav_migrations" (
  "id" SERIAL PRIMARY KEY,
  "version" VARCHAR(255) NOT NULL,
  "batch" INTEGER NOT NULL,
  "executed_at" TIMESTAMPTZ DEFAULT NOW()
);

The tracking system enables:

  • Batch rollbacks: Roll back entire batches atomically
  • Migration history: Track when and in what order migrations were applied
  • Domain isolation: Separate tracking per domain
  • Conflict prevention: Prevent running the same migration twice

Error Handling

Migration errors are handled gracefully with full transaction rollback:

# If a migration fails mid-batch
strav migrate
# Error: Migration 1704067200000 failed: column "invalid_column" does not exist
# ✓ Transaction rolled back - database unchanged

Common error scenarios:

  • SQL errors: Invalid SQL syntax or constraint violations
  • Schema conflicts: Schema changes conflict with existing data
  • Domain errors: Invalid domain scope specified
  • File errors: Missing migration files or corrupted manifest

Best Practices

Schema Changes

  • Make incremental schema changes rather than large rewrites
  • Test schema changes in development before production
  • Use descriptive migration messages
  • Review generated SQL before running migrations

Production Migrations

  • Always backup your database before running migrations
  • Test rollback procedures in staging environments
  • Consider maintenance windows for destructive changes
  • Monitor migration performance for large tables

Multi-Domain Management

  • Keep domain schemas focused and separate
  • Use consistent naming conventions across domains
  • Document domain purposes and dependencies
  • Coordinate cross-domain changes carefully
← Query Builder Multi-Domain Database →

© 2026 Strav Contributors / All rights reserved.