Application Migrations

Run your application's own schema migrations with the migrator from @queuert/postgres, @queuert/sqlite and @queuert/mysql.

Experimental

The migrator API (createPgMigrator, createSqliteMigrator, createMysqlMigrator, Migration, MigrationScript and Migrator, including their "sync" mode such as Migrator<"sync">) is experimental: it may change in a minor release.

Overview

@queuert/postgres, @queuert/sqlite and @queuert/mysql export the migrator they run Queuert's own schema with, so your application can run its migrations the same way. It is built for migrating on boot from many processes at once: one process migrates while the others wait, then find nothing left to do. Migrations run in the order of their list, can be reverted, and are functions that use your own query builder.

Create a Migrator

The migrator runs through the same state provider you pass to the PostgreSQL state adapter, here the postgres.js one:

src/index.ts

const migrations = createMigrations({ sql });
const migrator = createPgMigrator({
  stateProvider,
  migrations,
  lock,
  after,
});

lock sets the timing of the lease described in Several Processes, and after is a script that runs once the migrations have run, described in Before and After; both are optional. migrator.migrateToLatest() applies the pending migrations and resolves with what it applied, what it skipped and the unrecognized names described in Order.

It creates its tables on the first run, if they are missing: migration for the history and migration_lock for the lease, in the public schema. schema (created if missing), migrationTableName and lockTableName rename them. With SQLite, createSqliteMigrator takes the same options without schema. With MySQL or MariaDB, createMysqlMigrator puts them in the connection's database, and schema names another database, which must exist.

Define Migrations

Every migration has a unique name, an up and a down. Migration takes the provider's transaction context type, PostgresJsContext for postgres.js. This list, from the postgres.js state example, creates an invoices table, indexes it without locking writes, backfills its status in batches and numbers every invoice:

src/migrations.ts

import type { Migration } from "@queuert/postgres";
import type postgres from "postgres";

import type { PostgresJsContext } from "./provider.ts";

export const createMigrations = ({
  sql,
}: {
  sql: postgres.Sql;
}): Migration<PostgresJsContext>[] => [
  {
    name: "create_invoices",
    up: async ({ txSql }) => {
      await txSql`
        CREATE TABLE invoices (id SERIAL PRIMARY KEY, number TEXT, customer TEXT NOT NULL, amount_cents INTEGER NOT NULL, status TEXT)
      `;
      await txSql`
        INSERT INTO invoices (customer, amount_cents) VALUES (${"Alice"}, ${1200}), (${"Bob"}, ${3400})
      `;
    },
    down: async ({ txSql }) => {
      await txSql`DROP TABLE invoices`;
    },
  },
  {
    name: "invoices_customer_idx",
    type: "non-transactional",
    up: async () => {
      await sql`CREATE INDEX CONCURRENTLY IF NOT EXISTS invoices_customer_idx ON invoices (customer)`;
    },
    down: async () => {
      await sql`DROP INDEX CONCURRENTLY IF EXISTS invoices_customer_idx`;
    },
  },
  {
    name: "backfill_invoice_status",
    type: "non-transactional",
    up: async ({ assertLockHeld }) => {
      for (;;) {
        assertLockHeld();
        const { count } = await sql`
          UPDATE invoices SET status = ${"open"}
          WHERE id IN (SELECT id FROM invoices WHERE status IS NULL LIMIT 1000)
        `;
        if (count === 0) return;
      }
    },
    down: async () => {
      await sql`UPDATE invoices SET status = NULL`;
    },
  },
  {
    name: "assign_invoice_numbers",
    type: "non-transactional",
    up: async ({ assertLockHeld }) => {
      const rows = await sql<{ id: number }[]>`
        SELECT id FROM invoices WHERE number IS NULL ORDER BY id
      `;
      for (const { id } of rows) {
        assertLockHeld();
        await sql`UPDATE invoices SET number = ${`INV-${String(id).padStart(4, "0")}`} WHERE id = ${id}`;
      }
    },
    down: async () => {
      await sql`UPDATE invoices SET number = NULL`;
    },
  },
];

A migration is transactional unless it sets type. Its up and down receive the transaction context and run inside one transaction together with the history record, so your query builder works on the migration's connection.

A non-transactional migration's up and down run outside a transaction with their own database access, as CREATE INDEX CONCURRENTLY or a backfill in batches needs. The record is written after the function resolves, so it must be safe to run again.

On MySQL and MariaDB, DDL commits implicitly, so a transactional migration that changes the schema is not atomic: a failure after its DDL leaves the change applied and the migration unrecorded. Put DDL in non-transactional migrations written to be rerunnable (CREATE TABLE IF NOT EXISTS, IF [NOT] EXISTS on MariaDB), and keep row changes in transactional ones. Call assertLockHeld() between steps of long work: it throws once the lease is lost, so the migration stops before another process starts migrating.

Caution

Inside a transactional migration, run queries only through the txCtx that up and down receive. A query through your outer client waits for a connection the migration holds when the pool has one connection (Knex and Kysely over SQLite), and the async SQLite providers hold their write lock for the whole transaction, so taking that lock again never returns. Prisma's interactive transactions time out after 5 seconds by default; raise transactionOptions.timeout on the client for long transactional migrations.

When a migration fails, the migrator rejects with Migration "<name>" failed: <message> (or Reverting migration "<name>" failed: …), with the original error as cause.

Order

Migrations run in the order of the list, and names only need to be unique. Append new migrations at the end: two branches that each add one then meet as a merge conflict on the list. A run refuses to start when a migration that has not run is listed before one that has, which catches a migration inserted into a history that is already deployed.

migrateToLatest reports applied migrations the code doesn't know in unrecognized and keeps going, so an older release still boots against a database a newer release migrated.

Several Processes

Every run first claims a lease row in the lock table. One process migrates and extends the lease while it works; the others poll until it is released and then skip what it applied. A process that dies leaves the lease to expire, and the next process takes over. Two migrators over one database stand in for two processes booting at once; one resolves with every migration applied, the other with them all skipped:

src/index.ts

const [first, second] = await Promise.all([
  migrator.migrateToLatest(),
  createPgMigrator({ stateProvider, migrations, lock, after }).migrateToLatest(),
]);

The lock passed to both migrators sets the lease timing, with heartbeatIntervalMs below a third of ttlMs; it defaults to the lower of 15 seconds and a quarter of ttlMs:

src/index.ts

const lock = { ttlMs: 60_000, heartbeatIntervalMs: 15_000, pollIntervalMs: 1_000 };

With SQLite, claiming and extending the lease write to the database, so they wait for SQLite's write lock while another process runs a migration transaction. Set the driver's busy timeout longer than your longest migration transaction; otherwise a waiting process fails with SQLITE_BUSY instead of waiting its turn.

Synchronous SQLite Drivers

With a synchronous provider (better-sqlite3, node:sqlite, bun:sqlite), createSqliteMigrator returns a Migrator<"sync">. It takes the same options except lock, and its migrations, before and after are synchronous: up and down return nothing, and migrateToLatest and migrateTo return their result directly. It takes no lease. Each transactional migration still writes to the lock table first, so processes migrating at once apply it once, but a non-transactional migration can run in two of them; migrate from one process when you have those.

Revert

migrateTo(name) brings the database to the named migration. It applies the pending migrations up to it, or reverts the applied migrations after it with their down, last first. migrateTo(null) reverts every applied migration. Here reverted is ["assign_invoice_numbers", "backfill_invoice_status", "invoices_customer_idx"]:

src/index.ts

const { reverted } = await migrator.migrateTo("create_invoices");

Before reverting anything, it refuses if the database has applied migrations the code doesn't know. A migration that cannot be reverted throws from its down: the revert stops there, and that migration stays recorded. A transactional revert removes the record in the same transaction as its down; a non-transactional one removes it after its down, which must then be safe to run again.

Before and After

before and after run under the lease: before on every run, after the migrator has created its tables and before the history is read, and after once the migrations have run, unless one failed. They record nothing, and are a MigrationScript, taking the same { assertLockHeld } argument as a non-transactional migration. The after passed to the migrators above logs every run in a table of its own, including a run with nothing to do; the lease keeps the other process out while it creates the table:

src/index.ts

const after: MigrationScript = async ({ assertLockHeld }) => {
  assertLockHeld();
  await sql`CREATE TABLE IF NOT EXISTS migration_runs (id SERIAL PRIMARY KEY, finished_at TIMESTAMPTZ NOT NULL DEFAULT now())`;
  await sql`INSERT INTO migration_runs DEFAULT VALUES`;
};

A before is passed to createPgMigrator the same way.

An Existing History Table

To take over from another migration tool, copy the names it recorded into the migrator's history in before, which runs after the migrator has created its tables and before it reads the history. For Kysely's kysely_migration table:

TypeScript

const migrator = createPgMigrator({
  stateProvider,
  migrations,
  before: async () => {
    await pool.query(
      "INSERT INTO migration (name) SELECT name FROM kysely_migration ON CONFLICT (name) DO NOTHING",
    );
  },
});

The copied migrations count as applied when their names match, and the copy is a no-op on later runs. Keep the previous tool's migrator from running once the migrator has taken over: the two don't share a lock.

Testing a Provider

@queuert/postgres/conformance exports pgMigratorConformanceCases and createPgMigratorConformanceContext, and @queuert/sqlite/conformance and @queuert/mysql/conformance the SQLite and MySQL equivalents. They run the migrator over your state provider: history, transactional and non-transactional migrations, reverts and the lease. Over a synchronous SQLite provider, sqliteMigratorConformanceCases run the same cases synchronously, and the lease cases pass without running, against a context createSqliteMigratorConformanceContext builds from it. Register one test per case with any test runner:

TypeScript

const context = createPgMigratorConformanceContext({ stateProvider });

for (const conformanceCase of pgMigratorConformanceCases) {
  test(conformanceCase.title, async () => {
    await conformanceCase.run(context);
  });
}

The code on this page runs in examples/postgresql/state-postgres-js. The other PostgreSQL, SQLite and MySQL state examples, except MikroORM, run a few migrations of their own through their driver or ORM in src/migrations.ts.

esc

Search guides, integrations and every exported symbol.

↑ ↓ to navigate↵ to open