migrate

class MigrationError extends Error {

Error thrown for migration planning, checksum, history, and execution failures.

Raised for duplicate migration ids, checksum drift in an already-applied migration, invalid history table names, rollbacks of migrations that are missing from the provided set or define no down body, and migration modules that do not export up. Database errors raised while executing migration SQL propagate as-is and are not wrapped.

import { MigrationError, migrate } from 'fino:database/migrate';

try {
  await migrate(db, migrations);
} catch (err) {
  if (err instanceof MigrationError) console.error('migration rejected:', err.message);
  else throw err;
}

Constructors

interface Migration {

A reversible database migration.

Migrations are identified and ordered by id, so ids should sort in the order migrations must run. up and down may be SQL strings, arrays of SQL statements, or functions that return SQL (see MigrationBody). down is required only when a rollback includes the migration.

import { defineMigration } from 'fino:database/migrate';

const createUsers = defineMigration({
  id: '001_create_users',
  up: 'CREATE TABLE users (id TEXT PRIMARY KEY, name TEXT NOT NULL)',
  down: 'DROP TABLE users'
});

Properties

interface MigrationContext {

Function context passed to executable migration bodies.

When up or down is a function, it receives this context: the live connection, the migration's own definition, and the sql template tag from fino:database for building portable fragments.

import { defineMigration } from 'fino:database/migrate';

const seedAdmin = defineMigration({
  id: '002_seed_admin',
  checksum: 'seed-admin-v1',
  up: async ({ db }) => {
    await db.prepare('INSERT INTO users VALUES (?, ?)').run('1', 'admin');
  },
  down: `DELETE FROM users WHERE id = '1'`
});

Properties

interface MigrationRecord {

One row of migration history.

The history table is append-only: applying a migration inserts an 'up' row and rolling it back inserts a 'down' row. Functions such as getAppliedMigrations() collapse this log into the set of currently active migrations.

import { getAppliedMigrations } from 'fino:database/migrate';

for (const record of await getAppliedMigrations(db)) {
  console.log(record.id, new Date(record.appliedAt), `${record.durationMs}ms`);
}

Properties

interface MigrationPlan {

Planned migration state returned by planMigrations().

import { loadMigrations, planMigrations } from 'fino:database/migrate';

const plan = await planMigrations(db, await loadMigrations('./migrations/*.sql'));
console.log(`${plan.applied.length} applied, ${plan.pending.length} pending`);

Properties

interface LoadMigrationsOptions {

Options for loadMigrations().

import { loadMigrations } from 'fino:database/migrate';

const migrations = await loadMigrations('*.sql', { cwd: '/srv/app/migrations' });

Properties

interface MigrateOptions {

Options shared by migration planning and execution.

import { migrate } from 'fino:database/migrate';

await migrate(db, migrations, { tableName: 'schema_history', transaction: false });

Properties

interface RollbackOptions extends MigrateOptions {

Options for rollback execution.

import { rollback } from 'fino:database/migrate';

await rollback(db, migrations, { steps: 2 });

Properties

type MigrationBody = string | string[] | ( ( ctx: MigrationContext ) => void | string | string[] | Promise<void | string | string[]> )

SQL body accepted by migration execution.

A body is a single SQL string, an array of statements executed in order (one db.exec() per entry; blank entries are skipped), or a function receiving a MigrationContext. Functions may return SQL to execute, or perform their work directly through ctx.db and return nothing.

When the migration has no explicit checksum, its up body is resolved to text for checksumming — which invokes function bodies — so functions should either be pure SQL generators or be paired with an explicit checksum.

function defineMigration(migration: Migration): Migration

Return migration unchanged while preserving the public Migration type.

An identity helper for writing migrations inline or as TypeScript modules: it adds type checking and editor completion without altering the value.

import { defineMigration } from 'fino:database/migrate';

export default defineMigration({
  id: '003_add_email',
  up: 'ALTER TABLE users ADD COLUMN email TEXT',
  down: 'ALTER TABLE users DROP COLUMN email'
});

async function getAppliedMigrations( db: DatabaseConnection, options: MigrateOptions = { } ): Promise<MigrationRecord[]>

Read currently applied migrations from the history table.

Replays the append-only history log, so migrations that were later rolled back do not appear. A missing history table is treated as an empty migration history, making this safe to call against a fresh database.

import { getAppliedMigrations } from 'fino:database/migrate';

const applied = await getAppliedMigrations(db);
console.log(applied.map((record) => record.id));

async function planMigrations( db: DatabaseConnection, migrations: Migration[], options: MigrateOptions = { } ): Promise<MigrationPlan>

Compare migrations with database history and return applied and pending sets.

Migrations are de-duplicated and sorted by id before comparison; duplicate ids throw a MigrationError. For each migration that is already active, the current checksum of its up body is compared against the recorded one, and a mismatch throws a MigrationError naming the drifted migration.

This is a dry run: nothing is executed and no history is written, so it suits a migrate --dry-run style status command.

import { loadMigrations, planMigrations } from 'fino:database/migrate';

const plan = await planMigrations(db, await loadMigrations('./migrations/*.sql'));
for (const migration of plan.pending) console.log('would apply', migration.id);

async function migrate( db: DatabaseConnection, migrations: Migration[], options: MigrateOptions = { } ): Promise<MigrationRecord[]>

Apply all pending up migrations.

Creates the history table if needed, plans against current history — which rejects checksum drift before anything runs — then applies pending migrations in id order. Each migration executes inside db.transaction() unless transaction is false, so a failing statement rolls back both the migration's changes and its history row. Already-applied migrations are skipped, making repeated calls idempotent.

Returns records for the migrations applied by this call; an up-to-date database yields an empty array.

import { Database } from 'fino:database';
import { loadMigrations, migrate } from 'fino:database/migrate';

await using db = await Database.open(process.env.DATABASE_URL ?? ':memory:');
const applied = await migrate(db, await loadMigrations('./migrations/*.sql'));
for (const record of applied) console.log(`applied ${record.id} in ${record.durationMs}ms`);

async function rollback( db: DatabaseConnection, migrations: Migration[], options: RollbackOptions = { } ): Promise<MigrationRecord[]>

Roll back applied migrations by executing their down bodies.

Active migrations are reverted newest first (descending id); steps controls how many and defaults to 1. Every reverted migration must be present in migrations and define down, otherwise a MigrationError is thrown. Each rollback runs inside db.transaction() unless transaction is false, and appends a 'down' history row rather than deleting the original record. Returns records for the reverted migrations.

import { loadMigrations, rollback } from 'fino:database/migrate';

const migrations = await loadMigrations('./migrations/*.sql');
await rollback(db, migrations);               // undo the most recent migration
await rollback(db, migrations, { steps: 2 }); // undo the next two

async function loadMigrations( pattern: string | string[], options: LoadMigrationsOptions = { } ): Promise<Migration[]>

Load migrations from SQL files, TypeScript migration modules, or globs.

Each pattern is either a literal path or a glob (any of *, ?, {, [ triggers glob expansion through the filesystem's glob()); cwd anchors both forms. Matched paths are sorted, and each migration's id is derived from the file basename with .sql, .ts, or .sql.ts stripped, so filenames like 001_create_users.sql produce ordered ids.

.sql files may be plain SQL — the whole file becomes the up body — or directive SQL (see fino:database/sql) with an exported up() function and optional down(). Other files are imported as modules and must export up; exported down, id, and name are used when present. Throws a MigrationError for modules without a usable up export or for duplicate ids, and a MigrationParseError for directive SQL that does not export up().

import { loadMigrations, migrate } from 'fino:database/migrate';

// migrations/001_users.sql:
//   -- function up()
//   CREATE TABLE users (id TEXT PRIMARY KEY, name TEXT NOT NULL);
//
//   -- function down()
//   DROP TABLE users;
const migrations = await loadMigrations('./migrations/*.sql');
await migrate(db, migrations);