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.

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 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.
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 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.
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

backupDatabase()/restoreDatabase() are no-ops in every bundled adapter (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.

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.
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.