Seeds, namespaces and hooks

On this page

On SQLite:

  • SQLite cannot change journal_mode inside a transaction, so that migration carries sgl.migration(no_transaction: true)
  • SQLite has no CREATE INDEX CONCURRENTLY; VACUUM is the statement Seagull detects as non-transactional without a directive
  • the seed file runs whole, since modernc.org/sqlite accepts several statements in one call

Beyond applying migrations, the migrator has a few features for real deployments. A namespace keeps one program's migration history apart from another's in the same database. Hooks run your own code before a run and after each migration. Some statements cannot run inside a transaction, and a directive tells the migrator so. A seeder applies data files once each, so reference data can ship with the program. The example sets up the menu of a chip shop with all four.

Namespaces

The migrator records what it has applied in a history table, and the seeder in a seed table. Both are named from a namespace, sgl by default. migrate.WithNamespace("chippy") renames them to chippy_migrations and chippy_seeds, so several programs, or several modules of one program, can each keep their own history in one database without seeing each other's.

sgl.yaml sets migrate.namespace to match, so sgl migrate and sgl seed on the command line use the same tables as the program:

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: chippy
    dialect: sqlite
    migrations: db/migrations
    queries: db/queries
    seeds: db/seeds
    go:
      package: store
      out: internal/store
    migrate:
      namespace: chippy

To show the histories are separate, the program also reads the status of the same files against the default namespace's sgl_migrations, and finds nothing applied there.

Hooks

Hooks are options to NewMigrator. migrate.WithBeforeRun is called once before a run with the pending versions, and migrate.WithAfterMigration after each migration it applies. The example only prints from them, but a hook could record metrics, notify someone, or check a precondition: returning an error from a hook stops the run.

A migration outside a transaction

Each migration normally runs in a transaction, so it applies whole or not at all. Some statements refuse to run inside one.

SQLite will not change its journal mode inside a transaction, so the migration that switches to write-ahead logging carries sgl.migration(no_transaction: true):

db/migrations/0002_write_ahead_log.up.sql

-- sgl.migration(no_transaction: true)
PRAGMA journal_mode = WAL;

Seagull recognises some such statements by itself, such as VACUUM; PRAGMA journal_mode needs the directive. Its down file carries the directive too, since switching back has the same restriction.

Keep a statement like this in a migration of its own. If it shares a file with other statements, the whole file has to run without a transaction and is no longer atomic, and sgl vet warns with Q043. The migrations guide has more.

Seeds

Seeds are .sql files of data, in the directory seeds: names in sgl.yaml. migrate.NewSeeder is built like a migrator, and Apply runs each seed file that has not been applied, records it in the seed table, and returns how many it ran. Calling it at every start-up is safe: the second call finds nothing to do.

db/seeds/0001_menu.sql

INSERT INTO menu (dish, price_pence) VALUES
    ('cod and chips', 950),
    ('haddock and chips', 1020),
    ('mushy peas', 250)
ON CONFLICT (dish) DO NOTHING;

The file runs whole, since the driver accepts several statements in one call. It also says ON CONFLICT (dish) DO NOTHING, which keeps it harmless if the seed history is ever cleared and it runs again.

Using it

example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"
	"io/fs"

	"seagull.sh/seagull/examples/sqlite/029_seeds_namespaces_hooks/internal/store"
	"seagull.sh/seagull/sdk/migrate"
	"seagull.sh/seagull/sdk/migrate/migrate_backend_sqlite"
)

const (
	// namespace prefixes the tables that record this program's migrations and seeds.
	namespace = "chippy"
)

// run migrates the database under its own namespace with hooks that report progress,
// applies the seed files twice to show they run once, and prints the seeded menu.
//
// Takes database (*sql.DB) which is an empty database.
// Takes files (fs.FS) which holds db/migrations and db/seeds, the embed.FS in main.go.
// Takes output (io.Writer) which receives each step's result.
//
// Returns error when migrating, seeding or a query fails.
func run(ctx context.Context, database *sql.DB, files fs.FS, output io.Writer) error {
	// WithNamespace renames every table the migrator and seeder keep, so several programs or
	// modules can each keep their own history in one database. sgl.yaml sets
	// migrate.namespace to match, so `sgl migrate` uses the same tables.
	backend := migrate_backend_sqlite.New(migrate.WithNamespace(namespace))
	names := backend.Names()
	fmt.Fprintf(output, "history tables: %s and %s\n", names.History, names.Seeds)

	// Hooks run around the migrations: before the run with the pending versions, and after
	// each migration. Returning an error from a hook stops the run.
	migrator, err := migrate.NewMigrator(database, backend, files, "db/migrations",
		migrate.WithBeforeRun(func(_ context.Context, pending migrate.MigrationRunHookContext) error {
			fmt.Fprintf(output, "migrating: %d pending, versions %v\n", pending.PendingCount, pending.PendingVersions)
			return nil
		}),
		migrate.WithAfterMigration(func(_ context.Context, migration migrate.MigrationHookContext) error {
			fmt.Fprintf(output, "  applied %04d %s\n", migration.Version, migration.Name)
			return nil
		}),
	)
	if err != nil {
		return err
	}
	if _, err := migrator.Up(ctx); err != nil {
		return fmt.Errorf("migrating: %w", err)
	}

	// Migration 0002 ran outside a transaction, as its sgl.migration directive asks.
	var journalMode string
	if err := database.QueryRowContext(ctx, "PRAGMA journal_mode").Scan(&journalMode); err != nil {
		return fmt.Errorf("reading the journal mode: %w", err)
	}
	fmt.Fprintf(output, "journal mode: %s\n", journalMode)

	// The default namespace is a separate history in the same database, and it has recorded
	// nothing.
	if err := printOtherHistory(ctx, database, files, output); err != nil {
		return err
	}

	// A Seeder applies each seed file once and records it in the seed table, so calling
	// Apply at every start-up is safe. The seed file also says ON CONFLICT DO NOTHING, which
	// keeps it harmless if the seed history is ever cleared.
	seeder, err := migrate.NewSeeder(database, backend, files, "db/seeds")
	if err != nil {
		return err
	}
	for range 2 {
		seeded, err := seeder.Apply(ctx)
		if err != nil {
			return fmt.Errorf("seeding: %w", err)
		}
		fmt.Fprintf(output, "seeds applied: %d\n", seeded)
	}

	menu, err := store.New(database).ListMenu(ctx)
	if err != nil {
		return fmt.Errorf("listing the menu: %w", err)
	}
	for _, dish := range menu {
		fmt.Fprintf(output, "  %-17s %dp\n", dish.Dish, dish.PricePence)
	}
	return nil
}

// printOtherHistory reads the same migration files against the default namespace's
// history, which this program never wrote to.
//
// Takes database (*sql.DB) which is the migrated database.
// Takes files (fs.FS) which holds the migration files.
// Takes output (io.Writer) which receives the count.
//
// Returns error when the status cannot be read.
func printOtherHistory(ctx context.Context, database *sql.DB, files fs.FS, output io.Writer) error {
	backend := migrate_backend_sqlite.New()
	migrator, err := migrate.NewMigrator(database, backend, files, "db/migrations")
	if err != nil {
		return err
	}
	statuses, err := migrator.Status(ctx)
	if err != nil {
		return fmt.Errorf("reading the default history: %w", err)
	}
	applied := 0
	for _, status := range statuses {
		if status.Applied {
			applied++
		}
	}
	fmt.Fprintf(output, "%s (default namespace): %d of %d migrations applied\n", backend.Names().History, applied, len(statuses))
	return nil
}

What it prints

The hooks report the two pending migrations and each one as it is applied, and the journal mode reads wal afterwards. The default namespace has recorded nothing, and the second Apply runs no seeds.

history tables: chippy_migrations and chippy_seeds
migrating: 2 pending, versions [1 2]
  applied 0001 menu
  applied 0002 write_ahead_log
journal mode: wal
sgl_migrations (default namespace): 0 of 2 migrations applied
seeds applied: 1
seeds applied: 0
  cod and chips     950p
  haddock and chips 1020p
  mushy peas        250p

Try it

Add a CREATE TABLE to the end of migration 0002 and run sgl vet. It reports Q043: the new table would be created outside a transaction too, and a failure between the two statements would leave the migration half-applied.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/sqlite && go run ./029_seeds_namespaces_hooks

Its test runs it too, and checks what it prints against output.txt:

cd seagull/examples/sqlite && go test ./029_seeds_namespaces_hooks

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: chippy
    dialect: sqlite
    migrations: db/migrations
    queries: db/queries
    seeds: db/seeds
    go:
      package: store
      out: internal/store
    migrate:
      namespace: chippy

db/migrations/0001_menu.up.sql

CREATE TABLE menu (
    id          INTEGER PRIMARY KEY,
    dish        TEXT    NOT NULL UNIQUE,
    price_pence INTEGER NOT NULL
);

db/migrations/0002_write_ahead_log.up.sql

-- sgl.migration(no_transaction: true)
PRAGMA journal_mode = WAL;

db/migrations/0001_menu.down.sql

DROP TABLE menu;

db/migrations/0002_write_ahead_log.down.sql

-- sgl.migration(no_transaction: true)
PRAGMA journal_mode = DELETE;

db/queries/menu.sql

-- sgl.query(ListMenu, many)
SELECT dish, price_pence FROM menu ORDER BY dish;

db/seeds/0001_menu.sql

INSERT INTO menu (dish, price_pence) VALUES
    ('cod and chips', 950),
    ('haddock and chips', 1020),
    ('mushy peas', 250)
ON CONFLICT (dish) DO NOTHING;

example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"
	"io/fs"

	"seagull.sh/seagull/examples/sqlite/029_seeds_namespaces_hooks/internal/store"
	"seagull.sh/seagull/sdk/migrate"
	"seagull.sh/seagull/sdk/migrate/migrate_backend_sqlite"
)

const (
	// namespace prefixes the tables that record this program's migrations and seeds.
	namespace = "chippy"
)

// run migrates the database under its own namespace with hooks that report progress,
// applies the seed files twice to show they run once, and prints the seeded menu.
//
// Takes database (*sql.DB) which is an empty database.
// Takes files (fs.FS) which holds db/migrations and db/seeds, the embed.FS in main.go.
// Takes output (io.Writer) which receives each step's result.
//
// Returns error when migrating, seeding or a query fails.
func run(ctx context.Context, database *sql.DB, files fs.FS, output io.Writer) error {
	// WithNamespace renames every table the migrator and seeder keep, so several programs or
	// modules can each keep their own history in one database. sgl.yaml sets
	// migrate.namespace to match, so `sgl migrate` uses the same tables.
	backend := migrate_backend_sqlite.New(migrate.WithNamespace(namespace))
	names := backend.Names()
	fmt.Fprintf(output, "history tables: %s and %s\n", names.History, names.Seeds)

	// Hooks run around the migrations: before the run with the pending versions, and after
	// each migration. Returning an error from a hook stops the run.
	migrator, err := migrate.NewMigrator(database, backend, files, "db/migrations",
		migrate.WithBeforeRun(func(_ context.Context, pending migrate.MigrationRunHookContext) error {
			fmt.Fprintf(output, "migrating: %d pending, versions %v\n", pending.PendingCount, pending.PendingVersions)
			return nil
		}),
		migrate.WithAfterMigration(func(_ context.Context, migration migrate.MigrationHookContext) error {
			fmt.Fprintf(output, "  applied %04d %s\n", migration.Version, migration.Name)
			return nil
		}),
	)
	if err != nil {
		return err
	}
	if _, err := migrator.Up(ctx); err != nil {
		return fmt.Errorf("migrating: %w", err)
	}

	// Migration 0002 ran outside a transaction, as its sgl.migration directive asks.
	var journalMode string
	if err := database.QueryRowContext(ctx, "PRAGMA journal_mode").Scan(&journalMode); err != nil {
		return fmt.Errorf("reading the journal mode: %w", err)
	}
	fmt.Fprintf(output, "journal mode: %s\n", journalMode)

	// The default namespace is a separate history in the same database, and it has recorded
	// nothing.
	if err := printOtherHistory(ctx, database, files, output); err != nil {
		return err
	}

	// A Seeder applies each seed file once and records it in the seed table, so calling
	// Apply at every start-up is safe. The seed file also says ON CONFLICT DO NOTHING, which
	// keeps it harmless if the seed history is ever cleared.
	seeder, err := migrate.NewSeeder(database, backend, files, "db/seeds")
	if err != nil {
		return err
	}
	for range 2 {
		seeded, err := seeder.Apply(ctx)
		if err != nil {
			return fmt.Errorf("seeding: %w", err)
		}
		fmt.Fprintf(output, "seeds applied: %d\n", seeded)
	}

	menu, err := store.New(database).ListMenu(ctx)
	if err != nil {
		return fmt.Errorf("listing the menu: %w", err)
	}
	for _, dish := range menu {
		fmt.Fprintf(output, "  %-17s %dp\n", dish.Dish, dish.PricePence)
	}
	return nil
}

// printOtherHistory reads the same migration files against the default namespace's
// history, which this program never wrote to.
//
// Takes database (*sql.DB) which is the migrated database.
// Takes files (fs.FS) which holds the migration files.
// Takes output (io.Writer) which receives the count.
//
// Returns error when the status cannot be read.
func printOtherHistory(ctx context.Context, database *sql.DB, files fs.FS, output io.Writer) error {
	backend := migrate_backend_sqlite.New()
	migrator, err := migrate.NewMigrator(database, backend, files, "db/migrations")
	if err != nil {
		return err
	}
	statuses, err := migrator.Status(ctx)
	if err != nil {
		return fmt.Errorf("reading the default history: %w", err)
	}
	applied := 0
	for _, status := range statuses {
		if status.Applied {
			applied++
		}
	}
	fmt.Fprintf(output, "%s (default namespace): %d of %d migrations applied\n", backend.Names().History, applied, len(statuses))
	return nil
}

main.go

// Command 029_seeds_namespaces_hooks opens a SQLite database and runs the example in
// example.go, which migrates it under its own namespace with progress hooks, including a
// migration that must run outside a transaction, and then applies the seed files.
//
// Run it with `go run .`, or `go run . -db chippy.db` to keep the database; a second run
// against the same file applies no migrations and no seeds.
package main

import (
	"context"
	"database/sql"
	"embed"
	"flag"
	"log"
	"os"
	"path/filepath"

	_ "modernc.org/sqlite"
)

// files holds the migration and seed files, compiled into the binary.
//
//go:embed db/migrations db/seeds
var files 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 and seeds 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, files, os.Stdout)
}

internal/store/menu.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const listMenuSQL = `SELECT dish, price_pence FROM menu ORDER BY dish;`

type ListMenuRow struct {
	Dish       string `json:"dish"`
	PricePence int64  `json:"price_pence"`
}

func (queries *Queries) ListMenu(ctx context.Context) ([]ListMenuRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listMenuSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListMenuRow
	if rows.Next() {
		var row ListMenuRow
		scanArgs := []any{&row.Dish, &row.PricePence}
		for more := true; more; more = rows.Next() {
			row = ListMenuRow{}
			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
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Menu struct {
	ID         int64  `json:"id"`
	Dish       string `json:"dish"`
	PricePence int64  `json:"price_pence"`
}

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()
}

output.txt

history tables: chippy_migrations and chippy_seeds
migrating: 2 pending, versions [1 2]
  applied 0001 menu
  applied 0002 write_ahead_log
journal mode: wal
sgl_migrations (default namespace): 0 of 2 migrations applied
seeds applied: 1
seeds applied: 0
  cod and chips     950p
  haddock and chips 1020p
  mushy peas        250p

Also on other engines

PostgreSQLA namespaced migrator with before-run and after-migration hooks and a logger, an index built CONCURRENTLY in a migration that runs outside a transaction, and a Seeder that applies each seed file once and warns when one changes.