Arrays, maps and tuples in ClickHouse
On this page
On ClickHouse:
- LowCardinality(String) is a storage hint and maps to string
- ARRAY JOIN gives one row per array element, so the joined tag is a plain string
- A Map or Array parameter is one value, as in {prices:Map(String, UInt32)}
- The Tuple is written from scalar parameters, ({lat:Float64}, {lon:Float64}); a {location:Tuple(lat Float64, lon Float64)} parameter would take a map[string]any keyed by element name
- Map(String, UInt32) maps to map[string]uint32; through database/sql the named Tuple maps to map[string]any and IPv4 to net.IP, the types clickhouse-go's bridge scans into
ClickHouse tables are often wide and denormalised: rather than a second table of tags or prices, a row carries an array or a map of them. ClickHouse has column types for this, Array, Map and Tuple, along with types that say more about a value than String can, such as LowCardinality and IPv4. Seagull reads them from the migration and types the queries that use them.
The example keeps the kiosks along a seaside promenade, each with its tags, its prices and its location in one row.
The table
db/migrations/0001_kiosks.up.sql
CREATE TABLE kiosks (
id UInt64,
name String,
town LowCardinality(String),
till IPv4,
prices Map(String, UInt32),
tags Array(String),
location Tuple(lat Float64, lon Float64)
) ENGINE = MergeTree() ORDER BY id;
townisLowCardinality(String), a storage hint: ClickHouse keeps a dictionary of the few distinct values. To Go it is a plainstring.tagsisArray(String), a[]string.pricesisMap(String, UInt32), from item to price.locationis a namedTupleof latitude and longitude.tillisIPv4, the address of the kiosk's till.
Writing a row
An array or a map is one value, so it takes one parameter, typed with the full ClickHouse type: {tags:Array(String)} binds a Go slice and {prices:Map(String, UInt32)} a Go map.
db/queries/kiosks.sql
-- sgl.query(OpenKiosk, exec)
INSERT INTO kiosks (id, name, town, till, prices, tags, location)
VALUES (
{id:UInt64},
{name:String},
{town:String},
{till:IPv4},
{prices:Map(String, UInt32)},
{tags:Array(String)},
({lat:Float64}, {lon:Float64})
);
Seagull cannot bind a Tuple parameter yet, so the location is written as a tuple literal built from two Float64 parameters, which become the Lat and Lon fields.
internal/store
type OpenKioskParams struct {
ID uint64
Name string
Town string
Till net.IP
Prices map[string]uint32
Tags []string
Lat float64
Lon float64
}
func (queries *Queries) OpenKiosk(ctx context.Context, params OpenKioskParams) error {
_, err := queries.writer.ExecContext(ctx, openKioskSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_name", sglClickHouseShapedValue(params.Name, sglClickHouseShapes[1])), clickhouse.Named("p_town", sglClickHouseShapedValue(params.Town, sglClickHouseShapes[1])), clickhouse.Named("p_till", sglClickHouseShapedValue(params.Till, sglClickHouseShapes[2])), clickhouse.Named("p_prices", sglClickHouseShapedValue(params.Prices, sglClickHouseShapes[3])), clickhouse.Named("p_tags", sglClickHouseShapedValue(params.Tags, sglClickHouseShapes[4])), clickhouse.Named("p_lat", sglClickHouseShapedValue(params.Lat, sglClickHouseShapes[0])), clickhouse.Named("p_lon", sglClickHouseShapedValue(params.Lon, sglClickHouseShapes[0])))
return err
}
Each column type has a Go type: the prices are a map[string]uint32, and the till is a net.IP, the type clickhouse-go's database/sql bridge uses for an address.
Reading it back
GetKiosk selects every column. Tags is a []string, Prices a map[string]uint32 and Till a net.IP. The named Tuple is a map[string]any keyed by element name, which is what the bridge returns for it. The program prints the Go type of each.
db/queries/kiosks.sql
-- sgl.query(GetKiosk, one)
SELECT id, name, town, till, prices, tags, location FROM kiosks WHERE id = {id:UInt64};
One row per element: ARRAY JOIN
ARRAY JOIN unrolls an array: each kiosk produces one row per tag, and kiosks with no tags produce none. Because each row holds one element, the joined tag is a plain string, not a slice.
db/queries/kiosks.sql
-- sgl.query(KiosksByTag, many)
SELECT tag, name
FROM kiosks
ARRAY JOIN tags AS tag
ORDER BY tag, name;
internal/store
type KiosksByTagRow struct {
Tag string `json:"tag"`
Name string `json:"name"`
}
func (queries *Queries) KiosksByTag(ctx context.Context) ([]KiosksByTagRow, error) {
rows, err := queries.reader.QueryContext(ctx, kiosksByTagSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []KiosksByTagRow
if rows.Next() {
var row KiosksByTagRow
scanArgs := []any{&row.Tag, &row.Name}
for more := true; more; more = rows.Next() {
row = KiosksByTagRow{}
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
}
Filtering on a LowCardinality column
A LowCardinality(String) column is compared like any string, and its parameter is an ordinary String:
db/queries/kiosks.sql
-- sgl.query(KiosksInTown, many)
SELECT id, name
FROM kiosks
WHERE town = {town:String}
ORDER BY id;
Using it
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"net"
"seagull.sh/seagull/examples/clickhouse/035_clickhouse_arrays_maps/internal/store"
)
// run opens three kiosks along the promenade and reads them back. One kiosks row holds
// ClickHouse's composite types:
//
// - town is LowCardinality(String), a storage hint that dictionary-encodes repeated
// values; in Go it is a plain string;
// - tags is Array(String), a []string, and KiosksByTag unrolls it with ARRAY JOIN,
// which gives one row per tag, so the tag column is a plain string;
// - prices is Map(String, UInt32), bound from a Go map as one {prices:Map(String,
// UInt32)} value;
// - location is Tuple(lat Float64, lon Float64), written from two Float64 parameters as
// the tuple literal ({lat:Float64}, {lon:Float64}); a Tuple parameter would take a
// map[string]any keyed by element name;
// - till is IPv4, bound from a net.IP.
//
// Through database/sql, Seagull gives each the Go type clickhouse-go's bridge scans it
// into: Map(String, UInt32) is a map[string]uint32, the named Tuple is a map[string]any
// keyed by element name, and IPv4 is a net.IP. The native driver would give the Tuple a
// struct and the address a netip.Addr instead. The listing prints the Go type of each.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the kiosks.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
queries := store.New(database)
kiosks := []store.OpenKioskParams{
{
ID: 1,
Name: "Chips Ahoy",
Town: "Padstow",
Till: net.ParseIP("10.0.0.11"),
Prices: map[string]uint32{"chips": 350, "mushy peas": 150},
Tags: []string{"chips", "takeaway"},
Lat: 50.5413,
Lon: -4.9389,
},
{
ID: 2,
Name: "The Ice Cream Hut",
Town: "Padstow",
Till: net.ParseIP("10.0.0.12"),
Prices: map[string]uint32{"99 flake": 300},
Tags: []string{"ice cream", "takeaway"},
Lat: 50.5420,
Lon: -4.9375,
},
{
ID: 3,
Name: "Gull's Rest",
Town: "Rock",
Till: net.ParseIP("10.0.1.20"),
Prices: map[string]uint32{"crab sandwich": 650, "tea": 220},
Tags: []string{"cafe", "seating"},
Lat: 50.5465,
Lon: -4.9230,
},
}
for _, kiosk := range kiosks {
if err := queries.OpenKiosk(ctx, kiosk); err != nil {
return fmt.Errorf("opening %s: %w", kiosk.Name, err)
}
}
first, err := queries.GetKiosk(ctx, 1)
if err != nil {
return fmt.Errorf("getting kiosk 1: %w", err)
}
fmt.Fprintf(output, "town %-24T %s\n", first.Town, first.Town)
fmt.Fprintf(output, "tags %-24T %v\n", first.Tags, first.Tags)
fmt.Fprintf(output, "prices %-24T %v\n", first.Prices, first.Prices)
fmt.Fprintf(output, "location %-24T %v\n", first.Location, first.Location)
fmt.Fprintf(output, "till %-24T %v\n", first.Till, first.Till)
tagged, err := queries.KiosksByTag(ctx)
if err != nil {
return fmt.Errorf("listing kiosks by tag: %w", err)
}
fmt.Fprintln(output, "kiosks by tag, one row per tag:")
for _, row := range tagged {
fmt.Fprintf(output, " %-10s %s\n", row.Tag, row.Name)
}
padstow, err := queries.KiosksInTown(ctx, "Padstow")
if err != nil {
return fmt.Errorf("listing Padstow's kiosks: %w", err)
}
fmt.Fprintln(output, "kiosks in Padstow:")
for _, kiosk := range padstow {
fmt.Fprintf(output, " #%d %s\n", kiosk.ID, kiosk.Name)
}
return nil
}
What it prints
The first five lines show each field's Go type next to its value: the prices come back as a map[string]uint32, the location as a map of its named elements, and the till as a net.IP. "takeaway" appears twice under ARRAY JOIN, once for each kiosk that has it.
town string Padstow
tags []string [chips takeaway]
prices map[string]uint32 map[chips:350 mushy peas:150]
location map[string]interface {} map[lat:50.5413 lon:-4.9389]
till net.IP 10.0.0.11
kiosks by tag, one row per tag:
cafe Gull's Rest
chips Chips Ahoy
ice cream The Ice Cream Hut
seating Gull's Rest
takeaway Chips Ahoy
takeaway The Ice Cream Hut
kiosks in Padstow:
#1 Chips Ahoy
#2 The Ice Cream Hut
Try it
Add driver: clickhouse under go: in sgl.yaml and run sgl generate. With the native driver, Till becomes a netip.Addr and Location a struct with Lat and Lon fields: the native driver scans into both, where database/sql cannot. The program would then take a driver.Conn rather than a *sql.DB.
Run it locally
From the directory you cloned the Seagull repository into:
cd seagull/examples/clickhouse && go run ./035_clickhouse_arrays_maps
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 ./035_clickhouse_arrays_maps
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: promenade
dialect: clickhouse
migrations: db/migrations
queries: db/queries
go:
package: store
out: internal/store
db/migrations/0001_kiosks.up.sql
CREATE TABLE kiosks (
id UInt64,
name String,
town LowCardinality(String),
till IPv4,
prices Map(String, UInt32),
tags Array(String),
location Tuple(lat Float64, lon Float64)
) ENGINE = MergeTree() ORDER BY id;
db/migrations/0001_kiosks.down.sql
DROP TABLE kiosks;
db/queries/kiosks.sql
-- sgl.query(OpenKiosk, exec)
INSERT INTO kiosks (id, name, town, till, prices, tags, location)
VALUES (
{id:UInt64},
{name:String},
{town:String},
{till:IPv4},
{prices:Map(String, UInt32)},
{tags:Array(String)},
({lat:Float64}, {lon:Float64})
);
-- sgl.query(GetKiosk, one)
SELECT id, name, town, till, prices, tags, location FROM kiosks WHERE id = {id:UInt64};
-- sgl.query(KiosksByTag, many)
SELECT tag, name
FROM kiosks
ARRAY JOIN tags AS tag
ORDER BY tag, name;
-- sgl.query(KiosksInTown, many)
SELECT id, name
FROM kiosks
WHERE town = {town:String}
ORDER BY id;
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"net"
"seagull.sh/seagull/examples/clickhouse/035_clickhouse_arrays_maps/internal/store"
)
// run opens three kiosks along the promenade and reads them back. One kiosks row holds
// ClickHouse's composite types:
//
// - town is LowCardinality(String), a storage hint that dictionary-encodes repeated
// values; in Go it is a plain string;
// - tags is Array(String), a []string, and KiosksByTag unrolls it with ARRAY JOIN,
// which gives one row per tag, so the tag column is a plain string;
// - prices is Map(String, UInt32), bound from a Go map as one {prices:Map(String,
// UInt32)} value;
// - location is Tuple(lat Float64, lon Float64), written from two Float64 parameters as
// the tuple literal ({lat:Float64}, {lon:Float64}); a Tuple parameter would take a
// map[string]any keyed by element name;
// - till is IPv4, bound from a net.IP.
//
// Through database/sql, Seagull gives each the Go type clickhouse-go's bridge scans it
// into: Map(String, UInt32) is a map[string]uint32, the named Tuple is a map[string]any
// keyed by element name, and IPv4 is a net.IP. The native driver would give the Tuple a
// struct and the address a netip.Addr instead. The listing prints the Go type of each.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the kiosks.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
queries := store.New(database)
kiosks := []store.OpenKioskParams{
{
ID: 1,
Name: "Chips Ahoy",
Town: "Padstow",
Till: net.ParseIP("10.0.0.11"),
Prices: map[string]uint32{"chips": 350, "mushy peas": 150},
Tags: []string{"chips", "takeaway"},
Lat: 50.5413,
Lon: -4.9389,
},
{
ID: 2,
Name: "The Ice Cream Hut",
Town: "Padstow",
Till: net.ParseIP("10.0.0.12"),
Prices: map[string]uint32{"99 flake": 300},
Tags: []string{"ice cream", "takeaway"},
Lat: 50.5420,
Lon: -4.9375,
},
{
ID: 3,
Name: "Gull's Rest",
Town: "Rock",
Till: net.ParseIP("10.0.1.20"),
Prices: map[string]uint32{"crab sandwich": 650, "tea": 220},
Tags: []string{"cafe", "seating"},
Lat: 50.5465,
Lon: -4.9230,
},
}
for _, kiosk := range kiosks {
if err := queries.OpenKiosk(ctx, kiosk); err != nil {
return fmt.Errorf("opening %s: %w", kiosk.Name, err)
}
}
first, err := queries.GetKiosk(ctx, 1)
if err != nil {
return fmt.Errorf("getting kiosk 1: %w", err)
}
fmt.Fprintf(output, "town %-24T %s\n", first.Town, first.Town)
fmt.Fprintf(output, "tags %-24T %v\n", first.Tags, first.Tags)
fmt.Fprintf(output, "prices %-24T %v\n", first.Prices, first.Prices)
fmt.Fprintf(output, "location %-24T %v\n", first.Location, first.Location)
fmt.Fprintf(output, "till %-24T %v\n", first.Till, first.Till)
tagged, err := queries.KiosksByTag(ctx)
if err != nil {
return fmt.Errorf("listing kiosks by tag: %w", err)
}
fmt.Fprintln(output, "kiosks by tag, one row per tag:")
for _, row := range tagged {
fmt.Fprintf(output, " %-10s %s\n", row.Tag, row.Name)
}
padstow, err := queries.KiosksInTown(ctx, "Padstow")
if err != nil {
return fmt.Errorf("listing Padstow's kiosks: %w", err)
}
fmt.Fprintln(output, "kiosks in Padstow:")
for _, kiosk := range padstow {
fmt.Fprintf(output, " #%d %s\n", kiosk.ID, kiosk.Name)
}
return nil
}
main.go
// Command 035_clickhouse_arrays_maps applies its migrations to a ClickHouse database,
// then runs the example in example.go: promenade kiosks with LowCardinality, Array, Map,
// Tuple and IPv4 columns are stored, read back and unrolled with ARRAY JOIN.
//
// 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: 52, digits: -1, prepared: true, checked: true, driverMap: false, names: []string{}, elements: []sglClickHouseShape{}}, {kind: 123, 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: 110, digits: -1, prepared: true, checked: false, driverMap: false, names: []string{}, elements: []sglClickHouseShape{}}}}, {kind: 91, 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/kiosks.sql.go
// Code generated by sgl. DO NOT EDIT.
package store
import (
"context"
"net"
"github.com/ClickHouse/clickhouse-go/v2"
)
const getKioskSQL = `SELECT id, name, town, till, prices, tags, location FROM kiosks WHERE id = {p_id:UInt64};`
type GetKioskRow struct {
ID uint64 `json:"id"`
Name string `json:"name"`
Town string `json:"town"`
Till net.IP `json:"till"`
Prices map[string]uint32 `json:"prices"`
Tags []string `json:"tags"`
Location map[string]any `json:"location"`
}
func (queries *Queries) GetKiosk(ctx context.Context, id uint64) (GetKioskRow, error) {
var row GetKioskRow
err := queries.reader.QueryRowContext(ctx, getKioskSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(id, sglClickHouseShapes[0]))).Scan(&row.ID, &row.Name, &row.Town, &row.Till, &row.Prices, &row.Tags, &row.Location)
if err != nil {
return GetKioskRow{}, err
}
return row, nil
}
const kiosksByTagSQL = `SELECT tag, name
FROM kiosks
ARRAY JOIN tags AS tag
ORDER BY tag, name;`
type KiosksByTagRow struct {
Tag string `json:"tag"`
Name string `json:"name"`
}
func (queries *Queries) KiosksByTag(ctx context.Context) ([]KiosksByTagRow, error) {
rows, err := queries.reader.QueryContext(ctx, kiosksByTagSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []KiosksByTagRow
if rows.Next() {
var row KiosksByTagRow
scanArgs := []any{&row.Tag, &row.Name}
for more := true; more; more = rows.Next() {
row = KiosksByTagRow{}
if err := rows.Scan(scanArgs...); err != nil {
return nil, err
}
results = append(results, row)
}
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const kiosksInTownSQL = `SELECT id, name
FROM kiosks
WHERE town = {p_town:String}
ORDER BY id;`
type KiosksInTownRow struct {
ID uint64 `json:"id"`
Name string `json:"name"`
}
func (queries *Queries) KiosksInTown(ctx context.Context, town string) ([]KiosksInTownRow, error) {
rows, err := queries.reader.QueryContext(ctx, kiosksInTownSQL, clickhouse.Named("p_town", sglClickHouseShapedValue(town, sglClickHouseShapes[1])))
if err != nil {
return nil, err
}
defer rows.Close()
var results []KiosksInTownRow
if rows.Next() {
var row KiosksInTownRow
scanArgs := []any{&row.ID, &row.Name}
for more := true; more; more = rows.Next() {
row = KiosksInTownRow{}
if err := rows.Scan(scanArgs...); err != nil {
return nil, err
}
results = append(results, row)
}
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const openKioskSQL = `INSERT INTO kiosks (id, name, town, till, prices, tags, location)
VALUES (
{p_id:UInt64},
{p_name:String},
{p_town:String},
{p_till:IPv4},
{p_prices:Map(String, UInt32)},
{p_tags:Array(String)},
({p_lat:Float64}, {p_lon:Float64})
);`
type OpenKioskParams struct {
ID uint64
Name string
Town string
Till net.IP
Prices map[string]uint32
Tags []string
Lat float64
Lon float64
}
func (queries *Queries) OpenKiosk(ctx context.Context, params OpenKioskParams) error {
_, err := queries.writer.ExecContext(ctx, openKioskSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_name", sglClickHouseShapedValue(params.Name, sglClickHouseShapes[1])), clickhouse.Named("p_town", sglClickHouseShapedValue(params.Town, sglClickHouseShapes[1])), clickhouse.Named("p_till", sglClickHouseShapedValue(params.Till, sglClickHouseShapes[2])), clickhouse.Named("p_prices", sglClickHouseShapedValue(params.Prices, sglClickHouseShapes[3])), clickhouse.Named("p_tags", sglClickHouseShapedValue(params.Tags, sglClickHouseShapes[4])), clickhouse.Named("p_lat", sglClickHouseShapedValue(params.Lat, sglClickHouseShapes[0])), clickhouse.Named("p_lon", sglClickHouseShapedValue(params.Lon, sglClickHouseShapes[0])))
return err
}
internal/store/models.go
// Code generated by sgl. DO NOT EDIT.
package store
import "net"
type Kiosks struct {
ID uint64 `json:"id"`
Name string `json:"name"`
Town string `json:"town"`
Till net.IP `json:"till"`
Prices map[string]uint32 `json:"prices"`
Tags []string `json:"tags"`
Location map[string]any `json:"location"`
}
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
town string Padstow
tags []string [chips takeaway]
prices map[string]uint32 map[chips:350 mushy peas:150]
location map[string]interface {} map[lat:50.5413 lon:-4.9389]
till net.IP 10.0.0.11
kiosks by tag, one row per tag:
cafe Gull's Rest
chips Chips Ahoy
ice cream The Ice Cream Hut
seating Gull's Rest
takeaway Chips Ahoy
takeaway The Ice Cream Hut
kiosks in Padstow:
#1 Chips Ahoy
#2 The Ice Cream Hut