Core features

Migrations

Define your schema migrations as SQL files, inline objects, or bundled assets, run them inside a transaction with checksum and concurrency guards, squash old history into a baseline, roll back to any version, and apply one set across every database the registry opens.

Table of Contents

Each migration runs inside a transaction, and Sirannon records it in a _sirannon_migrations table, so it applies once however many times the application starts. The table also stores a checksum of every applied migration's SQL, and the file header's PRAGMA user_version matches the highest applied version after every migrate and rollback. This page covers both guards in their own sections. The first sections build one file, migrate.ts, step by step. You will load SQL files from a directory, apply them, move to inline migration objects, and roll back, and every step marks the lines that changed. The final sections cover three more scenarios: bundling migrations into a build that has no filesystem access, squashing a long history into one baseline migration, and declaring one migration set on the registry so that every database it opens applies the pending migrations before serving its first query.

File-based migrations

Place numbered SQL files in a directory using the .up.sql and .down.sql convention. Down files are optional, and rollback throws when a version in range has no down file.

migrations/
  001_create_users.up.sql
  001_create_users.down.sql
  002_add_email_index.up.sql
  003_create_orders.up.sql
  003_create_orders.down.sql

Sirannon writes the database file into a directory you create yourself, and loadMigrations reads a directory you create too, so make both before you run any of the code below. These commands write the same five files the tree shows.

mkdir -p data/tenants migrations
 
printf 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);\n' > migrations/001_create_users.up.sql
printf 'DROP TABLE users;\n' > migrations/001_create_users.down.sql
printf 'CREATE UNIQUE INDEX idx_users_email ON users (email);\n' > migrations/002_add_email_index.up.sql
printf 'CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);\n' > migrations/003_create_orders.up.sql
printf 'DROP TABLE orders;\n' > migrations/003_create_orders.down.sql

Timestamp-based versioning, such as 1709312400_create_users.up.sql, works the same way. Versions must be integers from 1 to 2,147,483,647 so that they fit the file header's PRAGMA user_version, which Schema version mirroring explains. Unix-second timestamps stay inside that range; YYYYMMDDHHMMSS-style timestamps exceed it and fail validation with a MigrationError coded MIGRATION_VALIDATION_ERROR.

loadMigrations takes the path to that directory, scans it for files whose names follow the <version>_<name>.up.sql or .down.sql convention, pairs the up and down files that share a version, and returns the migrations sorted by version. It ignores any file whose name does not match, so unrelated files in the directory cause no error. Open a database and apply them with db.migrate, which runs each pending file in its own transaction and skips any already recorded in _sirannon_migrations. Start migrate.ts with the driver, a Sirannon instance, and an open database.

migrate.ts
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
import { loadMigrations } from '@delali/sirannon-db/file-migrations'
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('shop', './data/shop.db')
 
const migrations = loadMigrations('./migrations')
const result = await db.migrate(migrations)
 
console.log(`applied ${result.applied.length}, skipped ${result.skipped}`)
applied 3, skipped 0

A second run of the file returns an empty result.applied, while result.skipped counts every version already in the tracking table, so a redeploy that changes nothing does no work.

Programmatic migrations

Loading from disk suits a project that keeps SQL under version control, and you can also pass the migration set inline. Each object declares a version, a name, an up statement, and an optional down statement for rollback. Swap the directory load for an array that defines the same first three versions in code.

migrate.ts
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
import { loadMigrations } from '@delali/sirannon-db/file-migrations'
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('shop', './data/shop.db')
 
const migrations = loadMigrations('./migrations') 
const migrations = [
  {
    version: 1,
    name: 'create_users',
    up: 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)',
    down: 'DROP TABLE users',
  },
  {
    version: 2,
    name: 'add_email_index',
    up: 'CREATE UNIQUE INDEX idx_users_email ON users (email)',
  },
  {
    version: 3,
    name: 'create_orders',
    up: 'CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL)',
    down: 'DROP TABLE orders',
  },
]
const result = await db.migrate(migrations)
 
console.log(`applied ${result.applied.length}, skipped ${result.skipped}`)

Version 2 defines no down statement, so it applies on the way up but cannot be undone. Both approaches feed db.migrate the same shape, and the tracking table treats file-loaded and inline migrations identically.

Rollback

db.rollback reverses applied migrations using their down statements. Called with no version it undoes the most recent one, so here it drops the orders table and its version 3 record. Add a rollback to the end of migrate.ts.

migrate.ts
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('shop', './data/shop.db')
 
const migrations = [
  {
    version: 1,
    name: 'create_users',
    up: 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)',
    down: 'DROP TABLE users',
  },
  {
    version: 2,
    name: 'add_email_index',
    up: 'CREATE UNIQUE INDEX idx_users_email ON users (email)',
  },
  {
    version: 3,
    name: 'create_orders',
    up: 'CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL)',
    down: 'DROP TABLE orders',
  },
]
const result = await db.migrate(migrations)
 
console.log(`applied ${result.applied.length}, skipped ${result.skipped}`)
 
await db.rollback(migrations)

Pass a target version to undo further. db.rollback(migrations, 2) reverses every migration above version 2, and db.rollback(migrations, 0) reverses everything back to an empty schema.

Rollback throws a MigrationError with the code MIGRATION_NO_DOWN if a version in range has no down statement, which is why version 2 above blocks a full rollback until you add one.

Sirannon validates every migration path before it touches the filesystem, and it rejects null bytes, .. segments, and control characters, so migration loading stays safe even when the directory path comes from configuration.

Migration checksums

When a migration applies, the tracking table records a checksum of its SQL alongside the version and name. Every later migrate compares each applied version's stored checksum against the SQL you pass in, before any pending migration runs, and an edited migration fails with a MigrationError coded MIGRATION_CHECKSUM_MISMATCH naming the modified version. The comparison ignores whitespace and line endings, so reformatting a file or converting CRLF to LF passes; Sirannon rejects an edit to the SQL itself. To change the schema after a migration has run somewhere, write a new migration, and if an applied file was edited by mistake, restore its original content.

Schema version mirroring

SQLite reserves a 32-bit integer in the database file header, readable with PRAGMA user_version. After every migrate and rollback, Sirannon sets it to the highest applied migration version, inside the same transaction as the migrations themselves, so that the header always matches the tracking table. Any tool that can run that pragma reads the schema version straight from the file. This mirroring is also why migration versions must fit the 1 to 2,147,483,647 range from File-based migrations.

Confirm it at the end of migrate.ts. The run applied versions 1 to 3 and rolled back version 3, so the header reads 2.

migrate.ts
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('shop', './data/shop.db')
 
const migrations = [
  {
    version: 1,
    name: 'create_users',
    up: 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)',
    down: 'DROP TABLE users',
  },
  {
    version: 2,
    name: 'add_email_index',
    up: 'CREATE UNIQUE INDEX idx_users_email ON users (email)',
  },
  {
    version: 3,
    name: 'create_orders',
    up: 'CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL)',
    down: 'DROP TABLE orders',
  },
]
const result = await db.migrate(migrations)
 
console.log(`applied ${result.applied.length}, skipped ${result.skipped}`)
 
await db.rollback(migrations)
 
const [row] = await db.query('PRAGMA user_version')
console.log(JSON.stringify(row, null, 2))

Bundled migrations

loadMigrations reads a directory at run time, which a bundled application cannot do once its files are packed into a build. Bundlers such as Vite and webpack inline .sql files as strings at build time, so a deploy target without filesystem access, such as a Next.js route or a serverless function, still has its migrations available. migrationsFromFiles turns the bundler's map of filename to SQL text into the same sorted, validated set that loadMigrations returns from a directory.

With Vite, pass the result of import.meta.glob with the ?raw query, which loads each file as a raw SQL string. import.meta.glob is Vite's own build-time API, so bundled.ts compiles inside a Vite build and nowhere else.

bundled.ts
import { migrationsFromFiles } from '@delali/sirannon-db'
 
const files = import.meta.glob('./migrations/*.sql', {
  query: '?raw',
  import: 'default',
  eager: true,
})
 
const migrations = migrationsFromFiles(files)

With webpack, build the map from require.context('./migrations', false, /\.sql$/) with the files loaded as source assets, and pass it to migrationsFromFiles the same way.

Keys may include any path prefix, because the function reads only the final segment, which must match <version>_<name>.up.sql or <version>_<name>.down.sql. A key whose final segment does not match, a non-string value, empty SQL, or a version with no up file fails with a MigrationError coded MIGRATION_VALIDATION_ERROR, and two entries on the same version fail with MIGRATION_DUPLICATE_VERSION. You pass the returned set to db.migrate exactly like a directory load, or to the registry migrations option covered further down.

Baseline migrations

On every fresh database, a project with hundreds of migrations runs all of them one by one to build a schema that one snapshot could express. A baseline migration squashes that history. Write one migration containing the full current schema and mark it with baseline: { through: N }, where N is the highest version it supersedes; the baseline's own version must be above N. The set below replaces versions 1 to 3 from the earlier sections with a snapshot at version 4, followed by a normal migration.

baseline.ts
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('shop', './data/shop-fresh.db')
 
const migrations = [
  {
    version: 4,
    name: 'baseline_schema',
    up: `CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
         CREATE UNIQUE INDEX idx_users_email ON users (email);
         CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL)`,
    baseline: { through: 3 },
  },
  {
    version: 5,
    name: 'add_order_status',
    up: "ALTER TABLE orders ADD COLUMN status TEXT NOT NULL DEFAULT 'pending'",
  },
]
const result = await db.migrate(migrations)
console.log(JSON.stringify(result, null, 2))

The example opens a new file, so the run takes the fresh-install path, where only the baseline and the migrations after it apply. A database that already has history never executes the baseline. It keeps its real history, counts the superseded versions as skipped, and continues with the migrations above through. A database whose history cannot reach version through, because the migrations bridging the gap are missing from the set, fails with a MigrationError coded MIGRATION_BASELINE_GAP before any migration runs.

Both loaders accept the baseline as an option whose version names the snapshot migration in the set, so the migration files stay plain SQL. loadMigrations('./migrations', { baseline: { version: 4, through: 3 } }) marks a directory set, and migrationsFromFiles(files, { baseline: { version: 4, through: 3 } }) marks a bundled one.

Roll a baseline out in three steps. Write the squash file containing the full schema, with a version above every migration it supersedes. Keep the superseded files in the set until every deployment has migrated past version through, because closing the gap on a database still below it requires those files. Then delete the superseded files.

Concurrent migrations

Two processes can migrate the same database file at once, which happens when several application instances deploy together. Each process plans and applies the pending set inside one transaction, so the first process to commit applies the migrations. The other process waits out SQLite's busy timeout, retries once against the committed state, and skips every migration the first process applied, so it normally applies nothing and reports the whole set as skipped. A process that still cannot acquire the write lock after its retry receives a MigrationError coded MIGRATION_CONCURRENT. The migration history stays intact, and running migrate again once the other process finishes succeeds.

Registry migrations

Everything above migrates one database you opened by hand. An operator hosting many databases, one file per tenant for example, would rather declare the schema once and have every database apply it on its own. Set migrations on SirannonOptions, the object you pass to new Sirannon, so that the registry applies the pending set to each database it opens after it creates the connections and before it registers the database. A caller never observes a database through get, resolve, or databases() while its migrations are unfinished.

Declare the set inline, then open a database. The registry migrates it before open resolves.

registry.ts
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
const driver = betterSqlite3()
 
const sirannon = new Sirannon({
  driver,
  migrations: [
    {
      version: 1,
      name: 'create_users',
      up: 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)',
      down: 'DROP TABLE users',
    },
  ],
})
 
const shop = await sirannon.open('shop', './data/shop.db')

With a lifecycle resolver, the registry applies the same set to tenant databases that open lazily. Add an autoOpen resolver that maps a tenant id to a file, then resolve a tenant, where the registry opens its file, applies the pending set, and only then returns the handle.

registry.ts
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
const driver = betterSqlite3()
 
const sirannon = new Sirannon({
  driver,
  migrations: [
    {
      version: 1,
      name: 'create_users',
      up: 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)',
      down: 'DROP TABLE users',
    },
  ],
  lifecycle: {
    autoOpen: {
      resolver: (id) => ({ path: `./data/tenants/${id}.db` }),
    },
  },
})
 
const shop = await sirannon.open('shop', './data/shop.db')
const tenant = await sirannon.resolve('tenant-42')

The lifecycle manager also applies idle timeouts, an open-handle cap, and least-recently-used eviction to those tenant handles. Registry migrations add one more guarantee: every database the registry returns is already on the current schema.

Load the set from a function

migrations also takes a function that returns the set, synchronous or asynchronous, so that an application can read its migrations from wherever they are stored. The registry calls the function once, on the first open that uses the set, and caches the result for the registry's lifetime.

registry.ts
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
import { loadMigrations } from '@delali/sirannon-db/file-migrations'
 
const driver = betterSqlite3()
 
const sirannon = new Sirannon({
  driver,
  migrations: [
    {
      version: 1,
      name: 'create_users',
      up: 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)',
      down: 'DROP TABLE users',
    },
  ],
  migrations: () => loadMigrations('./migrations'), 
  lifecycle: {
    autoOpen: {
      resolver: (id) => ({ path: `./data/tenants/${id}.db` }),
    },
  },
})
 
const shop = await sirannon.open('shop', './data/shop.db')
const tenant = await sirannon.resolve('tenant-42')

In a bundled application, return migrationsFromFiles(files) from the function, because it needs no filesystem access. If the function throws, that open fails with the function's own error and the next open calls it again. A return value that is not an array fails the open with the error code MIGRATION_SOURCE_INVALID.

When a migration fails

If a migration fails, open closes the database, leaves it unregistered, and rethrows the MigrationError with its failing version, so the caller receives the error in place of a handle to a half-migrated database. A later open of the same id retries and skips every version already in the tracking table, so the second run resumes where the first stopped. Concurrent resolve calls for the same cold tenant share one open, so the migration step runs once however many callers wait on it. Concurrent migrations covers separate processes opening the same file. A database opened with readOnly: true skips the set, because a read-only connection cannot create the tracking table or alter the schema; the open succeeds and leaves the schema unchanged.