sqlite-migrate/README.md

bchen-sqlite-migrate

Tiny, forward-only SQLite migration runner for better-sqlite3: numbered .sql files, sha256 checksum verification, idempotent re-apply, transactional per-migration, and genesis-stamping for pre-existing prod DBs.

Lifted from the authd auth service (PR #14, merge ced21ab) and generalized for cross-project use across the fleet.

Install

Public repo — no auth needed:

npm install git+https://gitea.bchen.dev/brendan/sqlite-migrate.git#v0.1.0

Pin to a tag (#v0.1.0), not a branch.

Peer dependency:

"peerDependencies": {
  "better-sqlite3": ">=11 <13"
}

Node 20+. ESM-only. Ships pre-compiled dist/, so no build step is needed in consumers.

Quick start

import Database from 'better-sqlite3';
import { applyMigrations } from 'bchen-sqlite-migrate';

const db = new Database('./app.db');
const summary = applyMigrations(db, './migrations', {
  genesisProbeTable: 'users',
  logger: console.log,
});
// summary: { applied, alreadyApplied, pending, stamped }

Migration files live in a single flat directory and follow the naming convention NNNN_name.sql (4+ digits, snake_case name, .sql extension), e.g. 0001_init.sql, 0002_articles_user_scope.sql.

API

applyMigrations(db, migrationsDir, opts?) => MigrationSummary

Apply all pending migrations in version order. Each migration runs inside a transaction; failure rolls back. Re-running is idempotent — already-applied migrations are skipped after a checksum compare; mismatch throws.

interface ApplyOptions {
  /** Per-message logger (info + WARN). Default no-op. */
  logger?: (msg: string) => void;
  /** Force-stamp the 0001 migration without executing it. */
  stampGenesis?: boolean;
  /** Default 'users'. Probe table for "is this a pre-existing prod DB?". */
  genesisProbeTable?: string;
}

interface MigrationSummary {
  applied: number;        // ran successfully this call
  pending: number;        // always 0; kept for forward-compat
  alreadyApplied: number; // already in schema_migrations
  stamped: string[];      // versions stamped without execution
}

listMigrations(migrationsDir) => MigrationFile[]

Reads + parses NNNN_*.sql files into { version, name, checksum, body }. Throws on filenames that don't match ^(\d{4,})_([a-z0-9_]+)\.sql$.

readAppliedRows(db) => MigrationRow[]

Reads schema_migrations. Returns [] if the table doesn't exist.

stampMigration(db, migrationsDir, version) => MigrationFile

Marks version as applied without running its body. Throws if already stamped. Useful for retrofitting an existing-prod DB to a new migration scheme.

Genesis stamping

When applying against a DB that has tables but no schema_migrations row, the runner can mark the 0001 migration as "applied" without executing its body. This lets you adopt the migration system on an existing prod DB without re-running CREATE TABLE against tables that already exist.

Two triggers:

1. applyMigrations(db, dir, { stampGenesis: true }) — explicit opt-in. Typically wired to an env var:

   DB_MIGRATIONS_STAMP_GENESIS=1 node ./scripts/db-migrate.mjs
   

2. genesisProbeTable — auto-detection. If this table exists in the DB and schema_migrations doesn't, stamp 0001. Default 'users'. Pass your project's first-table name to match a non-authd shape.

When stamping fires, the runner emits one WARN line via logger:

WARN genesis-stamp: marked 0001_init as applied without executing (detected pre-existing 'users' table)

Or, when triggered explicitly:

WARN genesis-stamp: marked 0001_init as applied without executing (stampGenesis option set)

Per-consumer call signatures (fleet)

Consumer	genesisProbeTable
authd	'users' (default — can omit)
buchinese	'sessions'
inventory	TBD — verify at adoption
nanodrop	'users'
dashcam	'sessions'
movement	TBD — verify at adoption

CLI helper

The package exports a wrapper at the bchen-sqlite-migrate/cli sub-path so each consumer writes a ~5-line shim:

// scripts/db-migrate.ts
import { runMigrateCli } from 'bchen-sqlite-migrate/cli';
import { openDb } from './_db-open.js';

process.exit(runMigrateCli({
  openDb,
  migrationsDir: new URL('../db/migrations/', import.meta.url).pathname,
  command: process.argv[2] as 'migrate' | 'status' | 'stamp',
  version: process.argv[3],
  stampGenesis: process.env.DB_MIGRATIONS_STAMP_GENESIS === '1',
  genesisProbeTable: 'users',
}));

runMigrateCli returns an exit code rather than calling process.exit itself — the consumer is responsible for process.exit(...). The wrapper closes the DB before returning. This keeps the helper testable (you can capture stdout/stderr via PassThrough streams).

Three commands:

• migrate — apply pending migrations.
• status — print each migration's state (applied:<v> / pending:<v> / checksum-mismatch:<v>). Exit code 2 if any mismatched.
• stamp <version> — manually mark a version as applied without running its body.

Development

git clone ssh://git@gitea.bchen.dev:2222/brendan/sqlite-migrate.git
cd sqlite-migrate
npm install
npm test
npm run build

dist/ is committed (consumers install via git+https://...#v0.1.0 and need the pre-built JS). CI verifies dist/ is in sync with src/ via git diff --exit-code dist/. Re-run npm run build before committing if you touched src/.

License

MIT — see LICENSE.

Security

This is a server-side library with no HTTP surface and no untrusted input. db.exec(file.body) runs each migration's SQL verbatim — by design, since migrations are trusted, committed-to-repo SQL. Do not point migrationsDir at a path that can be written to by untrusted users.