MySQL dialect features
On this page
On MySQL:
- SET columns map to string: the chosen values joined by commas, stored in the SET's own order, once each
- UNSIGNED integers map to Go's unsigned types: INT UNSIGNED to uint32, TINYINT UNSIGNED to uint8
- INSERT IGNORE counts 0 rows for a duplicate key, and also turns other errors, such as an unknown SET value, into warnings
- REPLACE INTO deletes every row that clashes on any unique key before it inserts, and execrows counts those deletions
- GROUP_CONCAT is nullable, as a group with no matching rows gives NULL
- Backticks quote rank, a reserved word since MySQL 8.0
- A column's COMMENT and the table's COMMENT = option become doc comments on the model and its fields under go.doc_comments
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
);
kitis aSET: a column that holds any number of the values it lists. It maps to a Gostringof the chosen values joined by commas.crossingsisINT UNSIGNED, so it maps touint32, andberthisTINYINT UNSIGNED, so it maps touint8. Go's unsigned types keep the full range, and a negative value cannot be passed by mistake.rankis quoted with backticks becauseRANKhas 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_DSN="$DATABASE_URL" go test ./039_mysql_dialect_features
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: 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