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

The same on CockroachDB. CockroachDB runs this example unchanged: the SQL and the Go that sgl generates are identical to the PostgreSQL version. Only dialect: in sgl.yaml changes, and the program migrates with migrate_backend_cockroachdb.New().

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, one)
INSERT INTO boats (name, berth) VALUES ($1, $2) RETURNING id;

-- sgl.query(MoveBerth, execresult)
UPDATE boats SET berth = $1 WHERE id = $2;

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

-- 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 NOT at_sea;

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

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

CockroachDB has no "last insert id". To learn the new boat's id, MoorBoat asks for it with RETURNING id, which makes the INSERT return a row, so its command is one:

internal/store

type MoorBoatParams struct {
	Name  string
	Berth int64
}

type MoorBoatRow struct {
	ID int64 `json:"id"`
}

func (queries *Queries) MoorBoat(ctx context.Context, params MoorBoatParams) (MoorBoatRow, error) {
	var row MoorBoatRow
	err := queries.writer.QueryRowContext(ctx, moorBoatSQL, params.Name, params.Berth).Scan(&row.ID)
	if err != nil {
		return MoorBoatRow{}, err
	}
	return row, nil
}

execresult still exists, and returns the driver's sql.Result untouched. RowsAffected works, but LastInsertId always fails, which the program shows on MoveBerth:

internal/store

type MoveBerthParams struct {
	Berth int64
	ID    int64
}

func (queries *Queries) MoveBerth(ctx context.Context, params MoveBerthParams) (sql.Result, error) {
	return queries.writer.ExecContext(ctx, moveBerthSQL, params.Berth, params.ID)
}

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/postgres/002_commands/internal/store"
)

// run moors three boats, moves one, 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:
//
//   - one returns a single row, or sql.ErrNoRows when nothing matches; on an INSERT with
//     RETURNING it hands back the new row's ID;
//   - execresult returns sql.Result. pgx's driver fills in RowsAffected, but its
//     LastInsertId always fails, as PostgreSQL has no last insert ID: use RETURNING;
//   - 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 {
		moored, err := queries.MoorBoat(ctx, arrival)
		if err != nil {
			return fmt.Errorf("mooring %s: %w", arrival.Name, err)
		}
		fmt.Fprintf(output, "one         moored %s as boat %d\n", arrival.Name, moored.ID)
	}

	result, err := queries.MoveBerth(ctx, store.MoveBerthParams{Berth: 4, ID: 2})
	if err != nil {
		return fmt.Errorf("moving boat 2: %w", err)
	}
	moved, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("reading the rows moved: %w", err)
	}
	fmt.Fprintf(output, "execresult  moved boat 2 to berth 4, %d row affected\n", moved)
	if _, err := result.LastInsertId(); err != nil {
		fmt.Fprintf(output, "execresult  LastInsertId fails: %v\n", err)
	}

	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

one         moored Kittiwake as boat 1
one         moored Salty Chip as boat 2
one         moored Gull Wing as boat 3
execresult  moved boat 2 to berth 4, 1 row affected
execresult  LastInsertId fails: LastInsertId is not supported by this driver
one         boat 2 is Salty Chip, at berth 4
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 4  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.

	PostgreSQL	SQLite	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 SQLite and MySQL.

Run it locally

From the directory you cloned the Seagull repository into:

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

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

cd seagull/examples/postgres && SGL_EXAMPLE_ENGINE=cockroachdb SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./002_commands

Needs a running CockroachDB 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: postgres
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      driver: database/sql

db/migrations/0001_boats.up.sql

CREATE TABLE boats (
    id     BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name   TEXT    NOT NULL,
    berth  BIGINT  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, one)
INSERT INTO boats (name, berth) VALUES ($1, $2) RETURNING id;

-- sgl.query(MoveBerth, execresult)
UPDATE boats SET berth = $1 WHERE id = $2;

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

-- 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 NOT at_sea;

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

example.go

package main

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

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

// run moors three boats, moves one, 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:
//
//   - one returns a single row, or sql.ErrNoRows when nothing matches; on an INSERT with
//     RETURNING it hands back the new row's ID;
//   - execresult returns sql.Result. pgx's driver fills in RowsAffected, but its
//     LastInsertId always fails, as PostgreSQL has no last insert ID: use RETURNING;
//   - 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 {
		moored, err := queries.MoorBoat(ctx, arrival)
		if err != nil {
			return fmt.Errorf("mooring %s: %w", arrival.Name, err)
		}
		fmt.Fprintf(output, "one         moored %s as boat %d\n", arrival.Name, moored.ID)
	}

	result, err := queries.MoveBerth(ctx, store.MoveBerthParams{Berth: 4, ID: 2})
	if err != nil {
		return fmt.Errorf("moving boat 2: %w", err)
	}
	moved, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("reading the rows moved: %w", err)
	}
	fmt.Fprintf(output, "execresult  moved boat 2 to berth 4, %d row affected\n", moved)
	if _, err := result.LastInsertId(); err != nil {
		fmt.Fprintf(output, "execresult  LastInsertId fails: %v\n", err)
	}

	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 PostgreSQL database, then runs the
// example in example.go: boats are moored, moved, looked up, listed, sent to sea and
// scrapped, one query per command kind.
//
// Run it with `go run . -dsn postgres://user:password@localhost:5432/example`, 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/jackc/pgx/v5/stdlib"

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

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

// execute opens and migrates the database at dsn, and runs the example against it.
//
// Takes dsn (string) which is the PostgreSQL connection string.
//
// 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 PostgreSQL connection string")
	}
	database, err := sql.Open("pgx", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.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) RETURNING id;`

type MoorBoatParams struct {
	Name  string
	Berth int64
}

type MoorBoatRow struct {
	ID int64 `json:"id"`
}

func (queries *Queries) MoorBoat(ctx context.Context, params MoorBoatParams) (MoorBoatRow, error) {
	var row MoorBoatRow
	err := queries.writer.QueryRowContext(ctx, moorBoatSQL, params.Name, params.Berth).Scan(&row.ID)
	if err != nil {
		return MoorBoatRow{}, err
	}
	return row, nil
}

const moveBerthSQL = `UPDATE boats SET berth = $1 WHERE id = $2;`

type MoveBerthParams struct {
	Berth int64
	ID    int64
}

func (queries *Queries) MoveBerth(ctx context.Context, params MoveBerthParams) (sql.Result, error) {
	return queries.writer.ExecContext(ctx, moveBerthSQL, params.Berth, params.ID)
}

const putToSeaSQL = `UPDATE boats SET at_sea = true WHERE NOT at_sea;`

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

one         moored Kittiwake as boat 1
one         moored Salty Chip as boat 2
one         moored Gull Wing as boat 3
execresult  moved boat 2 to berth 4, 1 row affected
execresult  LastInsertId fails: LastInsertId is not supported by this driver
one         boat 2 is Salty Chip, at berth 4
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 4  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