Missing rows without errors

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

A one query that finds nothing returns sql.ErrNoRows, as choosing a command showed. That is right when the row must exist. When a missing row is a normal answer, such as looking up a ring number that may not have been issued, every caller ends up writing the same errors.Is(err, sql.ErrNoRows) check, and a real failure is easy to lose among the expected ones.

optional: true on the query header changes the method's signature to (row, ok, error). A missing row gives ok false and a nil error, and the error is left for things that really went wrong, the same way a Go map lookup reports a missing key.

This example rings gulls and looks them up by ring.

Looking a row up

db/queries/gulls.sql

-- sgl.query(FindGull, one, optional: true)
SELECT id, ring, species FROM gulls WHERE ring = $1;

internal/store

type FindGullRow struct {
	ID      int64  `json:"id"`
	Ring    string `json:"ring"`
	Species string `json:"species"`
}

func (queries *Queries) FindGull(ctx context.Context, ring string) (FindGullRow, bool, error) {
	var row FindGullRow
	err := queries.reader.QueryRowContext(ctx, findGullSQL, ring).Scan(&row.ID, &row.Ring, &row.Species)
	if errors.Is(err, sql.ErrNoRows) {
		return FindGullRow{}, false, nil
	}
	if err != nil {
		return FindGullRow{}, false, err
	}
	return row, true, nil
}

The method still runs an ordinary single-row query. It catches sql.ErrNoRows itself and turns it into false, so the caller tests a bool instead of an error.

An insert that may do nothing

INSERT ... ON CONFLICT DO NOTHING RETURNING is the natural way to say "add this unless it is already there". When the ring is already taken, the insert is skipped, so RETURNING has no row to return. As a plain one query, that skipped insert would come back as sql.ErrNoRows, and sgl vet warns about exactly that combination with Q046. With optional: true, the skip is just ok false:

db/queries/gulls.sql

-- sgl.query(RingGull, one, optional: true)
INSERT INTO gulls (ring, species) VALUES ($1, $2)
ON CONFLICT (ring) DO NOTHING
RETURNING id, ring;

ON CONFLICT (ring) needs a unique constraint or index on ring to detect the conflict, which the migration gives it.

TimescaleDB takes the next id from the identity column's sequence before it checks for the conflict, and a skipped insert does not hand it back. So the gull that is turned away and then ringed with a new number becomes gull 4, not gull 3. Ids from a sequence are unique, not gapless.

Using it

The program tries to ring four gulls: the third with a ring that is already taken, and the fourth the same gull again with a new ring. Then it looks up one ring that exists and one that does not. Each call checks err first, then ok.

example.go

package main

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

	"seagull.sh/seagull/examples/postgres/018_optional_one/internal/store"
)

// run rings gulls and looks them up. Both queries are one queries with optional: true, so
// they return (row, ok, error): a missing row gives ok false and a nil error instead of
// sql.ErrNoRows, and error is left for real failures.
//
// RingGull inserts with ON CONFLICT (ring) DO NOTHING RETURNING. A ring that is already
// taken skips the insert, so RETURNING has no row and ok is false. Without optional: true
// sgl vet warns about this (Q046), as the call would fail with sql.ErrNoRows. optional
// applies only to a one query; on any other command sgl vet refuses it (Q047).
//
// PostgreSQL takes the next id from the identity column's sequence before it checks for
// the conflict, and a skipped insert does not hand it back. The black-headed gull that is
// turned away and then ringed GB1003 becomes gull 4, not gull 3.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each ringing and lookup.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	attempts := []store.RingGullParams{
		{Ring: "GB1001", Species: "herring gull"},
		{Ring: "GB1002", Species: "kittiwake"},
		{Ring: "GB1001", Species: "black-headed gull"},
		{Ring: "GB1003", Species: "black-headed gull"},
	}
	for _, attempt := range attempts {
		ringed, ok, err := queries.RingGull(ctx, attempt)
		if err != nil {
			return fmt.Errorf("ringing %s: %w", attempt.Ring, err)
		}
		if !ok {
			fmt.Fprintf(output, "ring %s is already taken, so the %s was not ringed\n", attempt.Ring, attempt.Species)
			continue
		}
		fmt.Fprintf(output, "ringed a %s as %s, gull %d\n", attempt.Species, ringed.Ring, ringed.ID)
	}

	for _, ring := range []string{"GB1002", "GB9999"} {
		gull, ok, err := queries.FindGull(ctx, ring)
		if err != nil {
			return fmt.Errorf("finding %s: %w", ring, err)
		}
		if !ok {
			fmt.Fprintf(output, "no gull wears ring %s\n", ring)
			continue
		}
		fmt.Fprintf(output, "ring %s is on gull %d, a %s\n", ring, gull.ID, gull.Species)
	}
	return nil
}

What it prints

ringed a herring gull as GB1001, gull 1
ringed a kittiwake as GB1002, gull 2
ring GB1001 is already taken, so the black-headed gull was not ringed
ringed a black-headed gull as GB1003, gull 4
ring GB1002 is on gull 2, a kittiwake
no gull wears ring GB9999

Try it

optional only makes sense for a one query. Add it to a many query and sgl vet refuses it with Q047; remove it from RingGull and sgl vet warns with Q046.

Across engines

RingGull has the same Go types on every engine that has this example.

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

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

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/postgres && go run ./018_optional_one

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

cd seagull/examples/postgres && SGL_EXAMPLE_ENGINE=timescaledb SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./018_optional_one

Needs a running TimescaleDB 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: ringing
    dialect: postgres
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      driver: database/sql
    migrate:
      dsn: ${env:DATABASE_URL}

db/migrations/0001_gulls.up.sql

CREATE TABLE gulls (
    id      BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ring    TEXT   NOT NULL UNIQUE,
    species TEXT   NOT NULL
);

db/migrations/0001_gulls.down.sql

DROP TABLE gulls;

db/queries/gulls.sql

-- sgl.query(RingGull, one, optional: true)
INSERT INTO gulls (ring, species) VALUES ($1, $2)
ON CONFLICT (ring) DO NOTHING
RETURNING id, ring;

-- sgl.query(FindGull, one, optional: true)
SELECT id, ring, species FROM gulls WHERE ring = $1;

example.go

package main

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

	"seagull.sh/seagull/examples/postgres/018_optional_one/internal/store"
)

// run rings gulls and looks them up. Both queries are one queries with optional: true, so
// they return (row, ok, error): a missing row gives ok false and a nil error instead of
// sql.ErrNoRows, and error is left for real failures.
//
// RingGull inserts with ON CONFLICT (ring) DO NOTHING RETURNING. A ring that is already
// taken skips the insert, so RETURNING has no row and ok is false. Without optional: true
// sgl vet warns about this (Q046), as the call would fail with sql.ErrNoRows. optional
// applies only to a one query; on any other command sgl vet refuses it (Q047).
//
// PostgreSQL takes the next id from the identity column's sequence before it checks for
// the conflict, and a skipped insert does not hand it back. The black-headed gull that is
// turned away and then ringed GB1003 becomes gull 4, not gull 3.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each ringing and lookup.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	attempts := []store.RingGullParams{
		{Ring: "GB1001", Species: "herring gull"},
		{Ring: "GB1002", Species: "kittiwake"},
		{Ring: "GB1001", Species: "black-headed gull"},
		{Ring: "GB1003", Species: "black-headed gull"},
	}
	for _, attempt := range attempts {
		ringed, ok, err := queries.RingGull(ctx, attempt)
		if err != nil {
			return fmt.Errorf("ringing %s: %w", attempt.Ring, err)
		}
		if !ok {
			fmt.Fprintf(output, "ring %s is already taken, so the %s was not ringed\n", attempt.Ring, attempt.Species)
			continue
		}
		fmt.Fprintf(output, "ringed a %s as %s, gull %d\n", attempt.Species, ringed.Ring, ringed.ID)
	}

	for _, ring := range []string{"GB1002", "GB9999"} {
		gull, ok, err := queries.FindGull(ctx, ring)
		if err != nil {
			return fmt.Errorf("finding %s: %w", ring, err)
		}
		if !ok {
			fmt.Fprintf(output, "no gull wears ring %s\n", ring)
			continue
		}
		fmt.Fprintf(output, "ring %s is on gull %d, a %s\n", ring, gull.ID, gull.Species)
	}
	return nil
}

main.go

// Command 018_optional_one applies its migrations to a PostgreSQL database, then runs the
// example in example.go: gulls are ringed and looked up, with a duplicate ring and an
// unknown ring reported as a false bool rather than an error.
//
// 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/gulls.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"database/sql"
	"errors"
)

const findGullSQL = `SELECT id, ring, species FROM gulls WHERE ring = $1;`

type FindGullRow struct {
	ID      int64  `json:"id"`
	Ring    string `json:"ring"`
	Species string `json:"species"`
}

func (queries *Queries) FindGull(ctx context.Context, ring string) (FindGullRow, bool, error) {
	var row FindGullRow
	err := queries.reader.QueryRowContext(ctx, findGullSQL, ring).Scan(&row.ID, &row.Ring, &row.Species)
	if errors.Is(err, sql.ErrNoRows) {
		return FindGullRow{}, false, nil
	}
	if err != nil {
		return FindGullRow{}, false, err
	}
	return row, true, nil
}

const ringGullSQL = `INSERT INTO gulls (ring, species) VALUES ($1, $2)
ON CONFLICT (ring) DO NOTHING
RETURNING id, ring;`

type RingGullParams struct {
	Ring    string
	Species string
}

type RingGullRow struct {
	ID   int64  `json:"id"`
	Ring string `json:"ring"`
}

func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) (RingGullRow, bool, error) {
	var row RingGullRow
	err := queries.writer.QueryRowContext(ctx, ringGullSQL, params.Ring, params.Species).Scan(&row.ID, &row.Ring)
	if errors.Is(err, sql.ErrNoRows) {
		return RingGullRow{}, false, nil
	}
	if err != nil {
		return RingGullRow{}, false, err
	}
	return row, true, nil
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Gulls struct {
	ID      int64  `json:"id"`
	Ring    string `json:"ring"`
	Species string `json:"species"`
}

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

ringed a herring gull as GB1001, gull 1
ringed a kittiwake as GB1002, gull 2
ring GB1001 is already taken, so the black-headed gull was not ringed
ringed a black-headed gull as GB1003, gull 4
ring GB1002 is on gull 2, a kittiwake
no gull wears ring GB9999