Types through CTEs, window functions and views

On this page

On ClickHouse:

  • WITH RECURSIVE needs ClickHouse 24.4 or later, with the analyser that is on by default
  • join_use_nulls = 1 makes the LEFT JOINs give NULL, as in SQL, instead of default values
  • rank() OVER is a UInt64, and sum() OVER of a UInt64 is a UInt64 that sums no rows to 0, not NULL, so BeachRank and BeachThefts are plain uint64
  • Seagull types the recursive CTE's generation from generation + 1, a UInt16, so it is uint16; the server keeps the first SELECT's UInt8, and database/sql converts each value
  • count and uniqExact map to uint64 and max(Date) to *time.Time

Reporting queries are rarely a plain SELECT from one table. They build intermediate results with WITH, walk hierarchies with a recursive CTE, rank and total rows with window functions, and read from views. Seagull follows each column's type and nullability through all of these, so a report gets the same typed row struct as a simple lookup, and you do not have to restate what the SQL already says.

This example keeps a colony of gulls, some of them the chicks of others, and a record of the snacks each has stolen. It reads the colony back three ways.

The schema

The migration creates two tables and a view. Seagull reads the view's own SELECT when it replays the migration, so it knows the type of every column the view returns, and a query can read from the view as if it were a table.

db/migrations/0001_gulls.up.sql

CREATE TABLE gulls (
    id        UInt64,
    name      String,
    parent_id Nullable(UInt64),
    beach     String
) ENGINE = MergeTree() ORDER BY id;

CREATE TABLE thefts (
    id        UInt64,
    gull_id   UInt64,
    snack     String,
    stolen_on Date
) ENGINE = MergeTree() ORDER BY id;

CREATE VIEW beach_records AS
SELECT
    gulls.beach,
    uniqExact(gulls.id) AS gulls,
    count(thefts.id) AS thefts,
    max(thefts.stolen_on) AS last_theft
FROM gulls
LEFT JOIN thefts ON thefts.gull_id = gulls.id
GROUP BY gulls.beach
SETTINGS join_use_nulls = 1;

A recursive CTE

FamilyTree starts from one gull and finds its descendants. The first part of the CTE picks the starting gull, at generation 0; the second joins each gull's chicks to the rows found so far, one generation further down, until there are none left.

db/queries/gulls.sql

-- sgl.query(FamilyTree, many)
WITH RECURSIVE family AS (
    SELECT id, name, parent_id, 0 AS generation
    FROM gulls
    WHERE id = {root:UInt64}
    UNION ALL
    SELECT gulls.id, gulls.name, gulls.parent_id, family.generation + 1
    FROM gulls
    JOIN family ON gulls.parent_id = family.id
)
SELECT family.name, family.generation, parents.name AS parent
FROM family
LEFT JOIN gulls AS parents ON parents.id = family.parent_id
ORDER BY family.generation, family.id
SETTINGS join_use_nulls = 1;

internal/store

type FamilyTreeRow struct {
	Name       string  `json:"name"`
	Generation uint8   `json:"generation"`
	Parent     *string `json:"parent"`
}

func (queries *Queries) FamilyTree(ctx context.Context, root uint64) ([]FamilyTreeRow, error) {
	rows, err := queries.reader.QueryContext(ctx, familyTreeSQL, clickhouse.Named("p_root", sglClickHouseShapedValue(root, sglClickHouseShapes[0])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []FamilyTreeRow
	for rows.Next() {
		var row FamilyTreeRow
		if err := rows.Scan(&row.Name, &row.Generation, &row.Parent); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

generation starts as the literal 0, a UInt8, and grows by generation + 1. ClickHouse adds two UInt8 values into a UInt16, so Seagull makes Generation a uint16. The server itself keeps the first SELECT's UInt8 for a recursive CTE's column, and database/sql converts each value to the wider field. parent comes from the LEFT JOIN back to gulls, which, with join_use_nulls = 1, finds no parent for the gull the tree starts from, so it is a *string.

A CTE and window functions

TheftLeague counts each gull's thefts in a CTE, then ranks the gulls on each beach and totals the beach, with two window functions over the CTE's rows:

db/queries/gulls.sql

-- sgl.query(TheftLeague, many)
WITH totals AS (
    SELECT gulls.name, gulls.beach, count(thefts.id) AS stolen
    FROM gulls
    LEFT JOIN thefts ON thefts.gull_id = gulls.id
    GROUP BY gulls.id, gulls.name, gulls.beach
)
SELECT
    name,
    beach,
    stolen,
    rank() OVER (PARTITION BY beach ORDER BY stolen DESC) AS beach_rank,
    sum(stolen) OVER (PARTITION BY beach) AS beach_thefts
FROM totals
ORDER BY beach, beach_rank, name
SETTINGS join_use_nulls = 1;

internal/store

type TheftLeagueRow struct {
	Name        string `json:"name"`
	Beach       string `json:"beach"`
	Stolen      uint64 `json:"stolen"`
	BeachRank   uint64 `json:"beach_rank"`
	BeachThefts uint64 `json:"beach_thefts"`
}

func (queries *Queries) TheftLeague(ctx context.Context) ([]TheftLeagueRow, error) {
	rows, err := queries.reader.QueryContext(ctx, theftLeagueSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []TheftLeagueRow
	if rows.Next() {
		var row TheftLeagueRow
		scanArgs := []any{&row.Name, &row.Beach, &row.Stolen, &row.BeachRank, &row.BeachThefts}
		for more := true; more; more = rows.Next() {
			row = TheftLeagueRow{}
			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 count carries through the CTE as stolen, a uint64, and rank() is a UInt64 that is never NULL. ClickHouse sums unsigned integers into a UInt64, and sums no rows to 0 rather than NULL, so BeachThefts is a plain uint64 too.

A view

BeachRecords reads the view like a table:

db/queries/gulls.sql

-- sgl.query(BeachRecords, many)
SELECT beach, gulls, thefts, last_theft FROM beach_records ORDER BY beach;

internal/store

type BeachRecordsRow struct {
	Beach     string     `json:"beach"`
	Gulls     uint64     `json:"gulls"`
	Thefts    uint64     `json:"thefts"`
	LastTheft *time.Time `json:"last_theft"`
}

type Gulls struct {
	ID       uint64  `json:"id"`
	Name     string  `json:"name"`
	ParentID *uint64 `json:"parent_id"`
	Beach    string  `json:"beach"`
}

type Thefts struct {
	ID       uint64    `json:"id"`
	GullID   uint64    `json:"gull_id"`
	Snack    string    `json:"snack"`
	StolenOn time.Time `json:"stolen_on"`
}

func (queries *Queries) BeachRecords(ctx context.Context) ([]BeachRecordsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, beachRecordsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []BeachRecordsRow
	for rows.Next() {
		var row BeachRecordsRow
		if err := rows.Scan(&row.Beach, &row.Gulls, &row.Thefts, &row.LastTheft); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

Its columns keep the types the view's own SELECT gives them. uniqExact and count are uint64, and max(stolen_on) over a Date column is a *time.Time, nil for a beach with no thefts thanks to the view's join_use_nulls = 1.

Using it

The program hatches six gulls across three beaches, records seven thefts, and prints the family tree of the first gull, the theft league and the beach records.

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/019_ctes_and_windows/internal/store"
)

// run hatches a colony of gulls, records their snack thefts and reads them back three
// ways. Seagull follows each column's type and nullability through the SQL, by
// ClickHouse's own rules:
//
//   - FamilyTree's recursive CTE starts from one gull and joins its chicks generation by
//     generation. The literal 0 is a UInt8 and adding 1 to a UInt8 gives a UInt16, so
//     Seagull makes Generation a uint16. The server keeps the first SELECT's UInt8 for a
//     recursive CTE's column, and database/sql converts each value to the wider field.
//     parent is *string because the LEFT JOIN, with join_use_nulls = 1, finds no parent
//     for the first gull;
//   - TheftLeague counts thefts in a CTE, so stolen is uint64, then ranks gulls on each
//     beach with rank() OVER, a UInt64, and totals the beach with sum() OVER, a UInt64
//     too, since ClickHouse sums unsigned integers to UInt64 and sums no rows to 0, not
//     NULL. Neither can be NULL, so BeachRank and BeachThefts are plain uint64;
//   - BeachRecords reads the beach_records view from the migration as if it were a table,
//     and its columns keep the types the view's own SELECT gives them.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the three listings.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	gulls := []store.HatchGullParams{
		{ID: 1, Name: "Steven", ParentID: nil, Beach: "Porthmeor"},
		{ID: 2, Name: "Gwen", ParentID: new(uint64(1)), Beach: "Porthmeor"},
		{ID: 3, Name: "Jago", ParentID: new(uint64(1)), Beach: "Porthminster"},
		{ID: 4, Name: "Kerensa", ParentID: new(uint64(2)), Beach: "Porthmeor"},
		{ID: 5, Name: "Tristan", ParentID: nil, Beach: "Porthminster"},
		{ID: 6, Name: "Morwenna", ParentID: nil, Beach: "Gwithian"},
	}
	for _, gull := range gulls {
		if err := queries.HatchGull(ctx, gull); err != nil {
			return fmt.Errorf("hatching %s: %w", gull.Name, err)
		}
	}
	thefts := []store.RecordTheftParams{
		{ID: 1, GullID: 1, Snack: "chips", StolenOn: day(1)},
		{ID: 2, GullID: 1, Snack: "pasty", StolenOn: day(3)},
		{ID: 3, GullID: 1, Snack: "ice cream", StolenOn: day(4)},
		{ID: 4, GullID: 2, Snack: "chips", StolenOn: day(2)},
		{ID: 5, GullID: 3, Snack: "pasty", StolenOn: day(2)},
		{ID: 6, GullID: 3, Snack: "chips", StolenOn: day(5)},
		{ID: 7, GullID: 5, Snack: "sandwich", StolenOn: day(3)},
	}
	for _, theft := range thefts {
		if err := queries.RecordTheft(ctx, theft); err != nil {
			return fmt.Errorf("recording a theft: %w", err)
		}
	}

	family, err := queries.FamilyTree(ctx, 1)
	if err != nil {
		return fmt.Errorf("tracing Steven's family: %w", err)
	}
	fmt.Fprintln(output, "Steven's family:")
	for _, member := range family {
		fmt.Fprintf(output, "  generation %d  %-8s parent %s\n", member.Generation, member.Name, orNull(member.Parent))
	}

	league, err := queries.TheftLeague(ctx)
	if err != nil {
		return fmt.Errorf("ranking thieves: %w", err)
	}
	fmt.Fprintln(output, "theft league:")
	for _, entry := range league {
		fmt.Fprintf(output, "  %-13s #%d %-8s %d of %d\n", entry.Beach, entry.BeachRank, entry.Name, entry.Stolen, entry.BeachThefts)
	}

	records, err := queries.BeachRecords(ctx)
	if err != nil {
		return fmt.Errorf("reading beach records: %w", err)
	}
	fmt.Fprintln(output, "beach records:")
	for _, record := range records {
		lastTheft := "NULL"
		if record.LastTheft != nil {
			lastTheft = record.LastTheft.Format(time.DateOnly)
		}
		fmt.Fprintf(output, "  %-13s gulls %d, thefts %d, last theft %s\n", record.Beach, record.Gulls, record.Thefts, lastTheft)
	}
	return nil
}

// day returns a date in July 2026, at midnight UTC. A Date parameter binds the calendar
// date a time has in its own location.
//
// Takes date (int) which is the day of the month.
//
// Returns time.Time which is the date.
func day(date int) time.Time {
	return time.Date(2026, time.July, date, 0, 0, 0, 0, time.UTC)
}

// orNull returns the text a nullable field holds, or NULL when the field is nil.
//
// Takes value (*string) which is a nullable field.
//
// Returns string which is the text to print.
func orNull(value *string) string {
	if value == nil {
		return "NULL"
	}
	return *value
}

What it prints

Steven's family:
  generation 0  Steven   parent NULL
  generation 1  Gwen     parent Steven
  generation 1  Jago     parent Steven
  generation 2  Kerensa  parent Gwen
theft league:
  Gwithian      #1 Morwenna 0 of 0
  Porthmeor     #1 Steven   3 of 4
  Porthmeor     #2 Gwen     1 of 4
  Porthmeor     #3 Kerensa  0 of 4
  Porthminster  #1 Jago     2 of 3
  Porthminster  #2 Tristan  1 of 3
beach records:
  Gwithian      gulls 1, thefts 0, last theft NULL
  Porthmeor     gulls 3, thefts 4, last theft 2026-07-04
  Porthminster  gulls 2, thefts 3, last theft 2026-07-05

When Seagull cannot type an expression, its field is any. An sgl.column directive with type: pins it to the type you know it has; Type overrides shows how.

Across engines

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

ClickHouseSQLitePostgreSQLMySQLDuckDB
thefts column·int64int64int64int64
beach_rank columnuint64int64int64int64int64
beach_thefts columnuint64*int64*apd.Decimal*apd.Decimal*big.Int
stolen columnuint64····

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

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 ./019_ctes_and_windows

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 ./019_ctes_and_windows

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

db/migrations/0001_gulls.up.sql

CREATE TABLE gulls (
    id        UInt64,
    name      String,
    parent_id Nullable(UInt64),
    beach     String
) ENGINE = MergeTree() ORDER BY id;

CREATE TABLE thefts (
    id        UInt64,
    gull_id   UInt64,
    snack     String,
    stolen_on Date
) ENGINE = MergeTree() ORDER BY id;

CREATE VIEW beach_records AS
SELECT
    gulls.beach,
    uniqExact(gulls.id) AS gulls,
    count(thefts.id) AS thefts,
    max(thefts.stolen_on) AS last_theft
FROM gulls
LEFT JOIN thefts ON thefts.gull_id = gulls.id
GROUP BY gulls.beach
SETTINGS join_use_nulls = 1;

db/migrations/0001_gulls.down.sql

DROP VIEW beach_records;
DROP TABLE thefts;
DROP TABLE gulls;

db/queries/gulls.sql

-- sgl.query(HatchGull, exec)
INSERT INTO gulls (id, name, parent_id, beach)
VALUES ({id:UInt64}, {name:String}, {parent_id:Nullable(UInt64)}, {beach:String});

-- sgl.query(RecordTheft, exec)
INSERT INTO thefts (id, gull_id, snack, stolen_on)
VALUES ({id:UInt64}, {gull_id:UInt64}, {snack:String}, {stolen_on:Date});

-- sgl.query(FamilyTree, many)
WITH RECURSIVE family AS (
    SELECT id, name, parent_id, 0 AS generation
    FROM gulls
    WHERE id = {root:UInt64}
    UNION ALL
    SELECT gulls.id, gulls.name, gulls.parent_id, family.generation + 1
    FROM gulls
    JOIN family ON gulls.parent_id = family.id
)
SELECT family.name, family.generation, parents.name AS parent
FROM family
LEFT JOIN gulls AS parents ON parents.id = family.parent_id
ORDER BY family.generation, family.id
SETTINGS join_use_nulls = 1;

-- sgl.query(TheftLeague, many)
WITH totals AS (
    SELECT gulls.name, gulls.beach, count(thefts.id) AS stolen
    FROM gulls
    LEFT JOIN thefts ON thefts.gull_id = gulls.id
    GROUP BY gulls.id, gulls.name, gulls.beach
)
SELECT
    name,
    beach,
    stolen,
    rank() OVER (PARTITION BY beach ORDER BY stolen DESC) AS beach_rank,
    sum(stolen) OVER (PARTITION BY beach) AS beach_thefts
FROM totals
ORDER BY beach, beach_rank, name
SETTINGS join_use_nulls = 1;

-- sgl.query(BeachRecords, many)
SELECT beach, gulls, thefts, last_theft FROM beach_records ORDER BY beach;

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/019_ctes_and_windows/internal/store"
)

// run hatches a colony of gulls, records their snack thefts and reads them back three
// ways. Seagull follows each column's type and nullability through the SQL, by
// ClickHouse's own rules:
//
//   - FamilyTree's recursive CTE starts from one gull and joins its chicks generation by
//     generation. The literal 0 is a UInt8 and adding 1 to a UInt8 gives a UInt16, so
//     Seagull makes Generation a uint16. The server keeps the first SELECT's UInt8 for a
//     recursive CTE's column, and database/sql converts each value to the wider field.
//     parent is *string because the LEFT JOIN, with join_use_nulls = 1, finds no parent
//     for the first gull;
//   - TheftLeague counts thefts in a CTE, so stolen is uint64, then ranks gulls on each
//     beach with rank() OVER, a UInt64, and totals the beach with sum() OVER, a UInt64
//     too, since ClickHouse sums unsigned integers to UInt64 and sums no rows to 0, not
//     NULL. Neither can be NULL, so BeachRank and BeachThefts are plain uint64;
//   - BeachRecords reads the beach_records view from the migration as if it were a table,
//     and its columns keep the types the view's own SELECT gives them.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the three listings.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	gulls := []store.HatchGullParams{
		{ID: 1, Name: "Steven", ParentID: nil, Beach: "Porthmeor"},
		{ID: 2, Name: "Gwen", ParentID: new(uint64(1)), Beach: "Porthmeor"},
		{ID: 3, Name: "Jago", ParentID: new(uint64(1)), Beach: "Porthminster"},
		{ID: 4, Name: "Kerensa", ParentID: new(uint64(2)), Beach: "Porthmeor"},
		{ID: 5, Name: "Tristan", ParentID: nil, Beach: "Porthminster"},
		{ID: 6, Name: "Morwenna", ParentID: nil, Beach: "Gwithian"},
	}
	for _, gull := range gulls {
		if err := queries.HatchGull(ctx, gull); err != nil {
			return fmt.Errorf("hatching %s: %w", gull.Name, err)
		}
	}
	thefts := []store.RecordTheftParams{
		{ID: 1, GullID: 1, Snack: "chips", StolenOn: day(1)},
		{ID: 2, GullID: 1, Snack: "pasty", StolenOn: day(3)},
		{ID: 3, GullID: 1, Snack: "ice cream", StolenOn: day(4)},
		{ID: 4, GullID: 2, Snack: "chips", StolenOn: day(2)},
		{ID: 5, GullID: 3, Snack: "pasty", StolenOn: day(2)},
		{ID: 6, GullID: 3, Snack: "chips", StolenOn: day(5)},
		{ID: 7, GullID: 5, Snack: "sandwich", StolenOn: day(3)},
	}
	for _, theft := range thefts {
		if err := queries.RecordTheft(ctx, theft); err != nil {
			return fmt.Errorf("recording a theft: %w", err)
		}
	}

	family, err := queries.FamilyTree(ctx, 1)
	if err != nil {
		return fmt.Errorf("tracing Steven's family: %w", err)
	}
	fmt.Fprintln(output, "Steven's family:")
	for _, member := range family {
		fmt.Fprintf(output, "  generation %d  %-8s parent %s\n", member.Generation, member.Name, orNull(member.Parent))
	}

	league, err := queries.TheftLeague(ctx)
	if err != nil {
		return fmt.Errorf("ranking thieves: %w", err)
	}
	fmt.Fprintln(output, "theft league:")
	for _, entry := range league {
		fmt.Fprintf(output, "  %-13s #%d %-8s %d of %d\n", entry.Beach, entry.BeachRank, entry.Name, entry.Stolen, entry.BeachThefts)
	}

	records, err := queries.BeachRecords(ctx)
	if err != nil {
		return fmt.Errorf("reading beach records: %w", err)
	}
	fmt.Fprintln(output, "beach records:")
	for _, record := range records {
		lastTheft := "NULL"
		if record.LastTheft != nil {
			lastTheft = record.LastTheft.Format(time.DateOnly)
		}
		fmt.Fprintf(output, "  %-13s gulls %d, thefts %d, last theft %s\n", record.Beach, record.Gulls, record.Thefts, lastTheft)
	}
	return nil
}

// day returns a date in July 2026, at midnight UTC. A Date parameter binds the calendar
// date a time has in its own location.
//
// Takes date (int) which is the day of the month.
//
// Returns time.Time which is the date.
func day(date int) time.Time {
	return time.Date(2026, time.July, date, 0, 0, 0, 0, time.UTC)
}

// orNull returns the text a nullable field holds, or NULL when the field is nil.
//
// Takes value (*string) which is a nullable field.
//
// Returns string which is the text to print.
func orNull(value *string) string {
	if value == nil {
		return "NULL"
	}
	return *value
}

main.go

// Command 019_ctes_and_windows applies its migrations to a ClickHouse database, then runs
// the example in example.go: a gull colony's family tree, theft league and beach records
// are read through a recursive CTE, window functions and a view.
//
// Run it with `go run .` against a local server, or `go run . -dsn <dsn>` to choose the
// database; give it an empty one.
package main

import (
	"context"
	"database/sql"
	"embed"
	"flag"
	"fmt"
	"log"
	"os"

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

	"seagull.sh/seagull/sdk/migrate"
	"seagull.sh/seagull/sdk/migrate/migrate_backend_clickhouse"
)

// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS

func main() {
	dsn := flag.String("dsn", "clickhouse://localhost:9000/default", "ClickHouse DSN of an empty database")
	flag.Parse()
	if err := execute(context.Background(), *dsn); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database the DSN names, and runs the example against it.
//
// Takes dsn (string) which names the ClickHouse database.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, dsn string) error {
	database, err := sql.Open("clickhouse", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_clickhouse.New(), migrations, "db/migrations")
	if err != nil {
		return err
	}
	if _, err := migrator.Up(ctx); err != nil {
		return fmt.Errorf("migrating: %w", err)
	}
	return run(ctx, database, os.Stdout)
}

internal/store/clickhouse_values.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"errors"
	"fmt"
	"math"
	"net"
	"net/netip"
	"reflect"
	"slices"
	"strconv"
	"strings"
	"time"
)

const (
	sglClickHouseMaxDepth         = 32
	sglClickHouseNanosecondDigits = 9
	sglClickHouseDigitGroup       = 3
)

var (
	sglClickHouseTextEscaper    = strings.NewReplacer("\\", "\\\\", "\t", "\\t", "\n", "\\n", "\r", "\\r", "\x00", "\\0")
	sglClickHouseQuotedEscaper  = strings.NewReplacer("\\", "\\\\", "'", "\\'", "\t", "\\t", "\n", "\\n", "\r", "\\r", "\x00", "\\0")
	sglClickHouseRefusalCleaner = strings.NewReplacer("\t", " ", "\n", " ", "\r", " ")
	errSglClickHouseShape       = errors.New("sgl: malformed ClickHouse parameter shape")
	errSglClickHouseValue       = errors.New("sgl: cannot write the value of a ClickHouse parameter")
)

type sglClickHouseShape struct {
	names     []string
	elements  []sglClickHouseShape
	digits    int
	prepared  bool
	checked   bool
	driverMap bool
	kind      byte
}

var sglClickHouseShapes = []sglClickHouseShape{{kind: 110, digits: -1, prepared: true, checked: false, driverMap: false, names: []string{}, elements: []sglClickHouseShape{}}, {kind: 113, digits: -1, prepared: true, checked: false, driverMap: false, names: []string{}, elements: []sglClickHouseShape{}}, {kind: 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/gulls.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"time"

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

const beachRecordsSQL = `SELECT beach, gulls, thefts, last_theft FROM beach_records ORDER BY beach;`

type BeachRecordsRow struct {
	Beach     string     `json:"beach"`
	Gulls     uint64     `json:"gulls"`
	Thefts    uint64     `json:"thefts"`
	LastTheft *time.Time `json:"last_theft"`
}

func (queries *Queries) BeachRecords(ctx context.Context) ([]BeachRecordsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, beachRecordsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []BeachRecordsRow
	for rows.Next() {
		var row BeachRecordsRow
		if err := rows.Scan(&row.Beach, &row.Gulls, &row.Thefts, &row.LastTheft); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const familyTreeSQL = `WITH RECURSIVE family AS (
    SELECT id, name, parent_id, 0 AS generation
    FROM gulls
    WHERE id = {p_root:UInt64}
    UNION ALL
    SELECT gulls.id, gulls.name, gulls.parent_id, family.generation + 1
    FROM gulls
    JOIN family ON gulls.parent_id = family.id
)
SELECT family.name, family.generation, parents.name AS parent
FROM family
LEFT JOIN gulls AS parents ON parents.id = family.parent_id
ORDER BY family.generation, family.id
SETTINGS join_use_nulls = 1;`

type FamilyTreeRow struct {
	Name       string  `json:"name"`
	Generation uint8   `json:"generation"`
	Parent     *string `json:"parent"`
}

func (queries *Queries) FamilyTree(ctx context.Context, root uint64) ([]FamilyTreeRow, error) {
	rows, err := queries.reader.QueryContext(ctx, familyTreeSQL, clickhouse.Named("p_root", sglClickHouseShapedValue(root, sglClickHouseShapes[0])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []FamilyTreeRow
	for rows.Next() {
		var row FamilyTreeRow
		if err := rows.Scan(&row.Name, &row.Generation, &row.Parent); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const hatchGullSQL = `INSERT INTO gulls (id, name, parent_id, beach)
VALUES ({p_id:UInt64}, {p_name:String}, {p_parent_id:Nullable(UInt64)}, {p_beach:String});`

type HatchGullParams struct {
	ID       uint64
	Name     string
	ParentID *uint64
	Beach    string
}

func (queries *Queries) HatchGull(ctx context.Context, params HatchGullParams) error {
	_, err := queries.writer.ExecContext(ctx, hatchGullSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_name", sglClickHouseShapedValue(params.Name, sglClickHouseShapes[1])), clickhouse.Named("p_parent_id", sglClickHouseShapedValue(params.ParentID, sglClickHouseShapes[0])), clickhouse.Named("p_beach", sglClickHouseShapedValue(params.Beach, sglClickHouseShapes[1])))
	return err
}

const recordTheftSQL = `INSERT INTO thefts (id, gull_id, snack, stolen_on)
VALUES ({p_id:UInt64}, {p_gull_id:UInt64}, {p_snack:String}, {p_stolen_on:Date});`

type RecordTheftParams struct {
	ID       uint64
	GullID   uint64
	Snack    string
	StolenOn time.Time
}

func (queries *Queries) RecordTheft(ctx context.Context, params RecordTheftParams) error {
	_, err := queries.writer.ExecContext(ctx, recordTheftSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_gull_id", sglClickHouseShapedValue(params.GullID, sglClickHouseShapes[0])), clickhouse.Named("p_snack", sglClickHouseShapedValue(params.Snack, sglClickHouseShapes[1])), clickhouse.Named("p_stolen_on", sglClickHouseShapedValue(params.StolenOn, sglClickHouseShapes[2])))
	return err
}

const theftLeagueSQL = `WITH totals AS (
    SELECT gulls.name, gulls.beach, count(thefts.id) AS stolen
    FROM gulls
    LEFT JOIN thefts ON thefts.gull_id = gulls.id
    GROUP BY gulls.id, gulls.name, gulls.beach
)
SELECT
    name,
    beach,
    stolen,
    rank() OVER (PARTITION BY beach ORDER BY stolen DESC) AS beach_rank,
    sum(stolen) OVER (PARTITION BY beach) AS beach_thefts
FROM totals
ORDER BY beach, beach_rank, name
SETTINGS join_use_nulls = 1;`

type TheftLeagueRow struct {
	Name        string `json:"name"`
	Beach       string `json:"beach"`
	Stolen      uint64 `json:"stolen"`
	BeachRank   uint64 `json:"beach_rank"`
	BeachThefts uint64 `json:"beach_thefts"`
}

func (queries *Queries) TheftLeague(ctx context.Context) ([]TheftLeagueRow, error) {
	rows, err := queries.reader.QueryContext(ctx, theftLeagueSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []TheftLeagueRow
	if rows.Next() {
		var row TheftLeagueRow
		scanArgs := []any{&row.Name, &row.Beach, &row.Stolen, &row.BeachRank, &row.BeachThefts}
		for more := true; more; more = rows.Next() {
			row = TheftLeagueRow{}
			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

import "time"

type Gulls struct {
	ID       uint64  `json:"id"`
	Name     string  `json:"name"`
	ParentID *uint64 `json:"parent_id"`
	Beach    string  `json:"beach"`
}

type Thefts struct {
	ID       uint64    `json:"id"`
	GullID   uint64    `json:"gull_id"`
	Snack    string    `json:"snack"`
	StolenOn time.Time `json:"stolen_on"`
}

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

Steven's family:
  generation 0  Steven   parent NULL
  generation 1  Gwen     parent Steven
  generation 1  Jago     parent Steven
  generation 2  Kerensa  parent Gwen
theft league:
  Gwithian      #1 Morwenna 0 of 0
  Porthmeor     #1 Steven   3 of 4
  Porthmeor     #2 Gwen     1 of 4
  Porthmeor     #3 Kerensa  0 of 4
  Porthminster  #1 Jago     2 of 3
  Porthminster  #2 Tristan  1 of 3
beach records:
  Gwithian      gulls 1, thefts 0, last theft NULL
  Porthmeor     gulls 3, thefts 4, last theft 2026-07-04
  Porthminster  gulls 2, thefts 3, last theft 2026-07-05

Also on other engines

SQLiteA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each.PostgreSQLA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each; SUM of a bigint arrives as a decimal.CockroachDBA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each; SUM of a bigint arrives as a decimal.TimescaleDBA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each; SUM of a bigint arrives as a decimal.MySQLA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each.MariaDBA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each.DuckDBA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each.