Pagination with defaults and limits

On this page

On ClickHouse:

  • LIMIT and OFFSET take {name:Type} placeholders, as in LIMIT {page_size:UInt32}; a placeholder that is the whole count becomes a plain int, as on the other engines
  • The binding line names the placeholder, as in -- {page_size:UInt32} as sgl.param(page_size, default: 3, max: 5); page_offset needs no line, as the placeholder already names it
  • The optional creature filter is a Nullable(String) placeholder, which appears twice and still binds once
  • No auto-increment, so each find's id is a parameter

A list that can grow without bound should be read a page at a time. Put the page size and the starting offset in LIMIT and OFFSET as parameters, and Seagull types them from where they sit: they are counts of rows, not column values, so each becomes a plain int.

Two qualities on the binding line protect the query from its callers. default: fills in a page size when the caller leaves it at zero, and max: caps it, so nobody can ask for every row at once by passing a large number.

This example logs creatures found in rock pools and pages through them.

A page at a time

db/queries/finds.sql

-- sgl.query(ListFinds, many)
-- {page_size:UInt32} as sgl.param(page_size, default: 3, max: 5)
SELECT id, creature, pool, size_mm
FROM finds
ORDER BY id
LIMIT {page_size:UInt32} OFFSET {page_offset:UInt32};

page_size has a default of 3 and a maximum of 5. page_offset has neither, since starting from the first row is what zero already means.

On ClickHouse each placeholder names its own type, as in {page_size:UInt32}, and the binding line names the placeholder it applies to. A placeholder that is the whole LIMIT or OFFSET count is still a count of rows, so it becomes an int here too, and the method formats it as the UInt32 the server expects. page_offset needs no binding line, as its placeholder already gives it a name.

internal/store

type ListFindsParams struct {
	PageSize   int
	PageOffset int
}

type ListFindsRow struct {
	ID       uint64 `json:"id"`
	Creature string `json:"creature"`
	Pool     string `json:"pool"`
	SizeMm   uint32 `json:"size_mm"`
}

func (queries *Queries) ListFinds(ctx context.Context, params ListFindsParams) ([]ListFindsRow, error) {
	if params.PageSize == 0 {
		params.PageSize = 3
	}
	if params.PageSize < 0 {
		params.PageSize = 0
	}
	if params.PageSize > 5 {
		params.PageSize = 5
	}
	if params.PageOffset < 0 {
		params.PageOffset = 0
	}
	rows, err := queries.reader.QueryContext(ctx, listFindsSQL, clickhouse.Named("p_page_size", sglClickHouseShapedValue(params.PageSize, sglClickHouseShapes[0])), clickhouse.Named("p_page_offset", sglClickHouseShapedValue(params.PageOffset, sglClickHouseShapes[0])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListFindsRow
	if rows.Next() {
		var row ListFindsRow
		scanArgs := []any{&row.ID, &row.Creature, &row.Pool, &row.SizeMm}
		for more := true; more; more = rows.Next() {
			row = ListFindsRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

The method adjusts the values in Go before the query runs. A size of zero becomes 3, anything over 5 becomes 5, and a negative size or offset becomes 0, so the database never sees one. The SQL itself is unchanged, and the page size is still a bound value.

LIMIT without ORDER BY takes whichever rows the database reaches first, which need not be the same on every call, so each paged query here orders its rows. That way page two starts where page one stopped.

A search endpoint

SearchFinds is the shape of a typical search endpoint: an optional filter, a fixed order and a page, all in one static query.

db/queries/finds.sql

-- sgl.query(SearchFinds, many)
-- {creature:Nullable(String)} as sgl.param(creature, optional: true)
-- {page_size:UInt32} as sgl.param(page_size, default: 3, max: 5)
SELECT id, creature, pool, size_mm
FROM finds
WHERE ({creature:Nullable(String)} IS NULL OR creature = {creature:Nullable(String)})
ORDER BY size_mm DESC, id
LIMIT {page_size:UInt32} OFFSET {page_offset:UInt32};

The filter is the IS NULL idiom from Optional filters, with a Nullable(String) placeholder, so Creature is a *string and a nil one matches every find. The placeholder appears twice and still binds once. The page parameters work exactly as in ListFinds.

To let the caller choose the order as well, sgl.sortable combines with LIMIT and OFFSET: the generated method places its ORDER BY before the LIMIT. Validated ORDER BY shows the directive, and the runtime query builder goes further, choosing filters, order and limit as the program runs.

Using it

The program logs seven finds, then asks for three pages: one with a page size of zero, the page after it, and one asking for 50 rows. Last, it searches for crabs two at a time, biggest first.

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/013_pagination/internal/store"
)

// run logs seven rock pool finds and pages through them. A placeholder that is the whole
// LIMIT or OFFSET count becomes a plain int, whatever ClickHouse type it names, so
// {page_size:UInt32} gives an int field. On page_size, default: 3 replaces a zero value
// and max: 5 caps a larger one, before the query runs, so a caller cannot ask for every
// row at once; a negative size or offset becomes 0.
//
// ClickHouse has no auto-increment, so each find's id is a parameter.
//
// SearchFinds is the shape of a search endpoint: an optional filter, a fixed order and a
// page, all in one static query.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each page.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	finds := []store.LogFindParams{
		{ID: 1, Creature: "crab", Pool: "north", SizeMm: 45},
		{ID: 2, Creature: "starfish", Pool: "east", SizeMm: 80},
		{ID: 3, Creature: "crab", Pool: "east", SizeMm: 60},
		{ID: 4, Creature: "blenny", Pool: "north", SizeMm: 70},
		{ID: 5, Creature: "crab", Pool: "south", SizeMm: 30},
		{ID: 6, Creature: "prawn", Pool: "north", SizeMm: 25},
		{ID: 7, Creature: "crab", Pool: "north", SizeMm: 55},
	}
	for _, find := range finds {
		if err := queries.LogFind(ctx, find); err != nil {
			return fmt.Errorf("logging a %s: %w", find.Creature, err)
		}
	}

	pages := []struct {
		label      string
		parameters store.ListFindsParams
	}{
		{label: "size 0 uses the default of 3", parameters: store.ListFindsParams{PageSize: 0, PageOffset: 0}},
		{label: "the next page, offset 3", parameters: store.ListFindsParams{PageSize: 3, PageOffset: 3}},
		{label: "size 50 is capped at 5", parameters: store.ListFindsParams{PageSize: 50, PageOffset: 0}},
	}
	for _, page := range pages {
		listed, err := queries.ListFinds(ctx, page.parameters)
		if err != nil {
			return fmt.Errorf("listing finds, %s: %w", page.label, err)
		}
		fmt.Fprintf(output, "%s:\n", page.label)
		for _, find := range listed {
			fmt.Fprintf(output, "  #%d %s in the %s pool\n", find.ID, find.Creature, find.Pool)
		}
	}

	for _, offset := range []int{0, 2} {
		crabs, err := queries.SearchFinds(ctx, store.SearchFindsParams{
			Creature:   new("crab"),
			PageSize:   2,
			PageOffset: offset,
		})
		if err != nil {
			return fmt.Errorf("searching crabs from %d: %w", offset, err)
		}
		fmt.Fprintf(output, "biggest crabs, offset %d:\n", offset)
		for _, crab := range crabs {
			fmt.Fprintf(output, "  #%d %dmm\n", crab.ID, crab.SizeMm)
		}
	}
	return nil
}

What it prints

The page size of zero gets the default of three rows, and the request for 50 is cut to five.

size 0 uses the default of 3:
  #1 crab in the north pool
  #2 starfish in the east pool
  #3 crab in the east pool
the next page, offset 3:
  #4 blenny in the north pool
  #5 crab in the south pool
  #6 prawn in the north pool
size 50 is capped at 5:
  #1 crab in the north pool
  #2 starfish in the east pool
  #3 crab in the east pool
  #4 blenny in the north pool
  #5 crab in the south pool
biggest crabs, offset 0:
  #3 60mm
  #7 55mm
biggest crabs, offset 2:
  #1 45mm
  #5 30mm

Across engines

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

ClickHouseSQLitePostgreSQLMySQLDuckDB
id columnuint64int64int64int64int64
size_mm columnuint32int64int64int32int32

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

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

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/clickhouse && go run ./013_pagination

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 ./013_pagination

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

db/migrations/0001_finds.up.sql

CREATE TABLE finds (
    id       UInt64,
    creature String,
    pool     String,
    size_mm  UInt32
) ENGINE = MergeTree() ORDER BY id;

db/migrations/0001_finds.down.sql

DROP TABLE finds;

db/queries/finds.sql

-- sgl.query(LogFind, exec)
INSERT INTO finds (id, creature, pool, size_mm)
VALUES ({id:UInt64}, {creature:String}, {pool:String}, {size_mm:UInt32});

-- sgl.query(ListFinds, many)
-- {page_size:UInt32} as sgl.param(page_size, default: 3, max: 5)
SELECT id, creature, pool, size_mm
FROM finds
ORDER BY id
LIMIT {page_size:UInt32} OFFSET {page_offset:UInt32};

-- sgl.query(SearchFinds, many)
-- {creature:Nullable(String)} as sgl.param(creature, optional: true)
-- {page_size:UInt32} as sgl.param(page_size, default: 3, max: 5)
SELECT id, creature, pool, size_mm
FROM finds
WHERE ({creature:Nullable(String)} IS NULL OR creature = {creature:Nullable(String)})
ORDER BY size_mm DESC, id
LIMIT {page_size:UInt32} OFFSET {page_offset:UInt32};

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/013_pagination/internal/store"
)

// run logs seven rock pool finds and pages through them. A placeholder that is the whole
// LIMIT or OFFSET count becomes a plain int, whatever ClickHouse type it names, so
// {page_size:UInt32} gives an int field. On page_size, default: 3 replaces a zero value
// and max: 5 caps a larger one, before the query runs, so a caller cannot ask for every
// row at once; a negative size or offset becomes 0.
//
// ClickHouse has no auto-increment, so each find's id is a parameter.
//
// SearchFinds is the shape of a search endpoint: an optional filter, a fixed order and a
// page, all in one static query.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each page.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	finds := []store.LogFindParams{
		{ID: 1, Creature: "crab", Pool: "north", SizeMm: 45},
		{ID: 2, Creature: "starfish", Pool: "east", SizeMm: 80},
		{ID: 3, Creature: "crab", Pool: "east", SizeMm: 60},
		{ID: 4, Creature: "blenny", Pool: "north", SizeMm: 70},
		{ID: 5, Creature: "crab", Pool: "south", SizeMm: 30},
		{ID: 6, Creature: "prawn", Pool: "north", SizeMm: 25},
		{ID: 7, Creature: "crab", Pool: "north", SizeMm: 55},
	}
	for _, find := range finds {
		if err := queries.LogFind(ctx, find); err != nil {
			return fmt.Errorf("logging a %s: %w", find.Creature, err)
		}
	}

	pages := []struct {
		label      string
		parameters store.ListFindsParams
	}{
		{label: "size 0 uses the default of 3", parameters: store.ListFindsParams{PageSize: 0, PageOffset: 0}},
		{label: "the next page, offset 3", parameters: store.ListFindsParams{PageSize: 3, PageOffset: 3}},
		{label: "size 50 is capped at 5", parameters: store.ListFindsParams{PageSize: 50, PageOffset: 0}},
	}
	for _, page := range pages {
		listed, err := queries.ListFinds(ctx, page.parameters)
		if err != nil {
			return fmt.Errorf("listing finds, %s: %w", page.label, err)
		}
		fmt.Fprintf(output, "%s:\n", page.label)
		for _, find := range listed {
			fmt.Fprintf(output, "  #%d %s in the %s pool\n", find.ID, find.Creature, find.Pool)
		}
	}

	for _, offset := range []int{0, 2} {
		crabs, err := queries.SearchFinds(ctx, store.SearchFindsParams{
			Creature:   new("crab"),
			PageSize:   2,
			PageOffset: offset,
		})
		if err != nil {
			return fmt.Errorf("searching crabs from %d: %w", offset, err)
		}
		fmt.Fprintf(output, "biggest crabs, offset %d:\n", offset)
		for _, crab := range crabs {
			fmt.Fprintf(output, "  #%d %dmm\n", crab.ID, crab.SizeMm)
		}
	}
	return nil
}

main.go

// Command 013_pagination applies its migrations to a ClickHouse database, then runs the
// example in example.go: a log of rock pool finds is read a page at a time, with a
// default and a maximum page size, and searched with an optional filter.
//
// Run it with `go run .` against a local server, or `go run . -dsn <dsn>` to choose the
// database; give it an empty one.
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", "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/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: 110, digits: -1, prepared: true, checked: false, driverMap: false, names: []string{}, elements: []sglClickHouseShape{}}, {kind: 113, 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/finds.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"

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

const listFindsSQL = `SELECT id, creature, pool, size_mm
FROM finds
ORDER BY id
LIMIT {p_page_size:UInt32} OFFSET {p_page_offset:UInt32};`

type ListFindsParams struct {
	PageSize   int
	PageOffset int
}

type ListFindsRow struct {
	ID       uint64 `json:"id"`
	Creature string `json:"creature"`
	Pool     string `json:"pool"`
	SizeMm   uint32 `json:"size_mm"`
}

func (queries *Queries) ListFinds(ctx context.Context, params ListFindsParams) ([]ListFindsRow, error) {
	if params.PageSize == 0 {
		params.PageSize = 3
	}
	if params.PageSize < 0 {
		params.PageSize = 0
	}
	if params.PageSize > 5 {
		params.PageSize = 5
	}
	if params.PageOffset < 0 {
		params.PageOffset = 0
	}
	rows, err := queries.reader.QueryContext(ctx, listFindsSQL, clickhouse.Named("p_page_size", sglClickHouseShapedValue(params.PageSize, sglClickHouseShapes[0])), clickhouse.Named("p_page_offset", sglClickHouseShapedValue(params.PageOffset, sglClickHouseShapes[0])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListFindsRow
	if rows.Next() {
		var row ListFindsRow
		scanArgs := []any{&row.ID, &row.Creature, &row.Pool, &row.SizeMm}
		for more := true; more; more = rows.Next() {
			row = ListFindsRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const logFindSQL = `INSERT INTO finds (id, creature, pool, size_mm)
VALUES ({p_id:UInt64}, {p_creature:String}, {p_pool:String}, {p_size_mm:UInt32});`

type LogFindParams struct {
	ID       uint64
	Creature string
	Pool     string
	SizeMm   uint32
}

func (queries *Queries) LogFind(ctx context.Context, params LogFindParams) error {
	_, err := queries.writer.ExecContext(ctx, logFindSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_creature", sglClickHouseShapedValue(params.Creature, sglClickHouseShapes[1])), clickhouse.Named("p_pool", sglClickHouseShapedValue(params.Pool, sglClickHouseShapes[1])), clickhouse.Named("p_size_mm", sglClickHouseShapedValue(params.SizeMm, sglClickHouseShapes[0])))
	return err
}

const searchFindsSQL = `SELECT id, creature, pool, size_mm
FROM finds
WHERE ({p_creature:Nullable(String)} IS NULL OR creature = {p_creature:Nullable(String)})
ORDER BY size_mm DESC, id
LIMIT {p_page_size:UInt32} OFFSET {p_page_offset:UInt32};`

type SearchFindsParams struct {
	Creature   *string
	PageSize   int
	PageOffset int
}

type SearchFindsRow struct {
	ID       uint64 `json:"id"`
	Creature string `json:"creature"`
	Pool     string `json:"pool"`
	SizeMm   uint32 `json:"size_mm"`
}

func (queries *Queries) SearchFinds(ctx context.Context, params SearchFindsParams) ([]SearchFindsRow, error) {
	if params.PageSize == 0 {
		params.PageSize = 3
	}
	if params.PageSize < 0 {
		params.PageSize = 0
	}
	if params.PageSize > 5 {
		params.PageSize = 5
	}
	if params.PageOffset < 0 {
		params.PageOffset = 0
	}
	rows, err := queries.reader.QueryContext(ctx, searchFindsSQL, clickhouse.Named("p_creature", sglClickHouseShapedValue(params.Creature, sglClickHouseShapes[1])), clickhouse.Named("p_page_size", sglClickHouseShapedValue(params.PageSize, sglClickHouseShapes[0])), clickhouse.Named("p_page_offset", sglClickHouseShapedValue(params.PageOffset, sglClickHouseShapes[0])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SearchFindsRow
	if rows.Next() {
		var row SearchFindsRow
		scanArgs := []any{&row.ID, &row.Creature, &row.Pool, &row.SizeMm}
		for more := true; more; more = rows.Next() {
			row = SearchFindsRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Finds struct {
	ID       uint64 `json:"id"`
	Creature string `json:"creature"`
	Pool     string `json:"pool"`
	SizeMm   uint32 `json:"size_mm"`
}

internal/store/querier.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type dbtxWrapper interface {
	WrapDBTX(inner any) any
}

type Queries struct {
	reader  DBTX
	writer  DBTX
	wrapper dbtxWrapper
}

func New(db DBTX) *Queries {
	wrapper, _ := db.(dbtxWrapper)
	return &Queries{reader: db, writer: db, wrapper: wrapper}
}

func NewWithReplica(writer DBTX, reader DBTX) *Queries {
	wrapper, _ := writer.(dbtxWrapper)
	return &Queries{reader: reader, writer: writer, wrapper: wrapper}
}

func (queries *Queries) WithTx(transaction *sql.Tx) *Queries {
	if queries == nil {
		return &Queries{reader: transaction, writer: transaction, wrapper: nil}
	}
	db := queries.wrapDBTX(transaction)
	return &Queries{reader: db, writer: db, wrapper: queries.wrapper}
}

func (queries *Queries) wrapDBTX(db DBTX) DBTX {
	if queries.wrapper == nil {
		return db
	}
	wrapped, ok := queries.wrapper.WrapDBTX(db).(DBTX)
	if !ok {
		return db
	}
	return wrapped
}

func (queries *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error {
	transaction, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer transaction.Rollback()
	if err = fn(queries.WithTx(transaction)); err != nil {
		return err
	}
	return transaction.Commit()
}

output.txt

size 0 uses the default of 3:
  #1 crab in the north pool
  #2 starfish in the east pool
  #3 crab in the east pool
the next page, offset 3:
  #4 blenny in the north pool
  #5 crab in the south pool
  #6 prawn in the north pool
size 50 is capped at 5:
  #1 crab in the north pool
  #2 starfish in the east pool
  #3 crab in the east pool
  #4 blenny in the north pool
  #5 crab in the south pool
biggest crabs, offset 0:
  #3 60mm
  #7 55mm
biggest crabs, offset 2:
  #1 45mm
  #5 30mm

Also on other engines

SQLiteRock pool finds read a page at a time, with a default and a maximum page size, then searched with an optional filter and a page.PostgreSQLRock pool finds read a page at a time, with a default and a maximum page size, then searched with an optional filter and a page.CockroachDBRock pool finds read a page at a time, with a default and a maximum page size, then searched with an optional filter and a page.TimescaleDBRock pool finds read a page at a time, with a default and a maximum page size, then searched with an optional filter and a page.MySQLRock pool finds read a page at a time, with a default and a maximum page size, then searched with an optional filter and a page.MariaDBRock pool finds read a page at a time, with a default and a maximum page size, then searched with an optional filter and a page.DuckDBRock pool finds read a page at a time, with a default and a maximum page size, then searched with an optional filter and a page.