Placeholders, inferred names and Params structs

On this page

On ClickHouse:

  • Placeholders are {name:Type}: the name becomes the field and the type sets the Go type, so nothing is inferred from columns and no binding line is needed
  • Each placeholder is sent as p_, so names such as limit and offset cannot clash with keywords
  • {seats:Int32} appears twice in BookSeats but is one field
  • ALTER TABLE ... UPDATE reports no changed rows, so BookSeats is asyncexec and the example compares seats_free before and after the booking
  • No auto-increment, so each sailing's id is a parameter

Every placeholder in a query becomes a parameter of the generated method. A query with a single plain parameter, such as an id, takes it as an ordinary argument. With two or more, the method takes a <Name>Params struct instead, so a call site reads as named fields rather than a row of positional values that are easy to swap by mistake.

Those fields need good names.

On ClickHouse the placeholder names itself: {origin:String} carries both a name and a type, so the field is Origin and its Go type is string. Nothing is inferred from columns.

This example is a ferry timetable: add sailings, search them two ways, and book seats.

Names from the SQL

FindSailings compares origin and destination with {origin:String} and {destination:String}, so its parameters are Origin and Destination, both string. A UInt64 placeholder would give a uint64, and an Int32 an int32.

db/queries/sailings.sql

-- sgl.query(FindSailings, many)
SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE origin = {origin:String} AND destination = {destination:String}
ORDER BY departs;

internal/store

type FindSailingsParams struct {
	Origin      string
	Destination string
}

type FindSailingsRow struct {
	ID          uint64 `json:"id"`
	Origin      string `json:"origin"`
	Destination string `json:"destination"`
	Departs     string `json:"departs"`
	SeatsFree   int32  `json:"seats_free"`
}

func (queries *Queries) FindSailings(ctx context.Context, params FindSailingsParams) ([]FindSailingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, findSailingsSQL, clickhouse.Named("p_origin", sglClickHouseShapedValue(params.Origin, sglClickHouseShapes[1])), clickhouse.Named("p_destination", sglClickHouseShapedValue(params.Destination, sglClickHouseShapes[1])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []FindSailingsRow
	if rows.Next() {
		var row FindSailingsRow
		scanArgs := []any{&row.ID, &row.Origin, &row.Destination, &row.Departs, &row.SeatsFree}
		for more := true; more; more = rows.Next() {
			row = FindSailingsRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

The method binds each value by its placeholder's name, with clickhouse.Named. AddSailing names its five placeholders after the columns they fill, which gives ID, Origin, Destination, Departs and SeatsFree.

Naming a parameter yourself

SailingsDeparting compares departs twice, for the start and the end of a window. The placeholders name the two bounds themselves, {earliest:String} and {latest:String}, so the struct has Earliest and Latest fields and no binding line is needed:

db/queries/sailings.sql

-- sgl.query(SailingsDeparting, many)
SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE departs >= {earliest:String} AND departs < {latest:String}
ORDER BY departs;

One value, used twice

BookSeats takes seats off a sailing, but only when enough are left, so it needs the number of seats twice: once to subtract and once in the guard.

{seats:Int32} appears twice, but it is one placeholder, so it is one Seats field, bound once.

db/queries/sailings.sql

-- sgl.query(BookSeats, asyncexec)
ALTER TABLE sailings
UPDATE seats_free = seats_free - {seats:Int32}
WHERE id = {sailing_id:UInt64} AND seats_free >= {seats:Int32};

internal/store

type BookSeatsParams struct {
	Seats     int32
	SailingID uint64
}

// BookSeats 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) BookSeats(ctx context.Context, params BookSeatsParams) error {
	_, err := queries.writer.ExecContext(ctx, bookSeatsSQL, clickhouse.Named("p_seats", sglClickHouseShapedValue(params.Seats, sglClickHouseShapes[0])), clickhouse.Named("p_sailing_id", sglClickHouseShapedValue(params.SailingID, sglClickHouseShapes[0])))
	return err
}

Using it

The program fills the timetable, searches it by route and by time of day, then makes the same booking twice. The first booking takes three of the four free seats on sailing 3. The second asks for three again, finds only one left, and the guard stops it.

BookSeats is asyncexec, which returns no count, so the program reads seats_free with SeatsFree before and after each booking to see whether it went through.

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/003_parameters/internal/store"
)

// run fills a ferry timetable, searches it two ways and books seats. A ClickHouse
// placeholder carries its own name and type, as in {origin:String}, so each Params field
// is named after the placeholder and typed from it: String gives string, Int32 int32 and
// UInt64 uint64. A query with two or more parameters takes a Params struct:
//
//   - AddSailing and FindSailings name their fields ID, Origin, Destination, Departs and
//     SeatsFree from {id:UInt64}, {origin:String} and the rest;
//   - SailingsDeparting compares departs twice, so it names the two bounds itself with
//     {earliest:String} and {latest:String}, and needs no binding line;
//   - BookSeats uses {seats:Int32} twice, but it is one field, bound once.
//
// Seagull sends each placeholder as p_<name>, as in {p_seats:Int32}, so a parameter may
// be called limit or offset without the server reading it as a keyword.
//
// ALTER TABLE ... UPDATE reports no changed rows, so BookSeats is asyncexec and the
// booking reads seats_free before and after to see whether the guard let it through.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the search results and bookings.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	timetable := []store.AddSailingParams{
		{ID: 1, Origin: "Penzance", Destination: "St Mary's", Departs: "09:15", SeatsFree: 40},
		{ID: 2, Origin: "St Mary's", Destination: "Tresco", Departs: "11:00", SeatsFree: 12},
		{ID: 3, Origin: "Penzance", Destination: "St Mary's", Departs: "14:30", SeatsFree: 4},
		{ID: 4, Origin: "St Mary's", Destination: "Penzance", Departs: "16:30", SeatsFree: 60},
	}
	for _, sailing := range timetable {
		if err := queries.AddSailing(ctx, sailing); err != nil {
			return fmt.Errorf("adding the %s sailing: %w", sailing.Departs, err)
		}
	}

	route, err := queries.FindSailings(ctx, store.FindSailingsParams{
		Origin:      "Penzance",
		Destination: "St Mary's",
	})
	if err != nil {
		return fmt.Errorf("finding sailings: %w", err)
	}
	fmt.Fprintln(output, "Penzance to St Mary's:")
	for _, sailing := range route {
		fmt.Fprintf(output, "  sailing %d at %s, %d seats free\n", sailing.ID, sailing.Departs, sailing.SeatsFree)
	}

	midday, err := queries.SailingsDeparting(ctx, store.SailingsDepartingParams{
		Earliest: "10:00",
		Latest:   "15:00",
	})
	if err != nil {
		return fmt.Errorf("finding midday sailings: %w", err)
	}
	fmt.Fprintln(output, "Departing between 10:00 and 15:00:")
	for _, sailing := range midday {
		fmt.Fprintf(output, "  sailing %d at %s, %s to %s\n", sailing.ID, sailing.Departs, sailing.Origin, sailing.Destination)
	}

	// The same booking twice: the first takes 3 of the 4 free seats, so the second changes
	// nothing because seats_free >= {seats:Int32} is now false.
	booking := store.BookSeatsParams{Seats: 3, SailingID: 3}
	for range 2 {
		booked, err := bookSeats(ctx, queries, booking)
		if err != nil {
			return err
		}
		if !booked {
			fmt.Fprintf(output, "sailing %d has fewer than %d seats free\n", booking.SailingID, booking.Seats)
			continue
		}
		fmt.Fprintf(output, "booked %d seats on sailing %d\n", booking.Seats, booking.SailingID)
	}
	return nil
}

// bookSeats runs BookSeats and reports whether it took the seats, by comparing seats_free
// before and after. The connection sets mutations_sync=2, so the mutation has finished
// when BookSeats returns.
//
// Takes queries (*store.Queries) which runs the queries.
// Takes booking (store.BookSeatsParams) which is the sailing and the number of seats.
//
// Returns bool which is true when the seats were booked.
// Returns error when a query fails.
func bookSeats(ctx context.Context, queries *store.Queries, booking store.BookSeatsParams) (bool, error) {
	before, err := queries.SeatsFree(ctx, booking.SailingID)
	if err != nil {
		return false, fmt.Errorf("reading seats on sailing %d: %w", booking.SailingID, err)
	}
	if err := queries.BookSeats(ctx, booking); err != nil {
		return false, fmt.Errorf("booking sailing %d: %w", booking.SailingID, err)
	}
	after, err := queries.SeatsFree(ctx, booking.SailingID)
	if err != nil {
		return false, fmt.Errorf("reading seats on sailing %d: %w", booking.SailingID, err)
	}
	return after.SeatsFree != before.SeatsFree, nil
}

What it prints

Penzance to St Mary's:
  sailing 1 at 09:15, 40 seats free
  sailing 3 at 14:30, 4 seats free
Departing between 10:00 and 15:00:
  sailing 2 at 11:00, St Mary's to Tresco
  sailing 3 at 14:30, Penzance to St Mary's
booked 3 seats on sailing 3
sailing 3 has fewer than 3 seats free

With the native ClickHouse driver

The ferry timetable over a native clickhouse-go connection, where each {name:Type} placeholder becomes a field bound as clickhouse.Named("p_", ...).

What changes:

  • Placeholders are {name:Type}: the name becomes the field and the type sets the Go type, so nothing is inferred from columns and no binding line is needed
  • The native emitter binds each value with clickhouse.Named("p_", ...), formatted by the generated clickhouse_values.go, which escapes backslashes, tabs, newlines, carriage returns and NULs
  • {seats:Int32} appears twice in BookSeats but is one field, bound once
  • The native driver reports no changed rows, so BookSeats is asyncexec and the example compares seats_free before and after the booking
  • No auto-increment, so each sailing's id is a parameter

db/queries/sailings.sql

-- sgl.query(AddSailing, exec)
INSERT INTO sailings (id, origin, destination, departs, seats_free)
VALUES ({id:UInt64}, {origin:String}, {destination:String}, {departs:String}, {seats_free:Int32});

-- sgl.query(FindSailings, many)
SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE origin = {origin:String} AND destination = {destination:String}
ORDER BY departs;

-- sgl.query(SailingsDeparting, many)
SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE departs >= {earliest:String} AND departs < {latest:String}
ORDER BY departs;

-- sgl.query(BookSeats, asyncexec)
ALTER TABLE sailings
UPDATE seats_free = seats_free - {seats:Int32}
WHERE id = {sailing_id:UInt64} AND seats_free >= {seats:Int32};

-- sgl.query(SeatsFree, one)
SELECT seats_free FROM sailings WHERE id = {sailing_id:UInt64};

internal/store

type FindSailingsParams struct {
	Origin      string
	Destination string
}

type FindSailingsRow struct {
	ID          uint64 `json:"id"`
	Origin      string `json:"origin"`
	Destination string `json:"destination"`
	Departs     string `json:"departs"`
	SeatsFree   int32  `json:"seats_free"`
}

func (queries *Queries) FindSailings(ctx context.Context, params FindSailingsParams) ([]FindSailingsRow, error) {
	rows, err := queries.db.Query(ctx, findSailingsSQL, clickhouse.Named("p_origin", sglClickHouseShapedValue(params.Origin, sglClickHouseShapes[1])), clickhouse.Named("p_destination", sglClickHouseShapedValue(params.Destination, sglClickHouseShapes[1])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []FindSailingsRow
	if rows.Next() {
		var row FindSailingsRow
		scanArgs := []any{&row.ID, &row.Origin, &row.Destination, &row.Departs, &row.SeatsFree}
		for more := true; more; more = rows.Next() {
			row = FindSailingsRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

example.go

package main

import (
	"context"
	"fmt"
	"io"

	"seagull.sh/seagull/examples/clickhouse/003_parameters_native/internal/store"
)

// run fills a ferry timetable, searches it two ways and books seats, over a native
// clickhouse-go connection. A ClickHouse placeholder carries its own name and type, as in
// {origin:String}, so each Params field is named after the placeholder and typed from it:
// String gives string, Int32 int32 and UInt64 uint64. A query with two or more parameters
// takes a Params struct:
//
//   - AddSailing and FindSailings name their fields ID, Origin, Destination, Departs and
//     SeatsFree from {id:UInt64}, {origin:String} and the rest;
//   - SailingsDeparting compares departs twice, so it names the two bounds itself with
//     {earliest:String} and {latest:String}, and needs no binding line;
//   - BookSeats uses {seats:Int32} twice, but it is one field, bound once.
//
// Seagull sends each placeholder as p_<name>, as in {p_seats:Int32}, so a parameter may
// be called limit or offset without the server reading it as a keyword. The generated
// method binds each value with clickhouse.Named("p_seats", ...), after the generated
// clickhouse_values.go has turned it into the text ClickHouse parses, escaping
// backslashes, tabs, newlines, carriage returns and NULs.
//
// ALTER TABLE ... UPDATE reports no changed rows, so BookSeats is asyncexec and the
// booking reads seats_free before and after to see whether the guard let it through.
//
// Takes connection (store.DBTX) which is a native connection to a migrated database.
// Takes output (io.Writer) which receives the search results and bookings.
//
// Returns error when a query fails.
func run(ctx context.Context, connection store.DBTX, output io.Writer) error {
	queries := store.New(connection)

	timetable := []store.AddSailingParams{
		{ID: 1, Origin: "Penzance", Destination: "St Mary's", Departs: "09:15", SeatsFree: 40},
		{ID: 2, Origin: "St Mary's", Destination: "Tresco", Departs: "11:00", SeatsFree: 12},
		{ID: 3, Origin: "Penzance", Destination: "St Mary's", Departs: "14:30", SeatsFree: 4},
		{ID: 4, Origin: "St Mary's", Destination: "Penzance", Departs: "16:30", SeatsFree: 60},
	}
	for _, sailing := range timetable {
		if err := queries.AddSailing(ctx, sailing); err != nil {
			return fmt.Errorf("adding the %s sailing: %w", sailing.Departs, err)
		}
	}

	route, err := queries.FindSailings(ctx, store.FindSailingsParams{
		Origin:      "Penzance",
		Destination: "St Mary's",
	})
	if err != nil {
		return fmt.Errorf("finding sailings: %w", err)
	}
	fmt.Fprintln(output, "Penzance to St Mary's:")
	for _, sailing := range route {
		fmt.Fprintf(output, "  sailing %d at %s, %d seats free\n", sailing.ID, sailing.Departs, sailing.SeatsFree)
	}

	midday, err := queries.SailingsDeparting(ctx, store.SailingsDepartingParams{
		Earliest: "10:00",
		Latest:   "15:00",
	})
	if err != nil {
		return fmt.Errorf("finding midday sailings: %w", err)
	}
	fmt.Fprintln(output, "Departing between 10:00 and 15:00:")
	for _, sailing := range midday {
		fmt.Fprintf(output, "  sailing %d at %s, %s to %s\n", sailing.ID, sailing.Departs, sailing.Origin, sailing.Destination)
	}

	// The same booking twice: the first takes 3 of the 4 free seats, so the second changes
	// nothing because seats_free >= {seats:Int32} is now false.
	booking := store.BookSeatsParams{Seats: 3, SailingID: 3}
	for range 2 {
		booked, err := bookSeats(ctx, queries, booking)
		if err != nil {
			return err
		}
		if !booked {
			fmt.Fprintf(output, "sailing %d has fewer than %d seats free\n", booking.SailingID, booking.Seats)
			continue
		}
		fmt.Fprintf(output, "booked %d seats on sailing %d\n", booking.Seats, booking.SailingID)
	}
	return nil
}

// bookSeats runs BookSeats and reports whether it took the seats, by comparing seats_free
// before and after. The connection sets mutations_sync=2, so the mutation has finished
// when BookSeats returns.
//
// Takes queries (*store.Queries) which runs the queries.
// Takes booking (store.BookSeatsParams) which is the sailing and the number of seats.
//
// Returns bool which is true when the seats were booked.
// Returns error when a query fails.
func bookSeats(ctx context.Context, queries *store.Queries, booking store.BookSeatsParams) (bool, error) {
	before, err := queries.SeatsFree(ctx, booking.SailingID)
	if err != nil {
		return false, fmt.Errorf("reading seats on sailing %d: %w", booking.SailingID, err)
	}
	if err := queries.BookSeats(ctx, booking); err != nil {
		return false, fmt.Errorf("booking sailing %d: %w", booking.SailingID, err)
	}
	after, err := queries.SeatsFree(ctx, booking.SailingID)
	if err != nil {
		return false, fmt.Errorf("reading seats on sailing %d: %w", booking.SailingID, err)
	}
	return after.SeatsFree != before.SeatsFree, nil
}

Across engines

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

ClickHouseSQLitePostgreSQLMySQLDuckDB
id columnuint64int64int64int64int64
seats_free columnint32int64int64int32int32

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 ./003_parameters

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 ./003_parameters

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

db/migrations/0001_sailings.up.sql

CREATE TABLE sailings (
    id          UInt64,
    origin      String,
    destination String,
    departs     String,
    seats_free  Int32
) ENGINE = MergeTree() ORDER BY id;

db/migrations/0001_sailings.down.sql

DROP TABLE sailings;

db/queries/sailings.sql

-- sgl.query(AddSailing, exec)
INSERT INTO sailings (id, origin, destination, departs, seats_free)
VALUES ({id:UInt64}, {origin:String}, {destination:String}, {departs:String}, {seats_free:Int32});

-- sgl.query(FindSailings, many)
SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE origin = {origin:String} AND destination = {destination:String}
ORDER BY departs;

-- sgl.query(SailingsDeparting, many)
SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE departs >= {earliest:String} AND departs < {latest:String}
ORDER BY departs;

-- sgl.query(BookSeats, asyncexec)
ALTER TABLE sailings
UPDATE seats_free = seats_free - {seats:Int32}
WHERE id = {sailing_id:UInt64} AND seats_free >= {seats:Int32};

-- sgl.query(SeatsFree, one)
SELECT seats_free FROM sailings WHERE id = {sailing_id:UInt64};

example.go

package main

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

	"seagull.sh/seagull/examples/clickhouse/003_parameters/internal/store"
)

// run fills a ferry timetable, searches it two ways and books seats. A ClickHouse
// placeholder carries its own name and type, as in {origin:String}, so each Params field
// is named after the placeholder and typed from it: String gives string, Int32 int32 and
// UInt64 uint64. A query with two or more parameters takes a Params struct:
//
//   - AddSailing and FindSailings name their fields ID, Origin, Destination, Departs and
//     SeatsFree from {id:UInt64}, {origin:String} and the rest;
//   - SailingsDeparting compares departs twice, so it names the two bounds itself with
//     {earliest:String} and {latest:String}, and needs no binding line;
//   - BookSeats uses {seats:Int32} twice, but it is one field, bound once.
//
// Seagull sends each placeholder as p_<name>, as in {p_seats:Int32}, so a parameter may
// be called limit or offset without the server reading it as a keyword.
//
// ALTER TABLE ... UPDATE reports no changed rows, so BookSeats is asyncexec and the
// booking reads seats_free before and after to see whether the guard let it through.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the search results and bookings.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	timetable := []store.AddSailingParams{
		{ID: 1, Origin: "Penzance", Destination: "St Mary's", Departs: "09:15", SeatsFree: 40},
		{ID: 2, Origin: "St Mary's", Destination: "Tresco", Departs: "11:00", SeatsFree: 12},
		{ID: 3, Origin: "Penzance", Destination: "St Mary's", Departs: "14:30", SeatsFree: 4},
		{ID: 4, Origin: "St Mary's", Destination: "Penzance", Departs: "16:30", SeatsFree: 60},
	}
	for _, sailing := range timetable {
		if err := queries.AddSailing(ctx, sailing); err != nil {
			return fmt.Errorf("adding the %s sailing: %w", sailing.Departs, err)
		}
	}

	route, err := queries.FindSailings(ctx, store.FindSailingsParams{
		Origin:      "Penzance",
		Destination: "St Mary's",
	})
	if err != nil {
		return fmt.Errorf("finding sailings: %w", err)
	}
	fmt.Fprintln(output, "Penzance to St Mary's:")
	for _, sailing := range route {
		fmt.Fprintf(output, "  sailing %d at %s, %d seats free\n", sailing.ID, sailing.Departs, sailing.SeatsFree)
	}

	midday, err := queries.SailingsDeparting(ctx, store.SailingsDepartingParams{
		Earliest: "10:00",
		Latest:   "15:00",
	})
	if err != nil {
		return fmt.Errorf("finding midday sailings: %w", err)
	}
	fmt.Fprintln(output, "Departing between 10:00 and 15:00:")
	for _, sailing := range midday {
		fmt.Fprintf(output, "  sailing %d at %s, %s to %s\n", sailing.ID, sailing.Departs, sailing.Origin, sailing.Destination)
	}

	// The same booking twice: the first takes 3 of the 4 free seats, so the second changes
	// nothing because seats_free >= {seats:Int32} is now false.
	booking := store.BookSeatsParams{Seats: 3, SailingID: 3}
	for range 2 {
		booked, err := bookSeats(ctx, queries, booking)
		if err != nil {
			return err
		}
		if !booked {
			fmt.Fprintf(output, "sailing %d has fewer than %d seats free\n", booking.SailingID, booking.Seats)
			continue
		}
		fmt.Fprintf(output, "booked %d seats on sailing %d\n", booking.Seats, booking.SailingID)
	}
	return nil
}

// bookSeats runs BookSeats and reports whether it took the seats, by comparing seats_free
// before and after. The connection sets mutations_sync=2, so the mutation has finished
// when BookSeats returns.
//
// Takes queries (*store.Queries) which runs the queries.
// Takes booking (store.BookSeatsParams) which is the sailing and the number of seats.
//
// Returns bool which is true when the seats were booked.
// Returns error when a query fails.
func bookSeats(ctx context.Context, queries *store.Queries, booking store.BookSeatsParams) (bool, error) {
	before, err := queries.SeatsFree(ctx, booking.SailingID)
	if err != nil {
		return false, fmt.Errorf("reading seats on sailing %d: %w", booking.SailingID, err)
	}
	if err := queries.BookSeats(ctx, booking); err != nil {
		return false, fmt.Errorf("booking sailing %d: %w", booking.SailingID, err)
	}
	after, err := queries.SeatsFree(ctx, booking.SailingID)
	if err != nil {
		return false, fmt.Errorf("reading seats on sailing %d: %w", booking.SailingID, err)
	}
	return after.SeatsFree != before.SeatsFree, nil
}

main.go

// Command 003_parameters applies its migrations to a ClickHouse database, then runs the
// example in example.go: a ferry timetable is searched and booked through queries whose
// parameters are named by their {name:Type} placeholders.
//
// Run it with `go run .` against a local server, or `go run . -dsn <dsn>` to choose the
// database; give it an empty one. Keep mutations_sync=2 in the DSN so each booking has
// finished before the seats are read back.
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

type Sailings struct {
	ID          uint64 `json:"id"`
	Origin      string `json:"origin"`
	Destination string `json:"destination"`
	Departs     string `json:"departs"`
	SeatsFree   int32  `json:"seats_free"`
}

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/sailings.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"

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

const addSailingSQL = `INSERT INTO sailings (id, origin, destination, departs, seats_free)
VALUES ({p_id:UInt64}, {p_origin:String}, {p_destination:String}, {p_departs:String}, {p_seats_free:Int32});`

type AddSailingParams struct {
	ID          uint64
	Origin      string
	Destination string
	Departs     string
	SeatsFree   int32
}

func (queries *Queries) AddSailing(ctx context.Context, params AddSailingParams) error {
	_, err := queries.writer.ExecContext(ctx, addSailingSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_origin", sglClickHouseShapedValue(params.Origin, sglClickHouseShapes[1])), clickhouse.Named("p_destination", sglClickHouseShapedValue(params.Destination, sglClickHouseShapes[1])), clickhouse.Named("p_departs", sglClickHouseShapedValue(params.Departs, sglClickHouseShapes[1])), clickhouse.Named("p_seats_free", sglClickHouseShapedValue(params.SeatsFree, sglClickHouseShapes[0])))
	return err
}

const bookSeatsSQL = `ALTER TABLE sailings
UPDATE seats_free = seats_free - {p_seats:Int32}
WHERE id = {p_sailing_id:UInt64} AND seats_free >= {p_seats:Int32};`

type BookSeatsParams struct {
	Seats     int32
	SailingID uint64
}

// BookSeats 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) BookSeats(ctx context.Context, params BookSeatsParams) error {
	_, err := queries.writer.ExecContext(ctx, bookSeatsSQL, clickhouse.Named("p_seats", sglClickHouseShapedValue(params.Seats, sglClickHouseShapes[0])), clickhouse.Named("p_sailing_id", sglClickHouseShapedValue(params.SailingID, sglClickHouseShapes[0])))
	return err
}

const findSailingsSQL = `SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE origin = {p_origin:String} AND destination = {p_destination:String}
ORDER BY departs;`

type FindSailingsParams struct {
	Origin      string
	Destination string
}

type FindSailingsRow struct {
	ID          uint64 `json:"id"`
	Origin      string `json:"origin"`
	Destination string `json:"destination"`
	Departs     string `json:"departs"`
	SeatsFree   int32  `json:"seats_free"`
}

func (queries *Queries) FindSailings(ctx context.Context, params FindSailingsParams) ([]FindSailingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, findSailingsSQL, clickhouse.Named("p_origin", sglClickHouseShapedValue(params.Origin, sglClickHouseShapes[1])), clickhouse.Named("p_destination", sglClickHouseShapedValue(params.Destination, sglClickHouseShapes[1])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []FindSailingsRow
	if rows.Next() {
		var row FindSailingsRow
		scanArgs := []any{&row.ID, &row.Origin, &row.Destination, &row.Departs, &row.SeatsFree}
		for more := true; more; more = rows.Next() {
			row = FindSailingsRow{}
			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 sailingsDepartingSQL = `SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE departs >= {p_earliest:String} AND departs < {p_latest:String}
ORDER BY departs;`

type SailingsDepartingParams struct {
	Earliest string
	Latest   string
}

type SailingsDepartingRow struct {
	ID          uint64 `json:"id"`
	Origin      string `json:"origin"`
	Destination string `json:"destination"`
	Departs     string `json:"departs"`
	SeatsFree   int32  `json:"seats_free"`
}

func (queries *Queries) SailingsDeparting(ctx context.Context, params SailingsDepartingParams) ([]SailingsDepartingRow, error) {
	rows, err := queries.reader.QueryContext(ctx, sailingsDepartingSQL, clickhouse.Named("p_earliest", sglClickHouseShapedValue(params.Earliest, sglClickHouseShapes[1])), clickhouse.Named("p_latest", sglClickHouseShapedValue(params.Latest, sglClickHouseShapes[1])))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SailingsDepartingRow
	if rows.Next() {
		var row SailingsDepartingRow
		scanArgs := []any{&row.ID, &row.Origin, &row.Destination, &row.Departs, &row.SeatsFree}
		for more := true; more; more = rows.Next() {
			row = SailingsDepartingRow{}
			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 seatsFreeSQL = `SELECT seats_free FROM sailings WHERE id = {p_sailing_id:UInt64};`

type SeatsFreeRow struct {
	SeatsFree int32 `json:"seats_free"`
}

func (queries *Queries) SeatsFree(ctx context.Context, sailingID uint64) (SeatsFreeRow, error) {
	var row SeatsFreeRow
	err := queries.reader.QueryRowContext(ctx, seatsFreeSQL, clickhouse.Named("p_sailing_id", sglClickHouseShapedValue(sailingID, sglClickHouseShapes[0]))).Scan(&row.SeatsFree)
	if err != nil {
		return SeatsFreeRow{}, err
	}
	return row, nil
}

output.txt

Penzance to St Mary's:
  sailing 1 at 09:15, 40 seats free
  sailing 3 at 14:30, 4 seats free
Departing between 10:00 and 15:00:
  sailing 2 at 11:00, St Mary's to Tresco
  sailing 3 at 14:30, Penzance to St Mary's
booked 3 seats on sailing 3
sailing 3 has fewer than 3 seats free

Also on other engines

SQLiteA ferry timetable whose Params structs take their field names from inserted and compared columns, binding lines and named placeholders.PostgreSQLA ferry timetable whose Params structs take their field names from inserted and compared columns, binding lines and named placeholders.CockroachDBA ferry timetable whose Params structs take their field names from inserted and compared columns, binding lines and named placeholders.TimescaleDBA ferry timetable whose Params structs take their field names from inserted and compared columns, binding lines and named placeholders.MySQLA ferry timetable whose Params structs take their field names from inserted and compared columns and from binding lines, with ?1 used twice.MariaDBA ferry timetable whose Params structs take their field names from inserted and compared columns and from binding lines, with ?1 used twice.DuckDBA ferry timetable whose Params structs take their field names from inserted and compared columns, binding lines and named placeholders.