Migrations

Schema migrations for PostgreSQL

JustScale provides a comprehensive migration system for PostgreSQL that can auto-generate migrations from your models, track migration history, and provide rollback capabilities.

Auto-Generation

Generate migrations by comparing your PgModels to the current database schema:

quick-sync.tsTypeScript

import { generateMigration, applyMigration, syncSchema } from '@justscale/postgres';
import { PgUser, PgProduct, PgOrder } from './models';

// Generate migration from model diff
const migration = await generateMigration(client, PgUser, PgProduct, PgOrder);

if (migration.hasChanges) {
  console.log('SQL to apply:', migration.sql);
  console.log('Changes:', migration.changes);

  // Apply the migration
  await applyMigration(client, migration);
}

// Or sync directly (dev mode - no tracking)
await syncSchema(client, PgUser, PgProduct);

The generateMigration function detects differences between your models and the database, then generates the necessary SQL to synchronize them.

Migration Files

For production environments, write migrations as TypeScript files using the Database API:

migrations/20240102_add_posts.tsTypeScript

import { defineMigration } from '@justscale/postgres';
import { field } from '@justscale/models';

export default defineMigration({
  async up({ db }) {
    await db.createTable('posts', {
      id: field.uuid().primaryKey(),
      title: field.string().max(200),
      content: field.text().optional(),
      authorId: field.uuid(),
      publishedAt: field.timestamp().optional(),
      createdAt: field.createdAt(),
    });

    await db.addForeignKey('posts', 'authorId',
      { table: 'users', column: 'id' },
      { onDelete: 'CASCADE' }
    );

    await db.createIndex('posts', ['authorId']);
  },

  async down({ db }) {
    await db.dropTable('posts');
  },
});

Migration Runner

The migration runner executes migration files and tracks which ones have been applied:

run-migrations.tsTypeScript

import { createMigrationRunner } from '@justscale/postgres';

const runner = createMigrationRunner(client, {
  directory: './migrations',
  table: '_migrations',  // Tracking table name
});

// Run all pending migrations
const applied = await runner.migrate();
console.log('Applied:', applied);

// Check migration status
const status = await runner.status();
for (const m of status) {
  console.log(m.applied ? '✓' : '✗', m.name);
}

// Rollback last batch
await runner.rollback();

// Reset and re-run all migrations
await runner.fresh();

Migrations are organized into batches. When you run migrate(), all pending migrations execute in a single batch, which can be rolled back together.

Database API

The Database API provides a clean interface for schema operations in migrations:

Table Operations

table-operations.tsTypeScript

// Create table
await db.createTable('products', {
  id: field.uuid().primaryKey(),
  name: field.string().max(200),
  price: field.decimal(10, 2),
});

// Alter table
await db.alterTable('products', (table) => {
  table.addColumn('sku', field.string().max(50).unique());
  table.dropColumn('legacy_field');
  table.renameColumn('old_name', 'new_name');
  table.setNotNull('required_field');
  table.setNullable('optional_field');
  table.setDefault('status', "'active'");
  table.dropDefault('status');
});

// Drop table
await db.dropTable('products');

// Rename table
await db.renameTable('old_name', 'new_name');

// Check if table exists
if (await db.hasTable('products')) {
  // ...
}

Index Operations

index-operations.tsTypeScript

// Simple index
await db.createIndex('users', 'email');

// Composite index
await db.createIndex('orders', ['customer_id', 'created_at']);

// Unique index
await db.createIndex('users', 'username', { unique: true });

// Partial index with condition
await db.createIndex('users', 'email', {
  where: "status = 'active'",
});

// GIN index for JSONB
await db.createIndex('documents', 'metadata', { using: 'gin' });

// Drop index
await db.dropIndex('idx_users_email');

Foreign Keys

foreign-keys.tsTypeScript

// Add foreign key
await db.addForeignKey('posts', 'author_id',
  { table: 'users', column: 'id' },
  { onDelete: 'CASCADE', onUpdate: 'NO ACTION' }
);

// Drop foreign key
await db.dropForeignKey('posts', 'fk_posts_author_id');

// Available referential actions:
// - CASCADE: Delete/update related rows
// - SET NULL: Set FK to NULL
// - SET DEFAULT: Set FK to default value
// - RESTRICT: Prevent operation
// - NO ACTION: Similar to RESTRICT (default)

Enum Types

enum-operations.tsTypeScript

// Create enum
await db.createType('UserStatus', ['active', 'inactive', 'suspended']);

// Add value to existing enum
await db.addTypeValue('UserStatus', 'banned', {
  after: 'suspended'
});

// Or add at the beginning
await db.addTypeValue('UserStatus', 'pending', {
  before: 'active'
});

// Drop enum (no columns can reference it)
await db.dropType('UserStatus');

// Drop if exists
await db.dropTypeIfExists('UserStatus');

Constraints

constraints.tsTypeScript

// Add unique constraint
await db.addUnique('users', 'email');
await db.addUnique('products', ['vendor_id', 'sku'], 'unique_vendor_sku');

// Drop unique constraint
await db.dropUnique('users', 'users_email_key');

// Add check constraint
await db.addCheck('products', 'price_positive', 'price > 0');

// Drop check constraint
await db.dropCheck('products', 'price_positive');

CLI Integration

Use createMigrationController to add migration commands to your CLI:

terminal.shTypeScript

# Run pending migrations
justscale migrate

# Show migration status
justscale migrate status

# Rollback last batch
justscale migrate rollback

# Generate migration from model changes
justscale migrate make add_avatar_to_users

# Show pending migrations
justscale migrate pending

# Reset and re-run all migrations
justscale migrate fresh

Schema Tracking

The migration runner uses a tracking table (default: _migrations) to record which migrations have been applied:

SQL

CREATE TABLE _migrations (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  batch INTEGER NOT NULL,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Each migration is recorded with its name and batch number. When you roll back, the entire batch is reverted in reverse order.

Data Operations

For seeder migrations, the Database API includes data manipulation methods:

migrations/20240103_seed_admin.tsTypeScript

import { defineMigration } from '@justscale/postgres';

export default defineMigration({
  async up({ db }) {
    // Check if already seeded (idempotent)
    const exists = await db.exists('users', {
      email: 'admin@example.com'
    });

    if (exists) {
      return;
    }

    // Insert admin user
    const admin = await db.insert('users', {
      email: 'admin@example.com',
      name: 'Admin User',
      status: 'active',
    });

    // Insert multiple records
    await db.insertMany('products', [
      { name: 'Product A', price: '19.99' },
      { name: 'Product B', price: '29.99' },
    ]);
  },

  async down({ db }) {
    await db.delete('users', { email: 'admin@example.com' });
  },
});

Available data methods: insert, insertMany,update, delete, exists, find, and findOne.

Best Practices

Always write reversible migrations - Implement both up and down methods so you can rollback changes
Make migrations idempotent - Use IF NOT EXISTS or check before creating/modifying schema
Test migrations locally - Run migrations on a dev database before production
Use transactions - Migrations automatically run in transactions, but be aware that some DDL operations in PostgreSQL cannot be rolled back
Generate from models - Use migrate make to auto-generate migrations when models change
Version control - Commit migration files to git so all team members have the same schema

Raw SQL

For complex operations, you can execute raw SQL in migrations:

raw-sql-migration.tsTypeScript

import { defineMigration } from '@justscale/postgres';

export default defineMigration({
  async up({ db }) {
    // Execute raw SQL
    await db.raw(`
      CREATE OR REPLACE FUNCTION update_updated_at()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = NOW();
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `);

    // Query and get results
    const results = await db.query('SELECT * FROM users WHERE status = $1');
  },

  async down({ db }) {
    await db.raw('DROP FUNCTION IF EXISTS update_updated_at()');
  },
});