Functions in migrations

On this page

On MySQL:

  • NO SQL and READS SQL DATA functions are read-only; MODIFIES SQL DATA, or an INSERT, UPDATE or DELETE in the body, makes a query write unless sgl.migration(readonly: true) precedes the CREATE FUNCTION
  • MySQL never checks a characteristic against the body, and refuses a function that is not DETERMINISTIC, NO SQL or READS SQL DATA while binary logging is on
  • A BEGIN ... END body needs no DELIMITER line in a migration; DELIMITER is a mysql client command, not SQL
  • No table-valued functions, so BoatHaul joins the tables itself and calls price_pence per row
  • Function arguments have no column to name them, so ?1 as sgl.param(boat) names them

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 three stored functions, each showing a different case:

db/migrations/0002_market_functions.up.sql

CREATE FUNCTION price_pence(weight_grams INT, pence_per_kilo INT) RETURNS INT
DETERMINISTIC NO SQL
RETURN weight_grams * pence_per_kilo / 1000;

CREATE FUNCTION land_catch(boat_name VARCHAR(50), caught VARCHAR(50), grams INT) RETURNS BIGINT
DETERMINISTIC MODIFIES SQL DATA
BEGIN
    INSERT INTO catches (boat, species, weight_grams) VALUES (boat_name, caught, grams);
    RETURN LAST_INSERT_ID();
END;

-- sgl.migration(readonly: true)
CREATE FUNCTION species_price(wanted VARCHAR(50)) RETURNS INT
DETERMINISTIC MODIFIES SQL DATA
RETURN (SELECT pence_per_kilo FROM prices WHERE species = wanted);

MySQL and MariaDB functions declare how they use data. NO SQL and READS SQL DATA functions are read-only; MODIFIES SQL DATA, or an INSERT, UPDATE or DELETE in the body, makes a query that calls the function a write.

  • price_pence is NO SQL: a pure calculation.
  • land_catch is MODIFIES SQL DATA and inserts a row. Its body is a BEGIN ... END block, which needs no DELIMITER line in a migration: DELIMITER is a command of the mysql client, not SQL, and Seagull splits the file into statements itself.
  • species_price says MODIFIES SQL DATA, but its body only reads. Neither server checks a characteristic against the body, so the declaration can be wrong, and the sgl.migration(readonly: true) line above it corrects it for Seagull.

With binary logging on, the server refuses a function that is not DETERMINISTIC, NO SQL or READS SQL DATA, so the two MODIFIES SQL DATA functions are declared DETERMINISTIC too.

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 sgl.param names the parameters:

db/queries/market.sql

-- sgl.query(LandCatch, one)
-- ?1 as sgl.param(boat)
-- ?2 as sgl.param(species)
-- ?3 as sgl.param(grams)
SELECT land_catch(?1, ?2, ?3) AS id;

Neither server lets a stored function return a table, so BoatHaul joins the tables itself and calls price_pence on each row. price_pence is NO SQL, so the query is read-only.

db/queries/market.sql

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

species and weight_grams come straight from the tables and are not NULL. A function's result can be NULL, so value_pence is a pointer.

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/mysql/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
// what it says about data access, along with what its body does, decides whether those
// queries are read-only, which picks the writer or the reader.
//
//   - land_catch says MODIFIES SQL DATA and its body inserts a row, so LandCatch goes to
//     the writer. Its body is a BEGIN ... END block, which a migration can hold without
//     DELIMITER, as Seagull splits the file into statements itself;
//   - price_pence says NO SQL, so BoatHaul, which calls it, goes to the reader;
//   - species_price says MODIFIES SQL DATA, which Seagull takes to mean it writes, but
//     its body only reads. MySQL never checks these characteristics against the body. The
//     sgl.migration(readonly: true) line above it in the migration says so, and
//     SpeciesPrice goes to the reader.
//
// With binary logging on, as it is by default, MySQL refuses a function that is not
// DETERMINISTIC, NO SQL or READS SQL DATA, so the two MODIFIES SQL DATA functions are
// declared DETERMINISTIC too.
//
// A function's result can be NULL, so the columns are pointers: species_price finds no
// row for a fish with no price.
//
// 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 %6dg %5sp\n", row.Species, 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/mysql && go run ./030_functions_in_migrations

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

cd seagull/examples/mysql && SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./030_functions_in_migrations

Needs a running MySQL 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: mysql
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_catches.up.sql

CREATE TABLE catches (
    id           BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    boat         VARCHAR(50) NOT NULL,
    species      VARCHAR(50) NOT NULL,
    weight_grams INT         NOT NULL
);

CREATE TABLE prices (
    species        VARCHAR(50) NOT NULL PRIMARY KEY,
    pence_per_kilo INT         NOT NULL
);

db/migrations/0002_market_functions.up.sql

CREATE FUNCTION price_pence(weight_grams INT, pence_per_kilo INT) RETURNS INT
DETERMINISTIC NO SQL
RETURN weight_grams * pence_per_kilo / 1000;

CREATE FUNCTION land_catch(boat_name VARCHAR(50), caught VARCHAR(50), grams INT) RETURNS BIGINT
DETERMINISTIC MODIFIES SQL DATA
BEGIN
    INSERT INTO catches (boat, species, weight_grams) VALUES (boat_name, caught, grams);
    RETURN LAST_INSERT_ID();
END;

-- sgl.migration(readonly: true)
CREATE FUNCTION species_price(wanted VARCHAR(50)) RETURNS INT
DETERMINISTIC MODIFIES SQL DATA
RETURN (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;
DROP FUNCTION land_catch;
DROP FUNCTION price_pence;

db/queries/market.sql

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

-- sgl.query(LandCatch, one)
-- ?1 as sgl.param(boat)
-- ?2 as sgl.param(species)
-- ?3 as sgl.param(grams)
SELECT land_catch(?1, ?2, ?3) AS id;

-- sgl.query(SpeciesPrice, one)
-- ?1 as sgl.param(species)
SELECT species_price(?1) AS pence_per_kilo;

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

example.go

package main

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

	"seagull.sh/seagull/examples/mysql/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
// what it says about data access, along with what its body does, decides whether those
// queries are read-only, which picks the writer or the reader.
//
//   - land_catch says MODIFIES SQL DATA and its body inserts a row, so LandCatch goes to
//     the writer. Its body is a BEGIN ... END block, which a migration can hold without
//     DELIMITER, as Seagull splits the file into statements itself;
//   - price_pence says NO SQL, so BoatHaul, which calls it, goes to the reader;
//   - species_price says MODIFIES SQL DATA, which Seagull takes to mean it writes, but
//     its body only reads. MySQL never checks these characteristics against the body. The
//     sgl.migration(readonly: true) line above it in the migration says so, and
//     SpeciesPrice goes to the reader.
//
// With binary logging on, as it is by default, MySQL refuses a function that is not
// DETERMINISTIC, NO SQL or READS SQL DATA, so the two MODIFIES SQL DATA functions are
// declared DETERMINISTIC too.
//
// A function's result can be NULL, so the columns are pointers: species_price finds no
// row for a fish with no price.
//
// 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 %6dg %5sp\n", row.Species, 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 MySQL database, then
// runs the example in example.go: catches are landed and priced through SQL functions
// created in a migration, and each query goes to the writer or the reader as Seagull's
// read-only detection decides.
//
// Run it with `go run . -dsn 'user:password@tcp(localhost:3306)/market'`, 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/go-sql-driver/mysql"

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

// 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"), "MySQL data source name; defaults to $DATABASE_URL")
	flag.Parse()
	if err := execute(context.Background(), *dsn); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database dsn names, and runs the example against it.
//
// Takes dsn (string) which is the database's data source name.
//
// 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 MySQL data source name")
	}
	config, err := mysql.ParseDSN(dsn)
	if err != nil {
		return err
	}
	// Seagull maps DATETIME and TIMESTAMP columns to time.Time, which the driver returns
	// only when parseTime is set.
	config.ParseTime = true
	dsn = config.FormatDSN()

	database, err := sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_mysql.New(migrate.WithDriverMultiStatements(config.MultiStatements)), 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 catches.species, catches.weight_grams, price_pence(catches.weight_grams, prices.pence_per_kilo) AS value_pence
FROM catches
JOIN prices ON prices.species = catches.species
WHERE catches.boat = ?
ORDER BY catches.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(?, ?, ?) 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 (?, ?);`

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(?) 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 reads DATETIME, DATE and TIMESTAMP columns into time.Time, so the DSN it connects with must set parseTime=true.
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.TimescaleDBSQL 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.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.