The migration lifecycle
On this page
On SQLite:
- the SQLite backend takes no lock, because SQLite lets one writer in at a time; the PostgreSQL variant shows locking
- 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 INTEGER 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.
SQLite lets one writer in at a time, so the SQLite backend takes no lock of its own. The PostgreSQL version of this example goes on to the locks that stop two instances migrating at once.
Using it
example.go
package main
import (
"context"
"database/sql"
"errors"
"fmt"
"io"
"io/fs"
"testing/fstest"
"seagull.sh/seagull/examples/sqlite/028_migration_lifecycle/internal/store"
"seagull.sh/seagull/sdk/migrate"
"seagull.sh/seagull/sdk/migrate/migrate_backend_sqlite"
)
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, and validation before and after an applied file is edited.
//
// 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_sqlite.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
}
return validate(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_sqlite.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_sqlite.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
}
// 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
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/sqlite && go run ./028_migration_lifecycle
Its test runs it too, and checks what it prints against output.txt:
cd seagull/examples/sqlite && go test ./028_migration_lifecycle
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: sqlite
migrations: db/migrations
queries: db/queries
go:
package: store
out: internal/store
db/migrations/0001_sightings.up.sql
CREATE TABLE sightings (
id INTEGER 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 (?, ?);
-- 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/sqlite/028_migration_lifecycle/internal/store"
"seagull.sh/seagull/sdk/migrate"
"seagull.sh/seagull/sdk/migrate/migrate_backend_sqlite"
)
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, and validation before and after an applied file is edited.
//
// 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_sqlite.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
}
return validate(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_sqlite.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_sqlite.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
}
// 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 opens a SQLite 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.
//
// Run it with `go run .`, or `go run . -db birdwatch.db` to keep the database; a second
// run against the same file starts from where the first one left it.
package main
import (
"context"
"database/sql"
"embed"
"flag"
"log"
"os"
"path/filepath"
_ "modernc.org/sqlite"
)
// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS
func main() {
path := flag.String("db", "", "SQLite database file; a temporary one when empty")
flag.Parse()
if err := execute(context.Background(), *path); err != nil {
log.Fatal(err)
}
}
// execute opens the database at path, or a temporary one when path is empty, and runs the
// example against it. The example migrates the database itself.
//
// Takes path (string) which is the database file, or empty.
//
// Returns error when the database cannot be opened or the example fails.
func execute(ctx context.Context, path string) error {
if path == "" {
directory, err := os.MkdirTemp("", "seagull-example-")
if err != nil {
return err
}
defer func() { _ = os.RemoveAll(directory) }()
path = filepath.Join(directory, "example.db")
}
database, err := sql.Open("sqlite", "file:"+path)
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 int64 `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