Skip to main content

Writing Migration Tasks

A migration package is a named collection of versioned migrations, each made up of one or more tasks. @sqb/migrator supports two ways of describing one: an inline config object, or a folder convention that it discovers for you with glob patterns.

The MigrationPackageConfig shape​

interface MigrationPackageConfig {
  name: string;
  description?: string;
  baseDir?: string; // defaults to the calling file's directory
  informationTableName?: string;
  migrations: (
    | string                                  // a glob pattern, resolved relative to baseDir
    | MigrationConfig                         // an inline migration object
    | (() => MigrationConfig)
    | (() => Promise<MigrationConfig>)
  )[];
}

interface MigrationConfig {
  version: number;
  backup?: boolean;
  tasks: (
    | string                                  // a glob pattern, resolved relative to this migration's folder
    | MigrationTask                           // an inline task object
    | (() => MigrationTask)
    | (() => Promise<MigrationTask>)
  )[];
}

MigrationPackage.load() (used internally by DbMigrator.execute()) turns a MigrationPackageConfig into a fully-resolved MigrationPackage, sorting migrations by version and throwing if the same version appears twice.

Option 1: inline migrations​

List each version's tasks directly. Task glob strings are resolved relative to baseDir + the migration's own baseDir (empty by default for inline entries).

migrations/index.ts
import { getDirname } from 'cross-dirname';
import type { MigrationPackageConfig } from '@sqb/migrator';

export const myMigrationPackage: MigrationPackageConfig = {
  name: 'my-app',
  baseDir: getDirname(),
  migrations: [
    {
      version: 10,
      tasks: ['sql/v010.task.sql'],
    },
    {
      version: 11,
      tasks: ['sql/v011.task.sql'],
    },
    {
      version: 12,
      tasks: [
        {
          title: 'Seed table1',
          tableName: 'table1',
          rows: [
            { id: 1, name: 'name1' },
            { id: 2, name: 'name2' },
          ],
        },
      ],
    },
  ],
};

Option 2: folder discovery​

Instead of listing versions by hand, point migrations at a glob pattern and let @sqb/migrator discover per-version folders on disk:

migrations/index.ts
import { getDirname } from 'cross-dirname';
import type { MigrationPackageConfig } from '@sqb/migrator';

export const myMigrationPackage: MigrationPackageConfig = {
  name: 'my-app',
  baseDir: getDirname(),
  migrations: ['migrations/**/*'],
};

For each file matched by the pattern named exactly migration.json, migration.ts, migration.js, migration.cjs, or migration.mjs, the migrator reads a MigrationConfig manifest — { version, tasks, backup? } — with baseDir set to that file's own directory. Any other file matched by the pattern is ignored at this stage.

A typical on-disk layout looks like this:

migrations/
  v13/
    migration.json       # { "version": 13, "tasks": ["*"] }
    v13-1.task.sql
    v13-2.task.json
  v14/
    migration.ts          # export default { version: 14, tasks: ['*'] }
    v14-1.task.sql
    v14-2.task.json

migration.json (plain data manifest):

migrations/v13/migration.json
{
  "version": 13,
  "tasks": ["*"]
}

migration.ts (code manifest — useful when you need computed values):

migrations/v14/migration.ts
const migration = {
  version: 14,
  tasks: ['*'],
};

export default migration;

Each manifest's own tasks: ['*'] pattern is then resolved relative to that folder, picking up every task file next to the manifest (see below for what counts as a task file). Version folder names themselves (v13, v14, or zero-padded names like v010, v011) are just a convention for humans/sorting — the actual version number always comes from the manifest's version field (or, for the inline form, the MigrationConfig.version you wrote directly).

Task files​

Whether referenced from an inline tasks: [...] array or discovered via a manifest's tasks: ['*'] pattern, a glob match is only treated as a task file if its name (before the extension) ends in .task — e.g. v13-1.task.sql, v13-2.task.json. Anything else matched by the glob (a stray ignorethis.sql, for instance) is silently skipped. Matched files are applied in alphabetical order, so name them so that sorts the way you want tasks to run (v13-1, v13-2, ...).

Three kinds of task are supported, distinguished by the shape of the object (whether inline or loaded from a file):

SQL script tasks — *.task.sql / *.task.json​

interface SqlScriptMigrationTask {
  title?: string;
  filename?: string; // set automatically when loaded from a file
  script: string | Function;
}

A .task.sql file is read as-is and used as script:

v13/v13-1.task.sql
CREATE TABLE $(schema).table3
(
    id integer NOT NULL,
    name varchar(256),
    active boolean default true,
    CONSTRAINT table3_pkey PRIMARY KEY (id)
) TABLESPACE $(tablespace);

ALTER TABLE $(schema).table3 OWNER to $(owner);

$(schema), $(tablespace), $(owner) are replaced using the values described in Script variables before the script runs.

A .task.json file with a script property is equivalent:

{
  "title": "Create table3",
  "script": "CREATE TABLE $(schema).table3 (...)"
}

script may also be a function (only from an inline MigrationConfig, since JSON can't encode functions) — it receives { migrationPackage, migration, task, variables } and returns the SQL string (or a promise of one) to run.

Insert-data tasks — *.task.json​

interface InsertDataMigrationTask {
  title?: string;
  filename?: string;
  tableName: string;
  rows: Record<string, any>[];
}
v13/v13-2.task.json
{
  "tableName": "table3",
  "rows": [
    { "id": 1, "name": "name1" },
    { "id": 2, "name": "name2" }
  ]
}

The adapter generates one INSERT INTO ... statement per row, quoting the table name and column names. tableName also supports $(...) variable substitution (e.g. $(schema).table3). If title is omitted it defaults to Migrate data into <tableName>.

Custom function tasks — inline only​

interface CustomMigrationTask {
  title?: string;
  fn: (connection: any, adapter: MigrationAdapter) => void | Promise<void>;
}

For anything that isn't plain SQL or a row insert, provide fn directly in an inline MigrationConfig (there's no .task.js file-based form for this — a JS/TS file matched by a manifest's task glob is only recognized if it exports a script, tableName+rows, or fn object, not a bare function). fn receives the adapter's underlying driver connection and the MigrationAdapter instance itself.

Version ordering and safety checks​

  • Migrations are always applied in ascending version order, regardless of the order they appear in migrations or the order glob matching returns them in.
  • Loading a package throws if two migrations declare the same version.
  • DbMigrator.execute() throws if targetVersion is lower than the package's lowest migration version, and if the database's already-tracked version is more than one version behind the package's lowest version (i.e. there's a gap the package can't bridge).
  • A migration with backup: true triggers the backup/restore lifecycle described in Running Migrations.

Next steps​