Most services eventually need a migration runner. I wanted one with no external dependencies and no hidden state: a single binary, a directory of SQL files, and a table that records what happened.

Why not PRAGMA user_version?

PRAGMA user_version is a single integer. It tells you which migration ran last, but not which file it was, when it ran, or whether the file changed afterwards. That is enough until someone edits an old migration and the schema silently drifts.

A small table is more useful:

ColumnPurpose
versionInteger parsed from the filename. Primary key.
nameThe original filename, for humans.
checksumSHA-256 of the SQL file.
applied_atTimestamp in UTC.

The runner

Migrations are embedded with go:embed, sorted by version, and applied one file per transaction. If an applied migration has a different checksum, the runner refuses to start.

//go:embed migrations/*.sql
var migrationFS embed.FS

type migration struct {
    version  int
    name     string
    sql      string
    checksum string
}

func apply(ctx context.Context, db *sql.DB, m migration) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    if _, err := tx.ExecContext(ctx, m.sql); err != nil {
        return err
    }
    if _, err := tx.ExecContext(ctx,
        `INSERT INTO schema_migrations (version, name, checksum, applied_at)
         VALUES (?, ?, ?, datetime('now'))`,
        m.version, m.name, m.checksum); err != nil {
        return err
    }
    return tx.Commit()
}

Details that matter

  • SQLite DDL is transactional, so a failed migration leaves no partial schema.
  • Enable PRAGMA foreign_keys = ON on every connection, not once at startup.
  • If more than one process can write, use a lock file or serialize the runner. Two migrations at once is not a feature.
  • Never edit an applied migration. Add a new one. The checksum is there to make that rule visible.

Result

One binary, one SQLite file, no migration service. It is not a framework; it is a small tool with a small surface. That is the point.

← All notes