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

On SQLite:

• ? placeholders, which the generated SQL numbers ?1, ?2
• execresult returns sql.Result, and SQLite's driver fills in LastInsertId
• BOOLEAN columns map to bool, stored as 0 and 1

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 = 1 WHERE at_sea = 0;

-- 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 int64  `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 SQLite's driver fills in LastInsertId, and that is the id the new boat was given.

internal/store

type MoorBoatParams struct {
	Name  string
	Berth int64
}

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/sqlite/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 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 without
// RETURNING, makes sgl vet report Q009.
//
// 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.
	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.

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

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

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

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

Run it locally

From the directory you cloned the Seagull repository into:

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

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

cd seagull/examples/sqlite && go test ./002_commands

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: sqlite
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_boats.up.sql

CREATE TABLE boats (
    id     INTEGER PRIMARY KEY,
    name   TEXT    NOT NULL,
    berth  INTEGER NOT NULL,
    at_sea BOOLEAN NOT NULL DEFAULT 0
);

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 = 1 WHERE at_sea = 0;

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

example.go

package main

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

	"seagull.sh/seagull/examples/sqlite/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 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 without
// RETURNING, makes sgl vet report Q009.
//
// 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.
	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 SQLite 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 .`, or `go run . -db harbour.db` to keep the database.
package main

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

	_ "modernc.org/sqlite"

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

// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS

func main() {
	path := flag.String("db", "", "SQLite database file; a temporary one when empty")
	flag.Parse()
	if err := execute(context.Background(), *path); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database at path, or a temporary one when path is empty,
// and runs the example against it.
//
// Takes path (string) which is the database file, or empty.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, path string) error {
	if path == "" {
		directory, err := os.MkdirTemp("", "seagull-example-")
		if err != nil {
			return err
		}
		defer func() { _ = os.RemoveAll(directory) }()
		path = filepath.Join(directory, "example.db")
	}
	database, err := sql.Open("sqlite", "file:"+path)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

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

internal/store/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 = ?1;`

type GetBoatRow struct {
	ID    int64  `json:"id"`
	Name  string `json:"name"`
	Berth int64  `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 int64  `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 (?1, ?2);`

type MoorBoatParams struct {
	Name  string
	Berth int64
}

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 = 1 WHERE at_sea = 0;`

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 = ?1;`

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 int64  `json:"berth"`
	AtSea bool   `json:"at_sea"`
}

internal/store/querier.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type dbtxWrapper interface {
	WrapDBTX(inner any) any
}

type Queries struct {
	reader  DBTX
	writer  DBTX
	wrapper dbtxWrapper
}

func New(db DBTX) *Queries {
	wrapper, _ := db.(dbtxWrapper)
	return &Queries{reader: db, writer: db, wrapper: wrapper}
}

func NewWithReplica(writer DBTX, reader DBTX) *Queries {
	wrapper, _ := writer.(dbtxWrapper)
	return &Queries{reader: reader, writer: writer, wrapper: wrapper}
}

func (queries *Queries) WithTx(transaction *sql.Tx) *Queries {
	if queries == nil {
		return &Queries{reader: transaction, writer: transaction, wrapper: nil}
	}
	db := queries.wrapDBTX(transaction)
	return &Queries{reader: db, writer: db, wrapper: queries.wrapper}
}

func (queries *Queries) wrapDBTX(db DBTX) DBTX {
	if queries.wrapper == nil {
		return db
	}
	wrapped, ok := queries.wrapper.WrapDBTX(db).(DBTX)
	if !ok {
		return db
	}
	return wrapped
}

func (queries *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error {
	transaction, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer transaction.Rollback()
	if err = fn(queries.WithTx(transaction)); err != nil {
		return err
	}
	return transaction.Commit()
}

output.txt

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