IN lists from Go slices

On this page

On ClickHouse:

  • IN {beaches:Array(String)} takes the whole slice as one array parameter, so no kind: slice is needed and nothing is rewritten at call time
  • An empty slice is an empty array, so IN [] matches nothing
  • No bind-variable limit applies, as the slice is one value
  • Q017 and Q018 do not arise, since an Array parameter is not expanded
  • ALTER TABLE ... UPDATE reports no changed rows, so BookHuts is asyncexec and the example counts booked huts before and after

IN (...) filters by a list of values, but a placeholder stands for one value, and the length of the list is only known when the program runs. Building the list into the SQL text by hand is how injection bugs start. With Seagull you pass a Go slice, and the generated code turns it into a list the database understands, with every element sent as a bound value.

ClickHouse takes a whole array as one parameter, so the query says IN {beaches:Array(String)} and the method takes a []string. There is no kind: slice binding, and nothing is rewritten when the method is called.

This example lets out beach huts: find the huts on some beaches, book several at once, and find free huts in the colours a family likes.

A slice parameter

db/queries/huts.sql

-- sgl.query(HutsOnBeaches, many)
SELECT number, beach, colour, booked
FROM huts
WHERE beach IN {beaches:Array(String)}
ORDER BY number;

Array(String) makes beaches a []string, and as the only parameter it is a plain argument:

internal/store

type HutsOnBeachesRow struct {
	Number uint64 `json:"number"`
	Beach  string `json:"beach"`
	Colour string `json:"colour"`
	Booked bool   `json:"booked"`
}

func (queries *Queries) HutsOnBeaches(ctx context.Context, beaches []string) ([]HutsOnBeachesRow, error) {
	rows, err := queries.reader.QueryContext(ctx, hutsOnBeachesSQL, clickhouse.Named("p_beaches", sglClickHouseShapedValue(beaches, sglClickHouseShapes[3])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []HutsOnBeachesRow
	if rows.Next() {
		var row HutsOnBeachesRow
		scanArgs := []any{&row.Number, &row.Beach, &row.Colour, &row.Booked}
		for more := true; more; more = rows.Next() {
			row = HutsOnBeachesRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

The method formats the whole slice as one array value and binds it by name. However long the slice is, it is one parameter, so there is no limit on bound values to reach. An empty slice is an empty array, which matches nothing, so asking for the huts on no beaches gives no huts rather than an error.

A slice after a plain parameter

FreeHutsInColours has an ordinary parameter and a slice in the same query. The beach is one value, and the colours are a list:

db/queries/huts.sql

-- sgl.query(FreeHutsInColours, many)
SELECT number, colour
FROM huts
WHERE beach = {beach:String} AND colour IN {colours:Array(String)} AND NOT booked
ORDER BY number;

The struct has a Beach string and a Colours slice, each bound by name.

Writing with a slice

Slices work in writes too. BookHuts books every free hut in the list at once:

db/queries/huts.sql

-- sgl.query(BookHuts, asyncexec)
ALTER TABLE huts UPDATE booked = true WHERE number IN {numbers:Array(UInt64)} AND NOT booked;

An ALTER TABLE ... UPDATE reports no changed rows, so BookHuts is asyncexec, and the program counts the booked huts with CountBooked before and after each booking. Hut 3 is taken by the first booking, so the second, which asks for huts 3 and 5, books only one.

Where a slice cannot go

On the engines that expand a slice into one placeholder per element, sgl vet refuses a slice parameter in a batch query (Q017) or a dynamic: runtime query (Q018). An Array parameter is a single value, so neither arises on ClickHouse.

Using it

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/010_slices/internal/store"
)

// run lets out beach huts by passing Go slices where the SQL has an IN list. ClickHouse
// takes a whole array as one parameter, so the query says IN {beaches:Array(String)} and
// the method takes a []string; no kind: slice binding is needed and the SQL is not
// rewritten at call time. The slice is sent as one array value however long it is, and an
// empty slice is an empty array, which matches nothing.
//
// Other engines expand IN (?1) into one placeholder per element, which is why sgl vet
// refuses a kind: slice parameter on a batch query (Q017) or a dynamic: runtime query
// (Q018); an Array parameter is a single value, so neither applies here.
//
// ALTER TABLE ... UPDATE reports no changed rows, so BookHuts is asyncexec and the
// example counts the booked huts before and after each booking.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each search and booking.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	huts := []store.AddHutParams{
		{Number: 1, Beach: "Polzeath", Colour: "blue"},
		{Number: 2, Beach: "Polzeath", Colour: "yellow"},
		{Number: 3, Beach: "Polzeath", Colour: "red"},
		{Number: 4, Beach: "Watergate", Colour: "blue"},
		{Number: 5, Beach: "Watergate", Colour: "red"},
		{Number: 6, Beach: "Fistral", Colour: "yellow"},
	}
	for _, hut := range huts {
		if err := queries.AddHut(ctx, hut); err != nil {
			return fmt.Errorf("adding hut %d: %w", hut.Number, err)
		}
	}

	for _, beaches := range [][]string{{"Polzeath", "Fistral"}, {"Watergate"}, {}} {
		found, err := queries.HutsOnBeaches(ctx, beaches)
		if err != nil {
			return fmt.Errorf("finding huts on %v: %w", beaches, err)
		}
		numbers := make([]uint64, 0, len(found))
		for _, hut := range found {
			numbers = append(numbers, hut.Number)
		}
		fmt.Fprintf(output, "huts on %v: %v\n", beaches, numbers)
	}

	// Hut 3 is taken by the first booking, so the second books only hut 5.
	for _, numbers := range [][]uint64{{1, 3, 4}, {3, 5}} {
		booked, err := bookHuts(ctx, queries, numbers)
		if err != nil {
			return err
		}
		fmt.Fprintf(output, "booking huts %v booked %d\n", numbers, booked)
	}

	free, err := queries.FreeHutsInColours(ctx, store.FreeHutsInColoursParams{
		Beach:   "Polzeath",
		Colours: []string{"blue", "yellow"},
	})
	if err != nil {
		return fmt.Errorf("finding free huts: %w", err)
	}
	for _, hut := range free {
		fmt.Fprintf(output, "hut %d at Polzeath is %s and free\n", hut.Number, hut.Colour)
	}
	return nil
}

// bookHuts books the huts that are free among numbers and returns how many it booked, by
// counting the booked huts before and after. The connection sets mutations_sync=2, so the
// mutation has finished when BookHuts returns.
//
// Takes queries (*store.Queries) which runs the queries.
// Takes numbers ([]uint64) which are the huts to book.
//
// Returns uint64 which is how many huts were booked.
// Returns error when a query fails.
func bookHuts(ctx context.Context, queries *store.Queries, numbers []uint64) (uint64, error) {
	before, err := queries.CountBooked(ctx)
	if err != nil {
		return 0, fmt.Errorf("counting booked huts: %w", err)
	}
	if err := queries.BookHuts(ctx, numbers); err != nil {
		return 0, fmt.Errorf("booking huts %v: %w", numbers, err)
	}
	after, err := queries.CountBooked(ctx)
	if err != nil {
		return 0, fmt.Errorf("counting booked huts: %w", err)
	}
	return after.Huts - before.Huts, nil
}

What it prints

huts on [Polzeath Fistral]: [1 2 3 6]
huts on [Watergate]: [4 5]
huts on []: []
booking huts [1 3 4] booked 3
booking huts [3 5] booked 1
hut 2 at Polzeath is yellow and free

With the native ClickHouse driver

Beach huts found and booked over a native connection, each Go slice bound as one {name:Array(T)} parameter through clickhouse.Named.

What changes:

  • IN {beaches:Array(String)} takes the whole slice as one array parameter, so no kind: slice is needed and nothing is rewritten at call time
  • Both drivers bind the slice as text: the native method, like database/sql, writes it as one array literal with the generated clickhouse_values.go before clickhouse.Named
  • An empty slice is an empty array, so IN [] matches nothing
  • Q017 and Q018 do not arise, since an Array parameter is not expanded
  • The native driver reports no changed rows, so BookHuts is asyncexec and the example counts booked huts before and after

db/queries/huts.sql

-- sgl.query(AddHut, exec)
INSERT INTO huts (number, beach, colour) VALUES ({number:UInt64}, {beach:String}, {colour:String});

-- sgl.query(HutsOnBeaches, many)
SELECT number, beach, colour, booked
FROM huts
WHERE beach IN {beaches:Array(String)}
ORDER BY number;

-- sgl.query(BookHuts, asyncexec)
ALTER TABLE huts UPDATE booked = true WHERE number IN {numbers:Array(UInt64)} AND NOT booked;

-- sgl.query(CountBooked, one)
SELECT count() AS huts FROM huts WHERE booked;

-- sgl.query(FreeHutsInColours, many)
SELECT number, colour
FROM huts
WHERE beach = {beach:String} AND colour IN {colours:Array(String)} AND NOT booked
ORDER BY number;

internal/store

type HutsOnBeachesRow struct {
	Number uint64 `json:"number"`
	Beach  string `json:"beach"`
	Colour string `json:"colour"`
	Booked bool   `json:"booked"`
}

func (queries *Queries) HutsOnBeaches(ctx context.Context, beaches []string) ([]HutsOnBeachesRow, error) {
	rows, err := queries.db.Query(ctx, hutsOnBeachesSQL, clickhouse.Named("p_beaches", sglClickHouseShapedValue(beaches, sglClickHouseShapes[3])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []HutsOnBeachesRow
	if rows.Next() {
		var row HutsOnBeachesRow
		scanArgs := []any{&row.Number, &row.Beach, &row.Colour, &row.Booked}
		for more := true; more; more = rows.Next() {
			row = HutsOnBeachesRow{}
			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
}

example.go

package main

import (
	"context"
	"fmt"
	"io"

	"seagull.sh/seagull/examples/clickhouse/010_slices_native/internal/store"
)

// run lets out beach huts by passing Go slices where the SQL has an IN list, over a
// native clickhouse-go connection. ClickHouse takes a whole array as one parameter, so
// the query says IN {beaches:Array(String)} and the method takes a []string; no kind:
// slice binding is needed and the SQL is not rewritten at call time. The native method
// writes the slice as one array literal with the generated clickhouse_values.go and binds
// that text with clickhouse.Named("p_beaches", ...), however long the slice is. An empty
// slice is an empty array, which matches nothing.
//
// Other engines expand IN (?1) into one placeholder per element, which is why sgl vet
// refuses a kind: slice parameter on a batch query (Q017) or a dynamic: runtime query
// (Q018); an Array parameter is a single value, so neither applies here.
//
// ALTER TABLE ... UPDATE reports no changed rows, so BookHuts is asyncexec and the
// example counts the booked huts before and after each booking.
//
// Takes connection (store.DBTX) which is a native connection to a migrated database.
// Takes output (io.Writer) which receives each search and booking.
//
// Returns error when a query fails.
func run(ctx context.Context, connection store.DBTX, output io.Writer) error {
	queries := store.New(connection)

	huts := []store.AddHutParams{
		{Number: 1, Beach: "Polzeath", Colour: "blue"},
		{Number: 2, Beach: "Polzeath", Colour: "yellow"},
		{Number: 3, Beach: "Polzeath", Colour: "red"},
		{Number: 4, Beach: "Watergate", Colour: "blue"},
		{Number: 5, Beach: "Watergate", Colour: "red"},
		{Number: 6, Beach: "Fistral", Colour: "yellow"},
	}
	for _, hut := range huts {
		if err := queries.AddHut(ctx, hut); err != nil {
			return fmt.Errorf("adding hut %d: %w", hut.Number, err)
		}
	}

	for _, beaches := range [][]string{{"Polzeath", "Fistral"}, {"Watergate"}, {}} {
		found, err := queries.HutsOnBeaches(ctx, beaches)
		if err != nil {
			return fmt.Errorf("finding huts on %v: %w", beaches, err)
		}
		numbers := make([]uint64, 0, len(found))
		for _, hut := range found {
			numbers = append(numbers, hut.Number)
		}
		fmt.Fprintf(output, "huts on %v: %v\n", beaches, numbers)
	}

	// Hut 3 is taken by the first booking, so the second books only hut 5.
	for _, numbers := range [][]uint64{{1, 3, 4}, {3, 5}} {
		booked, err := bookHuts(ctx, queries, numbers)
		if err != nil {
			return err
		}
		fmt.Fprintf(output, "booking huts %v booked %d\n", numbers, booked)
	}

	free, err := queries.FreeHutsInColours(ctx, store.FreeHutsInColoursParams{
		Beach:   "Polzeath",
		Colours: []string{"blue", "yellow"},
	})
	if err != nil {
		return fmt.Errorf("finding free huts: %w", err)
	}
	for _, hut := range free {
		fmt.Fprintf(output, "hut %d at Polzeath is %s and free\n", hut.Number, hut.Colour)
	}
	return nil
}

// bookHuts books the huts that are free among numbers and returns how many it booked, by
// counting the booked huts before and after. The connection sets mutations_sync=2, so the
// mutation has finished when BookHuts returns.
//
// Takes queries (*store.Queries) which runs the queries.
// Takes numbers ([]uint64) which are the huts to book.
//
// Returns uint64 which is how many huts were booked.
// Returns error when a query fails.
func bookHuts(ctx context.Context, queries *store.Queries, numbers []uint64) (uint64, error) {
	before, err := queries.CountBooked(ctx)
	if err != nil {
		return 0, fmt.Errorf("counting booked huts: %w", err)
	}
	if err := queries.BookHuts(ctx, numbers); err != nil {
		return 0, fmt.Errorf("booking huts %v: %w", numbers, err)
	}
	after, err := queries.CountBooked(ctx)
	if err != nil {
		return 0, fmt.Errorf("counting booked huts: %w", err)
	}
	return after.Huts - before.Huts, nil
}

Across engines

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

ClickHouseSQLitePostgreSQLMySQLDuckDB
number columnuint64int64int64int64int64

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

Compare the whole example side by side with SQLite, PostgreSQL, MySQL and DuckDB.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/clickhouse && go run ./010_slices

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 ./010_slices

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

db/migrations/0001_huts.up.sql

CREATE TABLE huts (
    number UInt64,
    beach  String,
    colour String,
    booked Bool DEFAULT false
) ENGINE = MergeTree() ORDER BY number;

db/migrations/0001_huts.down.sql

DROP TABLE huts;

db/queries/huts.sql

-- sgl.query(AddHut, exec)
INSERT INTO huts (number, beach, colour) VALUES ({number:UInt64}, {beach:String}, {colour:String});

-- sgl.query(HutsOnBeaches, many)
SELECT number, beach, colour, booked
FROM huts
WHERE beach IN {beaches:Array(String)}
ORDER BY number;

-- sgl.query(BookHuts, asyncexec)
ALTER TABLE huts UPDATE booked = true WHERE number IN {numbers:Array(UInt64)} AND NOT booked;

-- sgl.query(CountBooked, one)
SELECT count() AS huts FROM huts WHERE booked;

-- sgl.query(FreeHutsInColours, many)
SELECT number, colour
FROM huts
WHERE beach = {beach:String} AND colour IN {colours:Array(String)} AND NOT booked
ORDER BY number;

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/010_slices/internal/store"
)

// run lets out beach huts by passing Go slices where the SQL has an IN list. ClickHouse
// takes a whole array as one parameter, so the query says IN {beaches:Array(String)} and
// the method takes a []string; no kind: slice binding is needed and the SQL is not
// rewritten at call time. The slice is sent as one array value however long it is, and an
// empty slice is an empty array, which matches nothing.
//
// Other engines expand IN (?1) into one placeholder per element, which is why sgl vet
// refuses a kind: slice parameter on a batch query (Q017) or a dynamic: runtime query
// (Q018); an Array parameter is a single value, so neither applies here.
//
// ALTER TABLE ... UPDATE reports no changed rows, so BookHuts is asyncexec and the
// example counts the booked huts before and after each booking.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each search and booking.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	huts := []store.AddHutParams{
		{Number: 1, Beach: "Polzeath", Colour: "blue"},
		{Number: 2, Beach: "Polzeath", Colour: "yellow"},
		{Number: 3, Beach: "Polzeath", Colour: "red"},
		{Number: 4, Beach: "Watergate", Colour: "blue"},
		{Number: 5, Beach: "Watergate", Colour: "red"},
		{Number: 6, Beach: "Fistral", Colour: "yellow"},
	}
	for _, hut := range huts {
		if err := queries.AddHut(ctx, hut); err != nil {
			return fmt.Errorf("adding hut %d: %w", hut.Number, err)
		}
	}

	for _, beaches := range [][]string{{"Polzeath", "Fistral"}, {"Watergate"}, {}} {
		found, err := queries.HutsOnBeaches(ctx, beaches)
		if err != nil {
			return fmt.Errorf("finding huts on %v: %w", beaches, err)
		}
		numbers := make([]uint64, 0, len(found))
		for _, hut := range found {
			numbers = append(numbers, hut.Number)
		}
		fmt.Fprintf(output, "huts on %v: %v\n", beaches, numbers)
	}

	// Hut 3 is taken by the first booking, so the second books only hut 5.
	for _, numbers := range [][]uint64{{1, 3, 4}, {3, 5}} {
		booked, err := bookHuts(ctx, queries, numbers)
		if err != nil {
			return err
		}
		fmt.Fprintf(output, "booking huts %v booked %d\n", numbers, booked)
	}

	free, err := queries.FreeHutsInColours(ctx, store.FreeHutsInColoursParams{
		Beach:   "Polzeath",
		Colours: []string{"blue", "yellow"},
	})
	if err != nil {
		return fmt.Errorf("finding free huts: %w", err)
	}
	for _, hut := range free {
		fmt.Fprintf(output, "hut %d at Polzeath is %s and free\n", hut.Number, hut.Colour)
	}
	return nil
}

// bookHuts books the huts that are free among numbers and returns how many it booked, by
// counting the booked huts before and after. The connection sets mutations_sync=2, so the
// mutation has finished when BookHuts returns.
//
// Takes queries (*store.Queries) which runs the queries.
// Takes numbers ([]uint64) which are the huts to book.
//
// Returns uint64 which is how many huts were booked.
// Returns error when a query fails.
func bookHuts(ctx context.Context, queries *store.Queries, numbers []uint64) (uint64, error) {
	before, err := queries.CountBooked(ctx)
	if err != nil {
		return 0, fmt.Errorf("counting booked huts: %w", err)
	}
	if err := queries.BookHuts(ctx, numbers); err != nil {
		return 0, fmt.Errorf("booking huts %v: %w", numbers, err)
	}
	after, err := queries.CountBooked(ctx)
	if err != nil {
		return 0, fmt.Errorf("counting booked huts: %w", err)
	}
	return after.Huts - before.Huts, nil
}

main.go

// Command 010_slices applies its migrations to a ClickHouse database, then runs the
// example in example.go: beach huts are found and booked by passing Go slices as Array
// parameters.
//
// Run it with `go run .` against a local server, or `go run . -dsn <dsn>` to choose the
// database; give it an empty one. Keep mutations_sync=2 in the DSN so each booking has
// finished before the huts are counted.
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?mutations_sync=2", "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: 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/huts.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"

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

const addHutSQL = `INSERT INTO huts (number, beach, colour) VALUES ({p_number:UInt64}, {p_beach:String}, {p_colour:String});`

type AddHutParams struct {
	Number uint64
	Beach  string
	Colour string
}

func (queries *Queries) AddHut(ctx context.Context, params AddHutParams) error {
	_, err := queries.writer.ExecContext(ctx, addHutSQL, clickhouse.Named("p_number", sglClickHouseShapedValue(params.Number, sglClickHouseShapes[0])), clickhouse.Named("p_beach", sglClickHouseShapedValue(params.Beach, sglClickHouseShapes[1])), clickhouse.Named("p_colour", sglClickHouseShapedValue(params.Colour, sglClickHouseShapes[1])))
	return err
}

const bookHutsSQL = `ALTER TABLE huts UPDATE booked = true WHERE number IN {p_numbers:Array(UInt64)} AND NOT booked;`

// BookHuts issues an asynchronous mutation. The server accepts the
// statement and returns when the mutation has been queued; the
// background execution proceeds independently. Inspect the
// engine's mutation log (system.mutations on ClickHouse) for
// completion status. The returned error reflects only the
// acceptance step; a nil error does NOT guarantee that the
// mutation has finished applying.
func (queries *Queries) BookHuts(ctx context.Context, numbers []uint64) error {
	_, err := queries.writer.ExecContext(ctx, bookHutsSQL, clickhouse.Named("p_numbers", sglClickHouseShapedValue(numbers, sglClickHouseShapes[2])))
	return err
}

const countBookedSQL = `SELECT count() AS huts FROM huts WHERE booked;`

type CountBookedRow struct {
	Huts uint64 `json:"huts"`
}

func (queries *Queries) CountBooked(ctx context.Context) (CountBookedRow, error) {
	var row CountBookedRow
	err := queries.reader.QueryRowContext(ctx, countBookedSQL).Scan(&row.Huts)
	if err != nil {
		return CountBookedRow{}, err
	}
	return row, nil
}

const freeHutsInColoursSQL = `SELECT number, colour
FROM huts
WHERE beach = {p_beach:String} AND colour IN {p_colours:Array(String)} AND NOT booked
ORDER BY number;`

type FreeHutsInColoursParams struct {
	Beach   string
	Colours []string
}

type FreeHutsInColoursRow struct {
	Number uint64 `json:"number"`
	Colour string `json:"colour"`
}

func (queries *Queries) FreeHutsInColours(ctx context.Context, params FreeHutsInColoursParams) ([]FreeHutsInColoursRow, error) {
	rows, err := queries.reader.QueryContext(ctx, freeHutsInColoursSQL, clickhouse.Named("p_beach", sglClickHouseShapedValue(params.Beach, sglClickHouseShapes[1])), clickhouse.Named("p_colours", sglClickHouseShapedValue(params.Colours, sglClickHouseShapes[3])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []FreeHutsInColoursRow
	if rows.Next() {
		var row FreeHutsInColoursRow
		scanArgs := []any{&row.Number, &row.Colour}
		for more := true; more; more = rows.Next() {
			row = FreeHutsInColoursRow{}
			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 hutsOnBeachesSQL = `SELECT number, beach, colour, booked
FROM huts
WHERE beach IN {p_beaches:Array(String)}
ORDER BY number;`

type HutsOnBeachesRow struct {
	Number uint64 `json:"number"`
	Beach  string `json:"beach"`
	Colour string `json:"colour"`
	Booked bool   `json:"booked"`
}

func (queries *Queries) HutsOnBeaches(ctx context.Context, beaches []string) ([]HutsOnBeachesRow, error) {
	rows, err := queries.reader.QueryContext(ctx, hutsOnBeachesSQL, clickhouse.Named("p_beaches", sglClickHouseShapedValue(beaches, sglClickHouseShapes[3])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []HutsOnBeachesRow
	if rows.Next() {
		var row HutsOnBeachesRow
		scanArgs := []any{&row.Number, &row.Beach, &row.Colour, &row.Booked}
		for more := true; more; more = rows.Next() {
			row = HutsOnBeachesRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Huts struct {
	Number uint64 `json:"number"`
	Beach  string `json:"beach"`
	Colour string `json:"colour"`
	Booked bool   `json:"booked"`
}

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

huts on [Polzeath Fistral]: [1 2 3 6]
huts on [Watergate]: [4 5]
huts on []: []
booking huts [1 3 4] booked 3
booking huts [3 5] booked 1
hut 2 at Polzeath is yellow and free

Also on other engines

SQLiteBeach huts found and booked by passing Go slices to IN lists, including an empty slice and a slice after a scalar.PostgreSQLBeach huts found and booked by passing Go slices to IN lists, including an empty slice and a slice after a scalar.CockroachDBBeach huts found and booked by passing Go slices to IN lists, including an empty slice and a slice after a scalar.TimescaleDBBeach huts found and booked by passing Go slices to IN lists, including an empty slice and a slice after a scalar.MySQLBeach huts found and booked by passing Go slices to IN lists, including an empty slice and a slice after a scalar.MariaDBBeach huts found and booked by passing Go slices to IN lists, including an empty slice and a slice after a scalar.DuckDBBeach huts found and booked by passing Go slices to IN lists, including an empty slice and a slice after a scalar.