The runtime query builder

On ClickHouse:

• The builder binds values with $N placeholders, which clickhouse-go's database/sql driver binds positionally; a {name:Type} placeholder in the base query is rewritten to $N too
• The base query's WHERE makes runtime filters join with AND (hint Q022)
• Allowed columns are quoted with backticks, as in lost_property.colour
• ClickHouse has no default row order, so the red search adds OrderBy("id", "ASC")
• Claim is an ALTER TABLE ... UPDATE mutation, declared asyncexec

Some searches cannot be written as one static query. A filter screen with a dozen optional fields, a choice of sort columns and paging would need either a query for every combination or SQL glued together from strings, and glued SQL is where injection comes from. dynamic: runtime gives you a third way. The query you write is the fixed part, and Seagull generates a builder for it: Where, OrderBy, Limit and Offset add to it as the program runs, and All, One or Count run it. Every column, operator and direction a caller names is checked against an allow-list, and every value is sent as a bound parameter.

This example searches the lost property office on a pier for items nobody has claimed yet.

The query

db/queries/lost_property.sql

-- sgl.query(SearchUnclaimed, many, dynamic: runtime)
SELECT id, item, colour, found_at, days_held
FROM lost_property
WHERE claimed = false

dynamic: runtime on the header is all it takes. Two things about the SQL matter:

• It does not end with a semicolon. The builder appends conditions, ordering and limits to the end of the statement, which it cannot do after a terminator, so a trailing ; is error Q025.
• It already has a WHERE, which always applies: only unclaimed items. The builder's filters are joined to it with AND, and sgl vet points this out as hint Q022, so you can check that the fixed condition should indeed apply to every search.

What sgl generates

In place of a method that runs the query, SearchUnclaimed returns a builder:

internal/store

type SearchUnclaimedBuilder struct {
	queries      *Queries
	predicates   []sglPredicate
	orders       []string
	limit        int
	offset       int
	pendingError error
}

type Queries struct {
	reader  DBTX
	writer  DBTX
	wrapper dbtxWrapper
}

type sglPredicate struct {
	text   string
	values []any
	list   bool
}

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
}

func (queries *Queries) SearchUnclaimed() *SearchUnclaimedBuilder {
	return &SearchUnclaimedBuilder{queries: queries}
}

Each builder method adds one piece and returns the builder, so calls chain:

• Where(column, operator, value) adds a condition;
• OrderBy(column, direction) adds a sort key;
• Limit(n) and Offset(n) page the result;
• All(ctx) returns every row, One(ctx) the first, and Count(ctx) how many rows match.

Count counts every row the conditions match, whatever Limit and Offset say. Here it replaces the select list with COUNT(*). For a query that groups, is DISTINCT or uses a window function, it wraps the whole query instead, and sgl vet notes that as hint Q023; for one it cannot rewrite at all, the builder has no Count, and sgl vet warns with Q024.

The allow-lists

The builder only ever writes three kinds of caller input into the SQL, and each one comes from a closed list:

• Columns are the columns the query selects: id, item, colour, found_at and days_held. owner_phone is in the table but not in the select list, so it cannot be filtered or sorted on. To filter by a column, select it.
• Operators are a fixed set: comparisons such as =, <> and >=, LIKE and ILIKE, IS NULL and IS NOT NULL, and IN and NOT IN.
• Directions are ASC and DESC, optionally with NULLS FIRST or NULLS LAST.

Values are never written into the SQL at all. They are always bound as parameters, so a value cannot change the statement's structure, however it is written.

When the builder refuses

A column, operator or direction outside its list does not panic, and does not reach the database. The call that received it records an error, the later calls carry on, and the terminal (All, One or Count) returns the first error recorded. The errors are exported from the generated package, so callers can tell them apart with errors.Is, as in errors.Is(err, store.ErrSglUnknownColumn):

Error	Returned when
ErrSglUnknownColumn	Where or OrderBy names a column the query does not select, or a column expression of a shape it does not support
ErrSglUnknownOperator	Where uses an operator outside the allow-list
ErrSglUnknownDirection	OrderBy uses a direction outside the allow-list

That makes it safe to pass a search form's fields straight to the builder, and to turn a refusal into a "bad request" answer rather than a server error.

Using it

The program hands in five items and claims one. It then runs three searches: the red items, the items held for 30 days or more with the longest first, and a count of umbrellas. Next it searches for an item called ' OR '1'='1, which finds nothing, because the value is bound, not run. Last, it tries four unsafe inputs and checks with errors.Is that each is refused: a column the query does not select, SQL in a column name, an operator with SQL in it, and a direction with SQL in it.

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/014_runtime_builder/internal/store"
)

// run searches the pier's unclaimed lost property. dynamic: runtime turns SearchUnclaimed
// into a builder: Where, OrderBy, Limit and Offset add to the SQL, and All, One or Count
// run it. The base query already has WHERE claimed = false, so each Where is joined with
// AND, which sgl vet points out as hint Q022. The builder inserts its filters, order and
// limit at the places the query leaves for them, before any LIMIT or trailing semicolon,
// and Count replaces the SELECT list with COUNT(*), wrapping the whole query instead when
// it groups or is DISTINCT (Q023).
//
// The builder is safe with untrusted input. Where and OrderBy accept only the columns the
// query selects, so owner_phone, which the table has but the query leaves out, cannot be
// searched. Operators and directions come from fixed allow-lists, and every value is
// bound as a parameter, never written into the SQL. A refused input does not panic: the
// terminal returns ErrSglUnknownColumn, ErrSglUnknownOperator or ErrSglUnknownDirection.
//
// Claim is an ALTER TABLE ... UPDATE mutation, so it is asyncexec; the connection sets
// mutations_sync=2, so the claimed phone is gone from the searches that follow.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the searches and the refusals.
//
// Returns error when a query fails or an unsafe input is not refused.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	items := []store.HandInParams{
		{ID: 1, Item: "umbrella", Colour: "red", FoundAt: "the pier", DaysHeld: 40},
		{ID: 2, Item: "sunglasses", Colour: "black", FoundAt: "the beach", DaysHeld: 12},
		{ID: 3, Item: "bucket and spade", Colour: "red", FoundAt: "the beach", DaysHeld: 3},
		{ID: 4, Item: "umbrella", Colour: "blue", FoundAt: "the cafe", DaysHeld: 33},
		{ID: 5, Item: "phone", Colour: "black", FoundAt: "the pier", DaysHeld: 1},
	}
	for _, item := range items {
		if err := queries.HandIn(ctx, item); err != nil {
			return fmt.Errorf("handing in a %s: %w", item.Item, err)
		}
	}
	if err := queries.Claim(ctx, store.ClaimParams{OwnerPhone: new("07700 900123"), ID: 5}); err != nil {
		return fmt.Errorf("claiming the phone: %w", err)
	}

	red, err := queries.SearchUnclaimed().Where("colour", "=", "red").OrderBy("id", "ASC").All(ctx)
	if err != nil {
		return fmt.Errorf("finding red items: %w", err)
	}
	fmt.Fprintln(output, "red:")
	printItems(output, red)

	longest, err := queries.SearchUnclaimed().
		Where("days_held", ">=", 30).
		OrderBy("days_held", "DESC").
		Limit(2).
		All(ctx)
	if err != nil {
		return fmt.Errorf("finding long-held items: %w", err)
	}
	fmt.Fprintln(output, "held 30 days or more, longest first:")
	printItems(output, longest)

	umbrellas, err := queries.SearchUnclaimed().Where("item", "=", "umbrella").Count(ctx)
	if err != nil {
		return fmt.Errorf("counting umbrellas: %w", err)
	}
	fmt.Fprintf(output, "unclaimed umbrellas: %d\n", umbrellas)

	tautology, err := queries.SearchUnclaimed().Where("item", "=", "' OR '1'='1").All(ctx)
	if err != nil {
		return fmt.Errorf("searching for a quoted value: %w", err)
	}
	fmt.Fprintf(output, "items named ' OR '1'='1: %d, as the value is bound, not run\n", len(tautology))

	attempts := []struct {
		label  string
		search *store.SearchUnclaimedBuilder
	}{
		{label: "a column the query does not select", search: queries.SearchUnclaimed().Where("owner_phone", "IS NOT NULL", nil)},
		{label: "SQL in a column name", search: queries.SearchUnclaimed().OrderBy("id; DROP TABLE lost_property", "ASC")},
		{label: "an operator outside the allow-list", search: queries.SearchUnclaimed().Where("colour", "= 'red' OR 1=1 --", "red")},
		{label: "a direction outside the allow-list", search: queries.SearchUnclaimed().OrderBy("days_held", "DESC; DROP TABLE lost_property")},
	}
	for _, attempt := range attempts {
		_, err := attempt.search.All(ctx)
		switch {
		case errors.Is(err, store.ErrSglUnknownColumn), errors.Is(err, store.ErrSglUnknownOperator), errors.Is(err, store.ErrSglUnknownDirection):
			fmt.Fprintf(output, "refused %s: %v\n", attempt.label, err)
		case err == nil:
			return fmt.Errorf("%s: got no error, want a refusal", attempt.label)
		default:
			return fmt.Errorf("%s: got %w, want a refusal", attempt.label, err)
		}
	}
	return nil
}

// printItems writes one line per item.
//
// Takes output (io.Writer) which receives the lines.
// Takes items ([]store.SearchUnclaimedRow) which are the items to print.
func printItems(output io.Writer, items []store.SearchUnclaimedRow) {
	for _, item := range items {
		fmt.Fprintf(output, "  #%d %s %s, found at %s, held %d days\n", item.ID, item.Colour, item.Item, item.FoundAt, item.DaysHeld)
	}
}

What it prints

red:
  #1 red umbrella, found at the pier, held 40 days
  #3 red bucket and spade, found at the beach, held 3 days
held 30 days or more, longest first:
  #1 red umbrella, found at the pier, held 40 days
  #4 blue umbrella, found at the cafe, held 33 days
unclaimed umbrellas: 2
items named ' OR '1'='1: 0, as the value is bound, not run
refused a column the query does not select: sgl: unknown column in runtime query filter
refused SQL in a column name: sgl: unknown column in runtime query filter
refused an operator outside the allow-list: sgl: unknown operator in runtime query filter
refused a direction outside the allow-list: sgl: unknown sort direction in runtime query order

Try it

Add a semicolon to the end of SearchUnclaimed and run sgl vet: Q025 is an error, and nothing is generated until it is gone.

Across engines

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

	ClickHouse	SQLite	PostgreSQL	MySQL	MariaDB	DuckDB
id column	uint64	int64	int64	int64	int64	int64
days_held column	uint32	int64	int64	int32	int32	int32

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

Compare the whole example side by side with SQLite, PostgreSQL, MySQL, MariaDB and DuckDB.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/clickhouse && go run ./014_runtime_builder

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

cd seagull/examples/clickhouse && SGL_EXAMPLE_DSN=clickhouse://localhost:9000/default go test ./014_runtime_builder

Needs a running ClickHouse server: it connects to clickhouse://localhost:9000/default, or pass -dsn with 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: pier
    dialect: clickhouse
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_lost_property.up.sql

CREATE TABLE lost_property (
    id          UInt64,
    item        String,
    colour      String,
    found_at    String,
    days_held   UInt32,
    claimed     Bool DEFAULT false,
    owner_phone Nullable(String)
) ENGINE = MergeTree() ORDER BY id;

db/migrations/0001_lost_property.down.sql

DROP TABLE lost_property;

db/queries/lost_property.sql

-- sgl.query(HandIn, exec)
INSERT INTO lost_property (id, item, colour, found_at, days_held)
VALUES ({id:UInt64}, {item:String}, {colour:String}, {found_at:String}, {days_held:UInt32});

-- sgl.query(Claim, asyncexec)
ALTER TABLE lost_property
UPDATE claimed = true, owner_phone = {owner_phone:Nullable(String)}
WHERE id = {id:UInt64};

-- sgl.query(SearchUnclaimed, many, dynamic: runtime)
SELECT id, item, colour, found_at, days_held
FROM lost_property
WHERE claimed = false

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/014_runtime_builder/internal/store"
)

// run searches the pier's unclaimed lost property. dynamic: runtime turns SearchUnclaimed
// into a builder: Where, OrderBy, Limit and Offset add to the SQL, and All, One or Count
// run it. The base query already has WHERE claimed = false, so each Where is joined with
// AND, which sgl vet points out as hint Q022. The builder inserts its filters, order and
// limit at the places the query leaves for them, before any LIMIT or trailing semicolon,
// and Count replaces the SELECT list with COUNT(*), wrapping the whole query instead when
// it groups or is DISTINCT (Q023).
//
// The builder is safe with untrusted input. Where and OrderBy accept only the columns the
// query selects, so owner_phone, which the table has but the query leaves out, cannot be
// searched. Operators and directions come from fixed allow-lists, and every value is
// bound as a parameter, never written into the SQL. A refused input does not panic: the
// terminal returns ErrSglUnknownColumn, ErrSglUnknownOperator or ErrSglUnknownDirection.
//
// Claim is an ALTER TABLE ... UPDATE mutation, so it is asyncexec; the connection sets
// mutations_sync=2, so the claimed phone is gone from the searches that follow.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the searches and the refusals.
//
// Returns error when a query fails or an unsafe input is not refused.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	items := []store.HandInParams{
		{ID: 1, Item: "umbrella", Colour: "red", FoundAt: "the pier", DaysHeld: 40},
		{ID: 2, Item: "sunglasses", Colour: "black", FoundAt: "the beach", DaysHeld: 12},
		{ID: 3, Item: "bucket and spade", Colour: "red", FoundAt: "the beach", DaysHeld: 3},
		{ID: 4, Item: "umbrella", Colour: "blue", FoundAt: "the cafe", DaysHeld: 33},
		{ID: 5, Item: "phone", Colour: "black", FoundAt: "the pier", DaysHeld: 1},
	}
	for _, item := range items {
		if err := queries.HandIn(ctx, item); err != nil {
			return fmt.Errorf("handing in a %s: %w", item.Item, err)
		}
	}
	if err := queries.Claim(ctx, store.ClaimParams{OwnerPhone: new("07700 900123"), ID: 5}); err != nil {
		return fmt.Errorf("claiming the phone: %w", err)
	}

	red, err := queries.SearchUnclaimed().Where("colour", "=", "red").OrderBy("id", "ASC").All(ctx)
	if err != nil {
		return fmt.Errorf("finding red items: %w", err)
	}
	fmt.Fprintln(output, "red:")
	printItems(output, red)

	longest, err := queries.SearchUnclaimed().
		Where("days_held", ">=", 30).
		OrderBy("days_held", "DESC").
		Limit(2).
		All(ctx)
	if err != nil {
		return fmt.Errorf("finding long-held items: %w", err)
	}
	fmt.Fprintln(output, "held 30 days or more, longest first:")
	printItems(output, longest)

	umbrellas, err := queries.SearchUnclaimed().Where("item", "=", "umbrella").Count(ctx)
	if err != nil {
		return fmt.Errorf("counting umbrellas: %w", err)
	}
	fmt.Fprintf(output, "unclaimed umbrellas: %d\n", umbrellas)

	tautology, err := queries.SearchUnclaimed().Where("item", "=", "' OR '1'='1").All(ctx)
	if err != nil {
		return fmt.Errorf("searching for a quoted value: %w", err)
	}
	fmt.Fprintf(output, "items named ' OR '1'='1: %d, as the value is bound, not run\n", len(tautology))

	attempts := []struct {
		label  string
		search *store.SearchUnclaimedBuilder
	}{
		{label: "a column the query does not select", search: queries.SearchUnclaimed().Where("owner_phone", "IS NOT NULL", nil)},
		{label: "SQL in a column name", search: queries.SearchUnclaimed().OrderBy("id; DROP TABLE lost_property", "ASC")},
		{label: "an operator outside the allow-list", search: queries.SearchUnclaimed().Where("colour", "= 'red' OR 1=1 --", "red")},
		{label: "a direction outside the allow-list", search: queries.SearchUnclaimed().OrderBy("days_held", "DESC; DROP TABLE lost_property")},
	}
	for _, attempt := range attempts {
		_, err := attempt.search.All(ctx)
		switch {
		case errors.Is(err, store.ErrSglUnknownColumn), errors.Is(err, store.ErrSglUnknownOperator), errors.Is(err, store.ErrSglUnknownDirection):
			fmt.Fprintf(output, "refused %s: %v\n", attempt.label, err)
		case err == nil:
			return fmt.Errorf("%s: got no error, want a refusal", attempt.label)
		default:
			return fmt.Errorf("%s: got %w, want a refusal", attempt.label, err)
		}
	}
	return nil
}

// printItems writes one line per item.
//
// Takes output (io.Writer) which receives the lines.
// Takes items ([]store.SearchUnclaimedRow) which are the items to print.
func printItems(output io.Writer, items []store.SearchUnclaimedRow) {
	for _, item := range items {
		fmt.Fprintf(output, "  #%d %s %s, found at %s, held %d days\n", item.ID, item.Colour, item.Item, item.FoundAt, item.DaysHeld)
	}
}

main.go

// Command 014_runtime_builder applies its migrations to a ClickHouse database, then runs
// the example in example.go: unclaimed lost property is searched with filters, an order,
// a limit and a count chosen at run time, and unsafe inputs are refused.
//
// Run it with `go run .` against a local server, or `go run . -dsn <dsn>` to choose the
// database; give it an empty one. Keep mutations_sync=2 in the DSN so the claimed item
// has left the searches.
package main

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

	_ "github.com/ClickHouse/clickhouse-go/v2"

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

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

func main() {
	dsn := flag.String("dsn", "clickhouse://localhost:9000/default?mutations_sync=2", "ClickHouse DSN of an empty database")
	flag.Parse()
	if err := execute(context.Background(), *dsn); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database the DSN names, and runs the example against it.
//
// Takes dsn (string) which names the ClickHouse database.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, dsn string) error {
	database, err := sql.Open("clickhouse", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_clickhouse.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/bind_limits.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"errors"
	"fmt"
	"strings"
)

const sglMaxBindVariables = 65535

var ErrSglTooManyBindVariables = errors.New("sgl: too many bind variables")

type sglEdit struct {
	Text  string
	Start int
	End   int
}

func sglCheckBinds(count int) error {
	if count > sglMaxBindVariables {
		return fmt.Errorf("sgl: a statement of %d bind variables exceeds the limit of %d: %w", count, sglMaxBindVariables, ErrSglTooManyBindVariables)
	}
	return nil
}

func sglSplice(query string, edits ...[]sglEdit) string {
	size, count := len(query), 0
	for _, group := range edits {
		for _, edit := range group {
			size += len(edit.Text) - (edit.End - edit.Start)
			count++
		}
	}
	if count == 0 {
		return query
	}
	var builder strings.Builder
	builder.Grow(size)
	cursor := 0
	for _, group := range edits {
		for _, edit := range group {
			builder.WriteString(query[cursor:edit.Start])
			builder.WriteString(edit.Text)
			cursor = edit.End
		}
	}
	builder.WriteString(query[cursor:])
	return builder.String()
}

internal/store/clickhouse_values.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"errors"
	"fmt"
	"math"
	"net"
	"net/netip"
	"reflect"
	"slices"
	"strconv"
	"strings"
	"time"
)

const (
	sglClickHouseMaxDepth         = 32
	sglClickHouseNanosecondDigits = 9
	sglClickHouseDigitGroup       = 3
)

var (
	sglClickHouseTextEscaper    = strings.NewReplacer("\\", "\\\\", "\t", "\\t", "\n", "\\n", "\r", "\\r", "\x00", "\\0")
	sglClickHouseQuotedEscaper  = strings.NewReplacer("\\", "\\\\", "'", "\\'", "\t", "\\t", "\n", "\\n", "\r", "\\r", "\x00", "\\0")
	sglClickHouseRefusalCleaner = strings.NewReplacer("\t", " ", "\n", " ", "\r", " ")
	errSglClickHouseShape       = errors.New("sgl: malformed ClickHouse parameter shape")
	errSglClickHouseValue       = errors.New("sgl: cannot write the value of a ClickHouse parameter")
)

type sglClickHouseShape struct {
	names     []string
	elements  []sglClickHouseShape
	digits    int
	prepared  bool
	checked   bool
	driverMap bool
	kind      byte
}

var sglClickHouseShapes = []sglClickHouseShape{{kind: 113, digits: -1, prepared: true, checked: false, driverMap: false, names: []string{}, elements: []sglClickHouseShape{}}, {kind: 110, digits: -1, prepared: true, checked: false, driverMap: false, names: []string{}, elements: []sglClickHouseShape{}}}

func sglClickHouseValue(value any, shape string) string {
	parsed, err := sglClickHouseParseShape(shape)
	if err == nil {
		return sglClickHouseShapedValue(value, parsed)
	}
	return sglClickHouseRefusalCleaner.Replace(err.Error()) + "\t"
}

func sglClickHouseShapedValue(value any, shape sglClickHouseShape) string {
	text, err := sglClickHouseWhole(reflect.ValueOf(value), shape)
	if err != nil {
		return sglClickHouseRefusalCleaner.Replace(err.Error()) + "\t"
	}
	return text
}

func sglClickHouseWhole(value reflect.Value, shape sglClickHouseShape) (string, error) {
	current, err := sglClickHouseFollow(value, 0)
	if err != nil {
		return "", err
	}
	if !current.IsValid() {
		return "\\N", nil
	}
	shape = sglClickHouseResolve(current, shape)
	switch shape.kind {
	case '[', '{', '(':
		return sglClickHouseComposite(current, shape, 0)
	}
	text, err := sglClickHouseScalar(current, shape)
	if err != nil {
		return "", err
	}
	return sglClickHouseTextEscaper.Replace(text), nil
}

func sglClickHouseAppendElement(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
	current, err := sglClickHouseFollow(value, depth)
	if err != nil {
		return nil, err
	}
	if !current.IsValid() {
		return append(text, "NULL"...), nil
	}
	shape = sglClickHouseResolve(current, shape)
	switch shape.kind {
	case '[', '{', '(':
		return sglClickHouseAppendComposite(text, current, shape, depth)
	}
	if shape.kind == 'n' && sglClickHouseIsNumber(current) {
		return sglClickHouseAppendNumber(text, current), nil
	}
	scalar, err := sglClickHouseScalar(current, shape)
	if err != nil {
		return nil, err
	}
	if shape.kind == 'n' {
		return append(text, scalar...), nil
	}
	text = append(text, '\'')
	text = append(text, sglClickHouseQuotedEscaper.Replace(scalar)...)
	return append(text, '\''), nil
}

func sglClickHouseAppendNumber(text []byte, value reflect.Value) []byte {
	const decimalBase = 10
	switch value.Kind() {
	case reflect.Bool:
		return strconv.AppendBool(text, value.Bool())
	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
		return strconv.AppendInt(text, value.Int(), decimalBase)
	case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64, reflect.Uintptr:
		return strconv.AppendUint(text, value.Uint(), decimalBase)
	}
	number := value.Float()
	switch {
	case math.IsNaN(number):
		return append(text, "nan"...)
	case math.IsInf(number, 1):
		return append(text, "inf"...)
	case math.IsInf(number, -1):
		return append(text, "-inf"...)
	}
	return strconv.AppendFloat(text, number, 'g', -1, value.Type().Bits())
}

func sglClickHouseFollow(value reflect.Value, depth int) (reflect.Value, error) {
	for value.IsValid() && (value.Kind() == reflect.Pointer || value.Kind() == reflect.Interface) {
		if value.IsNil() {
			return reflect.Value{}, nil
		}
		depth++
		if depth > sglClickHouseMaxDepth {
			return reflect.Value{}, fmt.Errorf("%w: it nests more than %d levels deep or refers to itself", errSglClickHouseValue, sglClickHouseMaxDepth)
		}
		value = value.Elem()
	}
	return value, nil
}

func sglClickHouseResolve(value reflect.Value, shape sglClickHouseShape) sglClickHouseShape {
	if shape.kind != 'a' {
		return shape
	}
	unknown := sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 'a', checked: false, driverMap: false, prepared: false}
	if _, moment := sglClickHouseMoment(value); moment {
		return sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 't', checked: false, driverMap: false, prepared: false}
	}
	if sglClickHouseIsNumber(value) {
		return sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 'n', checked: false, driverMap: false, prepared: false}
	}
	if sglClickHouseHasText(value) {
		return sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 'q', checked: false, driverMap: false, prepared: false}
	}
	switch value.Kind() {
	case reflect.Slice, reflect.Array:
		if value.Type().Elem().Kind() != reflect.Uint8 {
			return sglClickHouseShape{names: nil, elements: []sglClickHouseShape{unknown}, digits: -1, kind: '[', checked: false, driverMap: false, prepared: false}
		}
	case reflect.Map:
		return sglClickHouseShape{names: nil, elements: []sglClickHouseShape{unknown, unknown}, digits: -1, kind: '{', checked: false, driverMap: false, prepared: false}
	case reflect.Struct:
		fields := make([]sglClickHouseShape, value.NumField())
		for index := range fields {
			fields[index] = unknown
		}
		return sglClickHouseShape{names: nil, elements: fields, digits: -1, kind: '(', checked: false, driverMap: false, prepared: false}
	}
	return sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 'q', checked: false, driverMap: false, prepared: false}
}

func sglClickHouseComposite(value reflect.Value, shape sglClickHouseShape, depth int) (string, error) {
	const initialCapacity = 64
	text, err := sglClickHouseAppendComposite(make([]byte, 0, initialCapacity), value, shape, depth)
	return string(text), err
}

func sglClickHouseAppendComposite(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
	if depth >= sglClickHouseMaxDepth {
		return nil, fmt.Errorf("%w: it nests more than %d levels deep or refers to itself", errSglClickHouseValue, sglClickHouseMaxDepth)
	}
	depth++
	switch shape.kind {
	case '[':
		return sglClickHouseAppendArray(text, value, shape, depth)
	case '{':
		return sglClickHouseAppendMap(text, value, shape, depth)
	default:
		return sglClickHouseAppendTuple(text, value, shape, depth)
	}
}

func sglClickHouseAppendArray(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
	if value.Kind() != reflect.Slice && value.Kind() != reflect.Array {
		return nil, fmt.Errorf("%w: an array needs a slice or an array, not %s", errSglClickHouseValue, value.Type())
	}
	text = append(text, '[')
	for index := 0; index < value.Len(); index++ {
		if index > 0 {
			text = append(text, ',')
		}
		var err error
		text, err = sglClickHouseAppendElement(text, value.Index(index), shape.elements[0], depth)
		if err != nil {
			return nil, err
		}
	}
	return append(text, ']'), nil
}

func sglClickHouseAppendMap(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
	if value.Kind() != reflect.Map {
		return nil, fmt.Errorf("%w: a map needs a Go map, not %s", errSglClickHouseValue, value.Type())
	}
	parts := make([]string, 0, value.Len())
	entries := value.MapRange()
	for entries.Next() {
		part, err := sglClickHouseAppendElement(nil, entries.Key(), shape.elements[0], depth)
		if err != nil {
			return nil, err
		}
		part = append(part, ':')
		part, err = sglClickHouseAppendElement(part, entries.Value(), shape.elements[1], depth)
		if err != nil {
			return nil, err
		}
		parts = append(parts, string(part))
	}
	slices.Sort(parts)
	text = append(text, '{')
	for index, part := range parts {
		if index > 0 {
			text = append(text, ',')
		}
		text = append(text, part...)
	}
	return append(text, '}'), nil
}

func sglClickHouseAppendTuple(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
	elements, err := sglClickHouseTupleElements(value, shape)
	if err != nil {
		return nil, err
	}
	text = append(text, '(')
	for index, element := range elements {
		if index > 0 {
			text = append(text, ", "...)
		}
		text, err = sglClickHouseAppendElement(text, element, shape.elements[index], depth)
		if err != nil {
			return nil, err
		}
	}
	return append(text, ')'), nil
}

func sglClickHouseTupleElements(value reflect.Value, shape sglClickHouseShape) ([]reflect.Value, error) {
	var elements []reflect.Value
	switch value.Kind() {
	case reflect.Struct:
		for index := range value.NumField() {
			if !value.Type().Field(index).IsExported() {
				return nil, fmt.Errorf("%w: a tuple cannot be read from %s, which has unexported fields", errSglClickHouseValue, value.Type())
			}
			elements = append(elements, value.Field(index))
		}
	case reflect.Map:
		if value.Type().Key().Kind() != reflect.String || slices.Contains(shape.names, "") {
			return nil, fmt.Errorf("%w: a tuple is read from a map only by field names", errSglClickHouseValue)
		}
		for _, name := range shape.names {
			elements = append(elements, value.MapIndex(reflect.ValueOf(name).Convert(value.Type().Key())))
		}
	case reflect.Slice, reflect.Array:
		for index := range value.Len() {
			elements = append(elements, value.Index(index))
		}
	default:
		return nil, fmt.Errorf("%w: a tuple needs a struct, a map or a slice, not %s", errSglClickHouseValue, value.Type())
	}
	if len(elements) != len(shape.elements) {
		return nil, fmt.Errorf("%w: the tuple has %d fields but the value holds %d", errSglClickHouseValue, len(shape.elements), len(elements))
	}
	return elements, nil
}

func sglClickHouseScalar(value reflect.Value, shape sglClickHouseShape) (string, error) {
	if shape.kind == '4' || shape.kind == '6' {
		address, isAddress, failure := sglClickHouseAddress(value, shape.kind)
		if failure != "" {
			return "", fmt.Errorf("%w: %s", errSglClickHouseValue, failure)
		}
		if isAddress {
			return address.String(), nil
		}
	}
	if moment, ok := sglClickHouseMoment(value); ok {
		return sglClickHouseTime(moment, shape)
	}
	if shape.kind == 'n' && sglClickHouseIsNumber(value) {
		return sglClickHouseNumber(value), nil
	}
	text, err := sglClickHouseText(value)
	if err == nil && shape.kind == 'n' && !sglClickHouseIsNumberText(text) {
		err = fmt.Errorf("%w: %q is not a number", errSglClickHouseValue, text)
	}
	return text, err
}

func sglClickHouseAddress(value reflect.Value, kind byte) (netip.Addr, bool, string) {
	var address netip.Addr
	switch value.Type() {
	case reflect.TypeFor[netip.Addr]():
		reflect.ValueOf(&address).Elem().Set(value)
		if !address.IsValid() {
			return address, true, "the zero netip.Addr holds no address"
		}
	case reflect.TypeFor[net.IP]():
		bytes := value.Bytes()
		if len(bytes) == 0 {
			return address, true, "an empty net.IP holds no address"
		}
		parsed, valid := netip.AddrFromSlice(bytes)
		if !valid {
			return address, true, fmt.Sprintf("a net.IP of %d bytes is not an IP address", len(bytes))
		}
		address = parsed
	default:
		return address, false, ""
	}
	address = address.WithZone("")
	if kind != '4' {
		return address, true, ""
	}
	if address.Is6() && !address.Is4In6() {
		return address, true, address.String() + " is an IPv6 address, which an IPv4 value cannot hold"
	}
	return address.Unmap(), true, ""
}

func sglClickHouseMoment(value reflect.Value) (time.Time, bool) {
	return reflect.TypeAssert[time.Time](value)
}

func sglClickHouseTime(moment time.Time, shape sglClickHouseShape) (string, error) {
	switch shape.kind {
	case 'd':
		return moment.Format("2006-01-02"), nil
	case 't':
		return sglClickHouseEpoch(moment, shape.digits), nil
	case 'q':
		return moment.Format(time.RFC3339Nano), nil
	default:
		return "", fmt.Errorf("%w: a time.Time cannot be written as a number", errSglClickHouseValue)
	}
}

func sglClickHouseEpoch(moment time.Time, digits int) string {
	const decimalBase = 10
	nanoseconds := int64(moment.Nanosecond())
	if digits < 0 {
		digits = sglClickHouseNanosecondDigits
		for digits > 0 && nanoseconds%int64(math.Pow10(sglClickHouseNanosecondDigits-digits+sglClickHouseDigitGroup)) == 0 {
			digits -= sglClickHouseDigitGroup
		}
	}
	seconds := moment.Unix()
	if digits == 0 {
		return strconv.FormatInt(seconds, decimalBase)
	}
	scale := int64(math.Pow10(digits))
	fraction := nanoseconds / int64(math.Pow10(sglClickHouseNanosecondDigits-digits))
	sign := ""
	if seconds < 0 {
		sign = "-"
		seconds = -seconds
		if fraction > 0 {
			seconds--
			fraction = scale - fraction
		}
	}
	return fmt.Sprintf("%s%d.%0*d", sign, seconds, digits, fraction)
}

func sglClickHouseIsNumber(value reflect.Value) bool {
	switch value.Kind() {
	case reflect.Bool, reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64,
		reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64, reflect.Uintptr,
		reflect.Float32, reflect.Float64:
		return true
	}
	return false
}

func sglClickHouseNumber(value reflect.Value) string {
	const decimalBase = 10
	switch value.Kind() {
	case reflect.Bool:
		return strconv.FormatBool(value.Bool())
	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
		return strconv.FormatInt(value.Int(), decimalBase)
	case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64, reflect.Uintptr:
		return strconv.FormatUint(value.Uint(), decimalBase)
	}
	number := value.Float()
	switch {
	case math.IsNaN(number):
		return "nan"
	case math.IsInf(number, 1):
		return "inf"
	case math.IsInf(number, -1):
		return "-inf"
	}
	return strconv.FormatFloat(number, 'g', -1, value.Type().Bits())
}

func sglClickHouseMethods(value reflect.Value) []any {
	if value.CanAddr() {
		return []any{value.Interface(), value.Addr().Interface()}
	}
	return []any{value.Interface()}
}

func sglClickHouseHasText(value reflect.Value) bool {
	for _, candidate := range sglClickHouseMethods(value) {
		switch candidate.(type) {
		case fmt.Stringer, interface{ String() (string, error) }, error:
			return true
		}
	}
	return false
}

func sglClickHouseText(value reflect.Value) (string, error) {
	for _, candidate := range sglClickHouseMethods(value) {
		switch typed := candidate.(type) {
		case fmt.Stringer:
			return typed.String(), nil
		case interface{ String() (string, error) }:
			text, err := typed.String()
			if err != nil {
				return "", fmt.Errorf("%w: its String method failed: %w", errSglClickHouseValue, err)
			}
			return text, nil
		case error:
			return "", fmt.Errorf("%w: an error is not a value", errSglClickHouseValue)
		}
	}
	switch value.Kind() {
	case reflect.String:
		return value.String(), nil
	case reflect.Slice, reflect.Array:
		if value.Type().Elem().Kind() == reflect.Uint8 {
			bytes := make([]byte, value.Len())
			reflect.Copy(reflect.ValueOf(bytes), value)
			return string(bytes), nil
		}
	}
	if sglClickHouseIsNumber(value) {
		return sglClickHouseNumber(value), nil
	}
	return "", fmt.Errorf("%w: a %s cannot be written as text", errSglClickHouseValue, value.Type())
}

func sglClickHouseIsNumberText(text string) bool {
	if text == "true" || text == "false" {
		return true
	}
	lower := strings.ToLower(sglClickHouseUnsigned(text))
	if lower == "nan" || lower == "inf" {
		return true
	}
	mantissa, exponent, scientific := strings.Cut(lower, "e")
	if scientific {
		exponent = sglClickHouseUnsigned(exponent)
		if exponent == "" || strings.Trim(exponent, "0123456789") != "" {
			return false
		}
	}
	whole, fraction, _ := strings.Cut(mantissa, ".")
	return whole+fraction != "" && strings.Trim(whole+fraction, "0123456789") == ""
}

func sglClickHouseUnsigned(text string) string {
	if strings.HasPrefix(text, "+") || strings.HasPrefix(text, "-") {
		return text[1:]
	}
	return text
}

func sglClickHouseParseShape(text string) (sglClickHouseShape, error) {
	shape, rest, err := sglClickHouseReadShape(text, 0)
	if err == nil && rest != "" {
		err = fmt.Errorf("%w: %q has %q left over", errSglClickHouseShape, text, rest)
	}
	return shape, err
}

func sglClickHouseReadShape(text string, depth int) (sglClickHouseShape, string, error) {
	shape := sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 0, checked: false, driverMap: false, prepared: false}
	if text == "" || depth > sglClickHouseMaxDepth {
		return shape, "", fmt.Errorf("%w: a shape is missing or nests too deeply", errSglClickHouseShape)
	}
	shape.kind, text = text[0], text[1:]
	switch shape.kind {
	case 'q', 'n', 'd', 'a', '4', '6':
		return shape, text, nil
	case 't':
		shape.digits = 0
		if text != "" && text[0] >= '0' && text[0] <= '9' {
			shape.digits, text = int(text[0]-'0'), text[1:]
		}
		return shape, text, nil
	case '[':
		return sglClickHouseReadElements(shape, text, depth, ']')
	case '{':
		return sglClickHouseReadElements(shape, text, depth, '}')
	case '(':
		return sglClickHouseReadElements(shape, text, depth, ')')
	}
	return shape, "", fmt.Errorf("%w: %q is not a shape", errSglClickHouseShape, shape.kind)
}

func sglClickHouseReadElements(shape sglClickHouseShape, text string, depth int, closing byte) (sglClickHouseShape, string, error) {
	for {
		var err error
		if shape, text, err = sglClickHouseReadElement(shape, text, depth); err != nil {
			return shape, "", err
		}
		if text == "" {
			return shape, "", fmt.Errorf("%w: %q is not closed", errSglClickHouseShape, string(shape.kind))
		}
		switch {
		case text[0] == closing && (shape.kind != '{' || len(shape.elements) == 2):
			return shape, text[1:], nil
		case shape.kind == '(' && text[0] == ',', shape.kind == '{' && text[0] == ':' && len(shape.elements) == 1:
			text = text[1:]
		default:
			return shape, "", fmt.Errorf("%w: %q is out of place", errSglClickHouseShape, text[:1])
		}
	}
}

func sglClickHouseReadElement(shape sglClickHouseShape, text string, depth int) (sglClickHouseShape, string, error) {
	name := ""
	if shape.kind == '(' && strings.HasPrefix(text, "'") {
		var err error
		if name, text, err = sglClickHouseReadName(text); err != nil {
			return shape, "", err
		}
	}
	element, rest, err := sglClickHouseReadShape(text, depth+1)
	if err != nil {
		return shape, "", err
	}
	shape.elements = append(shape.elements, element)
	shape.names = append(shape.names, name)
	return shape, rest, nil
}

func sglClickHouseReadName(text string) (name string, rest string, err error) {
	var written strings.Builder
	index := 1
	for index < len(text) {
		if text[index] != '\'' {
			written.WriteByte(text[index])
			index++
			continue
		}
		if index+1 < len(text) && text[index+1] == '\'' {
			written.WriteByte('\'')
			index += 2
			continue
		}
		if index+1 < len(text) && text[index+1] == '=' {
			return written.String(), text[index+2:], nil
		}
		break
	}
	return "", "", fmt.Errorf("%w: a tuple field's name is not closed by '=", errSglClickHouseShape)
}

internal/store/lost_property.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"

	"github.com/ClickHouse/clickhouse-go/v2"
)

const claimSQL = `ALTER TABLE lost_property
UPDATE claimed = true, owner_phone = {p_owner_phone:Nullable(String)}
WHERE id = {p_id:UInt64};`

type ClaimParams struct {
	OwnerPhone *string
	ID         uint64
}

// Claim issues an asynchronous mutation. The server accepts the
// statement and returns when the mutation has been queued; the
// background execution proceeds independently. Inspect the
// engine's mutation log (system.mutations on ClickHouse) for
// completion status. The returned error reflects only the
// acceptance step; a nil error does NOT guarantee that the
// mutation has finished applying.
func (queries *Queries) Claim(ctx context.Context, params ClaimParams) error {
	_, err := queries.writer.ExecContext(ctx, claimSQL, clickhouse.Named("p_owner_phone", sglClickHouseShapedValue(params.OwnerPhone, sglClickHouseShapes[0])), clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[1])))
	return err
}

const handInSQL = `INSERT INTO lost_property (id, item, colour, found_at, days_held)
VALUES ({p_id:UInt64}, {p_item:String}, {p_colour:String}, {p_found_at:String}, {p_days_held:UInt32});`

type HandInParams struct {
	ID       uint64
	Item     string
	Colour   string
	FoundAt  string
	DaysHeld uint32
}

func (queries *Queries) HandIn(ctx context.Context, params HandInParams) error {
	_, err := queries.writer.ExecContext(ctx, handInSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[1])), clickhouse.Named("p_item", sglClickHouseShapedValue(params.Item, sglClickHouseShapes[0])), clickhouse.Named("p_colour", sglClickHouseShapedValue(params.Colour, sglClickHouseShapes[0])), clickhouse.Named("p_found_at", sglClickHouseShapedValue(params.FoundAt, sglClickHouseShapes[0])), clickhouse.Named("p_days_held", sglClickHouseShapedValue(params.DaysHeld, sglClickHouseShapes[1])))
	return err
}

const searchUnclaimedSQL = `SELECT id, item, colour, found_at, days_held
FROM lost_property
WHERE claimed = false`

type SearchUnclaimedRow struct {
	ID       uint64 `json:"id"`
	Item     string `json:"item"`
	Colour   string `json:"colour"`
	FoundAt  string `json:"found_at"`
	DaysHeld uint32 `json:"days_held"`
}

var searchUnclaimedAllowedColumns = map[string]string{"colour": "`lost_property`.`colour`", "days_held": "`lost_property`.`days_held`", "found_at": "`lost_property`.`found_at`", "id": "`lost_property`.`id`", "item": "`lost_property`.`item`"}

const searchUnclaimedCountSQL = `SELECT COUNT(*) FROM lost_property
WHERE claimed = false`

type SearchUnclaimedBuilder struct {
	queries      *Queries
	predicates   []sglPredicate
	orders       []string
	limit        int
	offset       int
	pendingError error
}

func (queries *Queries) SearchUnclaimed() *SearchUnclaimedBuilder {
	return &SearchUnclaimedBuilder{queries: queries}
}

func (builder *SearchUnclaimedBuilder) Where(column string, operator string, value any) *SearchUnclaimedBuilder {
	predicate, err := sglNewPredicate(column, operator, value, searchUnclaimedAllowedColumns)
	if err != nil {
		if builder.pendingError == nil {
			builder.pendingError = err
		}
		return builder
	}
	builder.predicates = append(builder.predicates, predicate)
	return builder
}

func (builder *SearchUnclaimedBuilder) OrderBy(column string, direction string) *SearchUnclaimedBuilder {
	order, err := sglNewOrder(column, direction, searchUnclaimedAllowedColumns)
	if err != nil {
		if builder.pendingError == nil {
			builder.pendingError = err
		}
		return builder
	}
	builder.orders = append(builder.orders, order)
	return builder
}

func (builder *SearchUnclaimedBuilder) Limit(n int) *SearchUnclaimedBuilder {
	builder.limit = n
	return builder
}

func (builder *SearchUnclaimedBuilder) Offset(n int) *SearchUnclaimedBuilder {
	builder.offset = n
	return builder
}

func (builder *SearchUnclaimedBuilder) buildQuery() (string, []any, error) {
	if builder.pendingError != nil {
		return "", nil, builder.pendingError
	}
	args := make([]any, 0, sglFilterCount(builder.predicates)+sglPaginationCount(builder.limit, builder.offset))
	filter, args := sglFilterInto(builder.predicates, 1, args)
	pagination, args := sglPaginationInto(builder.limit, builder.offset, 1+sglFilterCount(builder.predicates), args)
	if err := sglCheckBinds(len(args)); err != nil {
		return "", nil, err
	}
	return sglSplice(searchUnclaimedSQL, sglWhereOpen(filter, 70, true), sglWhereClose(filter, 85, true), sglOrder(builder.orders, 85, 85, false), sglAt(pagination, 85)), args, nil
}

func (builder *SearchUnclaimedBuilder) All(ctx context.Context) ([]SearchUnclaimedRow, error) {
	query, args, err := builder.buildQuery()
	if err != nil {
		return nil, err
	}
	rows, err := builder.queries.reader.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []SearchUnclaimedRow
	if rows.Next() {
		var row SearchUnclaimedRow
		scanArgs := []any{&row.ID, &row.Item, &row.Colour, &row.FoundAt, &row.DaysHeld}
		for more := true; more; more = rows.Next() {
			row = SearchUnclaimedRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			items = append(items, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

func (builder *SearchUnclaimedBuilder) One(ctx context.Context) (SearchUnclaimedRow, error) {
	query, args, err := builder.buildQuery()
	if err != nil {
		return SearchUnclaimedRow{}, err
	}
	var row SearchUnclaimedRow
	err = builder.queries.reader.QueryRowContext(ctx, query, args...).Scan(&row.ID, &row.Item, &row.Colour, &row.FoundAt, &row.DaysHeld)
	if err != nil {
		return SearchUnclaimedRow{}, err
	}
	return row, nil
}

func (builder *SearchUnclaimedBuilder) buildCountQuery() (string, []any, error) {
	if builder.pendingError != nil {
		return "", nil, builder.pendingError
	}
	args := make([]any, 0, sglFilterCount(builder.predicates))
	filter, args := sglFilterInto(builder.predicates, 1, args)
	if err := sglCheckBinds(len(args)); err != nil {
		return "", nil, err
	}
	return sglSplice(searchUnclaimedCountSQL, sglWhereOpen(filter, 41, true), sglWhereClose(filter, 56, true)), args, nil
}

func (builder *SearchUnclaimedBuilder) Count(ctx context.Context) (int64, error) {
	query, args, err := builder.buildCountQuery()
	if err != nil {
		return 0, err
	}
	var count int64
	err = builder.queries.reader.QueryRowContext(ctx, query, args...).Scan(&count)
	if err != nil {
		return 0, err
	}
	return count, nil
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type LostProperty struct {
	ID         uint64  `json:"id"`
	Item       string  `json:"item"`
	Colour     string  `json:"colour"`
	FoundAt    string  `json:"found_at"`
	DaysHeld   uint32  `json:"days_held"`
	Claimed    bool    `json:"claimed"`
	OwnerPhone *string `json:"owner_phone"`
}

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()
}

internal/store/runtime_helpers.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"errors"
	"fmt"
	"reflect"
	"regexp"
	"slices"
	"strconv"
	"strings"
	"unicode"
)

const sglMaxColumnExpressionLength = 256

const sglEmptyMembership = "IN (SELECT NULL WHERE 0)"

const sglEmptyExclusion = "NOT IN (SELECT NULL WHERE 0)"

const sglLimitBefore = "LIMIT "

const sglLimitAfter = ""

const sglOffsetBefore = "OFFSET "

const sglOffsetAfter = ""

const sglUnboundedLimit = ""

var ErrSglUnknownColumn = errors.New("sgl: unknown column in runtime query filter")

var ErrSglUnknownOperator = errors.New("sgl: unknown operator in runtime query filter")

var ErrSglUnknownDirection = errors.New("sgl: unknown sort direction in runtime query order")

var sglAllowedOperators = map[string]bool{
	"=":           true,
	"!=":          true,
	"<>":          true,
	"<":           true,
	">":           true,
	"<=":          true,
	">=":          true,
	"LIKE":        true,
	"IS NULL":     true,
	"IS NOT NULL": true,
	"IN":          true,
	"NOT IN":      true,
	"ILIKE":       true,
}

var sglAllowedDirections = map[string]bool{
	"ASC":              true,
	"DESC":             true,
	"ASC NULLS FIRST":  true,
	"ASC NULLS LAST":   true,
	"DESC NULLS FIRST": true,
	"DESC NULLS LAST":  true,
}

var sglColumnExpressionRegex = regexp.MustCompile(`^(?i:(?:[\p{L}_][\p{L}\p{Nd}_]*)|\s*CAST\(\s*(?:[\p{L}_][\p{L}\p{Nd}_]*)\s+AS\s+(?:SMALLINT|BOOLEAN|INTEGER|BIGINT|DOUBLE|REAL|TEXT|INT)\s*\)\s*|\s*\(\s*(?:[\p{L}_][\p{L}\p{Nd}_]*)\s*\)::\s*(?:SMALLINT|BOOLEAN|INTEGER|BIGINT|DOUBLE|REAL|TEXT|INT))$`)

var sglCastPatterns = []*regexp.Regexp{regexp.MustCompile(`^(?i:\s*CAST\(\s*((?:[\p{L}_][\p{L}\p{Nd}_]*))\s+AS\s+((?:SMALLINT|BOOLEAN|INTEGER|BIGINT|DOUBLE|REAL|TEXT|INT))\s*\)\s*)$`), regexp.MustCompile(`^(?i:\s*\(\s*((?:[\p{L}_][\p{L}\p{Nd}_]*))\s*\)::\s*((?:SMALLINT|BOOLEAN|INTEGER|BIGINT|DOUBLE|REAL|TEXT|INT)))$`)}

var sglCastBefore = []string{"CAST(", "("}

var sglCastMiddle = []string{" AS ", ")::"}

var sglCastAfter = []string{")", ""}

var sglCastTypes = []string{"BOOLEAN", "SMALLINT", "INTEGER", "INT", "BIGINT", "REAL", "DOUBLE", "TEXT"}

type sglPredicate struct {
	text   string
	values []any
	list   bool
}

func sglNormaliseDirection(direction string) string {
	return strings.ToUpper(strings.TrimSpace(direction))
}

func sglResolveColumn(column string, allowed map[string]string) (string, bool) {
	const unsafeText = "\\\x00"
	trimmed := strings.TrimSpace(column)
	if len(trimmed) > sglMaxColumnExpressionLength || strings.ContainsAny(trimmed, unsafeText) {
		return "", false
	}
	if sglSimpleColumn(trimmed) {
		resolved, ok := allowed[trimmed]
		return resolved, ok
	}
	if !sglColumnExpressionRegex.MatchString(trimmed) {
		return "", false
	}
	inner, castType, form := sglSplitCast(trimmed)
	root, start := sglExtractColumnRoot(inner)
	resolved, ok := allowed[root]
	if !ok {
		return "", false
	}
	expression := inner[:start] + resolved + inner[start+len(root):]
	if form < 0 {
		return expression, true
	}
	return sglCastBefore[form] + expression + sglCastMiddle[form] + castType + sglCastAfter[form], true
}

func sglSimpleColumn(name string) bool {
	if name == "" {
		return false
	}
	for index, character := range name {
		if character != '_' && !unicode.IsLetter(character) && (index <= 0 || !unicode.IsDigit(character)) {
			return false
		}
	}
	return true
}

func sglSplitCast(expression string) (column, castType string, form int) {
	for index, pattern := range sglCastPatterns {
		match := pattern.FindStringSubmatch(expression)
		if match == nil {
			continue
		}
		written := strings.Fields(match[2])
		for _, spelled := range sglCastTypes {
			if slices.EqualFunc(strings.Fields(spelled), written, strings.EqualFold) {
				return strings.TrimSpace(match[1]), spelled, index
			}
		}
	}
	return expression, "", -1
}

func sglExtractColumnRoot(column string) (root string, start int) {
	start = len(column) - len(strings.TrimLeftFunc(column, unicode.IsSpace))
	trimmed := strings.TrimSpace(column)
	return trimmed, start
}

func sglIsUnaryOperator(op string) bool {
	switch strings.ToUpper(strings.TrimSpace(op)) {
	case "IS NULL", "IS NOT NULL":
		return true
	}
	return false
}

func sglIsMultiOperator(op string) bool {
	switch strings.ToUpper(strings.TrimSpace(op)) {
	case "IN", "NOT IN":
		return true
	}
	return false
}

func sglReflectSlice(value any) []any {
	switch values := value.(type) {
	case []string:
		return sglBoxSlice(values)
	case []int:
		return sglBoxSlice(values)
	case []int8:
		return sglBoxSlice(values)
	case []int16:
		return sglBoxSlice(values)
	case []int32:
		return sglBoxSlice(values)
	case []int64:
		return sglBoxSlice(values)
	case []uint:
		return sglBoxSlice(values)
	case []uint16:
		return sglBoxSlice(values)
	case []uint32:
		return sglBoxSlice(values)
	case []uint64:
		return sglBoxSlice(values)
	case []float32:
		return sglBoxSlice(values)
	case []float64:
		return sglBoxSlice(values)
	case []bool:
		return sglBoxSlice(values)
	}
	if value == nil {
		return nil
	}
	if boxed, ok := value.([]any); ok {
		return boxed
	}
	if blob, ok := value.([]byte); ok {
		return []any{blob}
	}
	reflected := reflect.ValueOf(value)
	if reflected.Kind() != reflect.Slice && reflected.Kind() != reflect.Array {
		return []any{value}
	}
	result := make([]any, reflected.Len())
	for index := range reflected.Len() {
		result[index] = reflected.Index(index).Interface()
	}
	return result
}

func sglBoxSlice[T any](values []T) []any {
	result := make([]any, len(values))
	for index, value := range values {
		result[index] = value
	}
	return result
}

func sglMultiValueLen(value any) int {
	if value == nil {
		return 0
	}
	if boxed, ok := value.([]any); ok {
		return len(boxed)
	}
	if _, ok := value.([]byte); ok {
		return 1
	}
	reflected := reflect.ValueOf(value)
	if reflected.Kind() != reflect.Slice && reflected.Kind() != reflect.Array {
		return 1
	}
	return reflected.Len()
}

func sglNewPredicate(column, operator string, value any, allowed map[string]string) (sglPredicate, error) {
	resolved, ok := sglResolveColumn(column, allowed)
	if !ok {
		return sglPredicate{}, ErrSglUnknownColumn
	}
	if !sglAllowedOperators[operator] {
		return sglPredicate{}, ErrSglUnknownOperator
	}
	const space = " "
	switch {
	case sglIsUnaryOperator(operator):
		return sglPredicate{text: resolved + space + operator, values: nil, list: false}, nil
	case sglIsMultiOperator(operator):
		count := sglMultiValueLen(value)
		if count > sglMaxBindVariables {
			return sglPredicate{}, fmt.Errorf("sgl: column %q IN/NOT IN list of %d exceeds the limit of %d: %w", column, count, sglMaxBindVariables, ErrSglTooManyBindVariables)
		}
		if count == 0 {
			empty := sglEmptyMembership
			if strings.EqualFold(strings.TrimSpace(operator), "NOT IN") {
				empty = sglEmptyExclusion
			}
			return sglPredicate{text: resolved + space + empty, values: nil, list: false}, nil
		}
		return sglPredicate{text: resolved + space + operator + space, values: sglReflectSlice(value), list: true}, nil
	default:
		return sglPredicate{text: resolved + space + operator + space, values: []any{value}, list: false}, nil
	}
}

func sglNewOrder(column, direction string, allowed map[string]string) (string, error) {
	resolved, ok := sglResolveColumn(column, allowed)
	if !ok {
		return "", ErrSglUnknownColumn
	}
	normalised := sglNormaliseDirection(direction)
	if !sglAllowedDirections[normalised] {
		return "", ErrSglUnknownDirection
	}
	return resolved + " " + normalised, nil
}

func sglWriteBindPlaceholder(builder *strings.Builder, number int) {
	const decimalBase = 10
	builder.WriteString("$")
	var digits [20]byte
	builder.Write(strconv.AppendInt(digits[:0], int64(number), decimalBase))
	builder.WriteString("")
}

func sglFilterCount(predicates []sglPredicate) int {
	count := 0
	for _, predicate := range predicates {
		count += len(predicate.values)
	}
	return count
}

func sglFilter(predicates []sglPredicate, next int) (string, []any) {
	var values []any
	if count := sglFilterCount(predicates); count > 0 {
		values = make([]any, 0, count)
	}
	return sglFilterInto(predicates, next, values)
}

func sglFilterInto(predicates []sglPredicate, next int, values []any) (string, []any) {
	const decimalBase = 10
	var builder strings.Builder
	start := len(values)
	size := 0
	width := len("$") + len("") + 2
	var digits [20]byte
	width += len(strconv.AppendInt(digits[:0], int64(next+sglFilterCount(predicates)-1), decimalBase))
	for _, predicate := range predicates {
		size += len(predicate.text) + len(" AND ()") + len(predicate.values)*width
	}
	builder.Grow(size)
	for index, predicate := range predicates {
		if index > 0 {
			builder.WriteString(" AND ")
		}
		builder.WriteString(predicate.text)
		if predicate.list {
			builder.WriteString("(")
		}
		for position, value := range predicate.values {
			if position > 0 {
				builder.WriteString(", ")
			}
			sglWriteBindPlaceholder(&builder, next+len(values)-start)
			values = append(values, value)
		}
		if predicate.list {
			builder.WriteString(")")
		}
	}
	return builder.String(), values
}

func sglWhereOpen(filter string, start int, present bool) []sglEdit {
	if filter == "" || !present {
		return nil
	}
	return []sglEdit{{Text: "(", Start: start, End: start}}
}

func sglWhereClose(filter string, end int, present bool) []sglEdit {
	if filter == "" {
		return nil
	}
	prefix := " WHERE "
	if present {
		prefix = ") AND "
	}
	return []sglEdit{{Text: prefix, Start: end, End: end}, {Text: filter, Start: end, End: end}}
}

func sglOrder(orders []string, start, end int, present bool) []sglEdit {
	if len(orders) == 0 {
		return nil
	}
	prefix, at := " ORDER BY ", start
	if present {
		prefix, at = ", ", end
	}
	return []sglEdit{{Text: prefix, Start: at, End: at}, {Text: strings.Join(orders, ", "), Start: at, End: at}}
}

func sglPagination(limit, offset, next int) (string, []any) {
	if limit <= 0 && offset <= 0 {
		return "", nil
	}
	return sglPaginationInto(limit, offset, next, make([]any, 0, sglPaginationCount(limit, offset)))
}

func sglPaginationCount(limit, offset int) int {
	count := 0
	if limit > 0 {
		count++
	}
	if offset > 0 {
		count++
	}
	return count
}

func sglPaginationInto(limit, offset, next int, values []any) (string, []any) {
	if limit <= 0 && offset <= 0 {
		return "", values
	}
	var builder strings.Builder
	builder.Grow(len(sglLimitBefore) + len(sglLimitAfter) + len(sglOffsetBefore) + len(sglOffsetAfter) + len(sglUnboundedLimit) + 2*(len("$")+len("")+21))
	start := len(values)
	write := func(before, after string, value int) {
		builder.WriteByte(' ')
		builder.WriteString(before)
		sglWriteBindPlaceholder(&builder, next+len(values)-start)
		builder.WriteString(after)
		values = append(values, value)
	}
	writeLimit := func() {
		switch {
		case limit > 0:
			write(sglLimitBefore, sglLimitAfter, limit)
		case offset > 0 && sglUnboundedLimit != "":
			builder.WriteByte(' ')
			builder.WriteString(sglLimitBefore)
			builder.WriteString(sglUnboundedLimit)
			builder.WriteString(sglLimitAfter)
		}
	}
	writeOffset := func() {
		if offset > 0 {
			write(sglOffsetBefore, sglOffsetAfter, offset)
		}
	}
	writeLimit()
	writeOffset()
	return builder.String(), values
}

func sglAt(text string, at int) []sglEdit {
	if text == "" {
		return nil
	}
	return []sglEdit{{Text: text, Start: at, End: at}}
}

output.txt

red:
  #1 red umbrella, found at the pier, held 40 days
  #3 red bucket and spade, found at the beach, held 3 days
held 30 days or more, longest first:
  #1 red umbrella, found at the pier, held 40 days
  #4 blue umbrella, found at the cafe, held 33 days
unclaimed umbrellas: 2
items named ' OR '1'='1: 0, as the value is bound, not run
refused a column the query does not select: sgl: unknown column in runtime query filter
refused SQL in a column name: sgl: unknown column in runtime query filter
refused an operator outside the allow-list: sgl: unknown operator in runtime query filter
refused a direction outside the allow-list: sgl: unknown sort direction in runtime query order