Migrations

Schema migrations for PostgreSQL

JustScale ships a migration system for PostgreSQL: defineMigration files on disk, a registry populated as a side effect of importing them, and the just migrate CLI that runs and tracks them. Migrations are generated artifacts — author your domain models, then run just migrate make to scaffold a migration that diffs your models against the current database.

Migration Files

A migration is a defineMigration file with up and down handlers that take a typed Database API. You can write them by hand, but the usual flow is to let just migrate make generate one and only edit when the generator can't express what you need.

migrations/20240102_add_posts.tsTypeScript

import { defineMigration } from '@justscale/postgres';
import { field } from '@justscale/core/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');
  },
});

Wiring the Migration Feature

You don't instantiate the runner directly. Add PostgresMigrationFeature to your app and it contributes the just migrate CLI commands plus the runtime that tracks applied batches in a _migrations table.

app.tsTypeScript

import JustScale from '@justscale/core';
import { PostgresClient, PostgresMigrationFeature } from '@justscale/postgres';

const app = JustScale()
  .add(PostgresClient)
  .add(PostgresMigrationFeature)
  .build();

await app.serve({ http: 3000 });

For dev-only commands (just migrate make, fresh, verify), additionally add PostgresMigrationDevFeature from @justscale/postgres/dev.

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 Commands

With PostgresMigrationFeature wired (and PostgresMigrationDevFeature for the dev-only commands), the following commands are available via the cluster socket:

Bash

# Run pending migrations
just migrate

# Show migration status
just migrate status

# Rollback last batch
just migrate rollback

# Show pending migrations
just migrate pending

# Dev-only: generate a migration by diffing models against the DB
just migrate make add_avatar_to_users

# Dev-only: reset and re-run all migrations (destructive)
just 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

Treat migrations as generated artifacts. Author your domain models, run just migrate make, and commit the result. If the generated SQL is wrong, fix the model or the generator and regenerate — don't hand-edit the migration file.
Reversible by default. The generator emits both up and down; keep both in sync if you do edit by hand.
Test against a real database. Run just migrate against a fresh local Postgres before shipping. pglite is fine for unit tests and CLI tooling, but local just dev uses real Postgres.
Watch DDL transactionality. Migrations run in a transaction, but some PostgreSQL DDL (e.g. CREATE INDEX CONCURRENTLY) cannot participate. Split those into their own migration.
Commit the migration files.They're part of the source — every collaborator and every environment derives the same schema by importing them.

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()');
  },
});