Strataline v4.0.3

Strataline is a structured migration system for PostgreSQL that treats database changes as layered, resumable operations, built to scale from small projects to distributed, orchestrated systems.

The name Strataline comes from:

• Strata: representing the layers of a database migration, including schema changes, data backfills, and cleanup steps
• Line: reflecting the path or flow each migration takes, whether inline or across distributed systems

Unlike traditional tools that rely on rigid up/down scripts, Strataline offers a modern framework approach:

• Define safe, phase-based migrations (beforeSchema, migration, afterSchema)
• Use job mode for simple, single-node projects, or scale out with distributed mode when needed
• Integrate directly into your app or scripts, using full TypeScript power and rich logging
• Test easily with built-in helpers, and spin up either temporary test instances or a persistent local Postgres dev server with no Docker required

Whether you're building a side project or orchestrating millions of rows in production, Strataline adapts to your needs, not the other way around.

Table of Contents

• Table of Contents
• Key Features
• Installation
• Basic Usage
  • Job Mode (Single Machine)
  • Distributed Mode (Orchestrated)
• Running Migrations
  • Using the Built-In CLI Helper
    • Basic Setup
    • Configuration Options
    • Environment Variables
    • Available Commands
    • Exit Codes
    • Graceful Shutdown
    • Pool Management
    • package.json Scripts
    • Node.js vs. Bun
  • Creating a Custom Migration Script
• Architecture
  • Job Mode
  • Distributed Mode
• Migration Results
  • Error Handling
  • Exported Types
• Backpressure Handling
• Graceful Shutdown & Cancellation
• Metadata & Checkpoints
• Logging & Schema Helpers
  • Logging
    • Logger Module
      • Creating Custom Loggers
  • Schema Helpers
• Database Tables
  • migration_status
  • migration_lock
    • Lock Lifecycle and Cleanup
• Development and Test Database Instances Utilities
  • Note for Bun Users (Using embedded-postgres)
  • Test DB Instance
    • Features
    • Usage
    • Logging
      • Migration Logging
    • Example in Tests
  • Local Dev DB Server
    • Setting Up a Dev Database Script
    • Configuration Options
    • Logging
    • Data Persistence
    • Process Management
    • Using With Your Application
    • Git Configuration
  • Locale and Collation
• Development

Key Features

• Phased Migration Approach: Each migration is separated into three distinct phases:

  • beforeSchema: Transactional DDL changes before data work
  • migration: Data transformation logic with support for inline or distributed execution
  • afterSchema: Optional final cleanup (e.g., setting NOT NULL, dropping old columns)

Transaction Model: The beforeSchema and afterSchema phases each run inside their own BEGIN/COMMIT transaction and receive a dedicated PoolClient. If the callback throws, that phase is rolled back automatically. The migration (data) phase instead receives the raw Pool and is responsible for managing its own transactions. This is intentional because long, batched, or resumable data work should not run inside a single giant transaction. That would hold locks for the whole run, bloat WAL, and throw away all progress on any failure.

Because you own the transactions, you choose the unit of atomicity. Often that's a batch of rows, but it can also be a logical entity that spans several tables that must change together. For example, on a social network, you might backfill a user along with their profiles and settings rows in one transaction so that user is updated all-or-nothing, then commit and move to the next user or batch. Commit as you go so progress is durable and the migration can resume from a checkpoint (see Metadata & Checkpoints) after an interruption.

The division of labor: the lock gives coarse exclusivity, so normally only one run happens at all. Your transactions give per-unit atomicity. Idempotency covers the fact that an interrupted batch may re-run on the next pass. Strataline can't fence your data writes by the lock because it never sees them, so that idempotency is on you (see the Resuming After a Failure note below).

Resuming After a Failure: Each of the three phases is tracked independently (before_schema_applied, migration_complete, after_schema_applied). When a migration is re-run, every phase that already completed is skipped and only the unfinished phase(s) run. So if afterSchema fails, the next run skips beforeSchema and the data migration (both already done) and retries only afterSchema. A data migration that defer()s or errors before completing will run again on the next pass, so write your data migration to be idempotent. But once it calls complete() and that completion is persisted, it is marked done and is never re-run, even if a later afterSchema then fails. (For example, if the database write that records completion itself fails right after complete(), the run is reported as an error and the data migration runs again on the next pass, which is yet another reason to keep it idempotent.)

• Flexible Execution Modes:

  • job mode: Migrations run inline on a single machine, ideal for development or small projects
  • distributed mode: Your infrastructure orchestrates and routes calls to migration logic, perfect for large-scale systems

• Backpressure Handling: The defer() function allows migrations to pause work and retry later, enabling staged rollouts and preventing system overload

• Library-First Design: Strataline is designed as a flexible library that integrates into your existing infrastructure, not as an opinionated CLI tool

Installation

bun install strataline
# or
npm add strataline
# or
yarn add strataline

Note: pg is a peer dependency, so install it alongside Strataline if it isn't already in your project (bun add pg / npm add pg / yarn add pg). Every example below imports Pool from it.

Basic Usage

Job Mode (Single Machine)

Job mode runs migrations inline on a single machine, ideal for development or small projects:

import { Pool } from "pg";
import { MigrationManager } from "strataline/migration";

// Create a PostgreSQL connection pool
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

// Create a migration manager
const migrationManager = new MigrationManager(pool);

// Register migrations
migrationManager.register([
  {
    id: "001-add-users-table",
    description: "Create users table and add initial indexes",

    // Schema changes before data migration (runs in a transaction)
    beforeSchema: async (client, helpers) => {
      await helpers.createTable(client, "users", {
        id: "SERIAL PRIMARY KEY",
        email: "VARCHAR(255) NOT NULL",
        name: "VARCHAR(255)",
        created_at: "TIMESTAMP WITH TIME ZONE DEFAULT NOW()",
      });

      await helpers.addIndex(
        client,
        "users",
        "users_email_idx",
        ["email"],
        true,
      );
    },

    // Data migration (runs separately)
    migration: async (pool, ctx) => {
      // Check the migration mode
      if (ctx.mode === "job") {
        // In job mode, we process all data unless a specific payload provides a
        // range. `ctx.payload` is always at least `{}` (never undefined), so read
        // the bounds with default values rather than an `|| { ... }` fallback
        // (which would never trigger, leaving startId/endId undefined).
        const { startId = 0, endId = Number.MAX_SAFE_INTEGER } = ctx.payload;

        ctx.logger.info({
          message: `Processing users from ID ${startId} to ${endId}`,
        });

        // Example: Import users from a legacy system
        const { rows } = await pool.query(
          "SELECT * FROM legacy_users WHERE id BETWEEN $1 AND $2",
          [startId, endId],
        );

        for (const user of rows) {
          await pool.query("INSERT INTO users (email, name) VALUES ($1, $2)", [
            user.email,
            user.name,
          ]);
        }

        ctx.logger.info({
          message: `Successfully processed ${rows.length} users`,
        });

        // Mark migration as complete
        ctx.complete();
      } else if (ctx.mode === "distributed") {
        // In distributed mode, we would route/schedule this as a job across multiple workers
        // and monitor when it has successfully been completed
        // If you don't plan to support this, you could provide an error message like below
        ctx.logger.error({
          message: "This migration is not designed to run in distributed mode",
        });

        ctx.defer("Migration not configured for distributed execution");
      }
    },

    // Schema changes after data migration (runs in a transaction)
    afterSchema: async (client, helpers) => {
      // Add constraints that couldn't be added before data was migrated
      await helpers.addColumn(
        client,
        "users",
        "email_verified",
        "BOOLEAN DEFAULT FALSE",
      );
    },
  },
]);

// Run migrations
async function runMigrations() {
  const result = await migrationManager.runSchemaChanges("job");

  if (result.success) {
    console.log("Migrations completed successfully!");

    if (result.migrationData && Object.keys(result.migrationData).length > 0) {
      console.log("Data returned from migrations:", result.migrationData);
    }
  } else {
    console.error("Migration failed:", result.reason);
  }
}

runMigrations().catch(console.error);

Distributed Mode (Orchestrated)

In distributed mode, your infrastructure acts as a router, scheduler, and monitor. The migration system applies schema changes, then your infrastructure is responsible for dividing the data and scheduling jobs for each batch by calling runDataMigrationJobOnly with a payload for each job.

How It Works:

• When distributed Mode Is Active (you called runSchemaChanges('distributed')):

  1. The migration function only orchestrates.
     • Discover the total work to do (row ranges, IDs, etc.).
     • Split that work into payload-sized batches.
     • Schedule each batch as its own job by invoking your queue / worker system (which will in turn call runDataMigrationJobOnly).
  2. Call ctx.defer('batches scheduled') so Strataline pauses, letting your jobs run in parallel.
  3. Once all jobs report success, rerun the runSchemaChanges migration function (still in distributed mode) and call ctx.complete() to let afterSchema and subsequent migrations proceed, officially marking the migration as being complete. The second run will find beforeSchema done, skip it, and jump straight to the data migration function.

• When job Mode Is Active (local run or a worker processing a batch):

  • No Payload Provided → you're on a single machine (dev/CI), so process the entire dataset, then ctx.complete().
  • Payload Provided → you're a worker handling a single batch that the distributed orchestrator created. Process just that slice and call ctx.complete(data) (or ctx.defer(reason, data) to retry later).

Example:

migration: async (pool, ctx) => {
  if (ctx.mode === "distributed") {
    // Orchestrate: discover data, split into batches, schedule jobs (each as a 'job'), monitor, etc.
    const { rows } = await pool.query(
      "SELECT MIN(id) AS min_id, MAX(id) AS max_id FROM legacy_users",
    );

    const minId = rows[0].min_id;
    const maxId = rows[0].max_id;
    const batchSize = 1000;
    const batches = [];

    for (let start = minId; start <= maxId; start += batchSize) {
      batches.push({
        startId: start,
        endId: Math.min(start + batchSize - 1, maxId),
      });
    }

    // Schedule jobs for each batch if not already (pseudo-code, replace with your job system)
    for (const batch of batches) {
      await scheduleJob("001-add-users-table", batch); // e.g., enqueue or trigger a 'job'
    }

    ctx.logger.info({ message: `Scheduled ${batches.length} batch jobs` });

    // Monitor jobs and ensure successful completion (pseudo-code, replace with your own job monitoring logic)
    const allJobsDone = await checkAllJobsComplete(
      "001-add-users-table",
      batches,
    );

    if (!allJobsDone) {
      ctx.defer("Waiting for all jobs to finish");
    } else {
      ctx.complete(); // All jobs finished, allow afterSchema and next migrations
    }
  } else if (ctx.mode === "job") {
    // Heads up: `ctx.mode === "job"` is true in BOTH a single-machine
    // runSchemaChanges("job") run (the orchestrator — here complete() IS
    // authoritative and marks the migration done) AND inside a worker via
    // runDataMigrationJobOnly (where complete() only reports success to your
    // job system and marks nothing). ctx.mode can't tell them apart — the call
    // path does. See "The Orchestrator Owns Migration State. Workers Don't."
    //
    // Do the actual work for this batch (or all data if no payload).
    // `ctx.payload` is always at least `{}`, so default the bounds here.
    const { startId = 0, endId = Number.MAX_SAFE_INTEGER } = ctx.payload;

    ctx.logger.info({
      message: `Processing users from ID ${startId} to ${endId}`,
    });

    // ... process all or the specified range ...
    // Example: return number of processed items
    const processedCount = 150; // Replace with actual count
    ctx.complete({ processed: processedCount });
  }
};

Important:

• In distributed mode, the migration function is for orchestration only: it discovers data, splits into batches, schedules jobs (each as a job), and monitors job completion. It never processes data directly.
• After scheduling jobs in distributed mode, check if all jobs are complete. If not, call ctx.defer() to pause and indicate to retry later. Only call ctx.complete() when all jobs are finished, which allows afterSchema and subsequent migrations to proceed.
• All actual data processing happens in job mode, which can process all data or just a batch (if a payload is provided).
• The migration function should always check ctx.mode and process accordingly:
  • In distributed mode, orchestrate the work and use ctx.defer('reason', data?) to pause, retry, or indicate that jobs were scheduled for background work, potentially passing back relevant data.
  • In job mode, process all data at once, or a specific range if a payload is provided. You can also use ctx.defer(reason, data?) to implement staged rollouts or pause for backpressure.
• If you call ctx.defer(reason, data?), the migration will be paused. afterSchema and any subsequent migrations will not run until you rerun the job and it calls ctx.complete(). This enables staged rollouts, retries, or background processing. The optional data is returned to the immediate caller. From a worker it comes back in DataMigrationJobResult.data (you forward/aggregate it however you like). On the orchestrator pass a defer(reason, data) halts the run, so that data is not surfaced in MigrationResult.migrationData (only data from migrations that complete()d earlier in the same run appears there). Instead the orchestrator persists the deferred data to the metadata column, where you read it back via ctx.metadata on the next run. A worker cannot persist metadata, as described below.
• Strataline is backend-agnostic: you can use any job scheduler, queue system, thread pool, or orchestration framework to schedule and monitor jobs as needed.

The Orchestrator Owns Migration State. Workers Don't. runDataMigrationJobOnly is a thin wrapper that runs your migration function for one batch (pass the batch via payload) and returns the result to you. It writes nothing to migration_status, including migration_complete, metadata, attempts, last_error, or anything else, and it never touches the migration lock. There is no acquire, renew, or release because your job system controls worker concurrency. So when a worker calls ctx.complete(), that just tells your job system its batch succeeded. It does not mark the whole migration done. Only the orchestrator pass (runSchemaChanges) writes migration state. It marks migration_complete and persists metadata once it confirms all jobs finished. This is deliberate: it prevents the footgun where one finished batch would prematurely flip the whole migration complete and let afterSchema run early. (A worker can still read ctx.metadata as read-only data, but for a worker the payload you pass in is usually the better way to hand it its slice/checkpoint, since you control it directly per call.)

Running Migrations

Strataline provides flexible options for running database migrations. Since it's designed as a library rather than a CLI tool, you have complete control over how migrations are executed. You can either use our convenient built-in CLI helper to get started quickly or create a custom migration script for more advanced scenarios.

Using the Built-In CLI Helper

For quick development or simpler use cases, Strataline provides a convenient CLI helper function called RunStratalineCLI. This function handles command parsing and execution for you with minimal setup.

Basic Setup

Create a script file to run your migrations:

// scripts/db-migrate.ts

// Load environment variables - this is only needed if you are using Node.js, Bun does not need it
// import 'dotenv/config'

import { RunStratalineCLI, createCLIConsoleLogger } from "strataline/cli";
import { migrations } from "../path/to/your/migrations";

// Use the built-in CLI console logger. The single argument is `migrateVerbose`
// (default `true`): it gates only the verbose per-migration `[MIGRATE-INFO]`
// lines. Migration errors/warnings and the CLI's own info always print, so
// passing `false` quiets the chatter without hiding problems. You can customize
// this or implement your own logger if needed.
const logger = createCLIConsoleLogger(true);

// Run the CLI with environment variables.
// RunStratalineCLI resolves with a result whose `exitCode` distinguishes the
// outcome (0 completed · 2 deferred · 3 locked · 4 aborted · 5 lock_lost); a
// genuine error is thrown, so the `.catch` maps that to exit 1.
RunStratalineCLI({
  migrations,
  loadFrom: "env", // Use environment variables for database connection
  logger,
})
  .then((result) => {
    process.exit(result.exitCode);
  })
  .catch((error) => {
    console.error(`Failed to run CLI: ${error.message}`);
    process.exit(1);
  });

Configuration Options

The RunStratalineCLI function accepts several configuration options:

• migrations: An array of your migration objects
• loadFrom: How to load the database connection
  • "env": Use environment variables (requires PostgreSQL environment variables)
  • "pool": Use a provided PostgreSQL pool
• envPrefix (optional): Prefix for environment variables (e.g., "APP_" would look for APP_POSTGRES_USER, "API_" would look for API_POSTGRES_USER)
• pool (optional): A PostgreSQL pool instance (required when loadFrom is "pool")
• logger: A function to handle logging
• signal (optional): An AbortSignal for graceful shutdown. The library never traps OS signals itself, so wire this to your own SIGTERM/SIGINT handling. When it aborts, an in-flight run stops at the next safe point and resolves with status: "aborted" (exit code 4). See Graceful Shutdown.
• argv (optional): An array to use instead of process.argv for command parsing (the command is read from index 2, and --distributed is detected anywhere in the array). Useful for tests or when embedding the CLI.
• env (optional): An environment object to use instead of process.env when loadFrom: "env". Useful for tests or when embedding the CLI.

Validation Errors (Thrown): The option combinations are mutually exclusive and validated up front:

• providing pool together with loadFrom: "env" throws (Cannot provide both pool and loadFrom='env'),
• loadFrom: "pool" without a pool throws (Must provide pool when loadFrom='pool'),
• providing envPrefix together with loadFrom: "pool" throws (Cannot provide envPrefix when loadFrom='pool').

Missing required env vars and an invalid POSTGRES_PORT (or invalid optional numeric vars) also throw, so wrap the call in a .catch (which maps to exit code 1).

Environment Variables

When using loadFrom: "env", the following environment variables are required:

• POSTGRES_USER: Database username
• POSTGRES_HOST: Database host
• POSTGRES_DATABASE: Database name
• POSTGRES_PASSWORD: Database password
• POSTGRES_PORT: Database port

Optional environment variables for pool configuration:

• POSTGRES_MAX_CONNECTIONS: Maximum number of connections in the pool (default: 20)
• POSTGRES_IDLE_TIMEOUT: Idle timeout in milliseconds (default: 30000)
• POSTGRES_CONNECTION_TIMEOUT: Connection timeout in milliseconds (default: 2000)

A ready-to-copy .env.example is included. To get started:

cp .env.example .env
# then edit .env with your database credentials

Bun loads .env automatically. On Node.js, add import 'dotenv/config' to your entrypoint (see scripts/db-migrate.ts). If you pass an envPrefix (e.g. "API_"), prefix every variable accordingly (API_POSTGRES_USER, ...).

Available Commands

The CLI supports the following commands:

• run: Run pending migrations
  • Option: --distributed to run in distributed mode
• status: Show migration status
• help: Display help information (default if no command is provided)

Note: Any unrecognized command falls through to help (printed, exit code 0). An unknown command is not treated as an error, and the returned result.command is normalized to "help" (not the raw unknown string), matching the run | status | help set the type documents. The --distributed flag and the signal option only affect run, and they are ignored by status and help.

Note: Every command, including help, first resolves the database configuration and tests the connection before it runs. With loadFrom: "env" that means missing/invalid env vars throw, and an unreachable database aborts, before any command output. So even help requires a working connection in env mode. If you just want the help text without a database, print it yourself rather than relying on the CLI.

Exit Codes

RunStratalineCLI resolves with a StratalineCLIResult that includes a suggested exitCode, so a wrapper script can distinguish outcomes. A genuine error is thrown (not returned), so callers that only .catch() still exit non-zero.

| Outcome | exitCode | Behavior | | ----------- | ---------- | --------------------------------------------------- | | completed | 0 | Returned | | error | 1 | Thrown (caller's .catch maps to 1) | | deferred | 2 | Returned because a migration paused itself | | locked | 3 | Returned because another process holds the lock | | aborted | 4 | Returned because graceful shutdown was requested | | lock_lost | 5 | Returned because the lock was lost mid-run (unsafe) |

Note locked (code 3) and lock_lost (code 5) are deliberately distinct: locked means another process already holds the lock so this run did nothing (benign), whereas lock_lost means this run held the lock and lost it partway through, a possible concurrent-run condition worth investigating.

RunStratalineCLI({ migrations, loadFrom: "env", logger })
  .then((result) => process.exit(result.exitCode))
  .catch((error) => {
    console.error(error.message);
    process.exit(1);
  });

The codes are also exported as STRATALINE_EXIT_CODES.

The full result shape (exported as StratalineCLIResult) is:

interface StratalineCLIResult {
  command: string; // The command that ran: "run", "status", or "help".
  status?: MigrationResult["status"]; // Only populated for "run". Undefined for "status"/"help".
  exitCode: number; // Suggested process exit code for this outcome.
  reason?: string; // Only populated for "run", when it did not simply complete.
}

status and reason are populated only for the run command. The status/help commands resolve with just { command, exitCode: 0 }, so result.status and result.reason are undefined for them. Branch on result.command (or check for undefined) before reading status.

The CLI logger type is exported as CLILoggerFunction (the signature of the logger you pass to RunStratalineCLI, and what createCLIConsoleLogger returns).

Graceful Shutdown

The CLI does not trap OS signals itself (so it won't interfere with however your app handles them). Instead, pass an AbortSignal and wire it to your own handler. The CLI forwards it down to the migration run:

const controller = new AbortController();
process.once("SIGTERM", () => controller.abort());
process.once("SIGINT", () => controller.abort());

const result = await RunStratalineCLI({
  migrations,
  loadFrom: "env",
  logger,
  signal: controller.signal,
});

When the signal aborts, an in-flight run stops at the next safe point and resolves with status: "aborted" (exit code 4). See Graceful Shutdown & Cancellation for how migrations observe the signal via ctx.signal.

Pool Management

Note: The CLI automatically manages the PostgreSQL pool lifecycle. It will create a pool if using environment variables or use your provided pool, and will properly end the pool when the operation completes. You do not need to end the pool yourself after calling RunStratalineCLI.

Heads up, it ends a pool you passed too. When loadFrom: "pool", RunStratalineCLI calls pool.end() in a finally block on the pool you supplied, not just on pools it created. The pool is dead after the call resolves, so don't plan to reuse it afterward. Create a dedicated pool for the CLI, or let it create one via loadFrom: "env".

The one exception is a synchronous configuration error, e.g. passing envPrefix together with loadFrom: "pool", which throws Cannot provide envPrefix when loadFrom='pool'. These checks run before the CLI adopts your pool, so on such a throw the pool is left open (it was never touched, and you still own the reference). That's deliberate: you can fix the config and retry with the same pool. Once the CLI gets past validation, the finally owns it and will end it.

package.json Scripts

Add these scripts to your package.json for convenient access:

{
  "scripts": {
    "db:migrate": "bun run scripts/db-migrate.ts run",
    "db:migrate:distributed": "bun run scripts/db-migrate.ts run --distributed",
    "db:status": "bun run scripts/db-migrate.ts status"
  }
}

Node.js vs. Bun

The example above works with both Node.js and Bun, with one difference:

• Bun: Environment variables are automatically loaded from .env files
• Node.js: You need to add import 'dotenv/config' to load environment variables from .env files

Creating a Custom Migration Script

For more control over the migration process, you can create your own custom migration script. This approach gives you complete flexibility in how migrations are executed, logged, and managed.

The recommended structure is one migration per file, a single index.ts that re-exports them as an ordered array, and a runner script that imports that array and drives the run. Here's the runner:

// migrate.ts
import { Pool } from "pg";
import { MigrationManager } from "strataline/migration";

// Import the ordered migrations array (defined in ./migrations/index.ts, shown below)
import { migrations } from "./migrations";

async function main() {
  // Parse command line arguments
  const args = process.argv.slice(2);
  const mode = args.includes("--distributed") ? "distributed" : "job";
  const verbose = args.includes("--verbose");

  // Create database connection
  const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
  });

  // Set up migration manager
  const migrationManager = new MigrationManager(pool);

  try {
    // Register the migrations (they run in array order)
    migrationManager.register(migrations);

    // Run migrations
    console.log(`Running migrations in ${mode} mode...`);
    const result = await migrationManager.runSchemaChanges(mode);

    if (result.success) {
      console.log("✅ Migrations completed successfully!");

      // Always show migrations applied in this run (even if none)
      console.log(
        `Applied during this run: ${result.completedMigrations.join(", ") || "none"}`,
      );

      // Always show migrations that were already applied in previous runs (even if none).
      // previouslyAppliedMigrations is always present (never undefined), so no `?.` is needed.
      console.log(
        `Previously applied: ${result.previouslyAppliedMigrations.join(", ") || "none"}`,
      );

      // Always show pending migrations (even if none)
      console.log(
        `Pending migrations: ${result.pendingMigrations.join(", ") || "none"}`,
      );

      if (
        verbose &&
        result.migrationData &&
        Object.keys(result.migrationData).length > 0
      ) {
        console.log(
          "Migration data:",
          JSON.stringify(result.migrationData, null, 2),
        );
      }
    } else {
      // `success` is false for several distinct outcomes, and NOT all of them
      // are failures. `locked` (another process holds the lock), `deferred` (a
      // migration paused itself), and `aborted` (graceful shutdown) are benign;
      // only `error` and `lock_lost` are real problems. Branch on `result.status`
      // rather than treating every `!success` as an error so you don't exit 1 on
      // a benign skip. (This is exactly what the built-in CLI does — see the
      // Exit Codes table; reuse `STRATALINE_EXIT_CODES` if you want the same map.)
      switch (result.status) {
        case "deferred":
          console.log(`⏸ Migration run paused (deferred): ${result.reason}`);
          break;
        case "locked":
          console.log(
            `⏭ Skipped — another process is running: ${result.reason}`,
          );
          break;
        case "aborted":
          console.log(`⏹ Migration run aborted: ${result.reason}`);
          break;
        case "lock_lost":
          console.error(`✗ Migration lock lost mid-run: ${result.reason}`);
          break;
        default:
          console.error(`❌ Migration failed: ${result.reason}`);
          break;
      }

      console.log(
        `Completed migrations in this run: ${result.completedMigrations.join(", ") || "none"}`,
      );

      if (result.previouslyAppliedMigrations.length > 0) {
        console.log(
          `Previously applied migrations: ${result.previouslyAppliedMigrations.join(", ")}`,
        );
      }

      console.log(
        `Pending migrations: ${result.pendingMigrations.join(", ") || "none"}`,
      );

      if (result.lastAttemptedMigration) {
        console.log(
          `Last attempted migration: ${result.lastAttemptedMigration}`,
        );
      }

      // Only `error` and `lock_lost` are non-zero failures here; benign
      // outcomes exit 0. Map to whatever exit codes suit your tooling.
      const failed = result.status === "error" || result.status === "lock_lost";
      process.exit(failed ? 1 : 0);
    }
  } catch (error) {
    console.error("Error running migrations:", error);
    process.exit(1);
  } finally {
    await pool.end();
  }
}

main().catch(console.error);

success: false is not always a failure. runSchemaChanges returns success: false for locked, deferred, and aborted too, all benign outcomes, alongside the genuine error and lock_lost. Branch on result.status (as above) when you need to tell them apart. The built-in CLI already does this and maps each status to a distinct exit code, so prefer it if you don't want to hand-roll the distinction.

The migrations array imported above comes from one migration per file, each a typed Migration, collected by a single index file.

Define each migration in its own file, typed with Migration so the object's shape is checked as you write it:

// migrations/001-add-users-table.ts
import type { Migration } from "strataline/migration";

export const migration001: Migration = {
  id: "001-add-users-table",
  description: "Create users table",
  beforeSchema: async (client, helpers) => {
    await helpers.createTable(client, "users", {
      id: "SERIAL PRIMARY KEY",
      email: "VARCHAR(255) NOT NULL",
    });
  },
  // migration / afterSchema as needed...
};

Then aggregate them in an index file, typing the array as Migration[]. This validates every migration at the point you collect them and gives register() a fully typed array:

// migrations/index.ts
import type { Migration } from "strataline/migration";
import { migration001 } from "./001-add-users-table";
import { migration002 } from "./002-add-posts-table";
// ... import additional migrations

// Export migrations in the order they should run
export const migrations: Migration[] = [
  migration001,
  migration002,
  // ... add additional migrations in order
];

register() replaces, and rejects duplicates. Each call to register() replaces any previously registered list (it does not append), and it throws if two migrations share the same id, so accidental duplicates fail fast at registration rather than silently running twice.

You can then add scripts to your package.json:

{
  "scripts": {
    "migrate": "bun run migrate.ts",
    "migrate:distributed": "bun run migrate.ts --distributed"
  }
}

This allows you to run migrations using:

# Run migrations in job mode
npm run migrate

# Run migrations in distributed mode
npm run migrate:distributed

# Run with verbose output
npm run migrate -- --verbose

You can also use ts-node instead of Bun if you prefer: just replace bun run with ts-node in your package.json scripts.

This approach ensures your migrations run in the exact order you specify, rather than relying on filesystem ordering.

Architecture

Strataline is designed with flexibility in mind, allowing you to choose the execution model that best fits your needs:

Job Mode

In job mode, migrations run inline on a single machine. This is useful for:

• Development environments
• Small projects with manageable data volumes
• CI/CD pipelines where migrations run before deployment

The job mode runs all migrations in sequence on a single machine, with each migration handling all of its data processing in one go.

Distributed Mode

In distributed mode, your infrastructure acts as a router/scheduler/monitor, while the actual work is done by calling runDataMigrationJobOnly as jobs. This is ideal for:

• Large-scale production systems
• Migrations that process millions of records
• Systems where you need fine-grained control over resource usage
• Environments where you want to limit the blast radius of migrations

The distributed mode works like this:

1. You run runSchemaChanges('distributed') to apply schema changes. This is the orchestrator pass. Its result's migrationData holds the data from any migrations that finished via ctx.complete(data) during this run. A migration that calls ctx.defer(reason, data) instead stops the run, so its data is not in migrationData. Because this is the orchestrator pass, that data is saved to the metadata column (read it back via ctx.metadata on the next run). (Standalone runDataMigrationJobOnly workers are different: they never persist metadata. They return their data in DataMigrationJobResult.data. See the orchestrator/worker note above.)
2. Your infrastructure determines how to split the data (e.g., by user ID ranges).
3. For each batch, you call runDataMigrationJobOnly(migrationId, payload). This function executes the migration part of your defined migration for that specific migrationId and payload.
   • If the migration calls ctx.complete(data) or ctx.defer(reason, data), the data provided there will be returned in the data field of the DataMigrationJobResult.
   • The overall result will be an object like { status: 'success', reason?: string, data?: TReturn }.
4. Each batch runs as a separate job, processing just its portion of the data. The data returned in the DataMigrationJobResult can be used for logging, monitoring, or further orchestration.
5. Your infrastructure handles scheduling, retries, and monitoring based on the status, reason, and data from runDataMigrationJobOnly.

runDataMigrationJobOnly status values: the returned status is one of:

• success: the data function ran and called ctx.complete() (or the migration has no migration function, so there was nothing to run). Note a worker never marks migration_complete. Only the orchestrator pass does.
• deferred: the data function called ctx.defer() (retry this batch later).
• error: the data function threw or finished without calling complete()/defer().
• already_complete: the data phase was already marked complete, so there is nothing to do.
• not_found: no migration is registered with that ID.
• invalid_state: the migration isn't ready for a data-only run (e.g. beforeSchema hasn't been applied yet, or afterSchema is applied while the data phase isn't complete).

Only success/deferred/error can carry data.

This approach allows you to:

• Process data in parallel across multiple machines
• Limit the impact of any single migration job
• Implement sophisticated retry and monitoring logic
• Handle backpressure and staged rollouts

mode is just a hint. Distribution is opt-in. runSchemaChanges always runs the data migration inline. "distributed" only changes anything if your function branches on ctx.mode to schedule jobs and defer(). A migration that ignores ctx.mode (just processes data and complete()s) run in distributed mode simply does all the work inline, identical to a single job. It degrades gracefully, no error. So you can adopt distributed mode per-migration, as you need it.

Migration Results

The runSchemaChanges() method returns a MigrationResult object that provides detailed information about the migration run:

interface MigrationResult {
  success: boolean; // Whether all migrations completed successfully
  // "locked"    = couldn't acquire the lock; another process holds it (benign).
  // "lock_lost" = held the lock but lost it mid-run (unsafe; run was aborted).
  // "aborted"   = stopped early via a caller-supplied AbortSignal (graceful).
  status:
    | "completed"
    | "locked"
    | "lock_lost"
    | "error"
    | "deferred"
    | "aborted";
  reason?: string; // User-friendly error/deferral message
  completedMigrations: string[]; // IDs of migrations completed in this run
  previouslyAppliedMigrations: string[]; // IDs *fully* applied in previous runs (every phase done). A migration that only partially applied before (e.g. a phase failed/was interrupted) is NOT counted here — it appears in pendingMigrations instead, so the two lists never overlap.
  pendingMigrations: string[]; // IDs of migrations still pending (including partially-applied ones resuming)
  lastAttemptedMigration?: string; // ID of the last migration attempted
  error?: Error; // Raw error object for debugging (unhandled exceptions only)
  migrationData: Record<string, unknown>; // Data returned by successful migrations. Always present — an empty object when no migration returned data.
}

Error Handling

Strataline provides two levels of error information:

• reason: Always a formatted string message suitable for display in logs/CLI output
• error: Raw Error object with stack trace, only present for unhandled exceptions (not for controlled migration phase errors)

The built-in CLI shows both the reason (always) and error details with stack trace (when available) to help with debugging.

Exported Types

Alongside MigrationResult, MigrationStatus, DataMigrationJobResult, and DataMigrationJobStatus, a few smaller types are exported from strataline/migration for when you want to annotate things explicitly (e.g. a wrapper function or a variable that holds the mode):

• MigrationMode ("job" | "distributed"): the value you pass to runSchemaChanges/runDataMigrationJobOnly and read back as ctx.mode.
• MigrationCompletionCallback<TReturn> / MigrationDeferCallback<TReturn>: the types of ctx.complete and ctx.defer. Usually inferred for you when you write the inline migration callback, and exported for the rare case you annotate them by hand.

Migration is generic: Migration<TPayload, TReturn>. The examples use the bare Migration (which defaults to Migration<Record<string, unknown>, unknown>), but you can parameterize it to type the two values that flow through a single migration: TPayload types ctx.payload (the per-batch input you pass to runDataMigrationJobOnly), and TReturn types the data argument to ctx.complete(data) / ctx.defer(reason, data) (and ctx.updateMetadata). For example, Migration<{ startId: number; endId: number }, { processed: number }> gives you a fully typed ctx.payload and a checked ctx.complete({ processed }). register<TPayload, TReturn>() and runDataMigrationJobOnly<TPayload, TReturn>() carry the same parameters.

The logger the MigrationManager constructor accepts is the Logger interface. If Logger collides with a type already in your codebase, alias it on import: import type { Logger as StratalineLogger } from "strataline/migration".

Backpressure Handling

Inside a migration you can call ctx.defer(reason?: string, data?: TReturn) to pause work and retry later. This is useful for:

• Handling backpressure when the system is under load
• Implementing staged rollouts of data changes
• Pausing when rate limits are reached
• Recovering from temporary failures
• Spawning background tasks in distributed mode and exiting to check status later, potentially returning data like a task ID or checkpoint.

When a migration calls defer(reason, data?), the current execution stops and should be retried later by your orchestration system. The data returned by defer (if provided) will be available in the DataMigrationJobResult when using runDataMigrationJobOnly. This is particularly powerful in distributed mode, where you might:

1. Spawn Background Tasks: Start a long-running process and defer the migration to check its status later
2. Implement Circuit Breakers: Detect system load and defer processing during peak times
3. Create Staged Rollouts: Process data in waves, deferring between each wave to monitor system health
4. Handle External Dependencies: Defer when dependent systems are unavailable or rate-limited

The defer() function accepts an optional reason parameter and an optional data parameter. The reason provides context for why the migration was paused, useful for monitoring and debugging. The data allows for returning structured information to the calling orchestrator.

Call exactly one, exactly once. A data migration must call either ctx.complete() or ctx.defer() per run, not both, and not the same one twice. A second call (or calling both) throws complete() or defer() already called. Finishing the function without calling either is also an error (Migration function finished without calling complete() or defer()), so always end on one of them. See Graceful Shutdown & Cancellation.

Graceful Shutdown & Cancellation

Strataline supports cooperative cancellation so a long-running migration can wind down cleanly when your process is asked to stop (e.g. a Kubernetes rolling deploy sends SIGTERM). The library never installs OS signal handlers itself, so you own your signals and pass in an AbortSignal. This works the same whether you drive MigrationManager directly or go through the CLI wrapper.

Using MigrationManager directly: pass a signal to runSchemaChanges, and have your data migration observe ctx.signal:

const controller = new AbortController();
process.once("SIGTERM", () => controller.abort());

const manager = new MigrationManager(pool);
manager.register([
  {
    id: "001-backfill",
    description: "Backfill in batches, stopping on shutdown",
    migration: async (pool, ctx) => {
      for (const batch of batches) {
        // Cooperatively stop at a safe point when shutdown is requested.
        if (ctx.signal.aborted) {
          ctx.defer("shutdown requested — will resume on next run");
          return;
        }
        await processBatch(pool, batch);
      }
      ctx.complete();
    },
  },
]);

const result = await manager.runSchemaChanges("job", {
  signal: controller.signal,
});
// result.status === "aborted" if the run was stopped via the signal

Key points:

• ctx.signal is always present. If you don't pass a signal, it's an AbortSignal that never aborts, so ctx.signal.aborted is safe to check unconditionally.
• Cancellation is cooperative. Strataline can't forcibly kill your in-flight code. Your migration must check ctx.signal.aborted (or listen for its "abort" event) and stop gracefully. When you see the abort, stop at a safe point and call ctx.defer("reason") (or ctx.complete() if the work genuinely finished). Do not just return without calling one of them: a data migration that finishes without calling complete() or defer() is treated as an error. Because migrations are resumable, after a defer() the next run picks up where it left off. (The overall run result is still "aborted" regardless, but defer() keeps the migration's recorded state clean.)
• Between migrations, the run also stops at the next migration boundary when the signal is aborted, returning status: "aborted".
• No run status is persisted. It is just feedback in the returned result. migration_status has no "status" column, only per-phase progress flags (plus last_error/metadata). So whether a run ends "deferred" or "aborted", the affected migration identically stays pending (phase flags incomplete) and resumes on the next run. (When a migration defer()s, the reason/data you passed do get persisted to last_error/metadata, but that's because you passed them, separate from the run's status.)
• Workers are cancelled the same way, with one return-value asymmetry: runDataMigrationJobOnly(id, payload, { signal }) exposes ctx.signal, but "aborted" is only ever a runSchemaChanges result. A cancelled worker should ctx.defer(), so its DataMigrationJobResult comes back as "deferred" (retry this batch later), never "aborted". Lock loss doesn't apply to workers either because runDataMigrationJobOnly never acquires the lock or runs the renewal timer. Only runSchemaChanges does.

Lock loss is treated as a safety abort. While a run is in progress the lock is renewed on a timer (see Lock Lifecycle and Cleanup). If a renewal discovers the lock is no longer ours because another process took it over after it expired, Strataline auto-aborts the in-flight run rather than continuing without exclusivity. Concretely, it trips the same abort path used for shutdown (so ctx.signal fires and your data migration can wind down), then runSchemaChanges returns status: "lock_lost" with a [lock] reason. This is a dedicated status, distinct from both "aborted" (a graceful shutdown) and "locked" (couldn't acquire in the first place), and through the CLI it exits with code 5, because running without a valid lock is an unsafe condition worth investigating.

From your data migration's point of view there's nothing new to handle: lock loss fires the same ctx.signal as a shutdown, so respond the same way. Stop at a safe point, call ctx.defer("reason"), and return. On lock loss specifically it barely matters what you call: Strataline already blocks afterSchema, fences your ctx.complete()/ctx.updateMetadata() writes (they no-op / return false, see Lock Lifecycle and Cleanup), and reports lock_lost regardless, so the migration stays pending for the new owner no matter what you do. The reason to reach for defer() is really the shutdown case, where you do still hold the lock and a stray ctx.complete() would wrongly mark not-yet-finished work as done. Treating the signal uniformly as "stop and defer()" is correct in both cases, so you never have to tell them apart. It's also the polite, clean way to exit the function, since a data migration that returns without calling complete() or defer() is treated as an error.

Loss is detected three ways, all converging on the same lock_lost abort:

1. Confirmed at renewal: a renewal that succeeds but finds the lock row now belongs to someone else. Triggers immediately.

2. Lease lapse during renewal failures: a transient renewal failure (the renewal query itself throwing, e.g. a brief DB hiccup) is logged and retried on the next tick. A momentary blip shouldn't kill a run that likely still holds the lock. But if renewals keep failing until the lease window (lockExpirySeconds) has lapsed, Strataline can no longer assume it holds the lock and treats it as a loss. Otherwise repeated renewal exceptions could never trip the confirmed path and the run would keep working past expiry while another process potentially takes over.

3. At write time (synchronous fence): every status write Strataline makes (the initial status-row insert, the per-attempt attempts/started_at/last_error bookkeeping, migration_complete, completed_at, the phase-applied flags, and metadata) is gated on still holding the lock: the statement carries an AND EXISTS (… migration_lock WHERE locked_by = <our id> AND lock_expires_at > <now>) clause, so once the lock has been taken over or our own lease has expired the write touches zero rows instead of racing the rightful owner. (Checking lock_expires_at matters because once the lease lapses another runner may take over at any instant, even before it actually has.) A state-advancing write that comes back empty is itself treated as a loss, closing the gap between the lease lapsing and the next renewal tick noticing. This is what stops a "zombie" run (one that ignored ctx.signal and called complete() anyway) from flipping migration_complete and misleading the new owner into skipping the data phase. For the schema phases the fenced flag write shares the phase's transaction, so a fenced-out write rolls the whole phase back, DDL included, meaning a schema change can't commit without the lock either.

   Note: the fence covers Strataline's own bookkeeping (and, transitively, the transactional DDL in a schema phase). It can not fence the arbitrary SQL your data migration runs on the pool. That's the one thing left unguarded, so the idempotency contract still stands. (There's also a small unavoidable window between a write's lock check passing and its COMMIT. The fence shrinks the exposure to that, it doesn't make a time-lease into a hard mutex.)

Via the CLI, pass the same signal as RunStratalineCLI({ ..., signal }). It forwards it to the run and maps "aborted" to exit code 4. See Graceful Shutdown under the CLI helper.

Metadata & Checkpoints

Each migration row has a freeform metadata JSONB column the migration can write to and read back. Unlike the transient data returned from a run, metadata is persisted and not cleared between attempts, so it's the right place for checkpoints, progress, or any state you want to carry across runs (e.g. { remaining: 120, jobId: "abc" }).

Three ways to interact with it from ctx:

• Read: ctx.metadata is a read-only snapshot of whatever was last persisted (or null), loaded fresh at the start of every run/attempt (orchestrator pass and worker call), not just the first. Use it to resume from a checkpoint. (To pass batch parameters into a worker, use payload instead because ctx.metadata is for persisted cross-run state.)
• Write on pause/finish: the data you pass to ctx.complete(data) / ctx.defer(reason, data) is persisted to metadata. (Passing no data leaves the existing value untouched. It is never auto-cleared.)
• Write mid-run: await ctx.updateMetadata(value) persists progress while the migration is still running, so an external observer can watch it via the status table. It resolves to a boolean (true if persisted, false on a worker no-op or a failed write) and is non-fatal: a failed progress write is logged and returns false rather than throwing, so it can't turn an otherwise healthy migration into an error.

Both writes happen only on the orchestrator pass (runSchemaChanges, job or distributed). On a runDataMigrationJobOnly worker they are no-ops. Only the read (ctx.metadata) works everywhere. See Ownership below.

migration: async (pool, ctx) => {
  // This is the single-machine / inline shape. To support distributed mode too,
  // branch on ctx.mode: orchestrate & schedule jobs when "distributed", process
  // (all data, or a slice from ctx.payload) when "job".

  // Resume from where a previous run left off.
  let cursor = (ctx.metadata as { cursor?: number } | null)?.cursor ?? 0;

  while (cursor < total) {
    if (ctx.signal.aborted) {
      ctx.defer("shutdown", { cursor }); // persist checkpoint, resume next run
      return;
    }
    cursor = await processBatch(pool, cursor);
    await ctx.updateMetadata({ cursor }); // live progress, visible in `status`
  }

  ctx.complete({ cursor }); // final state persisted to metadata
},

Ownership: what matters is the call path, not the mode. Any runSchemaChanges call is the orchestrator and writes metadata (and migration_complete). This includes single-machine job mode (runSchemaChanges("job")), not just distributed. Only runDataMigrationJobOnly (a worker) has no-op writes, though it can still read ctx.metadata. Watch out: ctx.mode === "job" is true in both a single-machine runSchemaChanges("job") run and inside a worker (which forces mode: "job"), so ctx.mode alone doesn't tell you whether your writes persist. The call path does. See the distributed-mode note below for why.

| You called | ctx.mode | complete() marks done? | metadata persists? | | --------------------------------- | ---------------- | ------------------------ | -------------------- | | runSchemaChanges("job") | "job" | Yes | Yes | | runSchemaChanges("distributed") | "distributed" | Yes | Yes | | runDataMigrationJobOnly(...) | "job" (forced) | No | No |

The first and third rows both run with ctx.mode === "job" yet differ on persistence, proof that the call path (which method you invoked), not ctx.mode, decides ownership. A worker just returns its value in DataMigrationJobResult.data. Your orchestrator pass decides whether to save it.

Logging & Schema Helpers

Strataline provides robust logging and schema helper utilities to make migrations safer and more traceable:

Logging

• All migration phases and helpers use a Logger interface for structured logs and errors.
• By default, logs are sent to the console, but you can provide your own logger by passing it to the MigrationManager.
• The migration system automatically adds contextual information to logs:
  • The migration ID is used as the task field
  • The current phase (beforeSchema, dataMigration, afterSchema) is used as the stage field, both on the ctx.logger passed to your data migration and on the helpers passed to your schema phases
  • This provides built-in traceability without manual configuration

Example:

migration: async (pool, ctx) => {
  // The logger already has the migration ID as the task
  ctx.logger.info({ message: "Starting migration batch" });
  // ...
  ctx.logger.error({ message: "Something went wrong", error: err });
  // Output includes: [migration-id] [dataMigration] Something went wrong
};

Logger Module

Strataline includes a dedicated logger module that provides:

• A generic Logger interface that can be implemented for different logging backends
• A class-based implementation with:
  • BaseLogger: An abstract base class that implements the Logger interface
  • ConsoleLogger: A concrete implementation that logs to the console
• A default consoleLogger instance for immediate use
• Structured logging with support for error objects and contextual information

The logger system automatically formats messages with task and stage prefixes, making it easy to trace the origin of each log message in complex migration scenarios.

A few lower-level building blocks are also exported from strataline/migration for advanced use: the LogData / LogLevel / LogDataInput types, the buildLogPrefix and getErrorMessage helpers, a MutableLogger (wraps another logger and can be toggled on/off via setVerbose, with isVerbose() to read the current state, handy in tests), createPrefixedLogger (a standalone function that creates a prefixed logger. Handles both BaseLogger subclasses and plain Logger objects), and PrefixedLogger (the internal class behind createPrefixed/createPrefixedLogger. Prefer those over constructing it directly). Most users only need BaseLogger, ConsoleLogger, and consoleLogger.

Creating Custom Loggers

You can create your own logger by extending the BaseLogger class:

import { BaseLogger, LogDataInput } from "strataline/migration";

// Create a custom logger that sends logs to a service
class ApiLogger extends BaseLogger {
  info(data: LogDataInput): void {
    // Send log to your logging service
    apiClient.sendLog({
      level: "info",
      message: data.message,
      context: {
        task: data.task,
        stage: data.stage,
      },
    });
  }

  error(data: LogDataInput): void {
    // Send error to your logging service
    apiClient.sendLog({
      level: "error",
      message: data.message,
      error: data.error,
      context: {
        task: data.task,
        stage: data.stage,
      },
    });
  }

  warn(data: LogDataInput): void {
    // Send warning to your logging service
    apiClient.sendLog({
      level: "warn",
      message: data.message,
      context: {
        task: data.task,
        stage: data.stage,
      },
    });
  }
}

// Create an instance and use it
const apiLogger = new ApiLogger();
const migrationManager = new MigrationManager(pool, apiLogger);

You can also create prefixed loggers easily with the createPrefixed method:

// Create a logger with prefilled task/stage information
const prefixedLogger = apiLogger.createPrefixed({
  task: "my-task",
  stage: "initialization",
});

// All logs will include the prefixes
prefixedLogger.info({ message: "Starting process" });
// Output includes: [my-task] [initialization] Starting process

Schema Helpers

The helpers object, passed as the second argument to beforeSchema and afterSchema functions, provides a set of safe, idempotent methods for common schema modifications. These helpers automatically log their actions using the configured logger and perform existence checks before attempting changes, preventing errors if an object already exists or doesn't exist when trying to remove it.

Schema Resolution: Existence checks resolve relations through Postgres's to_regclass / pg_catalog, so they honour the connection's search_path and accept schema-qualified names (e.g. "reporting.users"). The check looks in the same place the subsequent DDL will run, not blindly across every schema. Note that table, column, index, and constraint names are written directly into the SQL statement. SQL placeholders ($1, $2, …) can only stand in for values (data), never for identifiers like table or column names, so those names can't be parameterized and must be concatenated in. The same is true of the column types, default values, and constraint definitions you pass (e.g. columnType, defaultValue, the columns map values, and the constraints strings). These are interpolated directly too, not parameterized, so a value-shaped argument like defaultValue is not safe to build from user input. Treat all of them as trusted, code-defined values. Don't build them from untrusted input.

Available Helpers:

• createTable(client, tableName, columns, constraints?): Creates a table if it doesn't exist.
  • columns: An object mapping column names to their types (e.g., { id: "SERIAL PRIMARY KEY", name: "TEXT NOT NULL" }).
  • constraints (optional): An array of strings defining table constraints (e.g., ["CONSTRAINT uq_email UNIQUE (email)"]).
• addColumn(client, tableName, columnName, columnType, defaultValue?): Adds a column to a table if it doesn't exist. Throws an error if the table does not exist.
  • defaultValue (optional): A default value for the new column.
• removeColumn(client, tableName, columnName): Removes a column from a table if it exists. Throws an error if the table does not exist. Logs a message if the column doesn't exist.
• addIndex(client, tableName, indexName, columns, unique?): Adds an index to a table if it doesn't exist. Throws an error if the table does not exist, or if the indexName collides with an existing relation in the table's schema that isn't this index. (In Postgres, indexes share one namespace with tables, views, sequences, etc. per schema, so an index name must be unique across all of them. A clash with another table's index or a non-index relation is a conflict.)
  • columns: An array of column names to include in the index.
  • unique (optional, default false): Whether to create a unique index.
• removeIndex(client, indexName): Removes an index if it exists. Logs a message if the index doesn't exist.
• addForeignKey(client, tableName, constraintName, columnName, referencedTable, referencedColumn, onDelete?): Adds a foreign key constraint if it doesn't exist. Throws an error if the table or referenced table does not exist.
  • onDelete (optional, default 'NO ACTION'): Action to take on delete (CASCADE, SET NULL, RESTRICT, NO ACTION).
• addDeferrableForeignKey(client, tableName, constraintName, columnName, referencedTable, referencedColumn, onDelete?, initiallyDeferred?): Adds a deferrable foreign key constraint if it d