Migrations at start-up

On DuckDB:

• migrate_backend_duckdb.New() runs each migration in a transaction with its history row, since DuckDB DDL is transactional
• The backend binds its own queries with $N placeholders and takes no lock: DuckDB has none, and one process at a time opens a database file for writing
• DuckDB cannot add a NOT NULL column to an existing table, so 0002 adds home_port with a default and it maps to *string
• the history lives in the sgl_migrations table of the same database
• The program uses an in-memory database by default; -db keeps it in a file so a second run finds nothing to migrate

A service that owns its database can bring the schema up to date itself, every time it starts. The migration files are compiled into the binary with go:embed, and a migrator applies whatever the database does not have yet before the program does anything else. There is no separate migration step in the deploy and no files to ship beside the binary, and the schema the code runs against is the one it was generated from: these are the same files sgl generate reads. sqlc leaves applying migrations to another tool; Seagull's migrator is a Go package, seagull.sh/seagull/sdk/migrate, that reads the same files.

The migrations

Two migrations: the first creates a table, the second adds a column to it. They are plain SQL files, numbered in the order they apply, each with a .down.sql beside it to undo it.

db/migrations/0001_boats.up.sql

CREATE SEQUENCE boat_ids;

CREATE TABLE boats (
    id   BIGINT  PRIMARY KEY DEFAULT nextval('boat_ids'),
    name VARCHAR NOT NULL
);

db/migrations/0002_boat_home_port.up.sql

ALTER TABLE boats ADD COLUMN home_port VARCHAR DEFAULT 'Padstow';

DuckDB cannot add a NOT NULL column to a table that already exists, so home_port has a default but may be NULL, and its field is a *string.

Embedding the files

main.go embeds the migrations directory:

//go:embed db/migrations
var migrations embed.FS

The program needs nothing beside it on disk to bring a database up to date, and the files it applies are always the ones it was built with.

Migrating at start-up

Before it touches the schema, the program builds a migrator and calls Up. migrate.NewMigrator takes the database, a backend for the engine, the embedded files and the directory inside them. Up applies every migration not yet recorded in the history table, in version order, and returns how many it applied. On a database that is already up to date it applies none, which is what makes it safe to call on every start.

migrate_backend_duckdb.New() is the DuckDB backend. DuckDB's DDL is transactional, so each migration runs in a transaction with its history row. It takes no lock: DuckDB has none to take, and only one process at a time can open a database file for writing.

The program shows this by starting twice against the same database. The first start applies both migrations, adds a boat and lists the harbour; the second finds nothing to migrate and goes straight to work. Last, it reads the history with Status.

example.go

package main

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

	"seagull.sh/seagull/examples/duckdb/027_migrations_at_startup/internal/store"
	"seagull.sh/seagull/sdk/migrate"
	"seagull.sh/seagull/sdk/migrate/migrate_backend_duckdb"
)

// run starts the program twice against the same database. The first start applies both
// migrations before the program uses the schema; the second finds nothing to do.
//
// Takes database (*sql.DB) which is the database to migrate, empty or not.
// Takes migrations (fs.FS) which holds db/migrations, the embed.FS in main.go.
// Takes output (io.Writer) which receives what each start did.
//
// Returns error when migrating or a query fails.
func run(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
	for _, boat := range []string{"Kittiwake", "Puffin"} {
		if err := start(ctx, database, migrations, boat, output); err != nil {
			return err
		}
	}
	return printHistory(ctx, database, migrations, output)
}

// start is what the program does each time it starts: migrate first, then work. Here the
// work is adding a boat and listing the harbour.
//
// Takes database (*sql.DB) which is the database.
// Takes migrations (fs.FS) which holds the migration files.
// Takes boat (string) which names the boat to add.
// Takes output (io.Writer) which receives what happened.
//
// Returns error when migrating or a query fails.
func start(ctx context.Context, database *sql.DB, migrations fs.FS, boat string, output io.Writer) error {
	// migrate_backend_duckdb.New() is the DuckDB backend: it knows how to create the history
	// table, binds its own queries with $N placeholders, and runs each migration in a
	// transaction, as DuckDB DDL is transactional. The backend takes no database lock;
	// callers must serialise migrations across migrator instances. NewMigrator reads the
	// numbered *.up.sql and *.down.sql files from the directory inside migrations.
	migrator, err := migrate.NewMigrator(database, migrate_backend_duckdb.New(), migrations, "db/migrations")
	if err != nil {
		return err
	}

	// Up applies every migration not yet in the history, in version order, and returns how
	// many it applied. On a database that is up to date it applies none.
	applied, err := migrator.Up(ctx)
	if err != nil {
		return fmt.Errorf("migrating: %w", err)
	}
	fmt.Fprintf(output, "start-up: applied %d migrations\n", applied)

	// DuckDB cannot add a NOT NULL column to an existing table, so 0002 adds home_port with
	// a default only, and the column, like its field, may be NULL.
	queries := store.New(database)
	if err := queries.AddBoat(ctx, store.AddBoatParams{Name: boat, HomePort: new("Padstow")}); err != nil {
		return fmt.Errorf("adding %s: %w", boat, err)
	}
	boats, err := queries.ListBoats(ctx)
	if err != nil {
		return fmt.Errorf("listing boats: %w", err)
	}
	for _, row := range boats {
		homePort := "an unknown port"
		if row.HomePort != nil {
			homePort = *row.HomePort
		}
		fmt.Fprintf(output, "  %d %-9s of %s\n", row.ID, row.Name, homePort)
	}
	return nil
}

// printHistory prints the migration history the migrator keeps in the database.
//
// 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 history.
//
// Returns error when the history cannot be read.
func printHistory(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
	backend := migrate_backend_duckdb.New()
	migrator, err := migrate.NewMigrator(database, backend, migrations, "db/migrations")
	if err != nil {
		return err
	}
	statuses, err := migrator.Status(ctx)
	if err != nil {
		return fmt.Errorf("reading migration status: %w", err)
	}
	fmt.Fprintf(output, "history in %s:\n", backend.Names().History)
	for _, status := range statuses {
		fmt.Fprintf(output, "  %04d %-15s applied=%t\n", status.Version, status.Name, status.Applied)
	}
	return nil
}

What it prints

The first start applied two migrations and the second none, yet both could use the home_port column. The history table, sgl_migrations, records both versions.

start-up: applied 2 migrations
  1 Kittiwake of Padstow
start-up: applied 0 migrations
  1 Kittiwake of Padstow
  2 Puffin    of Padstow
history in sgl_migrations:
  0001 boats           applied=true
  0002 boat_home_port  applied=true

Try it

Add a third migration, such as 0003_boat_length.up.sql adding a column, and run sgl generate. Then run the program against the database from an earlier run: only the new migration is applied, and the rest of the history is left alone. Pass -db harbour.db to both runs, so the database is kept in a file between them.

The migrations guide covers the migrator further, and the migration lifecycle goes on to rolling back, status and checksums.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/duckdb && go run ./027_migrations_at_startup

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

cd seagull/examples/duckdb && go test ./027_migrations_at_startup

Needs cgo and a C compiler (the DuckDB driver).

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: harbour
    dialect: duckdb
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_boats.up.sql

CREATE SEQUENCE boat_ids;

CREATE TABLE boats (
    id   BIGINT  PRIMARY KEY DEFAULT nextval('boat_ids'),
    name VARCHAR NOT NULL
);

db/migrations/0002_boat_home_port.up.sql

ALTER TABLE boats ADD COLUMN home_port VARCHAR DEFAULT 'Padstow';

db/migrations/0001_boats.down.sql

DROP TABLE boats;
DROP SEQUENCE boat_ids;

db/migrations/0002_boat_home_port.down.sql

ALTER TABLE boats DROP COLUMN home_port;

db/queries/boats.sql

-- sgl.query(AddBoat, exec)
INSERT INTO boats (name, home_port) VALUES ($1, $2);

-- sgl.query(ListBoats, many)
SELECT id, name, home_port FROM boats ORDER BY id;

example.go

package main

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

	"seagull.sh/seagull/examples/duckdb/027_migrations_at_startup/internal/store"
	"seagull.sh/seagull/sdk/migrate"
	"seagull.sh/seagull/sdk/migrate/migrate_backend_duckdb"
)

// run starts the program twice against the same database. The first start applies both
// migrations before the program uses the schema; the second finds nothing to do.
//
// Takes database (*sql.DB) which is the database to migrate, empty or not.
// Takes migrations (fs.FS) which holds db/migrations, the embed.FS in main.go.
// Takes output (io.Writer) which receives what each start did.
//
// Returns error when migrating or a query fails.
func run(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
	for _, boat := range []string{"Kittiwake", "Puffin"} {
		if err := start(ctx, database, migrations, boat, output); err != nil {
			return err
		}
	}
	return printHistory(ctx, database, migrations, output)
}

// start is what the program does each time it starts: migrate first, then work. Here the
// work is adding a boat and listing the harbour.
//
// Takes database (*sql.DB) which is the database.
// Takes migrations (fs.FS) which holds the migration files.
// Takes boat (string) which names the boat to add.
// Takes output (io.Writer) which receives what happened.
//
// Returns error when migrating or a query fails.
func start(ctx context.Context, database *sql.DB, migrations fs.FS, boat string, output io.Writer) error {
	// migrate_backend_duckdb.New() is the DuckDB backend: it knows how to create the history
	// table, binds its own queries with $N placeholders, and runs each migration in a
	// transaction, as DuckDB DDL is transactional. The backend takes no database lock;
	// callers must serialise migrations across migrator instances. NewMigrator reads the
	// numbered *.up.sql and *.down.sql files from the directory inside migrations.
	migrator, err := migrate.NewMigrator(database, migrate_backend_duckdb.New(), migrations, "db/migrations")
	if err != nil {
		return err
	}

	// Up applies every migration not yet in the history, in version order, and returns how
	// many it applied. On a database that is up to date it applies none.
	applied, err := migrator.Up(ctx)
	if err != nil {
		return fmt.Errorf("migrating: %w", err)
	}
	fmt.Fprintf(output, "start-up: applied %d migrations\n", applied)

	// DuckDB cannot add a NOT NULL column to an existing table, so 0002 adds home_port with
	// a default only, and the column, like its field, may be NULL.
	queries := store.New(database)
	if err := queries.AddBoat(ctx, store.AddBoatParams{Name: boat, HomePort: new("Padstow")}); err != nil {
		return fmt.Errorf("adding %s: %w", boat, err)
	}
	boats, err := queries.ListBoats(ctx)
	if err != nil {
		return fmt.Errorf("listing boats: %w", err)
	}
	for _, row := range boats {
		homePort := "an unknown port"
		if row.HomePort != nil {
			homePort = *row.HomePort
		}
		fmt.Fprintf(output, "  %d %-9s of %s\n", row.ID, row.Name, homePort)
	}
	return nil
}

// printHistory prints the migration history the migrator keeps in the database.
//
// 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 history.
//
// Returns error when the history cannot be read.
func printHistory(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
	backend := migrate_backend_duckdb.New()
	migrator, err := migrate.NewMigrator(database, backend, migrations, "db/migrations")
	if err != nil {
		return err
	}
	statuses, err := migrator.Status(ctx)
	if err != nil {
		return fmt.Errorf("reading migration status: %w", err)
	}
	fmt.Fprintf(output, "history in %s:\n", backend.Names().History)
	for _, status := range statuses {
		fmt.Fprintf(output, "  %04d %-15s applied=%t\n", status.Version, status.Name, status.Applied)
	}
	return nil
}

main.go

// Command 027_migrations_at_startup opens a DuckDB database and runs the example in
// example.go, which applies the migrations embedded in the binary each time the program
// starts, then uses the schema they built.
//
// Run it with `go run .` for an in-memory database, or `go run . -db harbour.duckdb` to
// keep the database in a file; a second run against the same file finds nothing to
// migrate.
package main

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

	_ "github.com/duckdb/duckdb-go/v2"
)

// migrations holds the migration files, compiled into the binary, so the program needs no
// files beside it to bring a database up to date.
//
//go:embed db/migrations
var migrations embed.FS

func main() {
	path := flag.String("db", "", "DuckDB database file; an in-memory database when empty")
	flag.Parse()
	if err := execute(context.Background(), *path); err != nil {
		log.Fatal(err)
	}
}

// execute opens the DuckDB database at path, or an in-memory 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 {
	database, err := sql.Open("duckdb", path)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()
	return run(ctx, database, migrations, os.Stdout)
}

internal/store/boats.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const addBoatSQL = `INSERT INTO boats (name, home_port) VALUES ($1, $2);`

type AddBoatParams struct {
	Name     string
	HomePort *string
}

func (queries *Queries) AddBoat(ctx context.Context, params AddBoatParams) error {
	_, err := queries.writer.ExecContext(ctx, addBoatSQL, params.Name, params.HomePort)
	return err
}

const listBoatsSQL = `SELECT id, name, home_port FROM boats ORDER BY id;`

type ListBoatsRow struct {
	ID       int64   `json:"id"`
	Name     string  `json:"name"`
	HomePort *string `json:"home_port"`
}

func (queries *Queries) ListBoats(ctx context.Context) ([]ListBoatsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listBoatsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListBoatsRow
	for rows.Next() {
		var row ListBoatsRow
		if err := rows.Scan(&row.ID, &row.Name, &row.HomePort); 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 Boats struct {
	ID       int64   `json:"id"`
	Name     string  `json:"name"`
	HomePort *string `json:"home_port"`
}

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

start-up: applied 2 migrations
  1 Kittiwake of Padstow
start-up: applied 0 migrations
  1 Kittiwake of Padstow
  2 Puffin    of Padstow
history in sgl_migrations:
  0001 boats           applied=true
  0002 boat_home_port  applied=true