Your first query

On this page

On ClickHouse:

  • {name:Type} placeholders, sent through database/sql as clickhouse.Named("p_", ...) with the value formatted as text
  • No auto-increment and no RETURNING, so CreateTask is exec with a caller-chosen id and GetTask reads the row back with one
  • CompleteTask uses an ALTER TABLE ... UPDATE asynchronous mutation, declared asyncexec, and mutations_sync=2 makes it finish before the call returns
  • No execrows: clickhouse-go reports 0 rows affected for every statement, so the output names the task instead of counting rows
  • UInt64 maps to uint64, UInt8 to uint8 and DateTime to time.Time
  • Every table needs an engine; MergeTree with ORDER BY id

Seagull reads two things: the migrations that build your schema, and the queries you write against it. From them it generates a Go package with a method for every query, typed from the schema. This first example is a tasks list: one table, and three queries to add a task, complete it and list them all.

The schema

Migrations are plain SQL files, applied in order. Seagull replays them to learn every table and column, including which columns can be NULL.

db/migrations/0001_init.up.sql

CREATE TABLE tasks (
    id         UInt64,
    title      String,
    completed  UInt8    DEFAULT 0,
    created_at DateTime DEFAULT now()
) ENGINE = MergeTree() ORDER BY id;

The queries

Each query starts with an sgl.query header giving the method's name and its command: what the method returns. ListTasks returns every row, so it is many.

db/queries/tasks.sql

-- sgl.query(ListTasks, many)
SELECT id, title, completed, created_at FROM tasks ORDER BY id;

ClickHouse has no RETURNING and no auto-increment, so the program chooses the id and adding a task is a plain exec. Parameters are typed in the SQL itself, as {name:Type}.

db/queries/tasks.sql

-- sgl.query(CreateTask, exec)
INSERT INTO tasks (id, title) VALUES ({id:UInt64}, {title:String});

ClickHouse changes rows with a mutation that runs in the background, so completing a task is asyncexec. The example's connection sets mutations_sync so the list that follows sees the change.

db/queries/tasks.sql

-- sgl.query(CompleteTask, asyncexec)
ALTER TABLE tasks UPDATE completed = 1 WHERE id = {id:UInt64};

What sgl generates

sgl generate writes the package named in sgl.yaml. Every column of the query becomes a field of a row struct, typed from the schema: a column that can be NULL would be a pointer, and these cannot be, so none are.

internal/store

type ListTasksRow struct {
	ID        uint64    `json:"id"`
	Title     string    `json:"title"`
	Completed uint8     `json:"completed"`
	CreatedAt time.Time `json:"created_at"`
}

func (queries *Queries) ListTasks(ctx context.Context) ([]ListTasksRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listTasksSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListTasksRow
	if rows.Next() {
		var row ListTasksRow
		scanArgs := []any{&row.ID, &row.Title, &row.Completed, &row.CreatedAt}
		for more := true; more; more = rows.Next() {
			row = ListTasksRow{}
			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
}

Using it

The program opens the database, runs the migrations and calls the generated methods. There is no SQL in it and nothing to scan by hand: the methods take and return Go values.

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/001_first_query/internal/store"
)

// run adds two tasks, completes the first and lists them all, through the methods sgl
// generated from db/queries/tasks.sql.
//
// ClickHouse has no auto-increment and no RETURNING, so the caller chooses each task's id
// and reads a task back with GetTask. CompleteTask uses ALTER TABLE ... UPDATE, which
// queues a mutation that the server applies in the background, so the query is declared
// asyncexec and returns only an error (concept 36 explains why). The connection sets
// mutations_sync=2, which makes the server finish the mutation before it replies, so the
// list below already shows the task completed.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the task list.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	tasks := []store.CreateTaskParams{
		{ID: 1, Title: "Feed the seagulls"},
		{ID: 2, Title: "Count the chips"},
	}
	for _, task := range tasks {
		if err := queries.CreateTask(ctx, task); err != nil {
			return fmt.Errorf("creating task %d: %w", task.ID, err)
		}
	}

	feed, err := queries.GetTask(ctx, 1)
	if err != nil {
		return fmt.Errorf("getting task 1: %w", err)
	}
	fmt.Fprintf(output, "created task %d, %s\n", feed.ID, feed.Title)

	if err := queries.CompleteTask(ctx, feed.ID); err != nil {
		return fmt.Errorf("completing task %d: %w", feed.ID, err)
	}
	fmt.Fprintf(output, "completed task %d\n", feed.ID)

	listed, err := queries.ListTasks(ctx)
	if err != nil {
		return fmt.Errorf("listing tasks: %w", err)
	}
	for _, task := range listed {
		fmt.Fprintf(output, "%d\t%-18s completed=%d\n", task.ID, task.Title, task.Completed)
	}
	return nil
}

What it prints

created task 1, Feed the seagulls
completed task 1
1	Feed the seagulls  completed=1
2	Count the chips    completed=0

Try it

Change a column's type in the migration, or ask for a column that does not exist, and run sgl generate again: the Go changes with it, or sgl vet says what is wrong, with a code you can look up in the diagnostics.

With the native ClickHouse driver

The tasks example over a native clickhouse-go connection; the same queries generate methods on driver.Conn, migrated first through the driver's database/sql adapter.

What changes:

  • sgl.yaml sets go.driver: clickhouse, so store.New takes a native connection (the DBTX subset of driver.Conn) instead of *sql.DB
  • Parameters bind as clickhouse.Named("p_", ...), formatted as text by the generated clickhouse_values.go
  • The migrator runs on database/sql, so main.go migrates through clickhouse.OpenDB and then opens clickhouse.Open with the same options
  • No WithTx, RunInTx or NewWithReplica: ClickHouse has no transactions and the native emitter has no replica routing
  • execrows and execresult are refused, as the native driver reports no affected rows; CompleteTask is asyncexec

db/queries/tasks.sql

-- sgl.query(CreateTask, exec)
INSERT INTO tasks (id, title) VALUES ({id:UInt64}, {title:String});

-- sgl.query(GetTask, one)
SELECT id, title, completed, created_at FROM tasks WHERE id = {id:UInt64};

-- sgl.query(ListTasks, many)
SELECT id, title, completed, created_at FROM tasks ORDER BY id;

-- sgl.query(CompleteTask, asyncexec)
ALTER TABLE tasks UPDATE completed = 1 WHERE id = {id:UInt64};

internal/store

type CreateTaskParams struct {
	ID    uint64
	Title string
}

func (queries *Queries) CreateTask(ctx context.Context, params CreateTaskParams) error {
	return queries.db.Exec(ctx, createTaskSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_title", sglClickHouseShapedValue(params.Title, sglClickHouseShapes[1])))
}

example.go

package main

import (
	"context"
	"fmt"
	"io"

	"seagull.sh/seagull/examples/clickhouse/001_first_query_native/internal/store"
)

// run adds two tasks, completes the first and lists them all, through the methods sgl
// generated for the native clickhouse-go driver from db/queries/tasks.sql.
//
// The queries are the same as the database/sql variant's; only the generated code
// differs. store.DBTX is the subset of driver.Conn the methods call: Exec returns only an
// error, Query returns driver.Rows, and every {name:Type} placeholder is bound as
// clickhouse.Named("p_<name>", ...). ClickHouse has no auto-increment and no RETURNING,
// so the caller chooses each id, and CompleteTask is an ALTER TABLE ... UPDATE mutation
// declared asyncexec (concept 36). The connection sets mutations_sync=2, so the mutation
// has finished before the list is read.
//
// Takes connection (store.DBTX) which is a native connection to a migrated database.
// Takes output (io.Writer) which receives the task list.
//
// Returns error when a query fails.
func run(ctx context.Context, connection store.DBTX, output io.Writer) error {
	queries := store.New(connection)

	tasks := []store.CreateTaskParams{
		{ID: 1, Title: "Feed the seagulls"},
		{ID: 2, Title: "Count the chips"},
	}
	for _, task := range tasks {
		if err := queries.CreateTask(ctx, task); err != nil {
			return fmt.Errorf("creating task %d: %w", task.ID, err)
		}
	}

	feed, err := queries.GetTask(ctx, 1)
	if err != nil {
		return fmt.Errorf("getting task 1: %w", err)
	}
	fmt.Fprintf(output, "created task %d, %s\n", feed.ID, feed.Title)

	if err := queries.CompleteTask(ctx, feed.ID); err != nil {
		return fmt.Errorf("completing task %d: %w", feed.ID, err)
	}
	fmt.Fprintf(output, "completed task %d\n", feed.ID)

	listed, err := queries.ListTasks(ctx)
	if err != nil {
		return fmt.Errorf("listing tasks: %w", err)
	}
	for _, task := range listed {
		fmt.Fprintf(output, "%d\t%-18s completed=%d\n", task.ID, task.Title, task.Completed)
	}
	return nil
}

Across engines

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

ClickHouseSQLitePostgreSQLCockroachDBMySQLMariaDBDuckDB
id column·int64int64int64·int64int64
title column·stringstringstring·stringstring
completed column·int64boolbool·int32bool
created_at column·stringtime.Timetime.Time·time.Timetime.Time
id parameteruint64······

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

TimescaleDB runs the PostgreSQL version unchanged.

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

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/clickhouse && go run ./001_first_query

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 ./001_first_query

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

db/migrations/0001_init.up.sql

CREATE TABLE tasks (
    id         UInt64,
    title      String,
    completed  UInt8    DEFAULT 0,
    created_at DateTime DEFAULT now()
) ENGINE = MergeTree() ORDER BY id;

db/migrations/0001_init.down.sql

DROP TABLE tasks;

db/queries/tasks.sql

-- sgl.query(CreateTask, exec)
INSERT INTO tasks (id, title) VALUES ({id:UInt64}, {title:String});

-- sgl.query(GetTask, one)
SELECT id, title, completed, created_at FROM tasks WHERE id = {id:UInt64};

-- sgl.query(ListTasks, many)
SELECT id, title, completed, created_at FROM tasks ORDER BY id;

-- sgl.query(CompleteTask, asyncexec)
ALTER TABLE tasks UPDATE completed = 1 WHERE id = {id:UInt64};

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/001_first_query/internal/store"
)

// run adds two tasks, completes the first and lists them all, through the methods sgl
// generated from db/queries/tasks.sql.
//
// ClickHouse has no auto-increment and no RETURNING, so the caller chooses each task's id
// and reads a task back with GetTask. CompleteTask uses ALTER TABLE ... UPDATE, which
// queues a mutation that the server applies in the background, so the query is declared
// asyncexec and returns only an error (concept 36 explains why). The connection sets
// mutations_sync=2, which makes the server finish the mutation before it replies, so the
// list below already shows the task completed.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the task list.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	tasks := []store.CreateTaskParams{
		{ID: 1, Title: "Feed the seagulls"},
		{ID: 2, Title: "Count the chips"},
	}
	for _, task := range tasks {
		if err := queries.CreateTask(ctx, task); err != nil {
			return fmt.Errorf("creating task %d: %w", task.ID, err)
		}
	}

	feed, err := queries.GetTask(ctx, 1)
	if err != nil {
		return fmt.Errorf("getting task 1: %w", err)
	}
	fmt.Fprintf(output, "created task %d, %s\n", feed.ID, feed.Title)

	if err := queries.CompleteTask(ctx, feed.ID); err != nil {
		return fmt.Errorf("completing task %d: %w", feed.ID, err)
	}
	fmt.Fprintf(output, "completed task %d\n", feed.ID)

	listed, err := queries.ListTasks(ctx)
	if err != nil {
		return fmt.Errorf("listing tasks: %w", err)
	}
	for _, task := range listed {
		fmt.Fprintf(output, "%d\t%-18s completed=%d\n", task.ID, task.Title, task.Completed)
	}
	return nil
}

main.go

// Command 001_first_query applies its migrations to a ClickHouse database, then runs the
// example in example.go: two tasks are added, one is completed, and both are listed.
//
// 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 the completed task
// shows in the list.
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{}}}

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

// Code generated by sgl. DO NOT EDIT.

package store

import "time"

type Tasks struct {
	ID        uint64    `json:"id"`
	Title     string    `json:"title"`
	Completed uint8     `json:"completed"`
	CreatedAt time.Time `json:"created_at"`
}

internal/store/querier.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type dbtxWrapper interface {
	WrapDBTX(inner any) any
}

type Queries struct {
	reader  DBTX
	writer  DBTX
	wrapper dbtxWrapper
}

func New(db DBTX) *Queries {
	wrapper, _ := db.(dbtxWrapper)
	return &Queries{reader: db, writer: db, wrapper: wrapper}
}

func NewWithReplica(writer DBTX, reader DBTX) *Queries {
	wrapper, _ := writer.(dbtxWrapper)
	return &Queries{reader: reader, writer: writer, wrapper: wrapper}
}

func (queries *Queries) WithTx(transaction *sql.Tx) *Queries {
	if queries == nil {
		return &Queries{reader: transaction, writer: transaction, wrapper: nil}
	}
	db := queries.wrapDBTX(transaction)
	return &Queries{reader: db, writer: db, wrapper: queries.wrapper}
}

func (queries *Queries) wrapDBTX(db DBTX) DBTX {
	if queries.wrapper == nil {
		return db
	}
	wrapped, ok := queries.wrapper.WrapDBTX(db).(DBTX)
	if !ok {
		return db
	}
	return wrapped
}

func (queries *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error {
	transaction, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer transaction.Rollback()
	if err = fn(queries.WithTx(transaction)); err != nil {
		return err
	}
	return transaction.Commit()
}

internal/store/tasks.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"time"

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

const completeTaskSQL = `ALTER TABLE tasks UPDATE completed = 1 WHERE id = {p_id:UInt64};`

// CompleteTask 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) CompleteTask(ctx context.Context, id uint64) error {
	_, err := queries.writer.ExecContext(ctx, completeTaskSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(id, sglClickHouseShapes[0])))
	return err
}

const createTaskSQL = `INSERT INTO tasks (id, title) VALUES ({p_id:UInt64}, {p_title:String});`

type CreateTaskParams struct {
	ID    uint64
	Title string
}

func (queries *Queries) CreateTask(ctx context.Context, params CreateTaskParams) error {
	_, err := queries.writer.ExecContext(ctx, createTaskSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_title", sglClickHouseShapedValue(params.Title, sglClickHouseShapes[1])))
	return err
}

const getTaskSQL = `SELECT id, title, completed, created_at FROM tasks WHERE id = {p_id:UInt64};`

type GetTaskRow struct {
	ID        uint64    `json:"id"`
	Title     string    `json:"title"`
	Completed uint8     `json:"completed"`
	CreatedAt time.Time `json:"created_at"`
}

func (queries *Queries) GetTask(ctx context.Context, id uint64) (GetTaskRow, error) {
	var row GetTaskRow
	err := queries.reader.QueryRowContext(ctx, getTaskSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(id, sglClickHouseShapes[0]))).Scan(&row.ID, &row.Title, &row.Completed, &row.CreatedAt)
	if err != nil {
		return GetTaskRow{}, err
	}
	return row, nil
}

const listTasksSQL = `SELECT id, title, completed, created_at FROM tasks ORDER BY id;`

type ListTasksRow struct {
	ID        uint64    `json:"id"`
	Title     string    `json:"title"`
	Completed uint8     `json:"completed"`
	CreatedAt time.Time `json:"created_at"`
}

func (queries *Queries) ListTasks(ctx context.Context) ([]ListTasksRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listTasksSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListTasksRow
	if rows.Next() {
		var row ListTasksRow
		scanArgs := []any{&row.ID, &row.Title, &row.Completed, &row.CreatedAt}
		for more := true; more; more = rows.Next() {
			row = ListTasksRow{}
			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
}

output.txt

created task 1, Feed the seagulls
completed task 1
1	Feed the seagulls  completed=1
2	Count the chips    completed=0

Also on other engines

SQLiteA tasks table and three queries; every INTEGER column becomes int64 and RETURNING hands back the new row.PostgreSQLA tasks table and three queries through pgx's database/sql driver; BIGSERIAL becomes int64, TIMESTAMPTZ becomes time.Time and RETURNING hands back the new row.CockroachDBThe SQLite tasks program on CockroachDB; unique_rowid() fills the INT8 key, STRING and BOOL become string and bool, and the CockroachDB migration backend applies the schema.TimescaleDBA tasks table and three queries through pgx's database/sql driver; BIGSERIAL becomes int64, TIMESTAMPTZ becomes time.Time and RETURNING hands back the new row.MySQLA tasks table and three queries; MySQL has no RETURNING, so the insert is execresult and LastInsertId hands back the AUTO_INCREMENT id.MariaDBA tasks table and three queries; MariaDB has INSERT ... RETURNING, so the insert hands back the new row as it would on SQLite or PostgreSQL.DuckDBA tasks table and three queries; a sequence supplies the ids, RETURNING hands back the new row, and BOOLEAN and TIMESTAMP arrive as bool and time.Time.