Running Migrations

@sqb/migrator applies a set of versioned schema/data migrations to a database. The entry point is the DbMigrator class — construct one, optionally attach event listeners, then call execute() with a connection and a migration package.

PostgreSQL only

DbMigrator is written to be dialect-agnostic (connection.dialect picks the migration adapter internally), but today only a PostgreSQL adapter (PgMigrationAdapter) is implemented. execute()'s internal switch on connection.dialect throws

TypeError: Migration adapter for "<dialect>" dialect is not implemented yet

for any dialect other than 'postgres'. There is currently no built-in migration support for MySQL, MariaDB, SQL Server, Oracle, or SQLite.

Basic usage

import '@sqb/postgres';
import { DbMigrator } from '@sqb/migrator';

const migrator = new DbMigrator();

const applied = await migrator.execute({
  connection: {
    dialect: 'postgres',
    host: 'localhost',
    database: 'my_database',
    schema: 'public',
  },
  migrationPackage: {
    name: 'my-app',
    migrations: ['migrations/**/*'],
  },
});

console.log(applied); // true

@sqb/migrator depends on @sqb/postgres (and, transitively, postgrejs) as peer dependencies — the import '@sqb/postgres' side-effect import registers the postgres adapter that PgMigrationAdapter uses internally to connect.

DbMigrator

DbMigrator extends AsyncEventEmitter (from the strict-typed-events package) and exposes a single method, execute().

execute(options)

execute(options: DbMigratorOptions): Promise<boolean>

Resolves to true once every migration up to the target version has been applied. See DbMigrator for the full method/event reference.

DbMigratorOptions

Field	Type	Required	Description
connection	ClientConfiguration	yes	Connection info for the target database. connection.dialect selects the migration adapter (only 'postgres' is implemented — see the warning above).
migrationPackage	MigrationPackage | MigrationPackageConfig	yes	The set of versioned migrations to apply. See Writing Migration Tasks.
infoSchema	string	no	Schema used to store migrator bookkeeping tables. Defaults to __migration (the Postgres adapter's default).
scriptVariables	Record<string, string>	no	Extra $(name) substitution variables made available to .task.sql scripts, on top of the adapter's own defaults and connection.schema.
targetVersion	number	no	Migrate up to (and including) this version instead of the package's highest version. Throws if lower than the package's lowest version.

See DbMigratorOptions for the full interface reference.

What execute() does

1. Loads the migration package (resolving any glob patterns / manifest files — see Writing Migration Tasks) and computes its minimum and maximum migration versions.
2. Resolves targetVersion as min(options.targetVersion ?? Infinity, maxVersion). If this is lower than the package's minimum version, execute() rejects with Version mismatch. Target schema version (...) is lower than migration package min version (...).
3. Creates the migration adapter for connection.dialect (Postgres only today — see the warning above) and connects. This creates infoSchema (default __migration) plus two bookkeeping tables inside it if they don't already exist: migration_summary (one row per package, tracking status and current_version) and migration_events (an append-only log of every task attempted).
4. If the adapter's tracked version is behind the package's minimum version by more than one (i.e. there's a gap it can't bridge), it rejects with This package can migrate starting from ... but current version is ....
5. Acquires a Postgres advisory lock (lockSchema()) scoped to infoSchema + package name, so two processes can't migrate the same package concurrently, then re-reads the tracked version now that the lock is held.
6. If any migration in the package has backup: true, emits backup and calls the adapter's backupDatabase() before applying anything.
7. Applies each migration whose version is > adapter.version and <= targetVersion, in ascending version order, running its tasks in order. Every task execution is recorded as a row in migration_events (started, then success or error).
8. After all tasks in a migration finish, updates migration_summary.current_version to that migration's version.
9. On any failure, if a backup was taken, emits restore and calls restoreDatabase(), then re-throws the original error. Either way, unlockSchema() and close() run in a finally block.

note

The Postgres adapter's backupDatabase()/restoreDatabase() are currently no-ops (they resolve immediately without doing anything). Setting backup: true on a migration still drives the backup/restore lifecycle events, but does not by itself create or restore an actual database backup — set up your own backup/restore mechanism (e.g. pg_dump) if you need one, and hook it into the backup/restore events.

Events

DbMigrator emits the following events, in this order, during execute():

Event	Payload	When
start	—	Once, right after the schema lock is about to be acquired.
backup	—	Only if some migration in the package has backup: true, right before backupDatabase() is called.
migration-start	{ migration, total, index }	Before a migration's tasks start running. total is the number of migrations in the package; index is this migration's position.
task-start	{ migration, task, total, index }	Before a single task runs. total is the total task count across all migrations being applied; index is this task's position within its migration.
task-finish	{ migration, task, total, index }	After a task completes successfully.
migration-finish	{ migration, total, index }	After all of a migration's tasks complete and its version is recorded.
restore	—	Only on failure, and only if a backup was taken; right before restoreDatabase() is called.
finish	—	Once, after every targeted migration has been applied successfully.

migrator.on('migration-start', ({ migration, index, total }) => {
  console.log(`Applying migration ${migration.version} (${index + 1}/${total})`);
});
migrator.on('task-start', ({ task }) => {
  console.log(`  - ${task.title ?? task.filename ?? 'task'}`);
});
migrator.on('finish', () => console.log('Migration finished'));

Script variables

.task.sql files can reference $(name) placeholders, which are replaced before the script runs. The Postgres adapter provides these defaults:

Variable	Default
schema	connection.schema, or the connection's current schema if not set
tablespace	pg_default
owner	postgres

Anything passed in scriptVariables overrides these defaults. See Writing Migration Tasks for how .task.sql files use them.

Next steps

• Writing Migration Tasks — structuring a migration package, .task.sql/.task.json files, and migration.ts/migration.json manifests.
• DbMigrator — full API reference.