The runtime query builder

On this page

On MariaDB:

  • The builder binds every value with a plain ? placeholder, the only kind MariaDB takes
  • MariaDB has no -> or ->> JSON operators, so the builder's column grammar leaves out the JSON paths MySQL's accepts
  • The base query's WHERE makes runtime filters join with AND (hint Q022)
  • migrate_backend_mysql.New is the migration backend, as MariaDB speaks MySQL's protocol

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):

ErrorReturned when
ErrSglUnknownColumnWhere or OrderBy names a column the query does not select, or a column expression of a shape it does not support
ErrSglUnknownOperatorWhere uses an operator outside the allow-list
ErrSglUnknownDirectionOrderBy 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/mariadb/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.
//
// 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{
		{Item: "umbrella", Colour: "red", FoundAt: "the pier", DaysHeld: 40},
		{Item: "sunglasses", Colour: "black", FoundAt: "the beach", DaysHeld: 12},
		{Item: "bucket and spade", Colour: "red", FoundAt: "the beach", DaysHeld: 3},
		{Item: "umbrella", Colour: "blue", FoundAt: "the cafe", DaysHeld: 33},
		{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").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.

MariaDBSQLitePostgreSQLMySQLDuckDBClickHouse
id columnint64int64int64int64int64uint64
days_held columnint32int64int64int32int32uint32

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

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

Run it locally

From the directory you cloned the Seagull repository into:

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

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

cd seagull/examples/mariadb && SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./014_runtime_builder

Needs a running MariaDB 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: pier
    dialect: mariadb
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_lost_property.up.sql

CREATE TABLE lost_property (
    id          BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    item        VARCHAR(50) NOT NULL,
    colour      VARCHAR(20) NOT NULL,
    found_at    VARCHAR(50) NOT NULL,
    days_held   INT         NOT NULL,
    claimed     BOOLEAN     NOT NULL DEFAULT FALSE,
    owner_phone VARCHAR(20)
);

db/migrations/0001_lost_property.down.sql

DROP TABLE lost_property;

db/queries/lost_property.sql

-- sgl.query(HandIn, exec)
INSERT INTO lost_property (item, colour, found_at, days_held) VALUES (?, ?, ?, ?);

-- sgl.query(Claim, execrows)
UPDATE lost_property SET claimed = TRUE, owner_phone = ? WHERE id = ?;

-- 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/mariadb/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.
//
// 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{
		{Item: "umbrella", Colour: "red", FoundAt: "the pier", DaysHeld: 40},
		{Item: "sunglasses", Colour: "black", FoundAt: "the beach", DaysHeld: 12},
		{Item: "bucket and spade", Colour: "red", FoundAt: "the beach", DaysHeld: 3},
		{Item: "umbrella", Colour: "blue", FoundAt: "the cafe", DaysHeld: 33},
		{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").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 MariaDB 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 . -dsn 'user:password@tcp(localhost:3306)/pier'`, 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/go-sql-driver/mysql"

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

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

// execute opens and migrates the database dsn names, and runs the example against it.
//
// Takes dsn (string) which is the database's data source name.
//
// 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 MariaDB data source name")
	}
	config, err := mysql.ParseDSN(dsn)
	if err != nil {
		return err
	}
	// Seagull maps DATETIME and TIMESTAMP columns to time.Time, which the driver returns
	// only when parseTime is set.
	config.ParseTime = true
	dsn = config.FormatDSN()

	database, err := sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_mysql.New(migrate.WithDriverMultiStatements(config.MultiStatements)), 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/lost_property.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const claimSQL = `UPDATE lost_property SET claimed = TRUE, owner_phone = ? WHERE id = ?;`

type ClaimParams struct {
	OwnerPhone *string
	ID         int64
}

func (queries *Queries) Claim(ctx context.Context, params ClaimParams) (int64, error) {
	results, err := queries.writer.ExecContext(ctx, claimSQL, params.OwnerPhone, params.ID)
	if err != nil {
		return 0, err
	}
	return results.RowsAffected()
}

const handInSQL = `INSERT INTO lost_property (item, colour, found_at, days_held) VALUES (?, ?, ?, ?);`

type HandInParams struct {
	Item     string
	Colour   string
	FoundAt  string
	DaysHeld int32
}

func (queries *Queries) HandIn(ctx context.Context, params HandInParams) error {
	_, err := queries.writer.ExecContext(ctx, handInSQL, params.Item, params.Colour, params.FoundAt, params.DaysHeld)
	return err
}

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

type SearchUnclaimedRow struct {
	ID       int64  `json:"id"`
	Item     string `json:"item"`
	Colour   string `json:"colour"`
	FoundAt  string `json:"found_at"`
	DaysHeld int32  `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         int64   `json:"id"`
	Item       string  `json:"item"`
	Colour     string  `json:"colour"`
	FoundAt    string  `json:"found_at"`
	DaysHeld   int32   `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 reads DATETIME, DATE and TIMESTAMP columns into time.Time, so the DSN it connects with must set parseTime=true.
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"
	"strings"
	"unicode"
)

const sglMaxColumnExpressionLength = 256

const sglEmptyMembership = "IN (SELECT NULL FROM DUAL WHERE FALSE)"

const sglEmptyExclusion = "NOT IN (SELECT NULL FROM DUAL WHERE FALSE)"

const sglLimitBefore = "LIMIT "

const sglLimitAfter = ""

const sglOffsetBefore = "OFFSET "

const sglOffsetAfter = ""

const sglUnboundedLimit = "18446744073709551615"

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,
}

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}_]*|json_extract\([\p{L}_][\p{L}\p{Nd}_]*,\s*'[^'\\]*'\))|\s*CAST\(\s*(?:[\p{L}_][\p{L}\p{Nd}_]*|json_extract\([\p{L}_][\p{L}\p{Nd}_]*,\s*'[^'\\]*'\))\s+AS\s+(?:DATETIME|UNSIGNED|DECIMAL|DOUBLE|SIGNED|CHAR|DATE)\s*\)\s*)$`)

var sglCastPatterns = []*regexp.Regexp{regexp.MustCompile(`^(?i:\s*CAST\(\s*((?:[\p{L}_][\p{L}\p{Nd}_]*|json_extract\([\p{L}_][\p{L}\p{Nd}_]*,\s*'[^'\\]*'\)))\s+AS\s+((?:DATETIME|UNSIGNED|DECIMAL|DOUBLE|SIGNED|CHAR|DATE))\s*\)\s*)$`)}

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

var sglCastMiddle = []string{" AS "}

var sglCastAfter = []string{")"}

var sglCastTypes = []string{"SIGNED", "UNSIGNED", "DECIMAL", "DOUBLE", "CHAR", "DATE", "DATETIME"}

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)
	if len(trimmed) > len("json_extract(") && strings.EqualFold(trimmed[:len("json_extract(")], "json_extract(") {
		if argument, _, found := strings.Cut(trimmed[len("json_extract("):], ","); found && argument != "" {
			leading := len(argument) - len(strings.TrimLeftFunc(argument, unicode.IsSpace))
			return strings.TrimSpace(argument), start + len("json_extract(") + leading
		}
	}
	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("?")
	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
	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

Also on other engines

SQLiteUnclaimed lost property searched with filters, an order, a limit and a count chosen at run time, with unsafe columns, operators and directions refused.PostgreSQLUnclaimed lost property searched with filters, an order, a limit and a count chosen at run time, with unsafe columns, operators and directions refused.CockroachDBUnclaimed lost property searched with filters, an order, a limit and a count chosen at run time, with unsafe columns, operators and directions refused.TimescaleDBUnclaimed lost property searched with filters, an order, a limit and a count chosen at run time, with unsafe columns, operators and directions refused.MySQLUnclaimed lost property searched with filters, an order, a limit and a count chosen at run time, with unsafe columns, operators and directions refused.DuckDBUnclaimed lost property searched with filters, an order, a limit and a count chosen at run time, with unsafe columns, operators and directions refused.ClickHouseUnclaimed lost property searched with filters, an order, a limit and a count chosen at run time, with unsafe columns, operators and directions refused.