Migrations

Each migration runs inside a transaction and is tracked in a _sirannon_migrations table, so it only applies once no matter how often the application starts. This page builds one file, migrate.ts, step by step. You load SQL files from a directory, apply them, evolve to inline migration objects, and roll back. Each step highlights exactly what changed.

File-based migrations

Place numbered SQL files in a directory using the .up.sql and .down.sql convention. Down files are optional; rollback throws if a down file is missing for a version being rolled back.

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

Timestamp-based versioning, such as 1709312400_create_users.up.sql, works the same way.

loadMigrations reads that directory and returns the ordered set. 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.

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)
// result.applied: entries that ran this time
// result.skipped: number of entries already applied

Run the file again and result.applied comes back empty 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, but you can pass the migration set inline instead. Each object carries 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.

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)
// result.applied: entries that ran this time
// result.skipped: number of entries already applied

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.

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)
// result.applied: entries that ran this time
// result.skipped: number of entries already applied
 
await db.rollback(migrations) 

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

await db.rollback(migrations, 2)
await db.rollback(migrations, 0)

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.

Migration paths are validated before any filesystem access: null bytes, .. segments, and control characters are rejected, which keeps migration loading safe even when the directory path is assembled from configuration.