Asynchronous mutations
On this page
On ClickHouse:
- ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE queue a mutation; asyncexec gives an error-only method whose doc comment says a nil error means queued
- Declaring such a query exec works but sgl vet hints Q040; Q041, asyncexec on an engine without asynchronous mutations, cannot arise on ClickHouse
- mutations_sync=2 on the connection, or SETTINGS mutations_sync = 2 in the statement, makes the server wait until every replica has applied the mutation
- A lightweight DELETE FROM returns once the rows are marked deleted, so it is a plain exec
- No mutation reports changed rows
ClickHouse changes stored rows with mutations. ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE do not edit rows in place: the server rewrites the affected parts in the background, and by default it replies as soon as it has queued the work, not when the rows have changed. Code that treats such a call like an ordinary UPDATE can read the old values straight afterwards.
Seagull has a command for exactly this, asyncexec. It generates a method that returns only an error, with a doc comment saying that a nil error means the mutation was queued, not that it has finished.
The example keeps a harbour's fishing licences: it issues four, renews one, purges the expired ones and cancels another.
The table
db/migrations/0001_licences.up.sql
CREATE TABLE licences (
id UInt64,
boat String,
expires Date,
renewals UInt8 DEFAULT 0
) ENGINE = MergeTree() ORDER BY id;
A mutation that updates
RenewLicence pushes a licence's expiry on by a year and counts the renewal. It is an ALTER TABLE ... UPDATE, so it is declared asyncexec:
db/queries/licences.sql
-- sgl.query(RenewLicence, asyncexec)
ALTER TABLE licences
UPDATE expires = addYears(expires, 1), renewals = renewals + 1
WHERE id = {id:UInt64};
The generated method says what the call does and does not promise:
internal/store
// RenewLicence 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) RenewLicence(ctx context.Context, id uint64) error {
_, err := queries.writer.ExecContext(ctx, renewLicenceSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(id, sglClickHouseShapes[0])))
return err
}
A mutation reports no changed rows, so there is no execrows form; to see the effect, read the table again.
Declaring the query exec would generate a working method too, but sgl vet points it out with the hint Q040, because the method would look like a synchronous write. The opposite mistake is caught as well: asyncexec on an engine whose mutations are synchronous, such as SQLite, is the error Q041.
Waiting for a mutation: mutations_sync
When the next step depends on the change, make the server wait. The mutations_sync setting does that: 1 waits for the mutation on the server that received it, and 2 waits for every replica. It can be set in two places.
On the connection, it applies to every mutation. The program's default DSN sets mutations_sync=2, so RenewLicence has finished before the listing reads the licences again.
In the statement, it applies whatever the connection says. PurgeExpired carries SETTINGS mutations_sync = 2 in its own SQL:
db/queries/licences.sql
-- sgl.query(PurgeExpired, asyncexec)
ALTER TABLE licences
DELETE WHERE expires < {today:Date}
SETTINGS mutations_sync = 2;
It is still declared asyncexec: the setting changes when the server replies, and the declaration keeps the method's contract honest if someone later removes the setting.
A lightweight DELETE
DELETE FROM is ClickHouse's lightweight delete. It marks the rows as deleted and, by default, returns once that is done, so later queries no longer see them. It is not a queued mutation, so it is a plain exec:
db/queries/licences.sql
-- sgl.query(CancelLicence, exec)
DELETE FROM licences WHERE id = {id:UInt64};
Using it
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"time"
"seagull.sh/seagull/examples/clickhouse/036_clickhouse_async_mutations/internal/store"
)
// run issues four fishing licences, then renews, purges and cancels some. ClickHouse
// changes stored rows with mutations: ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE
// rewrite the affected parts in the background. The server replies once it has queued the
// mutation, not when the rows have changed, and it reports no changed rows. So
// RenewLicence and PurgeExpired are declared asyncexec: each method returns only an
// error, and its doc comment warns that a nil error means queued, not done. Declaring one
// exec still works, but sgl vet points it out as hint Q040; asyncexec on an engine whose
// mutations are synchronous is error Q041.
//
// The mutations_sync setting makes the server wait instead. This program's connection
// sets mutations_sync=2, which waits for every replica, so RenewLicence has finished
// before the listing reads it. PurgeExpired carries SETTINGS mutations_sync = 2 in its
// own SQL, so it waits whatever the connection says.
//
// CancelLicence is a lightweight DELETE FROM, which marks the rows deleted and, by
// default, returns once that is done, so it is a plain exec.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the licences before and after.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
queries := store.New(database)
licences := []store.IssueLicenceParams{
{ID: 1, Boat: "Kittiwake", Expires: date(2026, time.March, 31)},
{ID: 2, Boat: "Salty Chip", Expires: date(2026, time.April, 30)},
{ID: 3, Boat: "Gull Wing", Expires: date(2026, time.September, 30)},
{ID: 4, Boat: "Driftwood", Expires: date(2026, time.December, 31)},
}
for _, licence := range licences {
if err := queries.IssueLicence(ctx, licence); err != nil {
return fmt.Errorf("issuing licence %d: %w", licence.ID, err)
}
}
if err := printLicences(ctx, queries, "issued", output); err != nil {
return err
}
if err := queries.RenewLicence(ctx, 1); err != nil {
return fmt.Errorf("renewing licence 1: %w", err)
}
if err := queries.PurgeExpired(ctx, date(2026, time.June, 1)); err != nil {
return fmt.Errorf("purging expired licences: %w", err)
}
if err := queries.CancelLicence(ctx, 4); err != nil {
return fmt.Errorf("cancelling licence 4: %w", err)
}
return printLicences(ctx, queries, "after renewing #1, purging those expired by 1 June and cancelling #4", output)
}
// printLicences lists every licence under a heading.
//
// Takes queries (*store.Queries) which runs the query.
// Takes heading (string) which introduces the list.
// Takes output (io.Writer) which receives the list.
//
// Returns error when the query fails.
func printLicences(ctx context.Context, queries *store.Queries, heading string, output io.Writer) error {
licences, err := queries.ListLicences(ctx)
if err != nil {
return fmt.Errorf("listing licences: %w", err)
}
fmt.Fprintf(output, "%s:\n", heading)
for _, licence := range licences {
fmt.Fprintf(output, " #%d %-10s expires %s, renewals: %d\n", licence.ID, licence.Boat, licence.Expires.Format(time.DateOnly), licence.Renewals)
}
return nil
}
// date returns a date at midnight UTC. A Date parameter binds the calendar date a time
// has in its own location.
//
// Takes year (int) which is the year.
// Takes month (time.Month) which is the month.
// Takes day (int) which is the day of the month.
//
// Returns time.Time which is the date.
func date(year int, month time.Month, day int) time.Time {
return time.Date(year, month, day, 0, 0, 0, 0, time.UTC)
}
What it prints
After the changes, Kittiwake's licence runs to 2027 with one renewal, Salty Chip's expired licence has been purged, and Driftwood's has been cancelled. Every change is visible in the very next read, because each call waited.
issued:
#1 Kittiwake expires 2026-03-31, renewals: 0
#2 Salty Chip expires 2026-04-30, renewals: 0
#3 Gull Wing expires 2026-09-30, renewals: 0
#4 Driftwood expires 2026-12-31, renewals: 0
after renewing #1, purging those expired by 1 June and cancelling #4:
#1 Kittiwake expires 2027-03-31, renewals: 1
#3 Gull Wing expires 2026-09-30, renewals: 0
Try it
Change RenewLicence from asyncexec to exec and run sgl vet to see the Q040 hint. Then put it back, and run the program with a DSN that leaves out mutations_sync=2, such as -dsn clickhouse://localhost:9000/default: the renewal is queued rather than awaited, so the listing may show Kittiwake's old expiry date if it runs before the mutation finishes.
Run it locally
From the directory you cloned the Seagull repository into:
cd seagull/examples/clickhouse && go run ./036_clickhouse_async_mutations
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 ./036_clickhouse_async_mutations
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: harbour
dialect: clickhouse
migrations: db/migrations
queries: db/queries
go:
package: store
out: internal/store
db/migrations/0001_licences.up.sql
CREATE TABLE licences (
id UInt64,
boat String,
expires Date,
renewals UInt8 DEFAULT 0
) ENGINE = MergeTree() ORDER BY id;
db/migrations/0001_licences.down.sql
DROP TABLE licences;
db/queries/licences.sql
-- sgl.query(IssueLicence, exec)
INSERT INTO licences (id, boat, expires) VALUES ({id:UInt64}, {boat:String}, {expires:Date});
-- sgl.query(RenewLicence, asyncexec)
ALTER TABLE licences
UPDATE expires = addYears(expires, 1), renewals = renewals + 1
WHERE id = {id:UInt64};
-- sgl.query(PurgeExpired, asyncexec)
ALTER TABLE licences
DELETE WHERE expires < {today:Date}
SETTINGS mutations_sync = 2;
-- sgl.query(CancelLicence, exec)
DELETE FROM licences WHERE id = {id:UInt64};
-- sgl.query(ListLicences, many)
SELECT id, boat, expires, renewals FROM licences ORDER BY id;
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"time"
"seagull.sh/seagull/examples/clickhouse/036_clickhouse_async_mutations/internal/store"
)
// run issues four fishing licences, then renews, purges and cancels some. ClickHouse
// changes stored rows with mutations: ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE
// rewrite the affected parts in the background. The server replies once it has queued the
// mutation, not when the rows have changed, and it reports no changed rows. So
// RenewLicence and PurgeExpired are declared asyncexec: each method returns only an
// error, and its doc comment warns that a nil error means queued, not done. Declaring one
// exec still works, but sgl vet points it out as hint Q040; asyncexec on an engine whose
// mutations are synchronous is error Q041.
//
// The mutations_sync setting makes the server wait instead. This program's connection
// sets mutations_sync=2, which waits for every replica, so RenewLicence has finished
// before the listing reads it. PurgeExpired carries SETTINGS mutations_sync = 2 in its
// own SQL, so it waits whatever the connection says.
//
// CancelLicence is a lightweight DELETE FROM, which marks the rows deleted and, by
// default, returns once that is done, so it is a plain exec.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the licences before and after.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
queries := store.New(database)
licences := []store.IssueLicenceParams{
{ID: 1, Boat: "Kittiwake", Expires: date(2026, time.March, 31)},
{ID: 2, Boat: "Salty Chip", Expires: date(2026, time.April, 30)},
{ID: 3, Boat: "Gull Wing", Expires: date(2026, time.September, 30)},
{ID: 4, Boat: "Driftwood", Expires: date(2026, time.December, 31)},
}
for _, licence := range licences {
if err := queries.IssueLicence(ctx, licence); err != nil {
return fmt.Errorf("issuing licence %d: %w", licence.ID, err)
}
}
if err := printLicences(ctx, queries, "issued", output); err != nil {
return err
}
if err := queries.RenewLicence(ctx, 1); err != nil {
return fmt.Errorf("renewing licence 1: %w", err)
}
if err := queries.PurgeExpired(ctx, date(2026, time.June, 1)); err != nil {
return fmt.Errorf("purging expired licences: %w", err)
}
if err := queries.CancelLicence(ctx, 4); err != nil {
return fmt.Errorf("cancelling licence 4: %w", err)
}
return printLicences(ctx, queries, "after renewing #1, purging those expired by 1 June and cancelling #4", output)
}
// printLicences lists every licence under a heading.
//
// Takes queries (*store.Queries) which runs the query.
// Takes heading (string) which introduces the list.
// Takes output (io.Writer) which receives the list.
//
// Returns error when the query fails.
func printLicences(ctx context.Context, queries *store.Queries, heading string, output io.Writer) error {
licences, err := queries.ListLicences(ctx)
if err != nil {
return fmt.Errorf("listing licences: %w", err)
}
fmt.Fprintf(output, "%s:\n", heading)
for _, licence := range licences {
fmt.Fprintf(output, " #%d %-10s expires %s, renewals: %d\n", licence.ID, licence.Boat, licence.Expires.Format(time.DateOnly), licence.Renewals)
}
return nil
}
// date returns a date at midnight UTC. A Date parameter binds the calendar date a time
// has in its own location.
//
// Takes year (int) which is the year.
// Takes month (time.Month) which is the month.
// Takes day (int) which is the day of the month.
//
// Returns time.Time which is the date.
func date(year int, month time.Month, day int) time.Time {
return time.Date(year, month, day, 0, 0, 0, 0, time.UTC)
}
main.go
// Command 036_clickhouse_async_mutations applies its migrations to a ClickHouse database,
// then runs the example in example.go: fishing licences are renewed and purged with ALTER
// TABLE mutations declared asyncexec, and one is cancelled with a lightweight DELETE.
//
// Run it with `go run .` against a local server, or `go run . -dsn <dsn>` to choose the
// database; give it an empty one. The default DSN sets mutations_sync=2 so each mutation
// has finished before the licences are listed; without it the second listing may still
// show the old rows.
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: 100, 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/licences.sql.go
// Code generated by sgl. DO NOT EDIT.
package store
import (
"context"
"time"
"github.com/ClickHouse/clickhouse-go/v2"
)
const cancelLicenceSQL = `DELETE FROM licences WHERE id = {p_id:UInt64};`
func (queries *Queries) CancelLicence(ctx context.Context, id uint64) error {
_, err := queries.writer.ExecContext(ctx, cancelLicenceSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(id, sglClickHouseShapes[0])))
return err
}
const issueLicenceSQL = `INSERT INTO licences (id, boat, expires) VALUES ({p_id:UInt64}, {p_boat:String}, {p_expires:Date});`
type IssueLicenceParams struct {
ID uint64
Boat string
Expires time.Time
}
func (queries *Queries) IssueLicence(ctx context.Context, params IssueLicenceParams) error {
_, err := queries.writer.ExecContext(ctx, issueLicenceSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_boat", sglClickHouseShapedValue(params.Boat, sglClickHouseShapes[1])), clickhouse.Named("p_expires", sglClickHouseShapedValue(params.Expires, sglClickHouseShapes[2])))
return err
}
const listLicencesSQL = `SELECT id, boat, expires, renewals FROM licences ORDER BY id;`
type ListLicencesRow struct {
ID uint64 `json:"id"`
Boat string `json:"boat"`
Expires time.Time `json:"expires"`
Renewals uint8 `json:"renewals"`
}
func (queries *Queries) ListLicences(ctx context.Context) ([]ListLicencesRow, error) {
rows, err := queries.reader.QueryContext(ctx, listLicencesSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []ListLicencesRow
if rows.Next() {
var row ListLicencesRow
scanArgs := []any{&row.ID, &row.Boat, &row.Expires, &row.Renewals}
for more := true; more; more = rows.Next() {
row = ListLicencesRow{}
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 purgeExpiredSQL = `ALTER TABLE licences
DELETE WHERE expires < {p_today:Date}
SETTINGS mutations_sync = 2;`
// PurgeExpired 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) PurgeExpired(ctx context.Context, today time.Time) error {
_, err := queries.writer.ExecContext(ctx, purgeExpiredSQL, clickhouse.Named("p_today", sglClickHouseShapedValue(today, sglClickHouseShapes[2])))
return err
}
const renewLicenceSQL = `ALTER TABLE licences
UPDATE expires = addYears(expires, 1), renewals = renewals + 1
WHERE id = {p_id:UInt64};`
// RenewLicence 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) RenewLicence(ctx context.Context, id uint64) error {
_, err := queries.writer.ExecContext(ctx, renewLicenceSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(id, sglClickHouseShapes[0])))
return err
}
internal/store/models.go
// Code generated by sgl. DO NOT EDIT.
package store
import "time"
type Licences struct {
ID uint64 `json:"id"`
Boat string `json:"boat"`
Expires time.Time `json:"expires"`
Renewals uint8 `json:"renewals"`
}
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
issued:
#1 Kittiwake expires 2026-03-31, renewals: 0
#2 Salty Chip expires 2026-04-30, renewals: 0
#3 Gull Wing expires 2026-09-30, renewals: 0
#4 Driftwood expires 2026-12-31, renewals: 0
after renewing #1, purging those expired by 1 June and cancelling #4:
#1 Kittiwake expires 2027-03-31, renewals: 1
#3 Gull Wing expires 2026-09-30, renewals: 0