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.

Supported databases

DbMigrator is dialect-agnostic: connection.dialect picks a bundled migration adapter internally. Every database SQB has a connection adapter for is supported (since v6.0.8):

`connection.dialect`	Migration adapter	Requires
`'postgres'`	`PgMigrationAdapter`	`@sqb/postgres`
`'oracle'`	`OracleMigrationAdapter`	`@sqb/oracle`
`'mysql'`	`MysqlMigrationAdapter`	`@sqb/mysql`
`'mariadb'`	`MariadbMigrationAdapter`	`@sqb/mariadb`
`'mssql'`	`MssqlMigrationAdapter`	`@sqb/mssql`
`'sqlite'` (`driver` unset or `'sqlite'`)	`SqliteMigrationAdapter`	`@sqb/sqlite`
`'sqlite'` + `driver: 'sqljs'`	`SqljsMigrationAdapter`	`@sqb/sqljs`

@sqb/sqlite and @sqb/sqljs both register the 'sqlite' dialect, so connection.driver is what tells them apart. Any other dialect value makes execute() throw

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

All seven adapter packages are peer dependencies of @sqb/migrator, and DbMigrator imports them eagerly, so they — and their drivers (postgrejs, mysql2, mariadb, mssql, oracledb, sql.js), which the adapter packages in turn declare as peers — all need to be installed for @sqb/migrator to load, even if you only ever migrate one database. npm 7+ installs peer dependencies automatically. The migration adapter imports the connection adapter's entry point itself, so a separate side-effect import isn't strictly required.

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

The import '@sqb/postgres' side-effect import registers the postgres adapter that PgMigrationAdapter uses internally to connect (the migration adapter imports it too, so the line is optional but makes the dependency explicit). Swap in @sqb/mysql, @sqb/oracle, @sqb/sqlite, etc. and the matching connection.dialect to migrate another database with the same code:

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

await new DbMigrator().execute({
  connection: { dialect: 'sqlite', database: './app.sqlite' },
  migrationPackage: { name: 'my-app', migrations: ['migrations/**/*'] },
});

`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` (required) selects the migration adapter — see Supported databases; for SQLite, `connection.driver` picks between `@sqb/sqlite` and `@sqb/sqljs`.
`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 — or, for dialects with no real schema object (Oracle, SQLite), a table-name prefix. Defaults to `__migration`. See Dialect differences.
`scriptVariables`	`Record<string, string>`	no	Extra `$(name)` substitution variables made available to `.task.sql` scripts, on top of the adapter's own dialect-specific 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.
`transaction`	`'run' \| 'migration' \| 'none'`	no	Wraps the run in transactions so a failure leaves the database as it was. Defaults to `'none'`. PostgreSQL and SQLite only — see Transactional runs and dry runs. Since v6.3.0.
`dryRun`	`boolean`	no	Runs everything in one transaction, then rolls back instead of committing. Implies `transaction: 'run'`. Since v6.3.0.

See DbMigratorOptions for the full interface reference.

What `execute()` does

Loads the migration package (resolving any glob patterns / manifest files — see Writing Migration Tasks) and computes its minimum and maximum migration versions.
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 (...).
Creates the migration adapter for connection.dialect and connects. This creates infoSchema (default __migration) where the dialect supports it, 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). On Oracle the names are upper-cased (MIGRATION_SUMMARY, MIGRATION_EVENTS) and the event table's id is driven by a sequence plus trigger.
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 ....
Acquires an 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. The lock mechanism is dialect-specific — see Dialect differences.
If any migration in the package has backup: true, emits backup and calls the adapter's backupDatabase() before applying anything. No bundled adapter implements a backup, so on current dev this rejects and the run fails — see the note below.
If transaction is set (or dryRun is true), checks every pending string script for a statement the dialect cannot run in a transaction block, and begins the run's transaction ('run') — see Transactional runs and dry runs.
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). With transaction: 'migration', each migration gets its own transaction.
After all tasks in a migration finish, updates migration_summary.current_version to that migration's version.
On any failure, any open transaction is rolled back; then, 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.

backup: true is refused (since v6.3.0)

backupDatabase()/restoreDatabase() used to be no-ops, so a migration flagged backup: true ran without the protection the flag promised. Since v6.3.0 the base MigrationAdapter rejects both, and no bundled adapter overrides them: a package containing a backup: true migration now fails right after the backup event with Database backup is not supported by <Adapter>, before anything is applied. Up to v6.2.1 the flag was accepted but did nothing. For a run that leaves no trace when it fails, use transaction: 'run' on PostgreSQL or SQLite, or take your own backup (e.g. pg_dump) before calling execute().

Transactional runs and dry runs

Since v6.3.0. By default (transaction: 'none') every task is applied as it runs: when task k of n fails, tasks 1 to k-1 stay applied and the schema is left half-migrated. On a dialect that runs DDL inside transactions you can wrap the run instead:

await migrator.execute({
  connection: { dialect: 'postgres', database: 'my_database' },
  migrationPackage: myMigrationPackage,
  transaction: 'run', // or 'migration', or 'none' (the default)
});

Option	Behaviour
`transaction: 'run'`	One transaction for the whole run: every pending migration, its tasks and the version/event bookkeeping. Committed at the end, rolled back on any error.
`transaction: 'migration'`	One transaction per migration, committed after each. Migrations that already succeeded stay applied when a later one fails.
`dryRun: true`	Everything runs in one transaction and emits the same events as a real run, but ends in a rollback instead of a commit. Resolves like a real run, or rejects with the error a real run would have failed with, and leaves the database untouched, event log included. Implies `transaction: 'run'`; combining it with `'migration'` or `'none'` throws a `TypeError`.

Use a dry run to try a package against a restore of production before the real thing.

The failure is still recorded. After a rollback the error row is written to migration_events so it does not vanish with the transaction. A dry run never writes it, since it must leave the database as it was.
The schema lock stays session-level and surrounds the transaction.

Unsupported statements fail up front. In a transactional run, a script containing a statement the dialect cannot run inside a transaction block, or explicit transaction control (BEGIN/COMMIT/ROLLBACK, and ABORT on PostgreSQL) that would cut the run's transaction short, fails the run before anything is applied, naming the task, file and statement. The script is split into statements and each is matched from its start, so comments, string literals, dollar-quoted bodies and quoted identifiers are ignored, and a column named vacuum or a create database in the middle of a statement is not mistaken for one. END is not treated as transaction control, since it also closes a trigger or BEGIN ATOMIC body. A script produced by a function is checked when it runs. Apply such a migration with transaction: 'none'.

Dialect	Statements refused inside a transactional run
PostgreSQL	`CREATE`/`DROP INDEX CONCURRENTLY`, `REINDEX ... CONCURRENTLY`, `VACUUM`, `CREATE`/`DROP DATABASE`, `CREATE`/`DROP TABLESPACE`, `ALTER SYSTEM`, `DISCARD ALL`
SQLite (`sqlite`, `sqljs`)	`VACUUM`

Adapter support. Only the PostgreSQL, sqlite and sqljs adapters support it. MySQL, MariaDB and Oracle commit implicitly on DDL, so their adapters refuse 'run' and 'migration'; the SQL Server adapter does not implement transactions yet. Asking for a transaction on any of them throws TypeError: Transactional migration runs are not supported by the "<dialect>" migration adapter.

Dialect differences

The adapters all follow PgMigrationAdapter's structure, but a few things are necessarily dialect-driven:

	Postgres	MySQL / MariaDB	SQL Server	Oracle	SQLite (`sqlite` / `sqljs`)
`infoSchema`	Created with `CREATE SCHEMA IF NOT EXISTS`	Created with `CREATE DATABASE IF NOT EXISTS`	Created with `CREATE SCHEMA`	Table-name prefix only; must already exist	Table-name prefix only
Multi-statement `.task.sql`	Runs as one script	Runs as one script	Split on `GO` lines	Split on `;` and `/` lines	Runs as one script
Lock	Session-level `pg_advisory_lock`	`GET_LOCK()` / `RELEASE_LOCK()`	Transaction-scoped `sp_getapplock`	`DBMS_LOCK` (best effort)	No-op

Schema auto-creation. Postgres, MySQL, MariaDB, and SQL Server auto-create infoSchema. Oracle is the exception: a schema is a user there, and provisioning one is a DBA-level operation the adapter deliberately doesn't attempt — infoSchema (if given) is only a table-name prefix, and that schema/user must already exist. SQLite has no schema object at all, so infoSchema is likewise just a prefix there, and $(schema) resolves to main.
Multi-statement scripts. Postgres, MySQL, MariaDB, and SQLite run a whole script — trigger/procedure bodies included — in one call (the MySQL/MariaDB migration connection is opened with multipleStatements: true). Oracle's oracledb runs exactly one statement per call, so the adapter splits a script following the SQL*Plus convention: a PL/SQL block (a trigger, procedure, function, package body, or a bare BEGIN/DECLARE block) is terminated by a lone / on its own line, and everything else is plain ;-separated DDL/DML. SQL Server needs a different split: T-SQL requires CREATE TRIGGER/PROCEDURE/FUNCTION/VIEW to be the first statement in its batch, so a script mixing e.g. a CREATE TABLE with a CREATE TRIGGER needs a GO on its own line between them, exactly as in sqlcmd/SSMS.
Locking. Postgres, MySQL, and MariaDB use session-scoped advisory-lock functions, unaffected by the implicit commit their own DDL triggers. Oracle uses DBMS_LOCK for the same reason, falling back to running unprotected if that package isn't grantable in your environment. SQL Server DDL doesn't implicitly commit, so MssqlMigrationAdapter uses a transaction-scoped sp_getapplock, released when that transaction commits in unlockSchema() (or rolled back in close() if the run never got that far). SQLite is embedded and single-process with no server to arbitrate a lock between clients, so locking is a no-op.
MariaDB's GET_LOCK rejects a negative timeout (it warns and returns NULL instead of waiting forever, unlike MySQL), so MariadbMigrationAdapter waits with a very large positive timeout (2^30 seconds) to get the same effectively-infinite wait.
sqljs never writes back to disk on its own — sql.js loads a whole file into memory at connect time and only ever mutates that copy. SqljsMigrationAdapter exports and saves the result back to the original file when the migration run finishes (skipped for a :memory: database); otherwise every applied migration would be silently lost when the process exits.

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.
`commit`	—	When a transaction of a transactional run is committed: after each `migration-finish` with `transaction: 'migration'`, once at the end with `'run'`.
`rollback`	—	When a transaction is rolled back: on failure, or at the end of every dry run.
`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. Each adapter provides schema, tablespace, and owner defaults; a reference with no matching variable is left untouched:

Variable	Postgres	MySQL / MariaDB	SQL Server	Oracle	SQLite (`sqlite` / `sqljs`)
`schema`	`connection.schema`, else `CURRENT_SCHEMA` (`public`)	`connection.schema`, else `DATABASE()`	`connection.schema`, else `SCHEMA_NAME()` (`dbo`)	`connection.schema`, else the session's current schema	`main`
`tablespace`	`pg_default`	`''`	`''`	`USERS`	`''`
`owner`	`postgres`	`connection.user`	`connection.user`	the session's current schema	`connection.user`

Anything passed in scriptVariables overrides these defaults, and connection.schema (when set) is passed through as schema. 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.

Supported databases​

Basic usage​

DbMigrator​

execute(options)​

DbMigratorOptions​

What execute() does​

Transactional runs and dry runs​

Dialect differences​

Events​

Script variables​

Next steps​