002 Choosing a command: one, many, exec, execrows, execresult on MySQL

On MySQL:
? placeholders
No RETURNING (sgl vet reports Q052), so execresult and LastInsertId are how an INSERT reports the AUTO_INCREMENT id
BOOLEAN is TINYINT(1) in MySQL and maps to bool
execrows counts rows changed, not rows matched, unless the DSN sets clientFoundRows=true

The second argument of sgl.query is the command. It says what the caller needs back from the statement, and so it decides the generated method's signature: one row, a slice of rows, a count of changed rows, the driver's result, or only an error. Picking the command is picking the Go API you want to call.

This example keeps a harbour's boats, with one query for each command.

The queries

The whole query file is short enough to read in one go. Each header names the method and its command.

db/queries/boats.sql

-- sgl.query(MoorBoat, execresult)
INSERT INTO boats (name, berth) VALUES (?, ?);

-- sgl.query(GetBoat, one)
SELECT id, name, berth, at_sea FROM boats WHERE id = ?;

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

-- sgl.query(PutToSea, execrows)
UPDATE boats SET at_sea = TRUE WHERE at_sea = FALSE;

-- sgl.query(ScrapBoat, exec)
DELETE FROM boats WHERE id = ?;

Reading rows: one and many

one scans a single row into a struct and returns it. When nothing matches, the method returns the driver's error unchanged, which is sql.ErrNoRows, so the caller can tell "not found" apart from a failure with errors.Is.

internal/store

type GetBoatRow struct {
	ID    int64  `json:"id"`
	Name  string `json:"name"`
	Berth int32  `json:"berth"`
	AtSea bool   `json:"at_sea"`
}

func (queries *Queries) GetBoat(ctx context.Context, id int64) (GetBoatRow, error) {
	var row GetBoatRow
	err := queries.reader.QueryRowContext(ctx, getBoatSQL, id).Scan(&row.ID, &row.Name, &row.Berth, &row.AtSea)
	if err != nil {
		return GetBoatRow{}, err
	}
	return row, nil
}

many returns every row as a slice of the same kind of struct. No rows is not an error: the slice is just empty.

Writing: execresult, execrows and exec

execresult returns the driver's sql.Result untouched. Mooring a boat uses it, because LastInsertId gives the AUTO_INCREMENT id the new boat was given. MySQL has no RETURNING to turn the INSERT into a one query (sgl vet reports Q052 if you try), so this is how an insert hands back its id.

internal/store

type MoorBoatParams struct {
	Name  string
	Berth int32
}

func (queries *Queries) MoorBoat(ctx context.Context, params MoorBoatParams) (sql.Result, error) {
	return queries.writer.ExecContext(ctx, moorBoatSQL, params.Name, params.Berth)
}

execrows returns how many rows the statement changed, as an int64, without making the caller unpack a result. PutToSea only changes boats that are still in harbour, so running it twice tells two different stories: two boats the first time, none the second.

internal/store

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

exec returns only an error, for statements where the caller needs nothing back. Scrapping a boat is one:

internal/store

func (queries *Queries) ScrapBoat(ctx context.Context, id int64) error {
	_, err := queries.writer.ExecContext(ctx, scrapBoatSQL, id)
	return err
}

When the command does not fit

The command has to match what the statement returns. exec on a SELECT would throw the rows away, and one on a DELETE that returns nothing would have nothing to scan, so sgl vet reports both as Q009. It is a warning, so generation still succeeds, but the method would not do what its name promises.

Using it

The program moors three boats, looks one up, scraps one and checks that it is gone, puts the rest to sea twice, and lists the harbour. Each line of output starts with the command that produced it.

example.go

package main

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

	"seagull.sh/seagull/examples/mysql/002_commands/internal/store"
)

// run moors three boats, looks one up, scraps one, puts the rest to sea and lists the
// harbour. Each query in db/queries/boats.sql names a command, and the command decides
// what its generated method returns:
//
//   - execresult returns sql.Result, which carries the new row's AUTO_INCREMENT id;
//   - one returns a single row, or sql.ErrNoRows when nothing matches;
//   - many returns a slice of rows;
//   - execrows returns how many rows the statement changed;
//   - exec returns only an error.
//
// The command has to fit the statement: exec on a SELECT, or one on an INSERT, makes sgl
// vet report Q009. MySQL has no RETURNING to turn a write into a one query, and sgl vet
// refuses one with Q052, so execresult is how an INSERT hands back its id.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives what each call returned.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	arrivals := []store.MoorBoatParams{
		{Name: "Kittiwake", Berth: 1},
		{Name: "Salty Chip", Berth: 2},
		{Name: "Gull Wing", Berth: 5},
	}
	for _, arrival := range arrivals {
		result, err := queries.MoorBoat(ctx, arrival)
		if err != nil {
			return fmt.Errorf("mooring %s: %w", arrival.Name, err)
		}
		id, err := result.LastInsertId()
		if err != nil {
			return fmt.Errorf("reading %s's ID: %w", arrival.Name, err)
		}
		fmt.Fprintf(output, "execresult  moored %s as boat %d\n", arrival.Name, id)
	}

	boat, err := queries.GetBoat(ctx, 2)
	if err != nil {
		return fmt.Errorf("getting boat 2: %w", err)
	}
	fmt.Fprintf(output, "one         boat 2 is %s, at berth %d\n", boat.Name, boat.Berth)

	if err := queries.ScrapBoat(ctx, 3); err != nil {
		return fmt.Errorf("scrapping boat 3: %w", err)
	}
	fmt.Fprintln(output, "exec        scrapped boat 3")

	_, err = queries.GetBoat(ctx, 3)
	if err == nil {
		return errors.New("getting scrapped boat 3: got a boat, want sql.ErrNoRows")
	}
	if !errors.Is(err, sql.ErrNoRows) {
		return fmt.Errorf("getting scrapped boat 3: got %w, want sql.ErrNoRows", err)
	}
	fmt.Fprintln(output, "one         boat 3 is gone, so GetBoat returns sql.ErrNoRows")

	// PutToSea only changes boats still in harbour, so a second run changes none. MySQL
	// counts the rows a statement changed, not the rows its WHERE matched, unless the DSN
	// sets clientFoundRows=true.
	for range 2 {
		sailed, err := queries.PutToSea(ctx)
		if err != nil {
			return fmt.Errorf("putting boats to sea: %w", err)
		}
		fmt.Fprintf(output, "execrows    %d boats put to sea\n", sailed)
	}

	boats, err := queries.ListBoats(ctx)
	if err != nil {
		return fmt.Errorf("listing boats: %w", err)
	}
	for _, boat := range boats {
		fmt.Fprintf(output, "many        berth %d  %-10s  at sea: %t\n", boat.Berth, boat.Name, boat.AtSea)
	}
	return nil
}

What it prints

execresult  moored Kittiwake as boat 1
execresult  moored Salty Chip as boat 2
execresult  moored Gull Wing as boat 3
one         boat 2 is Salty Chip, at berth 2
exec        scrapped boat 3
one         boat 3 is gone, so GetBoat returns sql.ErrNoRows
execrows    2 boats put to sea
execrows    0 boats put to sea
many        berth 1  Kittiwake   at sea: true
many        berth 2  Salty Chip  at sea: true

Try it

Change ScrapBoat's command to one, or ListBoats's to exec, and run sgl vet. Each mismatch is reported as Q009, naming the command and how many columns the statement actually produces.

Across engines

Only these Go types of MoorBoat differ between engines; the rest are the same everywhere.

	MySQL	SQLite	PostgreSQL
`berth` parameter	`int32`	`int64`	`int64`
`id` column	·	·	`int64`

A · marks a parameter or column that version of the example does not have.

MariaDB runs the MySQL version unchanged.

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

Compare the whole example side by side with SQLite and PostgreSQL.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/mysql && go run ./002_commands

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

cd seagull/examples/mysql && SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./002_commands

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

db/migrations/0001_boats.up.sql

CREATE TABLE boats (
    id     BIGINT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   VARCHAR(100) NOT NULL,
    berth  INT          NOT NULL,
    at_sea BOOLEAN      NOT NULL DEFAULT FALSE
);

db/migrations/0001_boats.down.sql

DROP TABLE boats;

db/queries/boats.sql

-- sgl.query(MoorBoat, execresult)
INSERT INTO boats (name, berth) VALUES (?, ?);

-- sgl.query(GetBoat, one)
SELECT id, name, berth, at_sea FROM boats WHERE id = ?;

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

-- sgl.query(PutToSea, execrows)
UPDATE boats SET at_sea = TRUE WHERE at_sea = FALSE;

-- sgl.query(ScrapBoat, exec)
DELETE FROM boats WHERE id = ?;

example.go

package main

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

	"seagull.sh/seagull/examples/mysql/002_commands/internal/store"
)

// run moors three boats, looks one up, scraps one, puts the rest to sea and lists the
// harbour. Each query in db/queries/boats.sql names a command, and the command decides
// what its generated method returns:
//
//   - execresult returns sql.Result, which carries the new row's AUTO_INCREMENT id;
//   - one returns a single row, or sql.ErrNoRows when nothing matches;
//   - many returns a slice of rows;
//   - execrows returns how many rows the statement changed;
//   - exec returns only an error.
//
// The command has to fit the statement: exec on a SELECT, or one on an INSERT, makes sgl
// vet report Q009. MySQL has no RETURNING to turn a write into a one query, and sgl vet
// refuses one with Q052, so execresult is how an INSERT hands back its id.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives what each call returned.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	arrivals := []store.MoorBoatParams{
		{Name: "Kittiwake", Berth: 1},
		{Name: "Salty Chip", Berth: 2},
		{Name: "Gull Wing", Berth: 5},
	}
	for _, arrival := range arrivals {
		result, err := queries.MoorBoat(ctx, arrival)
		if err != nil {
			return fmt.Errorf("mooring %s: %w", arrival.Name, err)
		}
		id, err := result.LastInsertId()
		if err != nil {
			return fmt.Errorf("reading %s's ID: %w", arrival.Name, err)
		}
		fmt.Fprintf(output, "execresult  moored %s as boat %d\n", arrival.Name, id)
	}

	boat, err := queries.GetBoat(ctx, 2)
	if err != nil {
		return fmt.Errorf("getting boat 2: %w", err)
	}
	fmt.Fprintf(output, "one         boat 2 is %s, at berth %d\n", boat.Name, boat.Berth)

	if err := queries.ScrapBoat(ctx, 3); err != nil {
		return fmt.Errorf("scrapping boat 3: %w", err)
	}
	fmt.Fprintln(output, "exec        scrapped boat 3")

	_, err = queries.GetBoat(ctx, 3)
	if err == nil {
		return errors.New("getting scrapped boat 3: got a boat, want sql.ErrNoRows")
	}
	if !errors.Is(err, sql.ErrNoRows) {
		return fmt.Errorf("getting scrapped boat 3: got %w, want sql.ErrNoRows", err)
	}
	fmt.Fprintln(output, "one         boat 3 is gone, so GetBoat returns sql.ErrNoRows")

	// PutToSea only changes boats still in harbour, so a second run changes none. MySQL
	// counts the rows a statement changed, not the rows its WHERE matched, unless the DSN
	// sets clientFoundRows=true.
	for range 2 {
		sailed, err := queries.PutToSea(ctx)
		if err != nil {
			return fmt.Errorf("putting boats to sea: %w", err)
		}
		fmt.Fprintf(output, "execrows    %d boats put to sea\n", sailed)
	}

	boats, err := queries.ListBoats(ctx)
	if err != nil {
		return fmt.Errorf("listing boats: %w", err)
	}
	for _, boat := range boats {
		fmt.Fprintf(output, "many        berth %d  %-10s  at sea: %t\n", boat.Berth, boat.Name, boat.AtSea)
	}
	return nil
}

main.go

// Command 002_commands applies its migrations to a MySQL database, then runs the example
// in example.go: boats are moored, looked up, listed, sent to sea and scrapped, one query
// per command kind.
//
// 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/boats.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"database/sql"
)

const getBoatSQL = `SELECT id, name, berth, at_sea FROM boats WHERE id = ?;`

type GetBoatRow struct {
	ID    int64  `json:"id"`
	Name  string `json:"name"`
	Berth int32  `json:"berth"`
	AtSea bool   `json:"at_sea"`
}

func (queries *Queries) GetBoat(ctx context.Context, id int64) (GetBoatRow, error) {
	var row GetBoatRow
	err := queries.reader.QueryRowContext(ctx, getBoatSQL, id).Scan(&row.ID, &row.Name, &row.Berth, &row.AtSea)
	if err != nil {
		return GetBoatRow{}, err
	}
	return row, nil
}

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

type ListBoatsRow struct {
	ID    int64  `json:"id"`
	Name  string `json:"name"`
	Berth int32  `json:"berth"`
	AtSea bool   `json:"at_sea"`
}

func (queries *Queries) ListBoats(ctx context.Context) ([]ListBoatsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listBoatsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListBoatsRow
	if rows.Next() {
		var row ListBoatsRow
		scanArgs := []any{&row.ID, &row.Name, &row.Berth, &row.AtSea}
		for more := true; more; more = rows.Next() {
			row = ListBoatsRow{}
			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 moorBoatSQL = `INSERT INTO boats (name, berth) VALUES (?, ?);`

type MoorBoatParams struct {
	Name  string
	Berth int32
}

func (queries *Queries) MoorBoat(ctx context.Context, params MoorBoatParams) (sql.Result, error) {
	return queries.writer.ExecContext(ctx, moorBoatSQL, params.Name, params.Berth)
}

const putToSeaSQL = `UPDATE boats SET at_sea = TRUE WHERE at_sea = FALSE;`

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

const scrapBoatSQL = `DELETE FROM boats WHERE id = ?;`

func (queries *Queries) ScrapBoat(ctx context.Context, id int64) error {
	_, err := queries.writer.ExecContext(ctx, scrapBoatSQL, id)
	return err
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Boats struct {
	ID    int64  `json:"id"`
	Name  string `json:"name"`
	Berth int32  `json:"berth"`
	AtSea bool   `json:"at_sea"`
}

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

execresult  moored Kittiwake as boat 1
execresult  moored Salty Chip as boat 2
execresult  moored Gull Wing as boat 3
one         boat 2 is Salty Chip, at berth 2
exec        scrapped boat 3
one         boat 3 is gone, so GetBoat returns sql.ErrNoRows
execrows    2 boats put to sea
execrows    0 boats put to sea
many        berth 1  Kittiwake   at sea: true
many        berth 2  Salty Chip  at sea: true

Choosing a command: one, many, exec, execrows, execresult

The queries

Reading rows: one and many

Writing: execresult, execrows and exec

When the command does not fit

Using it

What it prints

Try it

Across engines

Run it locally

The files

Also on other engines