011 Optional filters on ClickHouse

On ClickHouse:
Each optional placeholder is typed Nullable(T), as in {destination:Nullable(String)}; a non-Nullable placeholder reads the NULL marker as text and the filter silently matches nothing
A {name:Type} placeholder may appear twice in a filter and still binds once, so no numbered placeholders are needed
The binding line names the placeholder, as in -- {destination:Nullable(String)} as sgl.param(destination, optional: true)
Nullable(T) already makes the field a pointer; optional: true records that leaving the filter out is intended

A search form has fields the user may leave blank: a destination, a top price, whether dogs are welcome. You want one query that applies the filters it is given and ignores the rest, not a query for every combination, and not SQL assembled from strings at run time.

The SQL does the switching. Write each filter so that a NULL turns it off, (param IS NULL OR column = param), and mark the parameter optional: true. Seagull makes its field a pointer, and a nil pointer is sent as NULL. The SQL never changes, only the values bound to it.

This example searches a harbour's boat trips four ways with one query.

The query

db/queries/trips.sql

-- sgl.query(SearchTrips, many)
-- {destination:Nullable(String)} as sgl.param(destination, optional: true)
-- {max_price:Nullable(UInt32)} as sgl.param(max_price, optional: true)
-- {dogs_welcome:Nullable(Bool)} as sgl.param(dogs_welcome, optional: true)
SELECT id, name, destination, price_pence, dogs_welcome
FROM trips
WHERE ({destination:Nullable(String)} IS NULL OR destination = {destination:Nullable(String)})
  AND ({max_price:Nullable(UInt32)} IS NULL OR price_pence <= {max_price:Nullable(UInt32)})
  AND ({dogs_welcome:Nullable(Bool)} IS NULL OR dogs_welcome = {dogs_welcome:Nullable(Bool)})
ORDER BY price_pence, id;

Each filter uses its placeholder twice, once in the IS NULL test and once in the comparison, and a {name:Type} placeholder used twice still binds once. The binding lines refer to a placeholder by the placeholder itself, {destination:Nullable(String)} as sgl.param(...), and mark it optional.

The placeholder's type has to be Nullable(T). With {destination:String}, the server reads the NULL marker as text, the IS NULL test is false, and a search left open quietly matches nothing. Nullable(T) on its own already makes the field a pointer; optional: true records that leaving the filter out is intended.

What sgl generates

Every optional parameter is a pointer field, so the zero value of the struct, with every field nil, is a search with no filters at all:

internal/store

type SearchTripsParams struct {
	Destination *string
	MaxPrice    *uint32
	DogsWelcome *bool
}

type SearchTripsRow struct {
	ID          uint64 `json:"id"`
	Name        string `json:"name"`
	Destination string `json:"destination"`
	PricePence  uint32 `json:"price_pence"`
	DogsWelcome bool   `json:"dogs_welcome"`
}

func (queries *Queries) SearchTrips(ctx context.Context, params SearchTripsParams) ([]SearchTripsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, searchTripsSQL, clickhouse.Named("p_destination", sglClickHouseShapedValue(params.Destination, sglClickHouseShapes[1])), clickhouse.Named("p_max_price", sglClickHouseShapedValue(params.MaxPrice, sglClickHouseShapes[0])), clickhouse.Named("p_dogs_welcome", sglClickHouseShapedValue(params.DogsWelcome, sglClickHouseShapes[0])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SearchTripsRow
	if rows.Next() {
		var row SearchTripsRow
		scanArgs := []any{&row.ID, &row.Name, &row.Destination, &row.PricePence, &row.DogsWelcome}
		for more := true; more; more = rows.Next() {
			row = SearchTripsRow{}
			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
}

Using it

The program adds four trips, then runs the same search four times: with no filters, by destination, by price, and by destination and dogs together. new("Gull Rock") makes a pointer to a value in one step, and any field left out stays nil.

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/011_optional_filters/internal/store"
)

// run searches boat trips four ways with one query. Each filter in SearchTrips is written
// as ({destination:Nullable(String)} IS NULL OR destination =
// {destination:Nullable(String)}), and its binding line says optional: true, which makes
// the Params field a pointer. A nil pointer is sent as NULL, so the IS NULL test is true
// and that filter matches every row; a set pointer applies the filter. The SQL never
// changes, only the values bound to it.
//
// The placeholder's type must be Nullable(T). With {destination:String} the server reads
// the NULL marker as text, the IS NULL test is false, and a search left open quietly
// matches nothing. Nullable(T) alone already makes the field a pointer; optional: true
// says the filter may be left out on purpose.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each search's trips.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	trips := []store.AddTripParams{
		{ID: 1, Name: "Seal spotting", Destination: "Gull Rock", PricePence: 1500, DogsWelcome: true},
		{ID: 2, Name: "Puffin cruise", Destination: "Puffin Island", PricePence: 2500, DogsWelcome: false},
		{ID: 3, Name: "Mackerel fishing", Destination: "Gull Rock", PricePence: 2000, DogsWelcome: false},
		{ID: 4, Name: "Sunset sail", Destination: "the bay", PricePence: 1200, DogsWelcome: true},
	}
	for _, trip := range trips {
		if err := queries.AddTrip(ctx, trip); err != nil {
			return fmt.Errorf("adding %s: %w", trip.Name, err)
		}
	}

	searches := []struct {
		label      string
		parameters store.SearchTripsParams
	}{
		{label: "no filters", parameters: store.SearchTripsParams{}},
		{label: "to Gull Rock", parameters: store.SearchTripsParams{Destination: new("Gull Rock")}},
		{label: "£15 or less", parameters: store.SearchTripsParams{MaxPrice: new(uint32(1500))}},
		{label: "to Gull Rock with a dog", parameters: store.SearchTripsParams{Destination: new("Gull Rock"), DogsWelcome: new(true)}},
	}
	for _, search := range searches {
		found, err := queries.SearchTrips(ctx, search.parameters)
		if err != nil {
			return fmt.Errorf("searching %s: %w", search.label, err)
		}
		names := make([]string, 0, len(found))
		for _, trip := range found {
			names = append(names, trip.Name)
		}
		fmt.Fprintf(output, "%-24s %s\n", search.label+":", strings.Join(names, ", "))
	}
	return nil
}

What it prints

Each line is the same query with different fields set. Results come back cheapest first, as the query orders them.

no filters:              Sunset sail, Seal spotting, Mackerel fishing, Puffin cruise
to Gull Rock:            Seal spotting, Mackerel fishing
£15 or less:             Sunset sail, Seal spotting
to Gull Rock with a dog: Seal spotting

Try it

Change the destination placeholders to {destination:String} and run the program: the searches without a destination stop matching anything, with no error to say why. That is why the type has to be Nullable(String).

Across engines

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

	ClickHouse	SQLite	PostgreSQL	MySQL	DuckDB
`max_price` parameter	`*uint32`	`*int64`	`*int64`	`*int32`	`*int32`
`id` column	`uint64`	`int64`	`int64`	`int64`	`int64`
`price_pence` column	`uint32`	`int64`	`int64`	`int32`	`int32`

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 ./011_optional_filters

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 ./011_optional_filters

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

db/migrations/0001_trips.up.sql

CREATE TABLE trips (
    id           UInt64,
    name         String,
    destination  String,
    price_pence  UInt32,
    dogs_welcome Bool
) ENGINE = MergeTree() ORDER BY id;

db/migrations/0001_trips.down.sql

DROP TABLE trips;

db/queries/trips.sql

-- sgl.query(AddTrip, exec)
INSERT INTO trips (id, name, destination, price_pence, dogs_welcome)
VALUES ({id:UInt64}, {name:String}, {destination:String}, {price_pence:UInt32}, {dogs_welcome:Bool});

-- sgl.query(SearchTrips, many)
-- {destination:Nullable(String)} as sgl.param(destination, optional: true)
-- {max_price:Nullable(UInt32)} as sgl.param(max_price, optional: true)
-- {dogs_welcome:Nullable(Bool)} as sgl.param(dogs_welcome, optional: true)
SELECT id, name, destination, price_pence, dogs_welcome
FROM trips
WHERE ({destination:Nullable(String)} IS NULL OR destination = {destination:Nullable(String)})
  AND ({max_price:Nullable(UInt32)} IS NULL OR price_pence <= {max_price:Nullable(UInt32)})
  AND ({dogs_welcome:Nullable(Bool)} IS NULL OR dogs_welcome = {dogs_welcome:Nullable(Bool)})
ORDER BY price_pence, id;

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/011_optional_filters/internal/store"
)

// run searches boat trips four ways with one query. Each filter in SearchTrips is written
// as ({destination:Nullable(String)} IS NULL OR destination =
// {destination:Nullable(String)}), and its binding line says optional: true, which makes
// the Params field a pointer. A nil pointer is sent as NULL, so the IS NULL test is true
// and that filter matches every row; a set pointer applies the filter. The SQL never
// changes, only the values bound to it.
//
// The placeholder's type must be Nullable(T). With {destination:String} the server reads
// the NULL marker as text, the IS NULL test is false, and a search left open quietly
// matches nothing. Nullable(T) alone already makes the field a pointer; optional: true
// says the filter may be left out on purpose.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each search's trips.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	trips := []store.AddTripParams{
		{ID: 1, Name: "Seal spotting", Destination: "Gull Rock", PricePence: 1500, DogsWelcome: true},
		{ID: 2, Name: "Puffin cruise", Destination: "Puffin Island", PricePence: 2500, DogsWelcome: false},
		{ID: 3, Name: "Mackerel fishing", Destination: "Gull Rock", PricePence: 2000, DogsWelcome: false},
		{ID: 4, Name: "Sunset sail", Destination: "the bay", PricePence: 1200, DogsWelcome: true},
	}
	for _, trip := range trips {
		if err := queries.AddTrip(ctx, trip); err != nil {
			return fmt.Errorf("adding %s: %w", trip.Name, err)
		}
	}

	searches := []struct {
		label      string
		parameters store.SearchTripsParams
	}{
		{label: "no filters", parameters: store.SearchTripsParams{}},
		{label: "to Gull Rock", parameters: store.SearchTripsParams{Destination: new("Gull Rock")}},
		{label: "£15 or less", parameters: store.SearchTripsParams{MaxPrice: new(uint32(1500))}},
		{label: "to Gull Rock with a dog", parameters: store.SearchTripsParams{Destination: new("Gull Rock"), DogsWelcome: new(true)}},
	}
	for _, search := range searches {
		found, err := queries.SearchTrips(ctx, search.parameters)
		if err != nil {
			return fmt.Errorf("searching %s: %w", search.label, err)
		}
		names := make([]string, 0, len(found))
		for _, trip := range found {
			names = append(names, trip.Name)
		}
		fmt.Fprintf(output, "%-24s %s\n", search.label+":", strings.Join(names, ", "))
	}
	return nil
}

main.go

// Command 011_optional_filters applies its migrations to a ClickHouse database, then runs
// the example in example.go: boat trips are searched with any mix of three optional
// filters.
//
// 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/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Trips struct {
	ID          uint64 `json:"id"`
	Name        string `json:"name"`
	Destination string `json:"destination"`
	PricePence  uint32 `json:"price_pence"`
	DogsWelcome bool   `json:"dogs_welcome"`
}

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/trips.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"

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

const addTripSQL = `INSERT INTO trips (id, name, destination, price_pence, dogs_welcome)
VALUES ({p_id:UInt64}, {p_name:String}, {p_destination:String}, {p_price_pence:UInt32}, {p_dogs_welcome:Bool});`

type AddTripParams struct {
	ID          uint64
	Name        string
	Destination string
	PricePence  uint32
	DogsWelcome bool
}

func (queries *Queries) AddTrip(ctx context.Context, params AddTripParams) error {
	_, err := queries.writer.ExecContext(ctx, addTripSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_name", sglClickHouseShapedValue(params.Name, sglClickHouseShapes[1])), clickhouse.Named("p_destination", sglClickHouseShapedValue(params.Destination, sglClickHouseShapes[1])), clickhouse.Named("p_price_pence", sglClickHouseShapedValue(params.PricePence, sglClickHouseShapes[0])), clickhouse.Named("p_dogs_welcome", sglClickHouseShapedValue(params.DogsWelcome, sglClickHouseShapes[0])))
	return err
}

const searchTripsSQL = `SELECT id, name, destination, price_pence, dogs_welcome
FROM trips
WHERE ({p_destination:Nullable(String)} IS NULL OR destination = {p_destination:Nullable(String)})
  AND ({p_max_price:Nullable(UInt32)} IS NULL OR price_pence <= {p_max_price:Nullable(UInt32)})
  AND ({p_dogs_welcome:Nullable(Bool)} IS NULL OR dogs_welcome = {p_dogs_welcome:Nullable(Bool)})
ORDER BY price_pence, id;`

type SearchTripsParams struct {
	Destination *string
	MaxPrice    *uint32
	DogsWelcome *bool
}

type SearchTripsRow struct {
	ID          uint64 `json:"id"`
	Name        string `json:"name"`
	Destination string `json:"destination"`
	PricePence  uint32 `json:"price_pence"`
	DogsWelcome bool   `json:"dogs_welcome"`
}

func (queries *Queries) SearchTrips(ctx context.Context, params SearchTripsParams) ([]SearchTripsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, searchTripsSQL, clickhouse.Named("p_destination", sglClickHouseShapedValue(params.Destination, sglClickHouseShapes[1])), clickhouse.Named("p_max_price", sglClickHouseShapedValue(params.MaxPrice, sglClickHouseShapes[0])), clickhouse.Named("p_dogs_welcome", sglClickHouseShapedValue(params.DogsWelcome, sglClickHouseShapes[0])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SearchTripsRow
	if rows.Next() {
		var row SearchTripsRow
		scanArgs := []any{&row.ID, &row.Name, &row.Destination, &row.PricePence, &row.DogsWelcome}
		for more := true; more; more = rows.Next() {
			row = SearchTripsRow{}
			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
}

output.txt

no filters:              Sunset sail, Seal spotting, Mackerel fishing, Puffin cruise
to Gull Rock:            Seal spotting, Mackerel fishing
£15 or less:             Sunset sail, Seal spotting
to Gull Rock with a dog: Seal spotting

Optional filters