Missing rows without errors

On SQLite:

• ON CONFLICT (ring) DO NOTHING needs a UNIQUE constraint or index on ring

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

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 (?, ?)
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.

Using it

The program tries to ring three gulls, the third with a ring that is already taken, then 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/sqlite/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).
//
// 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"},
	}
	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
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 PostgreSQL and DuckDB.

Run it locally

From the directory you cloned the Seagull repository into:

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

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

cd seagull/examples/sqlite && go test ./018_optional_one

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

db/migrations/0001_gulls.up.sql

CREATE TABLE gulls (
    id       INTEGER 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 (?, ?)
ON CONFLICT (ring) DO NOTHING
RETURNING id, ring;

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

example.go

package main

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

	"seagull.sh/seagull/examples/sqlite/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).
//
// 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"},
	}
	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 SQLite 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 .`, or `go run . -db ringing.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/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
ring GB1002 is on gull 2, a kittiwake
no gull wears ring GB9999