Validated ORDER BY

On this page

On ClickHouse:

  • The SQL carries no ORDER BY; the generated method appends one from the allow-list
  • Without ORDER BY, ClickHouse returns rows in no set order, as parts are read in parallel, so the raw-text listing prints only a count
  • crew is UInt8 and finish_seconds UInt32, so the row fields are uint8 and uint32

A list page often lets the user choose the order: by name, by price, newest first. The column name cannot be a bound parameter, because placeholders carry values, not identifiers, so the usual fix is to paste the column into the SQL text, which is exactly where injection comes from. sgl.sortable gives you a sort the caller can choose without that risk. You list the columns that may be sorted on, and the generated method adds an ORDER BY only for a column on that list.

This example lists a regatta's entries in whatever order the caller asks for.

The query

db/queries/entries.sql

-- sgl.query(ListEntries, many)
-- sgl.sortable(order_by, [boat, crew, finish_seconds])
SELECT id, boat, crew, finish_seconds FROM entries

sgl.sortable(order_by, [boat, crew, finish_seconds]) names the sort and lists the columns it allows. It is a header of its own and binds no placeholder, and the query carries no ORDER BY: the generated method adds one.

What sgl generates

internal/store

type ListEntriesParams struct {
	OrderBy          ListEntriesOrderBy
	OrderByDirection OrderDirection
}

type ListEntriesRow struct {
	ID            uint64 `json:"id"`
	Boat          string `json:"boat"`
	Crew          uint8  `json:"crew"`
	FinishSeconds uint32 `json:"finish_seconds"`
}

type ListEntriesOrderBy string

type OrderDirection string

func (queries *Queries) ListEntries(ctx context.Context, params ListEntriesParams) ([]ListEntriesRow, error) {
	query := listEntriesSQL
	switch string(params.OrderBy) {
	case "boat", "crew", "finish_seconds":
		query = query + (" ORDER BY " + string(params.OrderBy))
		switch params.OrderByDirection {
		case OrderAsc, OrderDesc:
			query = query + (" " + string(params.OrderByDirection))
		}
	}
	rows, err := queries.reader.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListEntriesRow
	if rows.Next() {
		var row ListEntriesRow
		scanArgs := []any{&row.ID, &row.Boat, &row.Crew, &row.FinishSeconds}
		for more := true; more; more = rows.Next() {
			row = ListEntriesRow{}
			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 Params struct gains an OrderBy field, named after the directive, and an OrderByDirection. OrderBy has its own type, ListEntriesOrderBy, with one constant per allowed column, and the direction is an OrderDirection, either OrderAsc or OrderDesc. The constants are what callers are meant to use, and a misspelt constant does not compile.

The method checks the field against the allowed columns with a switch, and only a listed column is ever added to the SQL. The direction is checked the same way.

A value that is not on the list

ListEntriesOrderBy is a string type, so any string converts to it, including text taken straight from a request. Such a value is not an error. It is ignored: the method adds no ORDER BY at all, and the query runs unsorted. A direction other than OrderAsc or OrderDesc is dropped in the same way, and the column is kept. Nothing from outside the list can reach the SQL.

If an unknown value should fail instead, check it yourself before calling the method.

Using it

The program enters four boats and lists them four ways: by finishing time, by name, by crew size largest first, and with a piece of raw text as the column, which is ignored.

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/012_sortable/internal/store"
)

// run lists a regatta's entries in four orders. sgl.sortable(order_by, [boat, crew,
// finish_seconds]) has no placeholder: it gives ListEntriesParams an OrderBy field, typed
// with one constant per allowed column, and an OrderByDirection. The method appends ORDER
// BY only when OrderBy is one of the allowed columns, so a value that is not, such as raw
// text from a request, adds nothing to the SQL. A sortable column the query does not
// select makes sgl vet report Q011.
//
// Without ORDER BY, ClickHouse returns rows in whatever order its parallel reads finish,
// so the last listing prints only how many entries came back.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each listing.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	entries := []store.EnterBoatParams{
		{ID: 1, Boat: "Kittiwake", Crew: 4, FinishSeconds: 3725},
		{ID: 2, Boat: "Salty Chip", Crew: 3, FinishSeconds: 3410},
		{ID: 3, Boat: "Gull Wing", Crew: 5, FinishSeconds: 3990},
		{ID: 4, Boat: "Driftwood", Crew: 2, FinishSeconds: 3588},
	}
	for _, entry := range entries {
		if err := queries.EnterBoat(ctx, entry); err != nil {
			return fmt.Errorf("entering %s: %w", entry.Boat, err)
		}
	}

	orders := []struct {
		label      string
		parameters store.ListEntriesParams
	}{
		{label: "results", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByFinishSeconds, OrderByDirection: store.OrderAsc}},
		{label: "by boat", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByBoat, OrderByDirection: store.OrderAsc}},
		{label: "largest crew first", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByCrew, OrderByDirection: store.OrderDesc}},
	}
	for _, order := range orders {
		listed, err := queries.ListEntries(ctx, order.parameters)
		if err != nil {
			return fmt.Errorf("listing entries %s: %w", order.label, err)
		}
		fmt.Fprintf(output, "%s:\n", order.label)
		for _, entry := range listed {
			finish := time.Duration(entry.FinishSeconds) * time.Second
			fmt.Fprintf(output, "  %-10s crew %d  %s\n", entry.Boat, entry.Crew, finish)
		}
	}

	unsorted, err := queries.ListEntries(ctx, store.ListEntriesParams{OrderBy: "boat; DROP TABLE entries", OrderByDirection: store.OrderAsc})
	if err != nil {
		return fmt.Errorf("listing entries with raw text: %w", err)
	}
	fmt.Fprintf(output, "raw text, ignored: %d entries, in no set order\n", len(unsorted))
	return nil
}

What it prints

The last listing has no ORDER BY, and ClickHouse reads parts in parallel, so its rows come back in no set order. The program prints only how many came back.

results:
  Salty Chip crew 3  56m50s
  Driftwood  crew 2  59m48s
  Kittiwake  crew 4  1h2m5s
  Gull Wing  crew 5  1h6m30s
by boat:
  Driftwood  crew 2  59m48s
  Gull Wing  crew 5  1h6m30s
  Kittiwake  crew 4  1h2m5s
  Salty Chip crew 3  56m50s
largest crew first:
  Gull Wing  crew 5  1h6m30s
  Kittiwake  crew 4  1h2m5s
  Salty Chip crew 3  56m50s
  Driftwood  crew 2  59m48s
raw text, ignored: 4 entries, in no set order

Try it

Add a column the query does not select, such as entered_at, to the sortable list and run sgl vet. It reports Q011: the list should only name columns the result carries.

Across engines

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

ClickHouseSQLitePostgreSQLMySQLDuckDB
id columnuint64int64int64int64int64
crew columnuint8int64int64int32int32
finish_seconds 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 ./012_sortable

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 ./012_sortable

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

db/migrations/0001_entries.up.sql

CREATE TABLE entries (
    id             UInt64,
    boat           String,
    crew           UInt8,
    finish_seconds UInt32
) ENGINE = MergeTree() ORDER BY id;

db/migrations/0001_entries.down.sql

DROP TABLE entries;

db/queries/entries.sql

-- sgl.query(EnterBoat, exec)
INSERT INTO entries (id, boat, crew, finish_seconds)
VALUES ({id:UInt64}, {boat:String}, {crew:UInt8}, {finish_seconds:UInt32});

-- sgl.query(ListEntries, many)
-- sgl.sortable(order_by, [boat, crew, finish_seconds])
SELECT id, boat, crew, finish_seconds FROM entries

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/012_sortable/internal/store"
)

// run lists a regatta's entries in four orders. sgl.sortable(order_by, [boat, crew,
// finish_seconds]) has no placeholder: it gives ListEntriesParams an OrderBy field, typed
// with one constant per allowed column, and an OrderByDirection. The method appends ORDER
// BY only when OrderBy is one of the allowed columns, so a value that is not, such as raw
// text from a request, adds nothing to the SQL. A sortable column the query does not
// select makes sgl vet report Q011.
//
// Without ORDER BY, ClickHouse returns rows in whatever order its parallel reads finish,
// so the last listing prints only how many entries came back.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each listing.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	entries := []store.EnterBoatParams{
		{ID: 1, Boat: "Kittiwake", Crew: 4, FinishSeconds: 3725},
		{ID: 2, Boat: "Salty Chip", Crew: 3, FinishSeconds: 3410},
		{ID: 3, Boat: "Gull Wing", Crew: 5, FinishSeconds: 3990},
		{ID: 4, Boat: "Driftwood", Crew: 2, FinishSeconds: 3588},
	}
	for _, entry := range entries {
		if err := queries.EnterBoat(ctx, entry); err != nil {
			return fmt.Errorf("entering %s: %w", entry.Boat, err)
		}
	}

	orders := []struct {
		label      string
		parameters store.ListEntriesParams
	}{
		{label: "results", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByFinishSeconds, OrderByDirection: store.OrderAsc}},
		{label: "by boat", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByBoat, OrderByDirection: store.OrderAsc}},
		{label: "largest crew first", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByCrew, OrderByDirection: store.OrderDesc}},
	}
	for _, order := range orders {
		listed, err := queries.ListEntries(ctx, order.parameters)
		if err != nil {
			return fmt.Errorf("listing entries %s: %w", order.label, err)
		}
		fmt.Fprintf(output, "%s:\n", order.label)
		for _, entry := range listed {
			finish := time.Duration(entry.FinishSeconds) * time.Second
			fmt.Fprintf(output, "  %-10s crew %d  %s\n", entry.Boat, entry.Crew, finish)
		}
	}

	unsorted, err := queries.ListEntries(ctx, store.ListEntriesParams{OrderBy: "boat; DROP TABLE entries", OrderByDirection: store.OrderAsc})
	if err != nil {
		return fmt.Errorf("listing entries with raw text: %w", err)
	}
	fmt.Fprintf(output, "raw text, ignored: %d entries, in no set order\n", len(unsorted))
	return nil
}

main.go

// Command 012_sortable applies its migrations to a ClickHouse database, then runs the
// example in example.go: regatta entries are listed in the orders a caller picks from an
// allow-list.
//
// 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/entries.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"

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

const enterBoatSQL = `INSERT INTO entries (id, boat, crew, finish_seconds)
VALUES ({p_id:UInt64}, {p_boat:String}, {p_crew:UInt8}, {p_finish_seconds:UInt32});`

type EnterBoatParams struct {
	ID            uint64
	Boat          string
	Crew          uint8
	FinishSeconds uint32
}

func (queries *Queries) EnterBoat(ctx context.Context, params EnterBoatParams) error {
	_, err := queries.writer.ExecContext(ctx, enterBoatSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_boat", sglClickHouseShapedValue(params.Boat, sglClickHouseShapes[1])), clickhouse.Named("p_crew", sglClickHouseShapedValue(params.Crew, sglClickHouseShapes[0])), clickhouse.Named("p_finish_seconds", sglClickHouseShapedValue(params.FinishSeconds, sglClickHouseShapes[0])))
	return err
}

const listEntriesSQL = `SELECT id, boat, crew, finish_seconds FROM entries`

type ListEntriesParams struct {
	OrderBy          ListEntriesOrderBy
	OrderByDirection OrderDirection
}

type ListEntriesOrderBy string

const (
	ListEntriesOrderByBoat          ListEntriesOrderBy = "boat"
	ListEntriesOrderByCrew          ListEntriesOrderBy = "crew"
	ListEntriesOrderByFinishSeconds ListEntriesOrderBy = "finish_seconds"
)

type ListEntriesRow struct {
	ID            uint64 `json:"id"`
	Boat          string `json:"boat"`
	Crew          uint8  `json:"crew"`
	FinishSeconds uint32 `json:"finish_seconds"`
}

func (queries *Queries) ListEntries(ctx context.Context, params ListEntriesParams) ([]ListEntriesRow, error) {
	query := listEntriesSQL
	switch string(params.OrderBy) {
	case "boat", "crew", "finish_seconds":
		query = query + (" ORDER BY " + string(params.OrderBy))
		switch params.OrderByDirection {
		case OrderAsc, OrderDesc:
			query = query + (" " + string(params.OrderByDirection))
		}
	}
	rows, err := queries.reader.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListEntriesRow
	if rows.Next() {
		var row ListEntriesRow
		scanArgs := []any{&row.ID, &row.Boat, &row.Crew, &row.FinishSeconds}
		for more := true; more; more = rows.Next() {
			row = ListEntriesRow{}
			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 Entries struct {
	ID            uint64 `json:"id"`
	Boat          string `json:"boat"`
	Crew          uint8  `json:"crew"`
	FinishSeconds uint32 `json:"finish_seconds"`
}

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/sortable.go

// Code generated by sgl. DO NOT EDIT.

package store

type OrderDirection string

const (
	OrderAsc  OrderDirection = "ASC"
	OrderDesc OrderDirection = "DESC"
)

output.txt

results:
  Salty Chip crew 3  56m50s
  Driftwood  crew 2  59m48s
  Kittiwake  crew 4  1h2m5s
  Gull Wing  crew 5  1h6m30s
by boat:
  Driftwood  crew 2  59m48s
  Gull Wing  crew 5  1h6m30s
  Kittiwake  crew 4  1h2m5s
  Salty Chip crew 3  56m50s
largest crew first:
  Gull Wing  crew 5  1h6m30s
  Kittiwake  crew 4  1h2m5s
  Salty Chip crew 3  56m50s
  Driftwood  crew 2  59m48s
raw text, ignored: 4 entries, in no set order

Also on other engines

SQLiteRegatta entries listed in the order a caller picks from generated constants, with raw text adding no ORDER BY at all.PostgreSQLRegatta entries listed in the order a caller picks from generated constants, with raw text adding no ORDER BY at all.CockroachDBRegatta entries listed in the order a caller picks from generated constants, with raw text adding no ORDER BY at all.TimescaleDBRegatta entries listed in the order a caller picks from generated constants, with raw text adding no ORDER BY at all.MySQLRegatta entries listed in the order a caller picks from generated constants, with raw text adding no ORDER BY at all.MariaDBRegatta entries listed in the order a caller picks from generated constants, with raw text adding no ORDER BY at all.DuckDBRegatta entries listed in the order a caller picks from generated constants, with raw text adding no ORDER BY at all.