From SQL types to Go types

On this page

On ClickHouse:

  • Integer widths and signs are exact: UInt16, UInt32 and UInt64 map to uint16, uint32 and uint64, Int64 to int64
  • Float32 maps to float32, keeping its width like the integers, and Bool maps to bool
  • UUID maps to uuid.UUID (github.com/google/uuid) and Decimal(P, S) to decimal.Decimal (github.com/shopspring/decimal), the types clickhouse-go scans into
  • The example passes the UUID and decimal as String and converts them with toUUID and toDecimal32, so example.go needs only the standard library
  • DateTime('UTC') maps to time.Time; a time parameter is sent as Unix seconds, so its moment is kept whatever the server's zone, and the column's zone makes it read back in UTC
  • No BLOB: String and FixedString(N) hold any bytes and map to string
  • Nullable(String) maps to *string; every other column is NOT NULL

Seagull reads each column's declared type from your migrations and picks the Go type that holds it without loss. Integers keep the width and sign the engine stores, floats their precision, exact decimals become a decimal type rather than a float, dates and times become time.Time, and a column that can be NULL becomes a pointer. You never choose a Go type for a column by hand, and you never scan a NUMERIC into a float64 by accident.

This example records one landing of fish, with a column of each common kind, reads it back, and prints the Go type every column became.

The schema

db/migrations/0001_landings.up.sql

CREATE TABLE landings (
    id        UInt64,
    catch_id  UUID,
    species   String,
    boxes     UInt16,
    fish      UInt32,
    weight_g  Int64,
    length_cm Float32,
    price     Decimal(9, 2),
    on_ice    Bool,
    landed_at DateTime('UTC'),
    tag       FixedString(4),
    notes     Nullable(String)
) ENGINE = MergeTree() ORDER BY id;

What sgl generates

The row type for GetLanding is the mapping, field by field:

internal/store

type GetLandingRow struct {
	ID       uint64          `json:"id"`
	CatchID  uuid.UUID       `json:"catch_id"`
	Species  string          `json:"species"`
	Boxes    uint16          `json:"boxes"`
	Fish     uint32          `json:"fish"`
	WeightG  int64           `json:"weight_g"`
	LengthCm float32         `json:"length_cm"`
	Price    decimal.Decimal `json:"price"`
	OnIce    bool            `json:"on_ice"`
	LandedAt time.Time       `json:"landed_at"`
	Tag      string          `json:"tag"`
	Notes    *string         `json:"notes"`
}

func (queries *Queries) GetLanding(ctx context.Context, id uint64) (GetLandingRow, error) {
	var row GetLandingRow
	err := queries.reader.QueryRowContext(ctx, getLandingSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(id, sglClickHouseShapes[0]))).Scan(&row.ID, &row.CatchID, &row.Species, &row.Boxes, &row.Fish, &row.WeightG, &row.LengthCm, &row.Price, &row.OnIce, &row.LandedAt, &row.Tag, &row.Notes)
	if err != nil {
		return GetLandingRow{}, err
	}
	return row, nil
}
  • ClickHouse is exact about width and sign, and so is the mapping: UInt16, UInt32 and UInt64 give uint16, uint32 and uint64, and Int64 gives int64.
  • Float32 gives float32, keeping its width like the integers, and Bool gives bool.
  • UUID gives uuid.UUID from github.com/google/uuid, and Decimal(9, 2) gives decimal.Decimal from github.com/shopspring/decimal: the types clickhouse-go scans them into.
  • DateTime('UTC') gives time.Time.
  • There is no BLOB. String and FixedString(N) hold any bytes, and both give string.
  • Nullable(String) gives *string. Every other column is NOT NULL, so nothing else is a pointer.

Parameters follow the same rules, so RecordLanding takes the same Go types it hands back.

The exceptions here are deliberate. The insert passes the catch id and the price as String placeholders and converts them in SQL with toUUID and toDecimal32, so the program needs only the standard library to write them:

db/queries/landings.sql

-- sgl.query(RecordLanding, exec)
INSERT INTO landings (id, catch_id, species, boxes, fish, weight_g, length_cm, price, on_ice, landed_at, tag, notes)
VALUES (
    {id:UInt64},
    toUUID({catch_id:String}),
    {species:String},
    {boxes:UInt16},
    {fish:UInt32},
    {weight_g:Int64},
    {length_cm:Float32},
    toDecimal32({price:String}, 2),
    {on_ice:Bool},
    {landed_at:DateTime('UTC')},
    {tag:FixedString(4)},
    {notes:Nullable(String)}
);

Using it

The program records a landing and reads it back, then prints each column's name, the Go type of its field (with %T) and its value.

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/005_type_mapping/internal/store"
)

// run records a landing of fish and reads it back, printing the Go type sgl chose for
// each column. ClickHouse types are exact about width and sign, and Seagull follows them:
// UInt16, UInt32 and UInt64 give uint16, uint32 and uint64, Int64 gives int64, Float32
// gives float32, and Bool gives bool. DateTime('UTC') gives time.Time; the generated code
// sends a time as Unix seconds, so the server keeps its moment whatever the zone, and the
// column's time zone makes it read back in UTC. FixedString(4) and String both give
// string, which in ClickHouse holds any bytes, and Nullable(String) gives *string.
//
// UUID gives uuid.UUID from github.com/google/uuid and Decimal(9, 2) gives
// decimal.Decimal from github.com/shopspring/decimal, the types clickhouse-go scans them
// into. A {price:Decimal(9, 2)} placeholder would make the parameter a decimal.Decimal
// too; this example passes the price and catch id as text and converts them in SQL with
// toDecimal32 and toUUID, so it needs nothing beyond the standard library.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives one line per column.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	if err := queries.RecordLanding(ctx, store.RecordLandingParams{
		ID:       1,
		CatchID:  "5f0c6c7e-8d0e-4b77-9a51-2c4a6f9e3b10",
		Species:  "mackerel",
		Boxes:    12,
		Fish:     480,
		WeightG:  163_200,
		LengthCm: 31.5,
		Price:    "412.50",
		OnIce:    true,
		LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
		Tag:      string([]byte{0x04, 0xa2, 0x19, 0xf3}),
		Notes:    new("landed at Newlyn"),
	}); err != nil {
		return fmt.Errorf("recording a landing: %w", err)
	}

	landing, err := queries.GetLanding(ctx, 1)
	if err != nil {
		return fmt.Errorf("getting landing 1: %w", err)
	}
	fmt.Fprintf(output, "id         %-16T %d\n", landing.ID, landing.ID)
	fmt.Fprintf(output, "catch_id   %-16T %s\n", landing.CatchID, landing.CatchID)
	fmt.Fprintf(output, "species    %-16T %s\n", landing.Species, landing.Species)
	fmt.Fprintf(output, "boxes      %-16T %d\n", landing.Boxes, landing.Boxes)
	fmt.Fprintf(output, "fish       %-16T %d\n", landing.Fish, landing.Fish)
	fmt.Fprintf(output, "weight_g   %-16T %d\n", landing.WeightG, landing.WeightG)
	fmt.Fprintf(output, "length_cm  %-16T %.1f\n", landing.LengthCm, landing.LengthCm)
	fmt.Fprintf(output, "price      %-16T %s\n", landing.Price, landing.Price.StringFixed(2))
	fmt.Fprintf(output, "on_ice     %-16T %t\n", landing.OnIce, landing.OnIce)
	fmt.Fprintf(output, "landed_at  %-16T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
	fmt.Fprintf(output, "tag        %-16T % x\n", landing.Tag, landing.Tag)
	fmt.Fprintf(output, "notes      %-16T %s\n", landing.Notes, *landing.Notes)
	return nil
}

What it prints

id         uint64           1
catch_id   uuid.UUID        5f0c6c7e-8d0e-4b77-9a51-2c4a6f9e3b10
species    string           mackerel
boxes      uint16           12
fish       uint32           480
weight_g   int64            163200
length_cm  float32          31.5
price      decimal.Decimal  412.50
on_ice     bool             true
landed_at  time.Time        2026-05-01T05:30:00Z
tag        string           04 a2 19 f3
notes      *string          landed at Newlyn

Changing a type

The defaults are a starting point. A go_type directive on a column, or an override in sgl.yaml, swaps in a type of your own, such as a named Pence type for money; Type overrides shows how. The type mapping reference lists every default, and the order in which overrides apply.

Across engines

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

ClickHouseSQLitePostgreSQLMySQLDuckDB
id parameteruint64int64int64int64int64
id columnuint64int64int64int64int64
boxes columnuint16int64int16int16int16
fish columnuint32int64int32int32int32
length_cm columnfloat32float64float32float64float64
tag columnstring[]byte[]byte[]byte[]byte
catch_id columnuuid.UUID·uuid.UUID·uuid.UUID
sea_temp column··float64··
price columndecimal.Decimal·apd.Decimal··
landed_on column··time.Time·time.Time
harbour_ip column··any··
berth column···uint8·
price_per_kg column···apd.Decimal·
quay column····uint8
water_temp column····float32
auction_lot column····*big.Int

A · marks a parameter or column that version of the example does not have.

TimescaleDB runs 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 ./005_type_mapping

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 ./005_type_mapping

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

db/migrations/0001_landings.up.sql

CREATE TABLE landings (
    id        UInt64,
    catch_id  UUID,
    species   String,
    boxes     UInt16,
    fish      UInt32,
    weight_g  Int64,
    length_cm Float32,
    price     Decimal(9, 2),
    on_ice    Bool,
    landed_at DateTime('UTC'),
    tag       FixedString(4),
    notes     Nullable(String)
) ENGINE = MergeTree() ORDER BY id;

db/migrations/0001_landings.down.sql

DROP TABLE landings;

db/queries/landings.sql

-- sgl.query(RecordLanding, exec)
INSERT INTO landings (id, catch_id, species, boxes, fish, weight_g, length_cm, price, on_ice, landed_at, tag, notes)
VALUES (
    {id:UInt64},
    toUUID({catch_id:String}),
    {species:String},
    {boxes:UInt16},
    {fish:UInt32},
    {weight_g:Int64},
    {length_cm:Float32},
    toDecimal32({price:String}, 2),
    {on_ice:Bool},
    {landed_at:DateTime('UTC')},
    {tag:FixedString(4)},
    {notes:Nullable(String)}
);

-- sgl.query(GetLanding, one)
SELECT id, catch_id, species, boxes, fish, weight_g, length_cm, price, on_ice, landed_at, tag, notes
FROM landings
WHERE id = {id:UInt64};

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/005_type_mapping/internal/store"
)

// run records a landing of fish and reads it back, printing the Go type sgl chose for
// each column. ClickHouse types are exact about width and sign, and Seagull follows them:
// UInt16, UInt32 and UInt64 give uint16, uint32 and uint64, Int64 gives int64, Float32
// gives float32, and Bool gives bool. DateTime('UTC') gives time.Time; the generated code
// sends a time as Unix seconds, so the server keeps its moment whatever the zone, and the
// column's time zone makes it read back in UTC. FixedString(4) and String both give
// string, which in ClickHouse holds any bytes, and Nullable(String) gives *string.
//
// UUID gives uuid.UUID from github.com/google/uuid and Decimal(9, 2) gives
// decimal.Decimal from github.com/shopspring/decimal, the types clickhouse-go scans them
// into. A {price:Decimal(9, 2)} placeholder would make the parameter a decimal.Decimal
// too; this example passes the price and catch id as text and converts them in SQL with
// toDecimal32 and toUUID, so it needs nothing beyond the standard library.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives one line per column.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	if err := queries.RecordLanding(ctx, store.RecordLandingParams{
		ID:       1,
		CatchID:  "5f0c6c7e-8d0e-4b77-9a51-2c4a6f9e3b10",
		Species:  "mackerel",
		Boxes:    12,
		Fish:     480,
		WeightG:  163_200,
		LengthCm: 31.5,
		Price:    "412.50",
		OnIce:    true,
		LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
		Tag:      string([]byte{0x04, 0xa2, 0x19, 0xf3}),
		Notes:    new("landed at Newlyn"),
	}); err != nil {
		return fmt.Errorf("recording a landing: %w", err)
	}

	landing, err := queries.GetLanding(ctx, 1)
	if err != nil {
		return fmt.Errorf("getting landing 1: %w", err)
	}
	fmt.Fprintf(output, "id         %-16T %d\n", landing.ID, landing.ID)
	fmt.Fprintf(output, "catch_id   %-16T %s\n", landing.CatchID, landing.CatchID)
	fmt.Fprintf(output, "species    %-16T %s\n", landing.Species, landing.Species)
	fmt.Fprintf(output, "boxes      %-16T %d\n", landing.Boxes, landing.Boxes)
	fmt.Fprintf(output, "fish       %-16T %d\n", landing.Fish, landing.Fish)
	fmt.Fprintf(output, "weight_g   %-16T %d\n", landing.WeightG, landing.WeightG)
	fmt.Fprintf(output, "length_cm  %-16T %.1f\n", landing.LengthCm, landing.LengthCm)
	fmt.Fprintf(output, "price      %-16T %s\n", landing.Price, landing.Price.StringFixed(2))
	fmt.Fprintf(output, "on_ice     %-16T %t\n", landing.OnIce, landing.OnIce)
	fmt.Fprintf(output, "landed_at  %-16T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
	fmt.Fprintf(output, "tag        %-16T % x\n", landing.Tag, landing.Tag)
	fmt.Fprintf(output, "notes      %-16T %s\n", landing.Notes, *landing.Notes)
	return nil
}

main.go

// Command 005_type_mapping applies its migrations to a ClickHouse database, then runs the
// example in example.go: one landing of fish is recorded and read back, printing each
// column's Go type and value.
//
// 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{}}, {kind: 116, digits: 0, 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/landings.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"time"

	"github.com/ClickHouse/clickhouse-go/v2"
	"github.com/google/uuid"
	"github.com/shopspring/decimal"
)

const getLandingSQL = `SELECT id, catch_id, species, boxes, fish, weight_g, length_cm, price, on_ice, landed_at, tag, notes
FROM landings
WHERE id = {p_id:UInt64};`

type GetLandingRow struct {
	ID       uint64          `json:"id"`
	CatchID  uuid.UUID       `json:"catch_id"`
	Species  string          `json:"species"`
	Boxes    uint16          `json:"boxes"`
	Fish     uint32          `json:"fish"`
	WeightG  int64           `json:"weight_g"`
	LengthCm float32         `json:"length_cm"`
	Price    decimal.Decimal `json:"price"`
	OnIce    bool            `json:"on_ice"`
	LandedAt time.Time       `json:"landed_at"`
	Tag      string          `json:"tag"`
	Notes    *string         `json:"notes"`
}

func (queries *Queries) GetLanding(ctx context.Context, id uint64) (GetLandingRow, error) {
	var row GetLandingRow
	err := queries.reader.QueryRowContext(ctx, getLandingSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(id, sglClickHouseShapes[0]))).Scan(&row.ID, &row.CatchID, &row.Species, &row.Boxes, &row.Fish, &row.WeightG, &row.LengthCm, &row.Price, &row.OnIce, &row.LandedAt, &row.Tag, &row.Notes)
	if err != nil {
		return GetLandingRow{}, err
	}
	return row, nil
}

const recordLandingSQL = `INSERT INTO landings (id, catch_id, species, boxes, fish, weight_g, length_cm, price, on_ice, landed_at, tag, notes)
VALUES (
    {p_id:UInt64},
    toUUID({p_catch_id:String}),
    {p_species:String},
    {p_boxes:UInt16},
    {p_fish:UInt32},
    {p_weight_g:Int64},
    {p_length_cm:Float32},
    toDecimal32({p_price:String}, 2),
    {p_on_ice:Bool},
    {p_landed_at:DateTime('UTC')},
    {p_tag:FixedString(4)},
    {p_notes:Nullable(String)}
);`

type RecordLandingParams struct {
	ID       uint64
	CatchID  string
	Species  string
	Boxes    uint16
	Fish     uint32
	WeightG  int64
	LengthCm float32
	Price    string
	OnIce    bool
	LandedAt time.Time
	Tag      string
	Notes    *string
}

func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) error {
	_, err := queries.writer.ExecContext(ctx, recordLandingSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_catch_id", sglClickHouseShapedValue(params.CatchID, sglClickHouseShapes[1])), clickhouse.Named("p_species", sglClickHouseShapedValue(params.Species, sglClickHouseShapes[1])), clickhouse.Named("p_boxes", sglClickHouseShapedValue(params.Boxes, sglClickHouseShapes[0])), clickhouse.Named("p_fish", sglClickHouseShapedValue(params.Fish, sglClickHouseShapes[0])), clickhouse.Named("p_weight_g", sglClickHouseShapedValue(params.WeightG, sglClickHouseShapes[0])), clickhouse.Named("p_length_cm", sglClickHouseShapedValue(params.LengthCm, sglClickHouseShapes[0])), clickhouse.Named("p_price", sglClickHouseShapedValue(params.Price, sglClickHouseShapes[1])), clickhouse.Named("p_on_ice", sglClickHouseShapedValue(params.OnIce, sglClickHouseShapes[0])), clickhouse.Named("p_landed_at", sglClickHouseShapedValue(params.LandedAt, sglClickHouseShapes[2])), clickhouse.Named("p_tag", sglClickHouseShapedValue(params.Tag, sglClickHouseShapes[1])), clickhouse.Named("p_notes", sglClickHouseShapedValue(params.Notes, sglClickHouseShapes[1])))
	return err
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"time"

	"github.com/google/uuid"
	"github.com/shopspring/decimal"
)

type Landings struct {
	ID       uint64          `json:"id"`
	CatchID  uuid.UUID       `json:"catch_id"`
	Species  string          `json:"species"`
	Boxes    uint16          `json:"boxes"`
	Fish     uint32          `json:"fish"`
	WeightG  int64           `json:"weight_g"`
	LengthCm float32         `json:"length_cm"`
	Price    decimal.Decimal `json:"price"`
	OnIce    bool            `json:"on_ice"`
	LandedAt time.Time       `json:"landed_at"`
	Tag      string          `json:"tag"`
	Notes    *string         `json:"notes"`
}

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

id         uint64           1
catch_id   uuid.UUID        5f0c6c7e-8d0e-4b77-9a51-2c4a6f9e3b10
species    string           mackerel
boxes      uint16           12
fish       uint32           480
weight_g   int64            163200
length_cm  float32          31.5
price      decimal.Decimal  412.50
on_ice     bool             true
landed_at  time.Time        2026-05-01T05:30:00Z
tag        string           04 a2 19 f3
notes      *string          landed at Newlyn

Also on other engines

SQLiteOne landing of fish read back column by column, printing the Go type each declared SQLite type became.PostgreSQLOne landing of fish read back column by column, printing the Go type each PostgreSQL type became, from apd.Decimal and uuid.UUID to an unmapped INET as any.TimescaleDBOne landing of fish read back column by column, printing the Go type each PostgreSQL type became, from apd.Decimal and uuid.UUID to an unmapped INET as any.MySQLOne landing of fish read back column by column, printing the Go type each MySQL column type became, unsigned integers and DECIMAL included.MariaDBOne landing of fish read back column by column, printing the Go type each MySQL column type became, unsigned integers and DECIMAL included.DuckDBOne landing of fish read back column by column, printing the Go type each DuckDB column type became, from unsigned and 128-bit integers to UUIDs.