Functions in migrations
On this page
On ClickHouse:
- A ClickHouse SQL function is a lambda, CREATE FUNCTION name AS args -> expression, with no body language, volatility or RETURNS clause
- Functions belong to the server, not the database, so the migration uses CREATE OR REPLACE and the down migration DROP FUNCTION IF EXISTS
- A lambda cannot read or write tables, so sgl.migration(readonly:) has nothing to override
- Seagull types a call to a lambda from its body: price_pence * 110 widens UInt32 to UInt64, so TotalPence is uint64, and the call reads no table, so the query is routed to the reader
A function created in a migration is part of the schema, just as a table is, and Seagull reads each CREATE FUNCTION when it replays the migrations.
A query that calls the function is typed from the function's body, with the call's arguments in place of its parameters, and the call is checked like any other.
The example prices a chip shop's menu with a service charge that a SQL function works out.
The functions
A ClickHouse SQL function is a lambda: a name, its arguments and one expression, with no body language, volatility or return type.
db/migrations/0002_with_service.up.sql
CREATE OR REPLACE FUNCTION with_service AS pence -> intDiv(pence * 110, 100);
Functions belong to the server rather than to one database, so the migration says CREATE OR REPLACE, and the down migration DROP FUNCTION IF EXISTS. A lambda can only compute from its arguments; it cannot read or write a table, so there is no data access to declare and sgl.migration(readonly:) has nothing to override.
The queries
MenuWithService calls the function like a built-in:
db/queries/menu.sql
-- sgl.query(MenuWithService, many)
SELECT dish, price_pence, with_service(price_pence) AS total_pence
FROM menu
ORDER BY id;
Seagull reads the lambda from the migration and types the call from its body, with price_pence in place of pence. price_pence is a UInt32; ClickHouse multiplies it by 110 into a UInt64, the next wider type, and intDiv keeps that, so TotalPence is a uint64, just as the server returns it. A lambda cannot touch a table, so the call leaves the query read-only, and it uses queries.reader.
internal/store
type MenuWithServiceRow struct {
Dish string `json:"dish"`
PricePence uint32 `json:"price_pence"`
TotalPence uint64 `json:"total_pence"`
}
func (queries *Queries) MenuWithService(ctx context.Context) ([]MenuWithServiceRow, error) {
rows, err := queries.reader.QueryContext(ctx, menuWithServiceSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []MenuWithServiceRow
if rows.Next() {
var row MenuWithServiceRow
scanArgs := []any{&row.Dish, &row.PricePence, &row.TotalPence}
for more := true; more; more = rows.Next() {
row = MenuWithServiceRow{}
if err := rows.Scan(scanArgs...); err != nil {
return nil, err
}
results = append(results, row)
}
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
Using it
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"seagull.sh/seagull/examples/clickhouse/030_functions_in_migrations/internal/store"
)
// run prices the chip shop's menu with a service charge worked out by a SQL function that
// a migration defines. In ClickHouse a SQL function is a lambda, CREATE FUNCTION
// with_service AS pence -> intDiv(pence * 110, 100), and it belongs to the server rather
// than to one database, so the migration says CREATE OR REPLACE and the down migration
// drops it. A lambda can only compute from its arguments; it cannot read or write a
// table, so a query that calls one stays read-only and there is no volatility to declare.
//
// Seagull reads the lambda from the migration and types each call from its body, with the
// argument in place of pence. price_pence is a UInt32; ClickHouse multiplies it by 110
// into a UInt64, the next wider type, and intDiv keeps that, so TotalPence is a uint64,
// as the server returns. The call reads no table, so the query stays a read, and a store
// built with NewWithReplica sends it to the reader.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the priced menu.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
queries := store.New(database)
dishes := []store.AddDishParams{
{ID: 1, Dish: "cod and chips", PricePence: 850},
{ID: 2, Dish: "haddock and chips", PricePence: 920},
{ID: 3, Dish: "mushy peas", PricePence: 150},
}
for _, dish := range dishes {
if err := queries.AddDish(ctx, dish); err != nil {
return fmt.Errorf("adding %s: %w", dish.Dish, err)
}
}
menu, err := queries.MenuWithService(ctx)
if err != nil {
return fmt.Errorf("pricing the menu: %w", err)
}
for _, dish := range menu {
fmt.Fprintf(output, "%-18s %4dp, with service %dp\n", dish.Dish, dish.PricePence, dish.TotalPence)
}
return nil
}
What it prints
cod and chips 850p, with service 935p
haddock and chips 920p, with service 1012p
mushy peas 150p, with service 165p
Try it
Change the lambda in migration 0002 to pence -> pence * 1.1 and run sgl generate. Multiplying by a Float64 gives a Float64, so TotalPence becomes a float64, and the program's %d has to become %.0f. The type follows the body, wherever the function is called.
Run it locally
From the directory you cloned the Seagull repository into:
cd seagull/examples/clickhouse && go run ./030_functions_in_migrations
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 ./030_functions_in_migrations
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: chippy
dialect: clickhouse
migrations: db/migrations
queries: db/queries
go:
package: store
out: internal/store
db/migrations/0001_menu.up.sql
CREATE TABLE menu (
id UInt64,
dish String,
price_pence UInt32
) ENGINE = MergeTree() ORDER BY id;
db/migrations/0002_with_service.up.sql
CREATE OR REPLACE FUNCTION with_service AS pence -> intDiv(pence * 110, 100);
db/migrations/0001_menu.down.sql
DROP TABLE menu;
db/migrations/0002_with_service.down.sql
DROP FUNCTION IF EXISTS with_service;
db/queries/menu.sql
-- sgl.query(AddDish, exec)
INSERT INTO menu (id, dish, price_pence) VALUES ({id:UInt64}, {dish:String}, {price_pence:UInt32});
-- sgl.query(MenuWithService, many)
SELECT dish, price_pence, with_service(price_pence) AS total_pence
FROM menu
ORDER BY id;
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"seagull.sh/seagull/examples/clickhouse/030_functions_in_migrations/internal/store"
)
// run prices the chip shop's menu with a service charge worked out by a SQL function that
// a migration defines. In ClickHouse a SQL function is a lambda, CREATE FUNCTION
// with_service AS pence -> intDiv(pence * 110, 100), and it belongs to the server rather
// than to one database, so the migration says CREATE OR REPLACE and the down migration
// drops it. A lambda can only compute from its arguments; it cannot read or write a
// table, so a query that calls one stays read-only and there is no volatility to declare.
//
// Seagull reads the lambda from the migration and types each call from its body, with the
// argument in place of pence. price_pence is a UInt32; ClickHouse multiplies it by 110
// into a UInt64, the next wider type, and intDiv keeps that, so TotalPence is a uint64,
// as the server returns. The call reads no table, so the query stays a read, and a store
// built with NewWithReplica sends it to the reader.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the priced menu.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
queries := store.New(database)
dishes := []store.AddDishParams{
{ID: 1, Dish: "cod and chips", PricePence: 850},
{ID: 2, Dish: "haddock and chips", PricePence: 920},
{ID: 3, Dish: "mushy peas", PricePence: 150},
}
for _, dish := range dishes {
if err := queries.AddDish(ctx, dish); err != nil {
return fmt.Errorf("adding %s: %w", dish.Dish, err)
}
}
menu, err := queries.MenuWithService(ctx)
if err != nil {
return fmt.Errorf("pricing the menu: %w", err)
}
for _, dish := range menu {
fmt.Fprintf(output, "%-18s %4dp, with service %dp\n", dish.Dish, dish.PricePence, dish.TotalPence)
}
return nil
}
main.go
// Command 030_functions_in_migrations applies its migrations to a ClickHouse database,
// then runs the example in example.go: a chip shop menu is priced with a service charge
// worked out by a SQL function the migrations define.
//
// Run it with `go run .` against a local server, or `go run . -dsn <dsn>` to choose the
// database; give it an empty one. The function belongs to the server, so the migration
// replaces any with_service function already there.
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{}}}
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/menu.sql.go
// Code generated by sgl. DO NOT EDIT.
package store
import (
"context"
"github.com/ClickHouse/clickhouse-go/v2"
)
const addDishSQL = `INSERT INTO menu (id, dish, price_pence) VALUES ({p_id:UInt64}, {p_dish:String}, {p_price_pence:UInt32});`
type AddDishParams struct {
ID uint64
Dish string
PricePence uint32
}
func (queries *Queries) AddDish(ctx context.Context, params AddDishParams) error {
_, err := queries.writer.ExecContext(ctx, addDishSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_dish", sglClickHouseShapedValue(params.Dish, sglClickHouseShapes[1])), clickhouse.Named("p_price_pence", sglClickHouseShapedValue(params.PricePence, sglClickHouseShapes[0])))
return err
}
const menuWithServiceSQL = `SELECT dish, price_pence, with_service(price_pence) AS total_pence
FROM menu
ORDER BY id;`
type MenuWithServiceRow struct {
Dish string `json:"dish"`
PricePence uint32 `json:"price_pence"`
TotalPence uint64 `json:"total_pence"`
}
func (queries *Queries) MenuWithService(ctx context.Context) ([]MenuWithServiceRow, error) {
rows, err := queries.reader.QueryContext(ctx, menuWithServiceSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []MenuWithServiceRow
if rows.Next() {
var row MenuWithServiceRow
scanArgs := []any{&row.Dish, &row.PricePence, &row.TotalPence}
for more := true; more; more = rows.Next() {
row = MenuWithServiceRow{}
if err := rows.Scan(scanArgs...); err != nil {
return nil, err
}
results = append(results, row)
}
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
internal/store/models.go
// Code generated by sgl. DO NOT EDIT.
package store
type Menu struct {
ID uint64 `json:"id"`
Dish string `json:"dish"`
PricePence uint32 `json:"price_pence"`
}
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
cod and chips 850p, with service 935p
haddock and chips 920p, with service 1012p
mushy peas 150p, with service 165p
Also on other engines
PostgreSQLSQL and PL/pgSQL functions created in a migration type the queries that call them, and whether they change data, read from their bodies or stated by a sgl.migration(readonly:) line, decides whether each query goes to the writer or the reader.TimescaleDBSQL and PL/pgSQL functions created in a migration type the queries that call them, and whether they change data, read from their bodies or stated by a sgl.migration(readonly:) line, decides whether each query goes to the writer or the reader.MySQLStored functions created in a migration type the queries that call them, and their data-access characteristic, body or a sgl.migration(readonly:) line decides whether each query goes to the writer or the reader.MariaDBStored functions created in a migration type the queries that call them, and their data-access characteristic, body or a sgl.migration(readonly:) line decides whether each query goes to the writer or the reader.