Arrays and lists as typed slices
On this page
On ClickHouse:
- Array(T) maps to []T in rows and parameters; the generated method formats a slice parameter as one array literal
- An Array column cannot be Nullable, so an empty slice stores an empty array, never NULL
- has(array, value) tests membership on the server
Some values are lists by nature: the ports a voyage called at, the species in a haul. When the database stores them in an array column, Seagull maps that column to a Go slice of the element's type, so you get a []string or a []int64 to range over, never an array literal to parse or a []any to type-assert. Slices go back in as parameters the same way.
This example lands hauls of fish, each with the species caught and their weights.
The schema
Array(String) and Array(UInt32) hold a list of one type each. ClickHouse does not allow an Array column to be Nullable, so an empty haul is an empty array, never NULL.
db/migrations/0001_hauls.up.sql
CREATE TABLE hauls (
id UInt64,
boat String,
species Array(String),
weights_kg Array(UInt32)
) ENGINE = MergeTree() ORDER BY id;
Reading and writing arrays
An Array(T) column arrives as a slice of T's Go type, so species is a []string and weights_kg a []uint32:
internal/store
type GetHaulRow struct {
ID uint64 `json:"id"`
Boat string `json:"boat"`
Species []string `json:"species"`
WeightsKg []uint32 `json:"weights_kg"`
}
func (queries *Queries) GetHaul(ctx context.Context, id uint64) (GetHaulRow, error) {
var row GetHaulRow
err := queries.reader.QueryRowContext(ctx, getHaulSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(id, sglClickHouseShapes[0]))).Scan(&row.ID, &row.Boat, &row.Species, &row.WeightsKg)
if err != nil {
return GetHaulRow{}, err
}
return row, nil
}
Parameters follow the same rule. LandHaul takes the two slices, and the generated method formats each one as a single array value for its Array placeholder:
db/queries/hauls.sql
-- sgl.query(LandHaul, exec)
INSERT INTO hauls (id, boat, species, weights_kg)
VALUES ({id:UInt64}, {boat:String}, {species:Array(String)}, {weights_kg:Array(UInt32)});
Searching inside an array
has(species, {fish:String}) tests whether the array holds a value, and does it on the server, so only the matching hauls come back:
db/queries/hauls.sql
-- sgl.query(HaulsWith, many)
SELECT id, boat, species, weights_kg
FROM hauls
WHERE has(species, {fish:String})
ORDER BY id;
Using it
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"seagull.sh/seagull/examples/clickhouse/008_arrays/internal/store"
)
// run lands four hauls of fish and reads them back. An Array(T) column arrives as a Go
// slice of T's type, so species, an Array(String), is []string and weights_kg, an
// Array(UInt32), is []uint32, in result rows and in parameters alike: LandHaul takes the
// slices and binds each as one {species:Array(String)} value.
//
// HaulsWith filters with has(species, {fish:String}), which looks inside the array on the
// server. An Array column cannot be Nullable in ClickHouse, so an empty haul is an empty
// array, never NULL.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the hauls.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
queries := store.New(database)
hauls := []store.LandHaulParams{
{ID: 1, Boat: "Kittiwake", Species: []string{"mackerel", "pollock"}, WeightsKg: []uint32{120, 45}},
{ID: 2, Boat: "Salty Chip", Species: []string{"crab", "lobster"}, WeightsKg: []uint32{30, 12}},
{ID: 3, Boat: "Gull Wing", Species: []string{"mackerel"}, WeightsKg: []uint32{80}},
{ID: 4, Boat: "Driftwood", Species: []string{}, WeightsKg: []uint32{}},
}
for _, haul := range hauls {
if err := queries.LandHaul(ctx, haul); err != nil {
return fmt.Errorf("landing haul %d: %w", haul.ID, err)
}
}
first, err := queries.GetHaul(ctx, 1)
if err != nil {
return fmt.Errorf("getting haul 1: %w", err)
}
fmt.Fprintf(output, "haul 1: species %T %v, weights_kg %T %v\n", first.Species, first.Species, first.WeightsKg, first.WeightsKg)
mackerel, err := queries.HaulsWith(ctx, "mackerel")
if err != nil {
return fmt.Errorf("finding mackerel: %w", err)
}
fmt.Fprintln(output, "hauls with mackerel:")
for _, haul := range mackerel {
fmt.Fprintf(output, " #%d %-10s %v %v kg\n", haul.ID, haul.Boat, haul.Species, haul.WeightsKg)
}
empty, err := queries.GetHaul(ctx, 4)
if err != nil {
return fmt.Errorf("getting haul 4: %w", err)
}
fmt.Fprintf(output, "haul 4 landed %d species, an empty array: %v\n", len(empty.Species), empty.Species)
return nil
}
What it prints
haul 1: species []string [mackerel pollock], weights_kg []uint32 [120 45]
hauls with mackerel:
#1 Kittiwake [mackerel pollock] [120 45] kg
#3 Gull Wing [mackerel] [80] kg
haul 4 landed 0 species, an empty array: []
To pass a Go slice where the SQL expects a list of values, as in IN (...), see IN lists from Go slices.
Run it locally
From the directory you cloned the Seagull repository into:
cd seagull/examples/clickhouse && go run ./008_arrays
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 ./008_arrays
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: quay
dialect: clickhouse
migrations: db/migrations
queries: db/queries
go:
package: store
out: internal/store
db/migrations/0001_hauls.up.sql
CREATE TABLE hauls (
id UInt64,
boat String,
species Array(String),
weights_kg Array(UInt32)
) ENGINE = MergeTree() ORDER BY id;
db/migrations/0001_hauls.down.sql
DROP TABLE hauls;
db/queries/hauls.sql
-- sgl.query(LandHaul, exec)
INSERT INTO hauls (id, boat, species, weights_kg)
VALUES ({id:UInt64}, {boat:String}, {species:Array(String)}, {weights_kg:Array(UInt32)});
-- sgl.query(GetHaul, one)
SELECT id, boat, species, weights_kg FROM hauls WHERE id = {id:UInt64};
-- sgl.query(HaulsWith, many)
SELECT id, boat, species, weights_kg
FROM hauls
WHERE has(species, {fish:String})
ORDER BY id;
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"seagull.sh/seagull/examples/clickhouse/008_arrays/internal/store"
)
// run lands four hauls of fish and reads them back. An Array(T) column arrives as a Go
// slice of T's type, so species, an Array(String), is []string and weights_kg, an
// Array(UInt32), is []uint32, in result rows and in parameters alike: LandHaul takes the
// slices and binds each as one {species:Array(String)} value.
//
// HaulsWith filters with has(species, {fish:String}), which looks inside the array on the
// server. An Array column cannot be Nullable in ClickHouse, so an empty haul is an empty
// array, never NULL.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the hauls.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
queries := store.New(database)
hauls := []store.LandHaulParams{
{ID: 1, Boat: "Kittiwake", Species: []string{"mackerel", "pollock"}, WeightsKg: []uint32{120, 45}},
{ID: 2, Boat: "Salty Chip", Species: []string{"crab", "lobster"}, WeightsKg: []uint32{30, 12}},
{ID: 3, Boat: "Gull Wing", Species: []string{"mackerel"}, WeightsKg: []uint32{80}},
{ID: 4, Boat: "Driftwood", Species: []string{}, WeightsKg: []uint32{}},
}
for _, haul := range hauls {
if err := queries.LandHaul(ctx, haul); err != nil {
return fmt.Errorf("landing haul %d: %w", haul.ID, err)
}
}
first, err := queries.GetHaul(ctx, 1)
if err != nil {
return fmt.Errorf("getting haul 1: %w", err)
}
fmt.Fprintf(output, "haul 1: species %T %v, weights_kg %T %v\n", first.Species, first.Species, first.WeightsKg, first.WeightsKg)
mackerel, err := queries.HaulsWith(ctx, "mackerel")
if err != nil {
return fmt.Errorf("finding mackerel: %w", err)
}
fmt.Fprintln(output, "hauls with mackerel:")
for _, haul := range mackerel {
fmt.Fprintf(output, " #%d %-10s %v %v kg\n", haul.ID, haul.Boat, haul.Species, haul.WeightsKg)
}
empty, err := queries.GetHaul(ctx, 4)
if err != nil {
return fmt.Errorf("getting haul 4: %w", err)
}
fmt.Fprintf(output, "haul 4 landed %d species, an empty array: %v\n", len(empty.Species), empty.Species)
return nil
}
main.go
// Command 008_arrays applies its migrations to a ClickHouse database, then runs the
// example in example.go: hauls of fish with Array columns are landed and read back as Go
// slices.
//
// 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: 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{}}}}, {kind: 91, 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{}}}}}
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/hauls.sql.go
// Code generated by sgl. DO NOT EDIT.
package store
import (
"context"
"github.com/ClickHouse/clickhouse-go/v2"
)
const getHaulSQL = `SELECT id, boat, species, weights_kg FROM hauls WHERE id = {p_id:UInt64};`
type GetHaulRow struct {
ID uint64 `json:"id"`
Boat string `json:"boat"`
Species []string `json:"species"`
WeightsKg []uint32 `json:"weights_kg"`
}
func (queries *Queries) GetHaul(ctx context.Context, id uint64) (GetHaulRow, error) {
var row GetHaulRow
err := queries.reader.QueryRowContext(ctx, getHaulSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(id, sglClickHouseShapes[0]))).Scan(&row.ID, &row.Boat, &row.Species, &row.WeightsKg)
if err != nil {
return GetHaulRow{}, err
}
return row, nil
}
const haulsWithSQL = `SELECT id, boat, species, weights_kg
FROM hauls
WHERE has(species, {p_fish:String})
ORDER BY id;`
type HaulsWithRow struct {
ID uint64 `json:"id"`
Boat string `json:"boat"`
Species []string `json:"species"`
WeightsKg []uint32 `json:"weights_kg"`
}
func (queries *Queries) HaulsWith(ctx context.Context, fish string) ([]HaulsWithRow, error) {
rows, err := queries.reader.QueryContext(ctx, haulsWithSQL, clickhouse.Named("p_fish", sglClickHouseShapedValue(fish, sglClickHouseShapes[1])))
if err != nil {
return nil, err
}
defer rows.Close()
var results []HaulsWithRow
for rows.Next() {
var row HaulsWithRow
if err := rows.Scan(&row.ID, &row.Boat, &row.Species, &row.WeightsKg); err != nil {
return nil, err
}
results = append(results, row)
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const landHaulSQL = `INSERT INTO hauls (id, boat, species, weights_kg)
VALUES ({p_id:UInt64}, {p_boat:String}, {p_species:Array(String)}, {p_weights_kg:Array(UInt32)});`
type LandHaulParams struct {
ID uint64
Boat string
Species []string
WeightsKg []uint32
}
func (queries *Queries) LandHaul(ctx context.Context, params LandHaulParams) error {
_, err := queries.writer.ExecContext(ctx, landHaulSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_boat", sglClickHouseShapedValue(params.Boat, sglClickHouseShapes[1])), clickhouse.Named("p_species", sglClickHouseShapedValue(params.Species, sglClickHouseShapes[2])), clickhouse.Named("p_weights_kg", sglClickHouseShapedValue(params.WeightsKg, sglClickHouseShapes[3])))
return err
}
internal/store/models.go
// Code generated by sgl. DO NOT EDIT.
package store
type Hauls struct {
ID uint64 `json:"id"`
Boat string `json:"boat"`
Species []string `json:"species"`
WeightsKg []uint32 `json:"weights_kg"`
}
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
haul 1: species []string [mackerel pollock], weights_kg []uint32 [120 45]
hauls with mackerel:
#1 Kittiwake [mackerel pollock] [120 45] kg
#3 Gull Wing [mackerel] [80] kg
haul 4 landed 0 species, an empty array: []
Also on other engines
PostgreSQLFishing voyages with TEXT[] and BIGINT[] columns read back as typed Go slices, searched with ANY and grouped with array_agg.CockroachDBFishing voyages with TEXT[] and BIGINT[] columns read back as typed Go slices, searched with ANY and grouped with array_agg.TimescaleDBFishing voyages with TEXT[] and BIGINT[] columns read back as typed Go slices, searched with ANY and grouped with array_agg.DuckDBFishing voyages with VARCHAR[] and INTEGER[] list columns read back as typed Go slices, searched with ANY and grouped with array_agg.