MySQL dialect features

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

MySQL and MariaDB have SQL of their own that other engines do not: INSERT IGNORE and REPLACE INTO, SET columns that hold any number of listed values, GROUP_CONCAT to gather a group into one string, and unsigned integer types. Seagull's MySQL dialect reads all of them, maps the types to Go, and works out which results can be NULL. Each has a sharp edge or two, and the example shows them as well.

The example keeps a harbour register: boats with their kit and crossings, their crews, and the berths they are assigned.

The schema

db/migrations/0001_harbour.up.sql

CREATE TABLE boats (
    registration CHAR(6)                                     NOT NULL PRIMARY KEY,
    name         VARCHAR(50)                                 NOT NULL,
    kit          SET('radio', 'radar', 'liferaft', 'winch')  NOT NULL DEFAULT '' COMMENT 'The equipment on board, any of the SET''s values.',
    crossings    INT UNSIGNED                                NOT NULL DEFAULT 0 COMMENT 'How many times the boat has left the harbour.'
) COMMENT = 'Boats on the harbour register, one per registration.';

CREATE TABLE crew (
    id           BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    registration CHAR(6)     NOT NULL,
    name         VARCHAR(50) NOT NULL,
    `rank`       VARCHAR(20) NOT NULL,
    FOREIGN KEY (registration) REFERENCES boats (registration)
);

CREATE TABLE berths (
    berth        TINYINT UNSIGNED NOT NULL PRIMARY KEY,
    registration CHAR(6)          NOT NULL UNIQUE
);

• kit is a SET: a column that holds any number of the values it lists. It maps to a Go string of the chosen values joined by commas.
• crossings is INT UNSIGNED, so it maps to uint32, and berth is TINYINT UNSIGNED, so it maps to uint8. Go's unsigned types keep the full range, and a negative value cannot be passed by mistake.
• rank is quoted with backticks because RANK has been a reserved word in MySQL since 8.0.

Skipping duplicates: INSERT IGNORE

RegisterBoat uses INSERT IGNORE, so registering a boat whose registration is already on the books does nothing instead of failing. Declared execrows, it returns 1 for a new boat and 0 for one that was ignored.

db/queries/harbour.sql

-- sgl.query(RegisterBoat, execrows)
INSERT IGNORE INTO boats (registration, name, kit) VALUES (?, ?, ?);

The sharp edge: IGNORE turns other errors into warnings too. A kit item the SET does not list is dropped without a word, as the program shows with a sonar that never arrives. When you only want to skip duplicates, ON DUPLICATE KEY UPDATE with a no-op, as in upserts, is the safer form.

Replacing rows: REPLACE INTO

REPLACE INTO inserts a row, first deleting every existing row that clashes with it on any unique key. berths has two: the berth itself, and registration, which is UNIQUE.

db/queries/harbour.sql

-- sgl.query(AssignBerth, execrows)
REPLACE INTO berths (berth, registration) VALUES (?, ?);

execrows counts the deleted rows as well as the inserted one. Moving Salty Chip to berth 1 clashes twice, with the Kittiwake's row for berth 1 and with Salty Chip's own row for berth 2, so it affects three rows, and the Kittiwake is left with no berth at all. That is usually a surprise, and the count is how you would notice.

internal/store

type AssignBerthParams struct {
	Berth        uint8
	Registration string
}

func (queries *Queries) AssignBerth(ctx context.Context, params AssignBerthParams) (int64, error) {
	results, err := queries.writer.ExecContext(ctx, assignBerthSQL, params.Berth, params.Registration)
	if err != nil {
		return 0, err
	}
	return results.RowsAffected()
}

Looking inside a SET: FIND_IN_SET

FIND_IN_SET finds a value in a comma-separated list, which is what a SET column reads as. Its first argument has no column to take a type from, so sgl.param names it and types it as text.

db/queries/harbour.sql

-- sgl.query(BoatsWithKit, many)
-- ?1 as sgl.param(item, type: text)
SELECT registration, name, kit FROM boats WHERE FIND_IN_SET(?1, kit) > 0 ORDER BY registration;

MySQL and MariaDB store a SET's values in the order the SET declares them, once each, so the Kittiwake's radar,radio,radar reads back as radio,radar.

Gathering a group: GROUP_CONCAT

Harbour lists each boat with its crew gathered into one string. A group with nothing to gather gives NULL, so Seagull types GROUP_CONCAT as nullable and Crew is a *string. Here that happens for a boat with no crew, which the LEFT JOIN keeps.

db/queries/harbour.sql

-- sgl.query(Harbour, many)
SELECT
    boats.registration,
    boats.name,
    boats.kit,
    boats.crossings,
    GROUP_CONCAT(CONCAT(crew.name, ' the ', crew.`rank`) ORDER BY crew.id SEPARATOR ', ') AS crew
FROM boats
LEFT JOIN crew ON crew.registration = boats.registration
GROUP BY boats.registration
ORDER BY boats.registration;

internal/store

type HarbourRow struct {
	Registration string  `json:"registration"`
	Name         string  `json:"name"`
	Kit          string  `json:"kit"`
	Crossings    uint32  `json:"crossings"`
	Crew         *string `json:"crew"`
}

type Crew struct {
	ID           int64  `json:"id"`
	Registration string `json:"registration"`
	Name         string `json:"name"`
	Rank         string `json:"rank"`
}

func (queries *Queries) Harbour(ctx context.Context) ([]HarbourRow, error) {
	rows, err := queries.reader.QueryContext(ctx, harbourSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []HarbourRow
	for rows.Next() {
		var row HarbourRow
		if err := rows.Scan(&row.Registration, &row.Name, &row.Kit, &row.Crossings, &row.Crew); 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/039_mysql_dialect_features/internal/store"
)

// run keeps a harbour's register with SQL that only MySQL and MariaDB speak, and prints
// what each statement did:
//
//   - kit is a SET column, which holds any number of the values it lists. It maps to a
//     string of the chosen values joined by commas, and MySQL stores them in the order
//     the SET lists them, once each. FIND_IN_SET looks one up;
//   - crossings is INT UNSIGNED, so Crossings is a uint32, and berth is TINYINT UNSIGNED,
//     so Berth is a uint8;
//   - RegisterBoat is INSERT IGNORE: a registration already on the books counts 0 rows
//     instead of failing. IGNORE turns other errors into warnings too, so a kit item the
//     SET does not list is dropped without a word;
//   - AssignBerth is REPLACE INTO, which deletes every row that clashes with the new one
//     on any unique key, then inserts it. execrows counts the deleted rows as well;
//   - Harbour gathers each boat's crew into one string with GROUP_CONCAT, which is NULL
//     for a boat with no crew. The crew's `rank` column is quoted with backticks, as RANK
//     is a reserved word since MySQL 8.0;
//   - boats documents itself with MySQL's COMMENT, after a column's definition and as the
//     table's COMMENT = option. sgl.yaml sets go.doc_comments, so they become the doc
//     comments of the Boats model and its Kit and Crossings fields: open internal/store
//     to see them.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives what each statement did.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	boats := []store.RegisterBoatParams{
		{Registration: "PZ1001", Name: "Kittiwake", Kit: "radar,radio,radar"},
		{Registration: "PZ2002", Name: "Salty Chip", Kit: "liferaft"},
		{Registration: "PZ1001", Name: "Kittiwake II", Kit: "winch"},
		{Registration: "PZ3003", Name: "Gull Wing", Kit: "radio,sonar"},
	}
	for _, boat := range boats {
		registered, err := queries.RegisterBoat(ctx, boat)
		if err != nil {
			return fmt.Errorf("registering %s: %w", boat.Name, err)
		}
		if registered == 0 {
			fmt.Fprintf(output, "INSERT IGNORE  %s is already registered, so %s was ignored\n", boat.Registration, boat.Name)
			continue
		}
		fmt.Fprintf(output, "INSERT IGNORE  registered %s as %s with kit %q\n", boat.Name, boat.Registration, boat.Kit)
	}

	for _, registration := range []string{"PZ1001", "PZ1001", "PZ3003"} {
		if _, err := queries.LogCrossing(ctx, registration); err != nil {
			return fmt.Errorf("logging a crossing for %s: %w", registration, err)
		}
	}
	crew := []store.SignOnParams{
		{Registration: "PZ1001", Name: "Morwenna", Rank: "skipper"},
		{Registration: "PZ1001", Name: "Jago", Rank: "deckhand"},
		{Registration: "PZ3003", Name: "Kerensa", Rank: "skipper"},
	}
	for _, member := range crew {
		if err := queries.SignOn(ctx, member); err != nil {
			return fmt.Errorf("signing on %s: %w", member.Name, err)
		}
	}

	// Moving Salty Chip to berth 1 clashes twice: berth 1 is the Kittiwake's, and
	// registration is UNIQUE, so Salty Chip's own berth 2 goes too.
	berths := []store.AssignBerthParams{
		{Berth: 1, Registration: "PZ1001"},
		{Berth: 2, Registration: "PZ2002"},
		{Berth: 1, Registration: "PZ2002"},
	}
	for _, berth := range berths {
		affected, err := queries.AssignBerth(ctx, berth)
		if err != nil {
			return fmt.Errorf("assigning berth %d: %w", berth.Berth, err)
		}
		fmt.Fprintf(output, "REPLACE INTO   berth %d for %s, rows affected: %d\n", berth.Berth, berth.Registration, affected)
	}
	assigned, err := queries.ListBerths(ctx)
	if err != nil {
		return fmt.Errorf("listing berths: %w", err)
	}
	for _, berth := range assigned {
		fmt.Fprintf(output, "  berth %d (%T): %s\n", berth.Berth, berth.Berth, berth.Name)
	}

	withRadio, err := queries.BoatsWithKit(ctx, "radio")
	if err != nil {
		return fmt.Errorf("finding boats with a radio: %w", err)
	}
	for _, boat := range withRadio {
		fmt.Fprintf(output, "FIND_IN_SET    %s has a radio among %q\n", boat.Name, boat.Kit)
	}

	harbour, err := queries.Harbour(ctx)
	if err != nil {
		return fmt.Errorf("listing the harbour: %w", err)
	}
	for _, boat := range harbour {
		crewList := "no crew"
		if boat.Crew != nil {
			crewList = *boat.Crew
		}
		fmt.Fprintf(output, "GROUP_CONCAT   %s %-10s crossings %d (%T), crew: %s\n", boat.Registration, boat.Name, boat.Crossings, boat.Crossings, crewList)
	}
	return nil
}

What it prints

The %T in the program prints the Go types of the unsigned columns: uint8 for the berth and uint32 for the crossings.

INSERT IGNORE  registered Kittiwake as PZ1001 with kit "radar,radio,radar"
INSERT IGNORE  registered Salty Chip as PZ2002 with kit "liferaft"
INSERT IGNORE  PZ1001 is already registered, so Kittiwake II was ignored
INSERT IGNORE  registered Gull Wing as PZ3003 with kit "radio,sonar"
REPLACE INTO   berth 1 for PZ1001, rows affected: 1
REPLACE INTO   berth 2 for PZ2002, rows affected: 1
REPLACE INTO   berth 1 for PZ2002, rows affected: 3
  berth 1 (uint8): Salty Chip
FIND_IN_SET    Kittiwake has a radio among "radio,radar"
FIND_IN_SET    Gull Wing has a radio among "radio"
GROUP_CONCAT   PZ1001 Kittiwake  crossings 2 (uint32), crew: Morwenna the skipper, Jago the deckhand
GROUP_CONCAT   PZ2002 Salty Chip crossings 0 (uint32), crew: no crew
GROUP_CONCAT   PZ3003 Gull Wing  crossings 1 (uint32), crew: Kerensa the skipper

Try it

Change crossings to BIGINT UNSIGNED in the migration and run sgl generate: Crossings becomes a uint64. Seagull follows the width of an integer type as well as its sign, so the Go field can always hold what the column can.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/mysql && go run ./039_mysql_dialect_features

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

cd seagull/examples/mysql && SGL_EXAMPLE_ENGINE=mariadb SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./039_mysql_dialect_features

Needs a running MariaDB 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: harbour
    dialect: mysql
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      doc_comments: true

db/migrations/0001_harbour.up.sql

CREATE TABLE boats (
    registration CHAR(6)                                     NOT NULL PRIMARY KEY,
    name         VARCHAR(50)                                 NOT NULL,
    kit          SET('radio', 'radar', 'liferaft', 'winch')  NOT NULL DEFAULT '' COMMENT 'The equipment on board, any of the SET''s values.',
    crossings    INT UNSIGNED                                NOT NULL DEFAULT 0 COMMENT 'How many times the boat has left the harbour.'
) COMMENT = 'Boats on the harbour register, one per registration.';

CREATE TABLE crew (
    id           BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    registration CHAR(6)     NOT NULL,
    name         VARCHAR(50) NOT NULL,
    `rank`       VARCHAR(20) NOT NULL,
    FOREIGN KEY (registration) REFERENCES boats (registration)
);

CREATE TABLE berths (
    berth        TINYINT UNSIGNED NOT NULL PRIMARY KEY,
    registration CHAR(6)          NOT NULL UNIQUE
);

db/migrations/0001_harbour.down.sql

DROP TABLE berths;
DROP TABLE crew;
DROP TABLE boats;

db/queries/harbour.sql

-- sgl.query(RegisterBoat, execrows)
INSERT IGNORE INTO boats (registration, name, kit) VALUES (?, ?, ?);

-- sgl.query(LogCrossing, execrows)
UPDATE boats SET crossings = crossings + 1 WHERE registration = ?;

-- sgl.query(SignOn, exec)
INSERT INTO crew (registration, name, `rank`) VALUES (?, ?, ?);

-- sgl.query(AssignBerth, execrows)
REPLACE INTO berths (berth, registration) VALUES (?, ?);

-- sgl.query(ListBerths, many)
SELECT berths.berth, boats.name
FROM berths
JOIN boats ON boats.registration = berths.registration
ORDER BY berths.berth;

-- sgl.query(BoatsWithKit, many)
-- ?1 as sgl.param(item, type: text)
SELECT registration, name, kit FROM boats WHERE FIND_IN_SET(?1, kit) > 0 ORDER BY registration;

-- sgl.query(Harbour, many)
SELECT
    boats.registration,
    boats.name,
    boats.kit,
    boats.crossings,
    GROUP_CONCAT(CONCAT(crew.name, ' the ', crew.`rank`) ORDER BY crew.id SEPARATOR ', ') AS crew
FROM boats
LEFT JOIN crew ON crew.registration = boats.registration
GROUP BY boats.registration
ORDER BY boats.registration;

example.go

package main

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

	"seagull.sh/seagull/examples/mysql/039_mysql_dialect_features/internal/store"
)

// run keeps a harbour's register with SQL that only MySQL and MariaDB speak, and prints
// what each statement did:
//
//   - kit is a SET column, which holds any number of the values it lists. It maps to a
//     string of the chosen values joined by commas, and MySQL stores them in the order
//     the SET lists them, once each. FIND_IN_SET looks one up;
//   - crossings is INT UNSIGNED, so Crossings is a uint32, and berth is TINYINT UNSIGNED,
//     so Berth is a uint8;
//   - RegisterBoat is INSERT IGNORE: a registration already on the books counts 0 rows
//     instead of failing. IGNORE turns other errors into warnings too, so a kit item the
//     SET does not list is dropped without a word;
//   - AssignBerth is REPLACE INTO, which deletes every row that clashes with the new one
//     on any unique key, then inserts it. execrows counts the deleted rows as well;
//   - Harbour gathers each boat's crew into one string with GROUP_CONCAT, which is NULL
//     for a boat with no crew. The crew's `rank` column is quoted with backticks, as RANK
//     is a reserved word since MySQL 8.0;
//   - boats documents itself with MySQL's COMMENT, after a column's definition and as the
//     table's COMMENT = option. sgl.yaml sets go.doc_comments, so they become the doc
//     comments of the Boats model and its Kit and Crossings fields: open internal/store
//     to see them.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives what each statement did.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	boats := []store.RegisterBoatParams{
		{Registration: "PZ1001", Name: "Kittiwake", Kit: "radar,radio,radar"},
		{Registration: "PZ2002", Name: "Salty Chip", Kit: "liferaft"},
		{Registration: "PZ1001", Name: "Kittiwake II", Kit: "winch"},
		{Registration: "PZ3003", Name: "Gull Wing", Kit: "radio,sonar"},
	}
	for _, boat := range boats {
		registered, err := queries.RegisterBoat(ctx, boat)
		if err != nil {
			return fmt.Errorf("registering %s: %w", boat.Name, err)
		}
		if registered == 0 {
			fmt.Fprintf(output, "INSERT IGNORE  %s is already registered, so %s was ignored\n", boat.Registration, boat.Name)
			continue
		}
		fmt.Fprintf(output, "INSERT IGNORE  registered %s as %s with kit %q\n", boat.Name, boat.Registration, boat.Kit)
	}

	for _, registration := range []string{"PZ1001", "PZ1001", "PZ3003"} {
		if _, err := queries.LogCrossing(ctx, registration); err != nil {
			return fmt.Errorf("logging a crossing for %s: %w", registration, err)
		}
	}
	crew := []store.SignOnParams{
		{Registration: "PZ1001", Name: "Morwenna", Rank: "skipper"},
		{Registration: "PZ1001", Name: "Jago", Rank: "deckhand"},
		{Registration: "PZ3003", Name: "Kerensa", Rank: "skipper"},
	}
	for _, member := range crew {
		if err := queries.SignOn(ctx, member); err != nil {
			return fmt.Errorf("signing on %s: %w", member.Name, err)
		}
	}

	// Moving Salty Chip to berth 1 clashes twice: berth 1 is the Kittiwake's, and
	// registration is UNIQUE, so Salty Chip's own berth 2 goes too.
	berths := []store.AssignBerthParams{
		{Berth: 1, Registration: "PZ1001"},
		{Berth: 2, Registration: "PZ2002"},
		{Berth: 1, Registration: "PZ2002"},
	}
	for _, berth := range berths {
		affected, err := queries.AssignBerth(ctx, berth)
		if err != nil {
			return fmt.Errorf("assigning berth %d: %w", berth.Berth, err)
		}
		fmt.Fprintf(output, "REPLACE INTO   berth %d for %s, rows affected: %d\n", berth.Berth, berth.Registration, affected)
	}
	assigned, err := queries.ListBerths(ctx)
	if err != nil {
		return fmt.Errorf("listing berths: %w", err)
	}
	for _, berth := range assigned {
		fmt.Fprintf(output, "  berth %d (%T): %s\n", berth.Berth, berth.Berth, berth.Name)
	}

	withRadio, err := queries.BoatsWithKit(ctx, "radio")
	if err != nil {
		return fmt.Errorf("finding boats with a radio: %w", err)
	}
	for _, boat := range withRadio {
		fmt.Fprintf(output, "FIND_IN_SET    %s has a radio among %q\n", boat.Name, boat.Kit)
	}

	harbour, err := queries.Harbour(ctx)
	if err != nil {
		return fmt.Errorf("listing the harbour: %w", err)
	}
	for _, boat := range harbour {
		crewList := "no crew"
		if boat.Crew != nil {
			crewList = *boat.Crew
		}
		fmt.Fprintf(output, "GROUP_CONCAT   %s %-10s crossings %d (%T), crew: %s\n", boat.Registration, boat.Name, boat.Crossings, boat.Crossings, crewList)
	}
	return nil
}

main.go

// Command 039_mysql_dialect_features applies its migrations to a MySQL database, then
// runs the example in example.go: a harbour register is kept with INSERT IGNORE, REPLACE
// INTO, a SET column, unsigned integers and GROUP_CONCAT.
//
// Run it with `go run . -dsn 'user:password@tcp(localhost:3306)/harbour'`, 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/harbour.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const assignBerthSQL = `REPLACE INTO berths (berth, registration) VALUES (?, ?);`

type AssignBerthParams struct {
	Berth        uint8
	Registration string
}

func (queries *Queries) AssignBerth(ctx context.Context, params AssignBerthParams) (int64, error) {
	results, err := queries.writer.ExecContext(ctx, assignBerthSQL, params.Berth, params.Registration)
	if err != nil {
		return 0, err
	}
	return results.RowsAffected()
}

const boatsWithKitSQL = `SELECT registration, name, kit FROM boats WHERE FIND_IN_SET(?, kit) > 0 ORDER BY registration;`

type BoatsWithKitRow struct {
	Registration string `json:"registration"`
	Name         string `json:"name"`
	Kit          string `json:"kit"`
}

func (queries *Queries) BoatsWithKit(ctx context.Context, item string) ([]BoatsWithKitRow, error) {
	rows, err := queries.reader.QueryContext(ctx, boatsWithKitSQL, item)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []BoatsWithKitRow
	if rows.Next() {
		var row BoatsWithKitRow
		scanArgs := []any{&row.Registration, &row.Name, &row.Kit}
		for more := true; more; more = rows.Next() {
			row = BoatsWithKitRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const harbourSQL = "SELECT\n    boats.registration,\n    boats.name,\n    boats.kit,\n    boats.crossings,\n    GROUP_CONCAT(CONCAT(crew.name, ' the ', crew.`rank`) ORDER BY crew.id SEPARATOR ', ') AS crew\nFROM boats\nLEFT JOIN crew ON crew.registration = boats.registration\nGROUP BY boats.registration\nORDER BY boats.registration;"

type HarbourRow struct {
	Registration string  `json:"registration"`
	Name         string  `json:"name"`
	Kit          string  `json:"kit"`
	Crossings    uint32  `json:"crossings"`
	Crew         *string `json:"crew"`
}

func (queries *Queries) Harbour(ctx context.Context) ([]HarbourRow, error) {
	rows, err := queries.reader.QueryContext(ctx, harbourSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []HarbourRow
	for rows.Next() {
		var row HarbourRow
		if err := rows.Scan(&row.Registration, &row.Name, &row.Kit, &row.Crossings, &row.Crew); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const listBerthsSQL = `SELECT berths.berth, boats.name
FROM berths
JOIN boats ON boats.registration = berths.registration
ORDER BY berths.berth;`

type ListBerthsRow struct {
	Berth uint8  `json:"berth"`
	Name  string `json:"name"`
}

func (queries *Queries) ListBerths(ctx context.Context) ([]ListBerthsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listBerthsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListBerthsRow
	if rows.Next() {
		var row ListBerthsRow
		scanArgs := []any{&row.Berth, &row.Name}
		for more := true; more; more = rows.Next() {
			row = ListBerthsRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const logCrossingSQL = `UPDATE boats SET crossings = crossings + 1 WHERE registration = ?;`

func (queries *Queries) LogCrossing(ctx context.Context, registration string) (int64, error) {
	results, err := queries.writer.ExecContext(ctx, logCrossingSQL, registration)
	if err != nil {
		return 0, err
	}
	return results.RowsAffected()
}

const registerBoatSQL = `INSERT IGNORE INTO boats (registration, name, kit) VALUES (?, ?, ?);`

type RegisterBoatParams struct {
	Registration string
	Name         string
	Kit          string
}

func (queries *Queries) RegisterBoat(ctx context.Context, params RegisterBoatParams) (int64, error) {
	results, err := queries.writer.ExecContext(ctx, registerBoatSQL, params.Registration, params.Name, params.Kit)
	if err != nil {
		return 0, err
	}
	return results.RowsAffected()
}

const signOnSQL = "INSERT INTO crew (registration, name, `rank`) VALUES (?, ?, ?);"

type SignOnParams struct {
	Registration string
	Name         string
	Rank         string
}

func (queries *Queries) SignOn(ctx context.Context, params SignOnParams) error {
	_, err := queries.writer.ExecContext(ctx, signOnSQL, params.Registration, params.Name, params.Rank)
	return err
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Berths struct {
	Berth        uint8  `json:"berth"`
	Registration string `json:"registration"`
}

// Boats on the harbour register, one per registration.
type Boats struct {
	Registration string `json:"registration"`
	Name         string `json:"name"`
	// The equipment on board, any of the SET's values.
	Kit string `json:"kit"`
	// How many times the boat has left the harbour.
	Crossings uint32 `json:"crossings"`
}

type Crew struct {
	ID           int64  `json:"id"`
	Registration string `json:"registration"`
	Name         string `json:"name"`
	Rank         string `json:"rank"`
}

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

INSERT IGNORE  registered Kittiwake as PZ1001 with kit "radar,radio,radar"
INSERT IGNORE  registered Salty Chip as PZ2002 with kit "liferaft"
INSERT IGNORE  PZ1001 is already registered, so Kittiwake II was ignored
INSERT IGNORE  registered Gull Wing as PZ3003 with kit "radio,sonar"
REPLACE INTO   berth 1 for PZ1001, rows affected: 1
REPLACE INTO   berth 2 for PZ2002, rows affected: 1
REPLACE INTO   berth 1 for PZ2002, rows affected: 3
  berth 1 (uint8): Salty Chip
FIND_IN_SET    Kittiwake has a radio among "radio,radar"
FIND_IN_SET    Gull Wing has a radio among "radio"
GROUP_CONCAT   PZ1001 Kittiwake  crossings 2 (uint32), crew: Morwenna the skipper, Jago the deckhand
GROUP_CONCAT   PZ2002 Salty Chip crossings 0 (uint32), crew: no crew
GROUP_CONCAT   PZ3003 Gull Wing  crossings 1 (uint32), crew: Kerensa the skipper