Migrations#

Numbered .up.sql / .down.sql pairs, applied one transaction at a time, with a checksummed ledger.

migrations/
  0001_create_users.up.sql
  0001_create_users.down.sql
  0002_add_email_index.up.sql
  0002_add_email_index.down.sql

import (
	"embed"
	"github.com/nelthaarion/breeze/v2/migrate"
)

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

sub, _ := fs.Sub(migrationsFS, "migrations")
runner := migrate.New(db, sub)

if err := runner.Up(context.Background()); err != nil {
	log.Fatal(err)
}

breeze makemigration create_users   # writes the .up.sql/.down.sql pair
breeze migrate up                   # apply everything pending
breeze migrate down 1               # roll back the last one
breeze migrate status               # what is applied, and what drifted

File naming#

<version>_<slug>.up.sql
<version>_<slug>.down.sql

version is four or more digits, slug is lowercase with underscores. Discovery fails rather than warns on an .up.sql with no matching .down.sql (or the reverse), and on two migrations sharing a version number — both refused because the failure otherwise caused is worse than a startup error: a missing .down.sql is discovered only at the moment you need to roll it back, and two migrations at the same version apply in an order that depends on directory iteration, differing between machines.

Up, Down, Status#

err := runner.Up(ctx)              // every pending migration, ascending
err := runner.Down(ctx, 1)         // the last n, descending
entries, err := runner.Status(ctx) // per-migration state

Each migration runs in its own transaction, not one transaction around the batch. Up stops at the first failure and does not attempt the rest, so a failed run leaves the database at the last migration that succeeded — a state the ledger records and Status can show. One big transaction would be cleaner in theory: several databases cannot run DDL transactionally, and a partial rollback of half a schema change is not recoverable by looking at it.

Status returns a StatusEntry per migration with Version, Name, Applied, AppliedAt, and ChecksumMismatch.

The ledger#

CREATE TABLE IF NOT EXISTS breeze_migrations (
    version    INTEGER PRIMARY KEY,
    name       TEXT      NOT NULL,
    checksum   TEXT      NOT NULL,
    applied_at TIMESTAMP NOT NULL
)

ANSI SQL that works on both Postgres and SQLite, with ? placeholders — database/sql's default convention. A driver requiring numbered placeholders (lib/pq) needs a wrapper that rewrites ? to $N.

Checksums#

checksum is the SHA-256 of the .up.sql content at the time it was applied. Status recomputes it and sets ChecksumMismatch when the file has changed since — a report, not an error, because editing an applied migration is sometimes a harmless typo fix and sometimes the beginning of an incident, and the runner cannot tell which.

The lock#

Concurrent runners are serialised by a sentinel row at version -1 in the same ledger table: INSERT version = -1 acquires it (the primary key makes it mutually exclusive), DELETE version = -1 releases it. A row rather than pg_advisory_lock because that function is Postgres-specific and this package targets any database/sql driver. -1 can never collide with a real migration, since discovered versions are parsed from filenames as unsigned digits.

A failed insert is reported as "another migration is running (or lock table is corrupted); wait and try again" — the portable API cannot distinguish a constraint violation from any other insert failure, so that ambiguity is stated in the error text rather than hidden. releaseLock warns to stderr rather than failing: by then the migration is done, and a stuck sentinel row blocks the next run, not this one.

Statement splitting#

Migration files are split on semicolons, with basic quote handling — this is deliberately naive and suits simple DDL. A CREATE FUNCTION body, a BEGIN … END block, or a semicolon inside a dollar-quoted string will split wrongly; put those in their own migration, one statement per file.

Concurrency#

Runner is not safe for concurrent use by multiple goroutines. It is safe against concurrent processes — that is what the lock is for. A deployment that runs migrations from every replica on startup works: one wins the lock, the rest wait.

Diagnostics#

curl localhost:3000/dashboard/api/diagnostics?subsystem=migrate

Reports database/migrations (whether DB and FS are set), connections (pool stats), and last_run (operation, when, count, duration, error). record is called from a defer in Up, Down and Status, so a panic or early return still records.

CLI#

breeze migrate up
breeze migrate down [n]
breeze migrate status

breeze migrate shells out to cmd/migrate in your project — the program breeze add migrator generates — so the project chooses its own database driver rather than the CLI embedding one. breeze makemigration <name> writes the pair, converting CreateUsersTable to create_users_table with acronym runs kept together (AddHTTPCache → add_http_cache).