Functions in migrations

On this page

The same on TimescaleDB. TimescaleDB runs this example unchanged: the SQL and the Go that sgl generates are identical to the PostgreSQL version. Only dialect: in sgl.yaml changes.

A function created in a migration is part of the schema, just as a table is, and Seagull reads each CREATE FUNCTION when it replays the migrations. A query that calls the function is typed from the function's arguments and result, and the call is checked like any other.

A function also has a second effect. A SELECT that calls a function which writes is itself a write, so whether the function reads or writes decides whether the query may go to a read replica. Seagull takes that from what the CREATE FUNCTION declares, and from what its body does, and a sgl.migration(readonly:) line corrects it where the declaration is wrong. If a dialect resolves a function without knowing either way, sgl vet reports Q045 and assumes it only reads.

The example runs a fish market: it prices the fish, lands catches through a function that inserts them, and reads the market back through functions that only read. To show where each query goes, it builds the queries with NewWithReplica on two labelled connections to the same database, as in read replicas.

The functions

Migration 0002 creates four functions, each showing a different case:

db/migrations/0002_market_functions.up.sql

CREATE FUNCTION price_pence(weight_grams INTEGER, pence_per_kilo INTEGER) RETURNS INTEGER
LANGUAGE sql IMMUTABLE STRICT
AS $$ SELECT weight_grams * pence_per_kilo / 1000 $$;

CREATE FUNCTION boat_haul(boat_name TEXT)
RETURNS TABLE (species TEXT, weight_grams INTEGER)
LANGUAGE sql STABLE
AS $$ SELECT species, weight_grams FROM catches WHERE boat = boat_name $$;

CREATE FUNCTION land_catch(boat_name TEXT, caught TEXT, grams INTEGER) RETURNS BIGINT
LANGUAGE plpgsql VOLATILE
AS $$
DECLARE
    landed BIGINT;
BEGIN
    INSERT INTO catches (boat, species, weight_grams) VALUES (boat_name, caught, grams)
    RETURNING id INTO landed;
    RETURN landed;
END;
$$;

-- sgl.migration(readonly: true)
CREATE FUNCTION species_price(wanted TEXT) RETURNS INTEGER
LANGUAGE sql VOLATILE
AS $$ SELECT pence_per_kilo FROM prices WHERE species = wanted $$;

PostgreSQL functions declare a volatility. IMMUTABLE and STABLE functions are read-only, and a VOLATILE one is taken to write. Seagull also scans the bodies, SQL and PL/pgSQL alike, and a function that calls a writing function writes too.

  • price_pence is IMMUTABLE: a pure calculation. It is also STRICT, so it returns NULL only when an argument is NULL.
  • boat_haul is STABLE and RETURNS TABLE, so it is a relation with typed columns that a query can select from.
  • land_catch is PL/pgSQL, VOLATILE, and inserts a row.
  • species_price is declared VOLATILE, so Seagull would take it to write, but its body only reads. The sgl.migration(readonly: true) line above it says so.

The queries

LandCatch is a SELECT, but it calls land_catch, so it is a write. A function argument has no column to take a name from, so the query names its parameters with :boat, :species and :grams:

db/queries/market.sql

-- sgl.query(LandCatch, one)
SELECT land_catch(:boat, :species, :grams) AS id;

BoatHaul selects from boat_haul as if it were a table and calls price_pence on each row. Both functions only read, so the query is read-only. The :boat passed to boat_haul takes the type of the function's TEXT argument.

db/queries/market.sql

-- sgl.query(BoatHaul, many)
SELECT haul.species, haul.weight_grams, price_pence(haul.weight_grams, prices.pence_per_kilo) AS value_pence
FROM boat_haul(:boat) AS haul
JOIN prices ON prices.species = haul.species
ORDER BY haul.species;

A function's result can be NULL, so its columns are pointers: boat_haul's columns may be NULL, and price_pence, though STRICT, is given arguments that may be.

internal/store

type BoatHaulRow struct {
	Species     *string `json:"species"`
	WeightGrams *int32  `json:"weight_grams"`
	ValuePence  *int32  `json:"value_pence"`
}

func (queries *Queries) BoatHaul(ctx context.Context, boat string) ([]BoatHaulRow, error) {
	rows, err := queries.reader.QueryContext(ctx, boatHaulSQL, boat)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []BoatHaulRow
	for rows.Next() {
		var row BoatHaulRow
		if err := rows.Scan(&row.Species, &row.WeightGrams, &row.ValuePence); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

Using it

example.go

package main

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

	"seagull.sh/seagull/examples/postgres/030_functions_in_migrations/internal/store"
)

// connection is a store.DBTX that notes its name before each statement, so the example
// can say whether Seagull sent a query to the writer or the reader. Both connections wrap
// the same database here; concept 24 puts the reader on a replica.
type connection struct {
	*sql.DB

	// last receives the name of the connection that ran the latest statement.
	last *string

	// name labels the connection.
	name string
}

// ExecContext notes the connection, then runs the statement on the database.
//
// Takes query (string) which is the SQL to run.
// Takes arguments (...any) which are its bind values.
//
// Returns sql.Result which is the database's result.
// Returns error when the statement fails.
func (c connection) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
	*c.last = c.name
	return c.DB.ExecContext(ctx, query, arguments...)
}

// QueryContext notes the connection, then runs the query on the database.
//
// Takes query (string) which is the SQL to run.
// Takes arguments (...any) which are its bind values.
//
// Returns *sql.Rows which holds the result rows.
// Returns error when the query fails.
func (c connection) QueryContext(ctx context.Context, query string, arguments ...any) (*sql.Rows, error) {
	*c.last = c.name
	return c.DB.QueryContext(ctx, query, arguments...)
}

// QueryRowContext notes the connection, then runs the query on the database.
//
// Takes query (string) which is the SQL to run.
// Takes arguments (...any) which are its bind values.
//
// Returns *sql.Row which holds the result row or the error.
func (c connection) QueryRowContext(ctx context.Context, query string, arguments ...any) *sql.Row {
	*c.last = c.name
	return c.DB.QueryRowContext(ctx, query, arguments...)
}

// run prices the fish, lands three catches and reads the market back, all through the SQL
// functions that migration 0002 creates. Seagull reads each CREATE FUNCTION when it
// builds the schema: the argument and return types type the queries that call it, and
// whether the function may change data decides whether those queries are read-only, which
// picks the writer or the reader. Volatility does not decide it.
//
//   - land_catch inserts a row, so LandCatch goes to the writer;
//   - price_pence and boat_haul only read data, so BoatHaul, which calls both, goes to
//     the reader. boat_haul RETURNS TABLE, so it is a relation with typed columns, and
//     its $1 is typed from the function's TEXT argument;
//   - species_price is declared VOLATILE, but its body only reads, so SpeciesPrice goes
//     to the reader. The sgl.migration(readonly: true) line above it in the migration
//     states that outright, as a function whose body Seagull cannot read needs.
//
// A function's result can be NULL, so the columns are pointers: species_price finds no
// row for a fish with no price. price_pence is STRICT, so a NULL argument gives a NULL
// result; here its arguments come from boat_haul, which may return NULLs.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each call's connection and result.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	var last string
	queries := store.NewWithReplica(
		connection{DB: database, last: &last, name: "writer"},
		connection{DB: database, last: &last, name: "reader"},
	)

	prices := []store.SetPriceParams{
		{Species: "crab", PencePerKilo: 1200},
		{Species: "mackerel", PencePerKilo: 450},
		{Species: "pollock", PencePerKilo: 800},
	}
	for _, price := range prices {
		if err := queries.SetPrice(ctx, price); err != nil {
			return fmt.Errorf("pricing %s: %w", price.Species, err)
		}
	}

	catches := []store.LandCatchParams{
		{Boat: "Kittiwake", Species: "mackerel", Grams: 12000},
		{Boat: "Kittiwake", Species: "crab", Grams: 3500},
		{Boat: "Puffin", Species: "pollock", Grams: 8000},
	}
	for _, catch := range catches {
		landed, err := queries.LandCatch(ctx, catch)
		if err != nil {
			return fmt.Errorf("landing %s: %w", catch.Species, err)
		}
		fmt.Fprintf(output, "LandCatch    on the %s: catch %s, %dg of %s from the %s\n", last, orNull(landed.ID), catch.Grams, catch.Species, catch.Boat)
	}

	for _, species := range []string{"crab", "halibut"} {
		price, err := queries.SpeciesPrice(ctx, species)
		if err != nil {
			return fmt.Errorf("pricing %s: %w", species, err)
		}
		if price.PencePerKilo == nil {
			fmt.Fprintf(output, "SpeciesPrice on the %s: %s has no price\n", last, species)
			continue
		}
		fmt.Fprintf(output, "SpeciesPrice on the %s: %s fetches %dp a kilo\n", last, species, *price.PencePerKilo)
	}

	haul, err := queries.BoatHaul(ctx, "Kittiwake")
	if err != nil {
		return fmt.Errorf("reading the Kittiwake's haul: %w", err)
	}
	fmt.Fprintf(output, "BoatHaul     on the %s: the Kittiwake's haul\n", last)
	for _, row := range haul {
		fmt.Fprintf(output, "  %-9s %6sg %5sp\n", orNull(row.Species), orNull(row.WeightGrams), orNull(row.ValuePence))
	}
	return nil
}

// orNull formats a nullable field, or returns NULL when the field is nil.
//
// Takes value (*T) which is a nullable field.
//
// Returns string which is the text to print.
func orNull[T any](value *T) string {
	if value == nil {
		return "NULL"
	}
	return fmt.Sprint(*value)
}

What it prints

LandCatch ran on the writer, and SpeciesPrice and BoatHaul on the reader. There is no price for halibut, so species_price returned NULL and the program printed that instead of a figure.

LandCatch    on the writer: catch 1, 12000g of mackerel from the Kittiwake
LandCatch    on the writer: catch 2, 3500g of crab from the Kittiwake
LandCatch    on the writer: catch 3, 8000g of pollock from the Puffin
SpeciesPrice on the reader: crab fetches 1200p a kilo
SpeciesPrice on the reader: halibut has no price
BoatHaul     on the reader: the Kittiwake's haul
  crab        3500g  4200p
  mackerel   12000g  5400p

Try it

Delete the sgl.migration(readonly: true) line from migration 0002 and run sgl generate. SpeciesPrice now uses queries.writer: Seagull believes the function's declaration, which says it may write.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/postgres && go run ./030_functions_in_migrations

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

cd seagull/examples/postgres && SGL_EXAMPLE_ENGINE=timescaledb SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./030_functions_in_migrations

Needs a running TimescaleDB 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: market
    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_catches.up.sql

CREATE TABLE catches (
    id           BIGINT  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    boat         TEXT    NOT NULL,
    species      TEXT    NOT NULL,
    weight_grams INTEGER NOT NULL
);

CREATE TABLE prices (
    species        TEXT    PRIMARY KEY,
    pence_per_kilo INTEGER NOT NULL
);

db/migrations/0002_market_functions.up.sql

CREATE FUNCTION price_pence(weight_grams INTEGER, pence_per_kilo INTEGER) RETURNS INTEGER
LANGUAGE sql IMMUTABLE STRICT
AS $$ SELECT weight_grams * pence_per_kilo / 1000 $$;

CREATE FUNCTION boat_haul(boat_name TEXT)
RETURNS TABLE (species TEXT, weight_grams INTEGER)
LANGUAGE sql STABLE
AS $$ SELECT species, weight_grams FROM catches WHERE boat = boat_name $$;

CREATE FUNCTION land_catch(boat_name TEXT, caught TEXT, grams INTEGER) RETURNS BIGINT
LANGUAGE plpgsql VOLATILE
AS $$
DECLARE
    landed BIGINT;
BEGIN
    INSERT INTO catches (boat, species, weight_grams) VALUES (boat_name, caught, grams)
    RETURNING id INTO landed;
    RETURN landed;
END;
$$;

-- sgl.migration(readonly: true)
CREATE FUNCTION species_price(wanted TEXT) RETURNS INTEGER
LANGUAGE sql VOLATILE
AS $$ SELECT pence_per_kilo FROM prices WHERE species = wanted $$;

db/migrations/0001_catches.down.sql

DROP TABLE prices;
DROP TABLE catches;

db/migrations/0002_market_functions.down.sql

DROP FUNCTION species_price(TEXT);
DROP FUNCTION land_catch(TEXT, TEXT, INTEGER);
DROP FUNCTION boat_haul(TEXT);
DROP FUNCTION price_pence(INTEGER, INTEGER);

db/queries/market.sql

-- sgl.query(SetPrice, exec)
INSERT INTO prices (species, pence_per_kilo) VALUES ($1, $2);

-- sgl.query(LandCatch, one)
SELECT land_catch(:boat, :species, :grams) AS id;

-- sgl.query(SpeciesPrice, one)
SELECT species_price(:species) AS pence_per_kilo;

-- sgl.query(BoatHaul, many)
SELECT haul.species, haul.weight_grams, price_pence(haul.weight_grams, prices.pence_per_kilo) AS value_pence
FROM boat_haul(:boat) AS haul
JOIN prices ON prices.species = haul.species
ORDER BY haul.species;

example.go

package main

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

	"seagull.sh/seagull/examples/postgres/030_functions_in_migrations/internal/store"
)

// connection is a store.DBTX that notes its name before each statement, so the example
// can say whether Seagull sent a query to the writer or the reader. Both connections wrap
// the same database here; concept 24 puts the reader on a replica.
type connection struct {
	*sql.DB

	// last receives the name of the connection that ran the latest statement.
	last *string

	// name labels the connection.
	name string
}

// ExecContext notes the connection, then runs the statement on the database.
//
// Takes query (string) which is the SQL to run.
// Takes arguments (...any) which are its bind values.
//
// Returns sql.Result which is the database's result.
// Returns error when the statement fails.
func (c connection) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
	*c.last = c.name
	return c.DB.ExecContext(ctx, query, arguments...)
}

// QueryContext notes the connection, then runs the query on the database.
//
// Takes query (string) which is the SQL to run.
// Takes arguments (...any) which are its bind values.
//
// Returns *sql.Rows which holds the result rows.
// Returns error when the query fails.
func (c connection) QueryContext(ctx context.Context, query string, arguments ...any) (*sql.Rows, error) {
	*c.last = c.name
	return c.DB.QueryContext(ctx, query, arguments...)
}

// QueryRowContext notes the connection, then runs the query on the database.
//
// Takes query (string) which is the SQL to run.
// Takes arguments (...any) which are its bind values.
//
// Returns *sql.Row which holds the result row or the error.
func (c connection) QueryRowContext(ctx context.Context, query string, arguments ...any) *sql.Row {
	*c.last = c.name
	return c.DB.QueryRowContext(ctx, query, arguments...)
}

// run prices the fish, lands three catches and reads the market back, all through the SQL
// functions that migration 0002 creates. Seagull reads each CREATE FUNCTION when it
// builds the schema: the argument and return types type the queries that call it, and
// whether the function may change data decides whether those queries are read-only, which
// picks the writer or the reader. Volatility does not decide it.
//
//   - land_catch inserts a row, so LandCatch goes to the writer;
//   - price_pence and boat_haul only read data, so BoatHaul, which calls both, goes to
//     the reader. boat_haul RETURNS TABLE, so it is a relation with typed columns, and
//     its $1 is typed from the function's TEXT argument;
//   - species_price is declared VOLATILE, but its body only reads, so SpeciesPrice goes
//     to the reader. The sgl.migration(readonly: true) line above it in the migration
//     states that outright, as a function whose body Seagull cannot read needs.
//
// A function's result can be NULL, so the columns are pointers: species_price finds no
// row for a fish with no price. price_pence is STRICT, so a NULL argument gives a NULL
// result; here its arguments come from boat_haul, which may return NULLs.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each call's connection and result.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	var last string
	queries := store.NewWithReplica(
		connection{DB: database, last: &last, name: "writer"},
		connection{DB: database, last: &last, name: "reader"},
	)

	prices := []store.SetPriceParams{
		{Species: "crab", PencePerKilo: 1200},
		{Species: "mackerel", PencePerKilo: 450},
		{Species: "pollock", PencePerKilo: 800},
	}
	for _, price := range prices {
		if err := queries.SetPrice(ctx, price); err != nil {
			return fmt.Errorf("pricing %s: %w", price.Species, err)
		}
	}

	catches := []store.LandCatchParams{
		{Boat: "Kittiwake", Species: "mackerel", Grams: 12000},
		{Boat: "Kittiwake", Species: "crab", Grams: 3500},
		{Boat: "Puffin", Species: "pollock", Grams: 8000},
	}
	for _, catch := range catches {
		landed, err := queries.LandCatch(ctx, catch)
		if err != nil {
			return fmt.Errorf("landing %s: %w", catch.Species, err)
		}
		fmt.Fprintf(output, "LandCatch    on the %s: catch %s, %dg of %s from the %s\n", last, orNull(landed.ID), catch.Grams, catch.Species, catch.Boat)
	}

	for _, species := range []string{"crab", "halibut"} {
		price, err := queries.SpeciesPrice(ctx, species)
		if err != nil {
			return fmt.Errorf("pricing %s: %w", species, err)
		}
		if price.PencePerKilo == nil {
			fmt.Fprintf(output, "SpeciesPrice on the %s: %s has no price\n", last, species)
			continue
		}
		fmt.Fprintf(output, "SpeciesPrice on the %s: %s fetches %dp a kilo\n", last, species, *price.PencePerKilo)
	}

	haul, err := queries.BoatHaul(ctx, "Kittiwake")
	if err != nil {
		return fmt.Errorf("reading the Kittiwake's haul: %w", err)
	}
	fmt.Fprintf(output, "BoatHaul     on the %s: the Kittiwake's haul\n", last)
	for _, row := range haul {
		fmt.Fprintf(output, "  %-9s %6sg %5sp\n", orNull(row.Species), orNull(row.WeightGrams), orNull(row.ValuePence))
	}
	return nil
}

// orNull formats a nullable field, or returns NULL when the field is nil.
//
// Takes value (*T) which is a nullable field.
//
// Returns string which is the text to print.
func orNull[T any](value *T) string {
	if value == nil {
		return "NULL"
	}
	return fmt.Sprint(*value)
}

main.go

// Command 030_functions_in_migrations applies its migrations to a PostgreSQL database,
// then runs the example in example.go: a fish market prices and lands catches through SQL
// functions defined in a migration, and each query goes to the writer or the reader
// according to whether the functions it calls change data.
//
// 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, as the
// migrations create its tables and the example adds rows each time it runs.
package main

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

	_ "github.com/jackc/pgx/v5/stdlib"

	"seagull.sh/seagull/sdk/migrate"
	"seagull.sh/seagull/sdk/migrate/migrate_backend_postgres"
)

// 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 opens and migrates the database at dsn, and runs the example against it.
//
// Takes dsn (string) which is the PostgreSQL connection string.
//
// Returns error when the database cannot be prepared 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() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.New(), migrations, "db/migrations")
	if err != nil {
		return err
	}
	if _, err := migrator.Up(ctx); err != nil {
		return fmt.Errorf("migrating: %w", err)
	}
	return run(ctx, database, os.Stdout)
}

internal/store/market.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const boatHaulSQL = `SELECT haul.species, haul.weight_grams, price_pence(haul.weight_grams, prices.pence_per_kilo) AS value_pence
FROM boat_haul($1) AS haul
JOIN prices ON prices.species = haul.species
ORDER BY haul.species;`

type BoatHaulRow struct {
	Species     *string `json:"species"`
	WeightGrams *int32  `json:"weight_grams"`
	ValuePence  *int32  `json:"value_pence"`
}

func (queries *Queries) BoatHaul(ctx context.Context, boat string) ([]BoatHaulRow, error) {
	rows, err := queries.reader.QueryContext(ctx, boatHaulSQL, boat)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []BoatHaulRow
	for rows.Next() {
		var row BoatHaulRow
		if err := rows.Scan(&row.Species, &row.WeightGrams, &row.ValuePence); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const landCatchSQL = `SELECT land_catch($1, $2, $3) AS id;`

type LandCatchParams struct {
	Boat    string
	Species string
	Grams   int32
}

type LandCatchRow struct {
	ID *int64 `json:"id"`
}

func (queries *Queries) LandCatch(ctx context.Context, params LandCatchParams) (LandCatchRow, error) {
	var row LandCatchRow
	err := queries.writer.QueryRowContext(ctx, landCatchSQL, params.Boat, params.Species, params.Grams).Scan(&row.ID)
	if err != nil {
		return LandCatchRow{}, err
	}
	return row, nil
}

const setPriceSQL = `INSERT INTO prices (species, pence_per_kilo) VALUES ($1, $2);`

type SetPriceParams struct {
	Species      string
	PencePerKilo int32
}

func (queries *Queries) SetPrice(ctx context.Context, params SetPriceParams) error {
	_, err := queries.writer.ExecContext(ctx, setPriceSQL, params.Species, params.PencePerKilo)
	return err
}

const speciesPriceSQL = `SELECT species_price($1) AS pence_per_kilo;`

type SpeciesPriceRow struct {
	PencePerKilo *int32 `json:"pence_per_kilo"`
}

func (queries *Queries) SpeciesPrice(ctx context.Context, species string) (SpeciesPriceRow, error) {
	var row SpeciesPriceRow
	err := queries.reader.QueryRowContext(ctx, speciesPriceSQL, species).Scan(&row.PencePerKilo)
	if err != nil {
		return SpeciesPriceRow{}, err
	}
	return row, nil
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Catches struct {
	ID          int64  `json:"id"`
	Boat        string `json:"boat"`
	Species     string `json:"species"`
	WeightGrams int32  `json:"weight_grams"`
}

type Prices struct {
	Species      string `json:"species"`
	PencePerKilo int32  `json:"pence_per_kilo"`
}

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

LandCatch    on the writer: catch 1, 12000g of mackerel from the Kittiwake
LandCatch    on the writer: catch 2, 3500g of crab from the Kittiwake
LandCatch    on the writer: catch 3, 8000g of pollock from the Puffin
SpeciesPrice on the reader: crab fetches 1200p a kilo
SpeciesPrice on the reader: halibut has no price
BoatHaul     on the reader: the Kittiwake's haul
  crab        3500g  4200p
  mackerel   12000g  5400p

Also on other engines

PostgreSQLSQL and PL/pgSQL functions created in a migration type the queries that call them, and whether they change data, read from their bodies or stated by a sgl.migration(readonly:) line, decides whether each query goes to the writer or the reader.MySQLStored functions created in a migration type the queries that call them, and their data-access characteristic, body or a sgl.migration(readonly:) line decides whether each query goes to the writer or the reader.MariaDBStored functions created in a migration type the queries that call them, and their data-access characteristic, body or a sgl.migration(readonly:) line decides whether each query goes to the writer or the reader.ClickHouseA chip shop menu priced by a lambda SQL function that a migration creates with CREATE OR REPLACE FUNCTION, called like a built-in from a query.