The migration lifecycle
On this page
On PostgreSQL:
- migrate_backend_postgres.New() holds a session-level advisory lock, pg_advisory_lock(hashtext('sgl_migrations')), for the whole run
- migrate_backend_postgres.NewPgBouncer() locks the single row of sgl_migration_lock with SELECT ... FOR UPDATE instead, as transaction pooling loses session locks
- WithNonBlockingLock makes a run fail at once with ErrLockNotAcquired instead of waiting; Up takes no lock when nothing is pending
- Down stops with a NoDownMigrationError at a migration with no .down.sql file
- Validate and Up return a ChecksumMismatchError when an applied file has changed, even by a comment
Up is the call a service makes every time it starts, as migrations at start-up shows. Running a database over months takes more than that: migrating only part of the way, rolling back a release, seeing what is applied, and noticing when someone has edited a file the database already ran. The migrator has a method for each, and this example walks a small birdwatching database through all of them.
The migrations
Three migrations. The first creates a table and the third adds a column; both have down files. The second tidies data and cannot be undone, because the original spelling is lost, so it has no down file at all.
db/migrations/0001_sightings.up.sql
CREATE TABLE sightings (
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
species TEXT NOT NULL,
place TEXT NOT NULL
);
db/migrations/0002_tidy_species.up.sql
UPDATE sightings SET species = lower(trim(species));
db/migrations/0003_sighting_count.up.sql
ALTER TABLE sightings ADD COLUMN birds INTEGER NOT NULL DEFAULT 1;
Status, UpTo and Up
The migrator is built as before, from the database, a backend and the embedded files. Status reads the files and the history table without changing anything, so it works even on a database that has never been migrated. For each migration it reports the version and name, whether it is applied, whether it has a down file, and, once it is applied, whether the file still matches what was run.
UpTo(ctx, 1) applies pending migrations up to and including version 1, and no further. The program records two sightings with untidy names at that point, then calls Up, which applies the rest: 0002 tidies the names it finds, and 0003 adds a column.
Down, and a migration that cannot go down
Down(ctx, 1) rolls back the newest applied migration by running its down file, so 0003's column goes. The next Down(ctx, 1) reaches 0002, which has no down file. Rather than skip it, the migrator stops with a *migrate.NoDownMigrationError naming the version, and leaves 0002 applied. A change that cannot be undone is rolled forward instead, with a new migration.
Checksums and Validate
When a migration is applied, its file's SHA-256 is recorded in the history. Validate compares every applied migration's recorded checksum with its file as it is now.
To show a mismatch, the program copies the files into memory and adds one comment line to the top of 0001, as if someone had tidied it after it shipped. Even that comment changes the checksum. Validate returns a *migrate.ChecksumMismatchError, Status marks the file as edited since it was applied, and Up refuses to run at all. The database no longer matches what the files say it should be, and the migrator will not guess which is right.
Locks
Two instances of a service starting together must not both migrate. migrate_backend_postgres.New() holds a session-level advisory lock for the whole run, keyed on the history table's name, so the second instance waits until the first has finished, then finds nothing left to do.
Waiting is not always what you want, for example in a deploy job that should fail fast. A migrator built with migrate.WithNonBlockingLock() gives up at once with migrate.ErrLockNotAcquired when the lock is taken. The program holds the advisory lock on a session of its own, standing in for another instance part-way through migrating, and Up is refused; once that session lets go, Up applies the pending migration.
Behind PgBouncer in transaction mode, each transaction may get a different server session, so a session lock would not stay with the migrator. migrate_backend_postgres.NewPgBouncer() locks the single row of a lock table with SELECT ... FOR UPDATE instead, creating the table the first time. The program holds that row in another transaction, and Up is refused again until the transaction commits.
Using it
example.go
package main
import (
"context"
"database/sql"
"errors"
"fmt"
"io"
"io/fs"
"testing/fstest"
"seagull.sh/seagull/examples/postgres/028_migration_lifecycle/internal/store"
"seagull.sh/seagull/sdk/migrate"
"seagull.sh/seagull/sdk/migrate/migrate_backend_postgres"
)
const (
// migrationsDirectory is where the migration files sit inside the file system.
migrationsDirectory = "db/migrations"
// editedFile is the applied migration the example edits to break its checksum.
editedFile = migrationsDirectory + "/0001_sightings.up.sql"
)
// run takes a database through a migration's life: status, up to a version, up, down, a
// down that cannot happen, validation before and after an applied file is edited, and the
// locks that stop two instances migrating at once.
//
// Takes database (*sql.DB) which is an empty database.
// Takes migrations (fs.FS) which holds db/migrations, the embed.FS in main.go.
// Takes output (io.Writer) which receives each step's result.
//
// Returns error when a step fails other than as the example expects.
func run(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.New(), migrations, migrationsDirectory)
if err != nil {
return err
}
// Status reads the files and the history without changing anything, so it works on a
// database that has never been migrated.
fmt.Fprintln(output, "before migrating:")
if err := printStatus(ctx, migrator, output); err != nil {
return err
}
// UpTo applies pending migrations up to and including a version.
applied, err := migrator.UpTo(ctx, 1)
if err != nil {
return fmt.Errorf("migrating to version 1: %w", err)
}
fmt.Fprintf(output, "UpTo(1) applied %d\n", applied)
if err := recordSightings(ctx, store.New(database)); err != nil {
return err
}
// Up applies the rest: 0002 tidies the names recorded above, 0003 adds a column.
applied, err = migrator.Up(ctx)
if err != nil {
return fmt.Errorf("migrating up: %w", err)
}
fmt.Fprintf(output, "Up applied %d\n", applied)
if err := printSightings(ctx, store.New(database), output); err != nil {
return err
}
// Down rolls back the newest applied migrations by running their down files.
rolledBack, err := migrator.Down(ctx, 1)
if err != nil {
return fmt.Errorf("rolling back: %w", err)
}
fmt.Fprintf(output, "Down(1) rolled back %d\n", rolledBack)
// The next one down, 0002, has no down file, so Down stops with an error and leaves it
// applied. Roll forward with a new migration instead.
_, err = migrator.Down(ctx, 1)
if err == nil {
return errors.New("rolling back 0002: want a NoDownMigrationError, got no error")
}
noDown, isNoDown := errors.AsType[*migrate.NoDownMigrationError](err)
if !isNoDown {
return fmt.Errorf("rolling back 0002: want a NoDownMigrationError, got %w", err)
}
fmt.Fprintf(output, "Down(1) refused: migration %d has no down file\n", noDown.Version)
fmt.Fprintln(output, "after rolling back:")
if err := printStatus(ctx, migrator, output); err != nil {
return err
}
if err := validate(ctx, database, migrations, output); err != nil {
return err
}
if err := advisoryLock(ctx, database, migrations, output); err != nil {
return err
}
return tableLock(ctx, database, migrations, output)
}
// validate checks the recorded checksums against the files, then edits an applied
// migration in a copy of the files and shows that Validate and Up both refuse it.
//
// Takes database (*sql.DB) which is the migrated database.
// Takes migrations (fs.FS) which holds the migration files.
// Takes output (io.Writer) which receives the results.
//
// Returns error when validation does not behave as the example expects.
func validate(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.New(), migrations, migrationsDirectory)
if err != nil {
return err
}
if err := migrator.Validate(ctx); err != nil {
return fmt.Errorf("validating: %w", err)
}
fmt.Fprintln(output, "Validate: every applied migration matches its file")
// Each applied migration's SHA-256 is recorded in the history. Changing an applied file,
// even only a comment, makes Validate and Up fail: the database no longer matches what
// the files say it should be.
edited, err := editedCopy(migrations, editedFile, "-- tidied up after it was applied\n")
if err != nil {
return err
}
drifted, err := migrate.NewMigrator(database, migrate_backend_postgres.New(), edited, migrationsDirectory)
if err != nil {
return err
}
err = drifted.Validate(ctx)
if err == nil {
return errors.New("validating the edited files: want a ChecksumMismatchError, got no error")
}
mismatch, isMismatch := errors.AsType[*migrate.ChecksumMismatchError](err)
if !isMismatch {
return fmt.Errorf("validating the edited files: want a ChecksumMismatchError, got %w", err)
}
fmt.Fprintf(output, "Validate after editing %04d_%s: checksum mismatch\n", mismatch.Version, mismatch.Name)
if err := printStatus(ctx, drifted, output); err != nil {
return err
}
_, err = drifted.Up(ctx)
if err == nil {
return errors.New("migrating the edited files: want a ChecksumMismatchError, got no error")
}
if _, isMismatch := errors.AsType[*migrate.ChecksumMismatchError](err); !isMismatch {
return fmt.Errorf("migrating the edited files: want a ChecksumMismatchError, got %w", err)
}
fmt.Fprintln(output, "Up after editing: refused for the same reason")
return nil
}
// advisoryLock shows the lock migrate_backend_postgres.New() takes: a session-level
// advisory lock keyed on the history table's name, held for the whole run. A migrator
// built with WithNonBlockingLock gives up at once with ErrLockNotAcquired when another
// session holds it, instead of waiting its turn.
//
// Takes database (*sql.DB) which is the migrated database.
// Takes migrations (fs.FS) which holds the migration files.
// Takes output (io.Writer) which receives the results.
//
// Returns error when the lock does not behave as the example expects.
func advisoryLock(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
backend := migrate_backend_postgres.New()
key := backend.Names().LockKey
migrator, err := migrate.NewMigrator(database, backend, migrations, migrationsDirectory, migrate.WithNonBlockingLock())
if err != nil {
return err
}
// A session of its own plays another instance of the program, part-way through
// migrating. The migrator hashes the key with hashtext, so this takes the same lock.
other, err := database.Conn(ctx)
if err != nil {
return err
}
defer func() { _ = other.Close() }()
if _, err := other.ExecContext(ctx, "SELECT pg_advisory_lock(hashtext($1))", key); err != nil {
return fmt.Errorf("taking the advisory lock: %w", err)
}
fmt.Fprintf(output, "another session holds the advisory lock on %s\n", key)
_, err = migrator.Up(ctx)
if err == nil {
return errors.New("migrating while the lock is held: want ErrLockNotAcquired, got no error")
}
if !errors.Is(err, migrate.ErrLockNotAcquired) {
return fmt.Errorf("migrating while the lock is held: want ErrLockNotAcquired, got %w", err)
}
fmt.Fprintf(output, " Up with WithNonBlockingLock: refused, %v\n", migrate.ErrLockNotAcquired)
if _, err := other.ExecContext(ctx, "SELECT pg_advisory_unlock(hashtext($1))", key); err != nil {
return fmt.Errorf("releasing the advisory lock: %w", err)
}
applied, err := migrator.Up(ctx)
if err != nil {
return fmt.Errorf("migrating once the lock is free: %w", err)
}
fmt.Fprintf(output, " Up once it is released: applied %d\n", applied)
return nil
}
// tableLock shows the lock migrate_backend_postgres.NewPgBouncer() takes instead.
// PgBouncer in transaction mode hands each transaction whichever server session is free,
// so a session-level advisory lock would not stay with the migrator; this backend locks
// the single row of a lock table with SELECT ... FOR UPDATE, and creates the table the
// first time it takes the lock.
//
// Takes database (*sql.DB) which is the migrated database.
// Takes migrations (fs.FS) which holds the migration files.
// Takes output (io.Writer) which receives the results.
//
// Returns error when the lock does not behave as the example expects.
func tableLock(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
backend := migrate_backend_postgres.NewPgBouncer()
lockTable := backend.Names().MigrationLock
migrator, err := migrate.NewMigrator(database, backend, migrations, migrationsDirectory, migrate.WithNonBlockingLock())
if err != nil {
return err
}
fmt.Fprintf(output, "PostgresPgBouncer locks the row in %s instead\n", lockTable)
rolledBack, err := migrator.Down(ctx, 1)
if err != nil {
return fmt.Errorf("rolling back under the table lock: %w", err)
}
fmt.Fprintf(output, " Down(1) rolled back %d\n", rolledBack)
// Another instance's transaction holds the row until it ends.
other, err := database.BeginTx(ctx, nil)
if err != nil {
return err
}
defer func() { _ = other.Rollback() }()
// A table name cannot be a bound parameter, so the lock table's name, which comes from
// the backend and not from input, is written into the statement.
if _, err := other.ExecContext(ctx, "SELECT lock_id FROM "+lockTable+" FOR UPDATE"); err != nil { //nolint:gosec // G202: lockTable is the backend's own table name, not input
return fmt.Errorf("locking %s: %w", lockTable, err)
}
fmt.Fprintln(output, "another transaction holds the row")
_, err = migrator.Up(ctx)
if err == nil {
return errors.New("migrating while the row is locked: want ErrLockNotAcquired, got no error")
}
if !errors.Is(err, migrate.ErrLockNotAcquired) {
return fmt.Errorf("migrating while the row is locked: want ErrLockNotAcquired, got %w", err)
}
fmt.Fprintf(output, " Up with WithNonBlockingLock: refused, %v\n", migrate.ErrLockNotAcquired)
if err := other.Commit(); err != nil {
return err
}
applied, err := migrator.Up(ctx)
if err != nil {
return fmt.Errorf("migrating once the row is free: %w", err)
}
fmt.Fprintf(output, " Up once it commits: applied %d\n", applied)
return nil
}
// printStatus writes one line per migration: whether it is applied, whether an applied
// file still matches its recorded checksum, and whether it has a down file.
//
// Takes migrator (*migrate.Migrator) which reads the status.
// Takes output (io.Writer) which receives the table.
//
// Returns error when the status cannot be read.
func printStatus(ctx context.Context, migrator *migrate.Migrator, output io.Writer) error {
statuses, err := migrator.Status(ctx)
if err != nil {
return fmt.Errorf("reading status: %w", err)
}
for _, status := range statuses {
state := "pending"
if status.Applied {
state = "applied"
}
down := "no down file"
if status.HasDownMigration {
down = "down file"
}
edited := ""
if status.Applied && !status.ChecksumMatch {
edited = ", edited since it was applied"
}
fmt.Fprintf(output, " %04d %-15s %-8s %s%s\n", status.Version, status.Name, state, down, edited)
}
return nil
}
// recordSightings adds two sightings whose species names need tidying.
//
// Takes queries (*store.Queries) which add the rows.
//
// Returns error when a row cannot be added.
func recordSightings(ctx context.Context, queries *store.Queries) error {
sightings := []store.RecordSightingParams{
{Species: " Herring Gull", Place: "the pier"},
{Species: "KITTIWAKE ", Place: "the harbour wall"},
}
for _, sighting := range sightings {
if err := queries.RecordSighting(ctx, sighting); err != nil {
return fmt.Errorf("recording a sighting: %w", err)
}
}
return nil
}
// printSightings writes every sighting, quoting the species to show its spacing.
//
// Takes queries (*store.Queries) which read the rows.
// Takes output (io.Writer) which receives them.
//
// Returns error when the rows cannot be read.
func printSightings(ctx context.Context, queries *store.Queries, output io.Writer) error {
sightings, err := queries.ListSightings(ctx)
if err != nil {
return fmt.Errorf("listing sightings: %w", err)
}
for _, sighting := range sightings {
fmt.Fprintf(output, " sighting %d: %q at %s\n", sighting.ID, sighting.Species, sighting.Place)
}
return nil
}
// editedCopy copies every file in files into memory and puts a line at the top of one of
// them, as if someone had edited it.
//
// Takes files (fs.FS) which holds the originals.
// Takes name (string) which is the path of the file to edit.
// Takes line (string) which is put at the top of it.
//
// Returns fstest.MapFS which holds the copy.
// Returns error when a file cannot be read or name is missing.
func editedCopy(files fs.FS, name, line string) (fstest.MapFS, error) {
copied := fstest.MapFS{}
err := fs.WalkDir(files, ".", func(path string, entry fs.DirEntry, err error) error {
if err != nil || entry.IsDir() {
return err
}
data, err := fs.ReadFile(files, path)
if err != nil {
return err
}
copied[path] = &fstest.MapFile{Data: data}
return nil
})
if err != nil {
return nil, err
}
original, ok := copied[name]
if !ok {
return nil, fmt.Errorf("no file %s to edit", name)
}
original.Data = append([]byte(line), original.Data...)
return copied, nil
}
What it prints
The status table is printed three times: before anything runs, after the rollbacks (0003 pending again, 0002 still applied), and with the edited copy of the files.
before migrating:
0001 sightings pending down file
0002 tidy_species pending no down file
0003 sighting_count pending down file
UpTo(1) applied 1
Up applied 2
sighting 1: "herring gull" at the pier
sighting 2: "kittiwake" at the harbour wall
Down(1) rolled back 1
Down(1) refused: migration 2 has no down file
after rolling back:
0001 sightings applied down file
0002 tidy_species applied no down file
0003 sighting_count pending down file
Validate: every applied migration matches its file
Validate after editing 0001_sightings: checksum mismatch
0001 sightings applied down file, edited since it was applied
0002 tidy_species applied no down file
0003 sighting_count pending down file
Up after editing: refused for the same reason
another session holds the advisory lock on sgl_migrations
Up with WithNonBlockingLock: refused, migration lock is already held
Up once it is released: applied 1
PostgresPgBouncer locks the row in sgl_migration_lock instead
Down(1) rolled back 1
another transaction holds the row
Up with WithNonBlockingLock: refused, migration lock is already held
Up once it commits: applied 1
Try it
Change migrator.UpTo(ctx, 1) to migrator.UpTo(ctx, 2) and run the example again. The tidying migration now runs before the sightings are recorded, so it finds nothing to tidy, and the names come out with their stray spaces and capitals. A data migration changes only the rows that exist when it runs, which is why the order of UpTo, your writes and Up matters. (The line still says UpTo(1), because the label is part of the Fprintf.)
The same operations are available from the command line, as migrating from the CLI shows.
Run it locally
From the directory you cloned the Seagull repository into:
cd seagull/examples/postgres && go run ./028_migration_lifecycle
Its test runs it too, and checks what it prints against output.txt:
cd seagull/examples/postgres && SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./028_migration_lifecycle
Needs a running PostgreSQL server: set DATABASE_URL, or pass -dsn, to the address of an empty database.
The files
Every file of the example, as it is in the Seagull repository.
sgl.yaml
# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.
version: 1
databases:
- name: birdwatch
dialect: postgres
migrations: db/migrations
queries: db/queries
go:
package: store
out: internal/store
driver: database/sql
migrate:
dsn: ${env:DATABASE_URL}
db/migrations/0001_sightings.up.sql
CREATE TABLE sightings (
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
species TEXT NOT NULL,
place TEXT NOT NULL
);
db/migrations/0002_tidy_species.up.sql
UPDATE sightings SET species = lower(trim(species));
db/migrations/0003_sighting_count.up.sql
ALTER TABLE sightings ADD COLUMN birds INTEGER NOT NULL DEFAULT 1;
db/migrations/0001_sightings.down.sql
DROP TABLE sightings;
db/migrations/0003_sighting_count.down.sql
ALTER TABLE sightings DROP COLUMN birds;
db/queries/sightings.sql
-- sgl.query(RecordSighting, exec)
INSERT INTO sightings (species, place) VALUES ($1, $2);
-- sgl.query(ListSightings, many)
SELECT id, species, place FROM sightings ORDER BY id;
example.go
package main
import (
"context"
"database/sql"
"errors"
"fmt"
"io"
"io/fs"
"testing/fstest"
"seagull.sh/seagull/examples/postgres/028_migration_lifecycle/internal/store"
"seagull.sh/seagull/sdk/migrate"
"seagull.sh/seagull/sdk/migrate/migrate_backend_postgres"
)
const (
// migrationsDirectory is where the migration files sit inside the file system.
migrationsDirectory = "db/migrations"
// editedFile is the applied migration the example edits to break its checksum.
editedFile = migrationsDirectory + "/0001_sightings.up.sql"
)
// run takes a database through a migration's life: status, up to a version, up, down, a
// down that cannot happen, validation before and after an applied file is edited, and the
// locks that stop two instances migrating at once.
//
// Takes database (*sql.DB) which is an empty database.
// Takes migrations (fs.FS) which holds db/migrations, the embed.FS in main.go.
// Takes output (io.Writer) which receives each step's result.
//
// Returns error when a step fails other than as the example expects.
func run(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.New(), migrations, migrationsDirectory)
if err != nil {
return err
}
// Status reads the files and the history without changing anything, so it works on a
// database that has never been migrated.
fmt.Fprintln(output, "before migrating:")
if err := printStatus(ctx, migrator, output); err != nil {
return err
}
// UpTo applies pending migrations up to and including a version.
applied, err := migrator.UpTo(ctx, 1)
if err != nil {
return fmt.Errorf("migrating to version 1: %w", err)
}
fmt.Fprintf(output, "UpTo(1) applied %d\n", applied)
if err := recordSightings(ctx, store.New(database)); err != nil {
return err
}
// Up applies the rest: 0002 tidies the names recorded above, 0003 adds a column.
applied, err = migrator.Up(ctx)
if err != nil {
return fmt.Errorf("migrating up: %w", err)
}
fmt.Fprintf(output, "Up applied %d\n", applied)
if err := printSightings(ctx, store.New(database), output); err != nil {
return err
}
// Down rolls back the newest applied migrations by running their down files.
rolledBack, err := migrator.Down(ctx, 1)
if err != nil {
return fmt.Errorf("rolling back: %w", err)
}
fmt.Fprintf(output, "Down(1) rolled back %d\n", rolledBack)
// The next one down, 0002, has no down file, so Down stops with an error and leaves it
// applied. Roll forward with a new migration instead.
_, err = migrator.Down(ctx, 1)
if err == nil {
return errors.New("rolling back 0002: want a NoDownMigrationError, got no error")
}
noDown, isNoDown := errors.AsType[*migrate.NoDownMigrationError](err)
if !isNoDown {
return fmt.Errorf("rolling back 0002: want a NoDownMigrationError, got %w", err)
}
fmt.Fprintf(output, "Down(1) refused: migration %d has no down file\n", noDown.Version)
fmt.Fprintln(output, "after rolling back:")
if err := printStatus(ctx, migrator, output); err != nil {
return err
}
if err := validate(ctx, database, migrations, output); err != nil {
return err
}
if err := advisoryLock(ctx, database, migrations, output); err != nil {
return err
}
return tableLock(ctx, database, migrations, output)
}
// validate checks the recorded checksums against the files, then edits an applied
// migration in a copy of the files and shows that Validate and Up both refuse it.
//
// Takes database (*sql.DB) which is the migrated database.
// Takes migrations (fs.FS) which holds the migration files.
// Takes output (io.Writer) which receives the results.
//
// Returns error when validation does not behave as the example expects.
func validate(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.New(), migrations, migrationsDirectory)
if err != nil {
return err
}
if err := migrator.Validate(ctx); err != nil {
return fmt.Errorf("validating: %w", err)
}
fmt.Fprintln(output, "Validate: every applied migration matches its file")
// Each applied migration's SHA-256 is recorded in the history. Changing an applied file,
// even only a comment, makes Validate and Up fail: the database no longer matches what
// the files say it should be.
edited, err := editedCopy(migrations, editedFile, "-- tidied up after it was applied\n")
if err != nil {
return err
}
drifted, err := migrate.NewMigrator(database, migrate_backend_postgres.New(), edited, migrationsDirectory)
if err != nil {
return err
}
err = drifted.Validate(ctx)
if err == nil {
return errors.New("validating the edited files: want a ChecksumMismatchError, got no error")
}
mismatch, isMismatch := errors.AsType[*migrate.ChecksumMismatchError](err)
if !isMismatch {
return fmt.Errorf("validating the edited files: want a ChecksumMismatchError, got %w", err)
}
fmt.Fprintf(output, "Validate after editing %04d_%s: checksum mismatch\n", mismatch.Version, mismatch.Name)
if err := printStatus(ctx, drifted, output); err != nil {
return err
}
_, err = drifted.Up(ctx)
if err == nil {
return errors.New("migrating the edited files: want a ChecksumMismatchError, got no error")
}
if _, isMismatch := errors.AsType[*migrate.ChecksumMismatchError](err); !isMismatch {
return fmt.Errorf("migrating the edited files: want a ChecksumMismatchError, got %w", err)
}
fmt.Fprintln(output, "Up after editing: refused for the same reason")
return nil
}
// advisoryLock shows the lock migrate_backend_postgres.New() takes: a session-level
// advisory lock keyed on the history table's name, held for the whole run. A migrator
// built with WithNonBlockingLock gives up at once with ErrLockNotAcquired when another
// session holds it, instead of waiting its turn.
//
// Takes database (*sql.DB) which is the migrated database.
// Takes migrations (fs.FS) which holds the migration files.
// Takes output (io.Writer) which receives the results.
//
// Returns error when the lock does not behave as the example expects.
func advisoryLock(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
backend := migrate_backend_postgres.New()
key := backend.Names().LockKey
migrator, err := migrate.NewMigrator(database, backend, migrations, migrationsDirectory, migrate.WithNonBlockingLock())
if err != nil {
return err
}
// A session of its own plays another instance of the program, part-way through
// migrating. The migrator hashes the key with hashtext, so this takes the same lock.
other, err := database.Conn(ctx)
if err != nil {
return err
}
defer func() { _ = other.Close() }()
if _, err := other.ExecContext(ctx, "SELECT pg_advisory_lock(hashtext($1))", key); err != nil {
return fmt.Errorf("taking the advisory lock: %w", err)
}
fmt.Fprintf(output, "another session holds the advisory lock on %s\n", key)
_, err = migrator.Up(ctx)
if err == nil {
return errors.New("migrating while the lock is held: want ErrLockNotAcquired, got no error")
}
if !errors.Is(err, migrate.ErrLockNotAcquired) {
return fmt.Errorf("migrating while the lock is held: want ErrLockNotAcquired, got %w", err)
}
fmt.Fprintf(output, " Up with WithNonBlockingLock: refused, %v\n", migrate.ErrLockNotAcquired)
if _, err := other.ExecContext(ctx, "SELECT pg_advisory_unlock(hashtext($1))", key); err != nil {
return fmt.Errorf("releasing the advisory lock: %w", err)
}
applied, err := migrator.Up(ctx)
if err != nil {
return fmt.Errorf("migrating once the lock is free: %w", err)
}
fmt.Fprintf(output, " Up once it is released: applied %d\n", applied)
return nil
}
// tableLock shows the lock migrate_backend_postgres.NewPgBouncer() takes instead.
// PgBouncer in transaction mode hands each transaction whichever server session is free,
// so a session-level advisory lock would not stay with the migrator; this backend locks
// the single row of a lock table with SELECT ... FOR UPDATE, and creates the table the
// first time it takes the lock.
//
// Takes database (*sql.DB) which is the migrated database.
// Takes migrations (fs.FS) which holds the migration files.
// Takes output (io.Writer) which receives the results.
//
// Returns error when the lock does not behave as the example expects.
func tableLock(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
backend := migrate_backend_postgres.NewPgBouncer()
lockTable := backend.Names().MigrationLock
migrator, err := migrate.NewMigrator(database, backend, migrations, migrationsDirectory, migrate.WithNonBlockingLock())
if err != nil {
return err
}
fmt.Fprintf(output, "PostgresPgBouncer locks the row in %s instead\n", lockTable)
rolledBack, err := migrator.Down(ctx, 1)
if err != nil {
return fmt.Errorf("rolling back under the table lock: %w", err)
}
fmt.Fprintf(output, " Down(1) rolled back %d\n", rolledBack)
// Another instance's transaction holds the row until it ends.
other, err := database.BeginTx(ctx, nil)
if err != nil {
return err
}
defer func() { _ = other.Rollback() }()
// A table name cannot be a bound parameter, so the lock table's name, which comes from
// the backend and not from input, is written into the statement.
if _, err := other.ExecContext(ctx, "SELECT lock_id FROM "+lockTable+" FOR UPDATE"); err != nil { //nolint:gosec // G202: lockTable is the backend's own table name, not input
return fmt.Errorf("locking %s: %w", lockTable, err)
}
fmt.Fprintln(output, "another transaction holds the row")
_, err = migrator.Up(ctx)
if err == nil {
return errors.New("migrating while the row is locked: want ErrLockNotAcquired, got no error")
}
if !errors.Is(err, migrate.ErrLockNotAcquired) {
return fmt.Errorf("migrating while the row is locked: want ErrLockNotAcquired, got %w", err)
}
fmt.Fprintf(output, " Up with WithNonBlockingLock: refused, %v\n", migrate.ErrLockNotAcquired)
if err := other.Commit(); err != nil {
return err
}
applied, err := migrator.Up(ctx)
if err != nil {
return fmt.Errorf("migrating once the row is free: %w", err)
}
fmt.Fprintf(output, " Up once it commits: applied %d\n", applied)
return nil
}
// printStatus writes one line per migration: whether it is applied, whether an applied
// file still matches its recorded checksum, and whether it has a down file.
//
// Takes migrator (*migrate.Migrator) which reads the status.
// Takes output (io.Writer) which receives the table.
//
// Returns error when the status cannot be read.
func printStatus(ctx context.Context, migrator *migrate.Migrator, output io.Writer) error {
statuses, err := migrator.Status(ctx)
if err != nil {
return fmt.Errorf("reading status: %w", err)
}
for _, status := range statuses {
state := "pending"
if status.Applied {
state = "applied"
}
down := "no down file"
if status.HasDownMigration {
down = "down file"
}
edited := ""
if status.Applied && !status.ChecksumMatch {
edited = ", edited since it was applied"
}
fmt.Fprintf(output, " %04d %-15s %-8s %s%s\n", status.Version, status.Name, state, down, edited)
}
return nil
}
// recordSightings adds two sightings whose species names need tidying.
//
// Takes queries (*store.Queries) which add the rows.
//
// Returns error when a row cannot be added.
func recordSightings(ctx context.Context, queries *store.Queries) error {
sightings := []store.RecordSightingParams{
{Species: " Herring Gull", Place: "the pier"},
{Species: "KITTIWAKE ", Place: "the harbour wall"},
}
for _, sighting := range sightings {
if err := queries.RecordSighting(ctx, sighting); err != nil {
return fmt.Errorf("recording a sighting: %w", err)
}
}
return nil
}
// printSightings writes every sighting, quoting the species to show its spacing.
//
// Takes queries (*store.Queries) which read the rows.
// Takes output (io.Writer) which receives them.
//
// Returns error when the rows cannot be read.
func printSightings(ctx context.Context, queries *store.Queries, output io.Writer) error {
sightings, err := queries.ListSightings(ctx)
if err != nil {
return fmt.Errorf("listing sightings: %w", err)
}
for _, sighting := range sightings {
fmt.Fprintf(output, " sighting %d: %q at %s\n", sighting.ID, sighting.Species, sighting.Place)
}
return nil
}
// editedCopy copies every file in files into memory and puts a line at the top of one of
// them, as if someone had edited it.
//
// Takes files (fs.FS) which holds the originals.
// Takes name (string) which is the path of the file to edit.
// Takes line (string) which is put at the top of it.
//
// Returns fstest.MapFS which holds the copy.
// Returns error when a file cannot be read or name is missing.
func editedCopy(files fs.FS, name, line string) (fstest.MapFS, error) {
copied := fstest.MapFS{}
err := fs.WalkDir(files, ".", func(path string, entry fs.DirEntry, err error) error {
if err != nil || entry.IsDir() {
return err
}
data, err := fs.ReadFile(files, path)
if err != nil {
return err
}
copied[path] = &fstest.MapFile{Data: data}
return nil
})
if err != nil {
return nil, err
}
original, ok := copied[name]
if !ok {
return nil, fmt.Errorf("no file %s to edit", name)
}
original.Data = append([]byte(line), original.Data...)
return copied, nil
}
main.go
// Command 028_migration_lifecycle connects to a PostgreSQL database and runs the example
// in example.go, which takes the embedded migrations through UpTo, Up, Down, Status and
// Validate, including a migration with no down file and an applied file edited later, and
// then shows the advisory and table locks that keep two instances from migrating at once.
//
// Run it with `go run . -dsn postgres://user:password@localhost:5432/example`, or set
// DATABASE_URL instead of passing -dsn. Give it an empty database of its own; a second
// run against the same database starts from where the first one left it.
package main
import (
"context"
"database/sql"
"embed"
"errors"
"flag"
"log"
"os"
_ "github.com/jackc/pgx/v5/stdlib"
)
// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS
func main() {
dsn := flag.String("dsn", os.Getenv("DATABASE_URL"), "PostgreSQL connection string; defaults to $DATABASE_URL")
flag.Parse()
if err := execute(context.Background(), *dsn); err != nil {
log.Fatal(err)
}
}
// execute connects to the database at dsn and runs the example against it. The example
// migrates the database itself.
//
// Takes dsn (string) which is the PostgreSQL connection string.
//
// Returns error when the database cannot be opened or the example fails.
func execute(ctx context.Context, dsn string) error {
if dsn == "" {
return errors.New("set -dsn or DATABASE_URL to a PostgreSQL connection string")
}
database, err := sql.Open("pgx", dsn)
if err != nil {
return err
}
defer func() { _ = database.Close() }()
return run(ctx, database, migrations, os.Stdout)
}
internal/store/models.go
// Code generated by sgl. DO NOT EDIT.
package store
type Sightings struct {
ID int64 `json:"id"`
Species string `json:"species"`
Place string `json:"place"`
Birds int32 `json:"birds"`
}
internal/store/querier.go
// Code generated by sgl. DO NOT EDIT.
package store
import (
"context"
"database/sql"
)
type DBTX interface {
ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}
type dbtxWrapper interface {
WrapDBTX(inner any) any
}
type Queries struct {
reader DBTX
writer DBTX
wrapper dbtxWrapper
}
func New(db DBTX) *Queries {
wrapper, _ := db.(dbtxWrapper)
return &Queries{reader: db, writer: db, wrapper: wrapper}
}
func NewWithReplica(writer DBTX, reader DBTX) *Queries {
wrapper, _ := writer.(dbtxWrapper)
return &Queries{reader: reader, writer: writer, wrapper: wrapper}
}
func (queries *Queries) WithTx(transaction *sql.Tx) *Queries {
if queries == nil {
return &Queries{reader: transaction, writer: transaction, wrapper: nil}
}
db := queries.wrapDBTX(transaction)
return &Queries{reader: db, writer: db, wrapper: queries.wrapper}
}
func (queries *Queries) wrapDBTX(db DBTX) DBTX {
if queries.wrapper == nil {
return db
}
wrapped, ok := queries.wrapper.WrapDBTX(db).(DBTX)
if !ok {
return db
}
return wrapped
}
func (queries *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error {
transaction, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer transaction.Rollback()
if err = fn(queries.WithTx(transaction)); err != nil {
return err
}
return transaction.Commit()
}
internal/store/sightings.sql.go
// Code generated by sgl. DO NOT EDIT.
package store
import "context"
const listSightingsSQL = `SELECT id, species, place FROM sightings ORDER BY id;`
type ListSightingsRow struct {
ID int64 `json:"id"`
Species string `json:"species"`
Place string `json:"place"`
}
func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error) {
rows, err := queries.reader.QueryContext(ctx, listSightingsSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []ListSightingsRow
if rows.Next() {
var row ListSightingsRow
scanArgs := []any{&row.ID, &row.Species, &row.Place}
for more := true; more; more = rows.Next() {
row = ListSightingsRow{}
if err := rows.Scan(scanArgs...); err != nil {
return nil, err
}
results = append(results, row)
}
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const recordSightingSQL = `INSERT INTO sightings (species, place) VALUES ($1, $2);`
type RecordSightingParams struct {
Species string
Place string
}
func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) error {
_, err := queries.writer.ExecContext(ctx, recordSightingSQL, params.Species, params.Place)
return err
}
output.txt
before migrating:
0001 sightings pending down file
0002 tidy_species pending no down file
0003 sighting_count pending down file
UpTo(1) applied 1
Up applied 2
sighting 1: "herring gull" at the pier
sighting 2: "kittiwake" at the harbour wall
Down(1) rolled back 1
Down(1) refused: migration 2 has no down file
after rolling back:
0001 sightings applied down file
0002 tidy_species applied no down file
0003 sighting_count pending down file
Validate: every applied migration matches its file
Validate after editing 0001_sightings: checksum mismatch
0001 sightings applied down file, edited since it was applied
0002 tidy_species applied no down file
0003 sighting_count pending down file
Up after editing: refused for the same reason
another session holds the advisory lock on sgl_migrations
Up with WithNonBlockingLock: refused, migration lock is already held
Up once it is released: applied 1
PostgresPgBouncer locks the row in sgl_migration_lock instead
Down(1) rolled back 1
another transaction holds the row
Up with WithNonBlockingLock: refused, migration lock is already held
Up once it commits: applied 1