Migrations at start-up
On this page
On ClickHouse:
- migrate_backend_clickhouse.New() runs each statement of a migration without a transaction and records the migration once all of them succeed
- The ClickHouse backend takes no lock; coordinate start-ups that migrate in parallel outside the migrator
- The history lives in sgl_migrations, an append-only ReplacingMergeTree read with FINAL
- ALTER TABLE ... ADD COLUMN with a DEFAULT adds the column; existing rows read the default
A service that owns its database can bring the schema up to date itself, every time it starts. The migration files are compiled into the binary with go:embed, and a migrator applies whatever the database does not have yet before the program does anything else. There is no separate migration step in the deploy and no files to ship beside the binary, and the schema the code runs against is the one it was generated from: these are the same files sgl generate reads. sqlc leaves applying migrations to another tool; Seagull's migrator is a Go package, seagull.sh/seagull/sdk/migrate, that reads the same files.
The migrations
Two migrations: the first creates a table, the second adds a column to it. They are plain SQL files, numbered in the order they apply, each with a .down.sql beside it to undo it.
db/migrations/0001_boats.up.sql
CREATE TABLE boats (
id UInt64,
name String
) ENGINE = MergeTree() ORDER BY id;
db/migrations/0002_boat_home_port.up.sql
ALTER TABLE boats ADD COLUMN home_port String DEFAULT 'Padstow';
ADD COLUMN with a DEFAULT adds the column without rewriting the table; rows written before it read the default.
Embedding the files
main.go embeds the migrations directory:
//go:embed db/migrations
var migrations embed.FS
The program needs nothing beside it on disk to bring a database up to date, and the files it applies are always the ones it was built with.
Migrating at start-up
Before it touches the schema, the program builds a migrator and calls Up. migrate.NewMigrator takes the database, a backend for the engine, the embedded files and the directory inside them. Up applies every migration not yet recorded in the history table, in version order, and returns how many it applied. On a database that is already up to date it applies none, which is what makes it safe to call on every start.
migrate_backend_clickhouse.New() is the ClickHouse backend. ClickHouse has no transactions, so it runs each statement of a migration on its own and records the migration only once every statement has succeeded. It takes no lock either, so make sure only one process migrates at a time, for example by migrating from one place such as CI, or by coordinating start-ups outside the migrator.
The program shows this by starting twice against the same database. The first start applies both migrations, adds a boat and lists the harbour; the second finds nothing to migrate and goes straight to work. Last, it reads the history with Status.
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"io/fs"
"seagull.sh/seagull/examples/clickhouse/027_migrations_at_startup/internal/store"
"seagull.sh/seagull/sdk/migrate"
"seagull.sh/seagull/sdk/migrate/migrate_backend_clickhouse"
)
// run starts the program twice against the same database. The first start applies both
// migrations before the program uses the schema; the second finds nothing to do.
//
// Takes database (*sql.DB) which is the database to migrate, empty or not.
// Takes migrations (fs.FS) which holds db/migrations, the embed.FS in main.go.
// Takes output (io.Writer) which receives what each start did.
//
// Returns error when migrating or a query fails.
func run(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
boats := []store.AddBoatParams{
{ID: 1, Name: "Kittiwake", HomePort: "Padstow"},
{ID: 2, Name: "Puffin", HomePort: "Padstow"},
}
for _, boat := range boats {
if err := start(ctx, database, migrations, boat, output); err != nil {
return err
}
}
return printHistory(ctx, database, migrations, output)
}
// start is what the program does each time it starts: migrate first, then work. Here the
// work is adding a boat and listing the harbour.
//
// Takes database (*sql.DB) which is the database.
// Takes migrations (fs.FS) which holds the migration files.
// Takes boat (store.AddBoatParams) which is the boat to add.
// Takes output (io.Writer) which receives what happened.
//
// Returns error when migrating or a query fails.
func start(ctx context.Context, database *sql.DB, migrations fs.FS, boat store.AddBoatParams, output io.Writer) error {
// migrate_backend_clickhouse.New() is the ClickHouse backend. It runs each statement of
// a migration without a transaction and records the migration once every statement has
// succeeded; it takes no lock, so only one process should migrate at a time. NewMigrator
// reads the numbered *.up.sql and *.down.sql files from the directory inside migrations.
migrator, err := migrate.NewMigrator(database, migrate_backend_clickhouse.New(), migrations, "db/migrations")
if err != nil {
return err
}
// Up applies every migration not yet in the history, in version order, and returns how
// many it applied. On a database that is up to date it applies none.
applied, err := migrator.Up(ctx)
if err != nil {
return fmt.Errorf("migrating: %w", err)
}
fmt.Fprintf(output, "start-up: applied %d migrations\n", applied)
queries := store.New(database)
if err := queries.AddBoat(ctx, boat); err != nil {
return fmt.Errorf("adding %s: %w", boat.Name, err)
}
listed, err := queries.ListBoats(ctx)
if err != nil {
return fmt.Errorf("listing boats: %w", err)
}
for _, row := range listed {
fmt.Fprintf(output, " %d %-9s of %s\n", row.ID, row.Name, row.HomePort)
}
return nil
}
// printHistory prints the migration history the migrator keeps in the database.
//
// Takes database (*sql.DB) which is the migrated database.
// Takes migrations (fs.FS) which holds the migration files.
// Takes output (io.Writer) which receives the history.
//
// Returns error when the history cannot be read.
func printHistory(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
backend := migrate_backend_clickhouse.New()
migrator, err := migrate.NewMigrator(database, backend, migrations, "db/migrations")
if err != nil {
return err
}
statuses, err := migrator.Status(ctx)
if err != nil {
return fmt.Errorf("reading migration status: %w", err)
}
fmt.Fprintf(output, "history in %s:\n", backend.Names().History)
for _, status := range statuses {
fmt.Fprintf(output, " %04d %-15s applied=%t\n", status.Version, status.Name, status.Applied)
}
return nil
}
What it prints
The first start applied two migrations and the second none, yet both could use the home_port column. The history table, sgl_migrations, records both versions.
start-up: applied 2 migrations
1 Kittiwake of Padstow
start-up: applied 0 migrations
1 Kittiwake of Padstow
2 Puffin of Padstow
history in sgl_migrations:
0001 boats applied=true
0002 boat_home_port applied=true
Try it
Add a third migration, such as 0003_boat_length.up.sql adding a column, and run sgl generate. Then run the program against the database from an earlier run: only the new migration is applied, and the rest of the history is left alone.
The migrations guide covers the migrator further, and the migration lifecycle goes on to rolling back, status and checksums.
Run it locally
From the directory you cloned the Seagull repository into:
cd seagull/examples/clickhouse && go run ./027_migrations_at_startup
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 ./027_migrations_at_startup
Needs a running ClickHouse server: it connects to clickhouse://localhost:9000/default, or pass -dsn with the address of an empty database.
The files
Every file of the example, as it is in the Seagull repository.
sgl.yaml
# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.
version: 1
databases:
- name: harbour
dialect: clickhouse
migrations: db/migrations
queries: db/queries
go:
package: store
out: internal/store
db/migrations/0001_boats.up.sql
CREATE TABLE boats (
id UInt64,
name String
) ENGINE = MergeTree() ORDER BY id;
db/migrations/0002_boat_home_port.up.sql
ALTER TABLE boats ADD COLUMN home_port String DEFAULT 'Padstow';
db/migrations/0001_boats.down.sql
DROP TABLE boats;
db/migrations/0002_boat_home_port.down.sql
ALTER TABLE boats DROP COLUMN home_port;
db/queries/boats.sql
-- sgl.query(AddBoat, exec)
INSERT INTO boats (id, name, home_port) VALUES ({id:UInt64}, {name:String}, {home_port:String});
-- sgl.query(ListBoats, many)
SELECT id, name, home_port FROM boats ORDER BY id;
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"io/fs"
"seagull.sh/seagull/examples/clickhouse/027_migrations_at_startup/internal/store"
"seagull.sh/seagull/sdk/migrate"
"seagull.sh/seagull/sdk/migrate/migrate_backend_clickhouse"
)
// run starts the program twice against the same database. The first start applies both
// migrations before the program uses the schema; the second finds nothing to do.
//
// Takes database (*sql.DB) which is the database to migrate, empty or not.
// Takes migrations (fs.FS) which holds db/migrations, the embed.FS in main.go.
// Takes output (io.Writer) which receives what each start did.
//
// Returns error when migrating or a query fails.
func run(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
boats := []store.AddBoatParams{
{ID: 1, Name: "Kittiwake", HomePort: "Padstow"},
{ID: 2, Name: "Puffin", HomePort: "Padstow"},
}
for _, boat := range boats {
if err := start(ctx, database, migrations, boat, output); err != nil {
return err
}
}
return printHistory(ctx, database, migrations, output)
}
// start is what the program does each time it starts: migrate first, then work. Here the
// work is adding a boat and listing the harbour.
//
// Takes database (*sql.DB) which is the database.
// Takes migrations (fs.FS) which holds the migration files.
// Takes boat (store.AddBoatParams) which is the boat to add.
// Takes output (io.Writer) which receives what happened.
//
// Returns error when migrating or a query fails.
func start(ctx context.Context, database *sql.DB, migrations fs.FS, boat store.AddBoatParams, output io.Writer) error {
// migrate_backend_clickhouse.New() is the ClickHouse backend. It runs each statement of
// a migration without a transaction and records the migration once every statement has
// succeeded; it takes no lock, so only one process should migrate at a time. NewMigrator
// reads the numbered *.up.sql and *.down.sql files from the directory inside migrations.
migrator, err := migrate.NewMigrator(database, migrate_backend_clickhouse.New(), migrations, "db/migrations")
if err != nil {
return err
}
// Up applies every migration not yet in the history, in version order, and returns how
// many it applied. On a database that is up to date it applies none.
applied, err := migrator.Up(ctx)
if err != nil {
return fmt.Errorf("migrating: %w", err)
}
fmt.Fprintf(output, "start-up: applied %d migrations\n", applied)
queries := store.New(database)
if err := queries.AddBoat(ctx, boat); err != nil {
return fmt.Errorf("adding %s: %w", boat.Name, err)
}
listed, err := queries.ListBoats(ctx)
if err != nil {
return fmt.Errorf("listing boats: %w", err)
}
for _, row := range listed {
fmt.Fprintf(output, " %d %-9s of %s\n", row.ID, row.Name, row.HomePort)
}
return nil
}
// printHistory prints the migration history the migrator keeps in the database.
//
// Takes database (*sql.DB) which is the migrated database.
// Takes migrations (fs.FS) which holds the migration files.
// Takes output (io.Writer) which receives the history.
//
// Returns error when the history cannot be read.
func printHistory(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
backend := migrate_backend_clickhouse.New()
migrator, err := migrate.NewMigrator(database, backend, migrations, "db/migrations")
if err != nil {
return err
}
statuses, err := migrator.Status(ctx)
if err != nil {
return fmt.Errorf("reading migration status: %w", err)
}
fmt.Fprintf(output, "history in %s:\n", backend.Names().History)
for _, status := range statuses {
fmt.Fprintf(output, " %04d %-15s applied=%t\n", status.Version, status.Name, status.Applied)
}
return nil
}
main.go
// Command 027_migrations_at_startup opens a ClickHouse database and runs the example in
// example.go, which applies the migrations embedded in the binary each time the program
// starts, then uses the schema they built.
//
// Run it with `go run .` against a local server, or `go run . -dsn <dsn>` to choose the
// database; give it an empty one. A second run against the same database finds nothing to
// migrate.
package main
import (
"context"
"database/sql"
"embed"
"flag"
"log"
"os"
_ "github.com/ClickHouse/clickhouse-go/v2"
)
// migrations holds the migration files, compiled into the binary, so the program needs no
// files beside it to bring a database up to date.
//
//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 the database the DSN names and runs the example against it. The example
// migrates the database itself.
//
// Takes dsn (string) which names the ClickHouse database.
//
// Returns error when the database cannot be opened 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() }()
return run(ctx, database, migrations, os.Stdout)
}
internal/store/boats.sql.go
// Code generated by sgl. DO NOT EDIT.
package store
import (
"context"
"github.com/ClickHouse/clickhouse-go/v2"
)
const addBoatSQL = `INSERT INTO boats (id, name, home_port) VALUES ({p_id:UInt64}, {p_name:String}, {p_home_port:String});`
type AddBoatParams struct {
ID uint64
Name string
HomePort string
}
func (queries *Queries) AddBoat(ctx context.Context, params AddBoatParams) error {
_, err := queries.writer.ExecContext(ctx, addBoatSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_name", sglClickHouseShapedValue(params.Name, sglClickHouseShapes[1])), clickhouse.Named("p_home_port", sglClickHouseShapedValue(params.HomePort, sglClickHouseShapes[1])))
return err
}
const listBoatsSQL = `SELECT id, name, home_port FROM boats ORDER BY id;`
type ListBoatsRow struct {
ID uint64 `json:"id"`
Name string `json:"name"`
HomePort string `json:"home_port"`
}
func (queries *Queries) ListBoats(ctx context.Context) ([]ListBoatsRow, error) {
rows, err := queries.reader.QueryContext(ctx, listBoatsSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []ListBoatsRow
if rows.Next() {
var row ListBoatsRow
scanArgs := []any{&row.ID, &row.Name, &row.HomePort}
for more := true; more; more = rows.Next() {
row = ListBoatsRow{}
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/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 Boats struct {
ID uint64 `json:"id"`
Name string `json:"name"`
HomePort string `json:"home_port"`
}
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
start-up: applied 2 migrations
1 Kittiwake of Padstow
start-up: applied 0 migrations
1 Kittiwake of Padstow
2 Puffin of Padstow
history in sgl_migrations:
0001 boats applied=true
0002 boat_home_port applied=true
Also on other engines
SQLiteThe migration files are embedded in the binary, and each start-up runs migrate.NewMigrator with the SQLite backend and Up, which applies only what the database lacks.PostgreSQLThe migration files are embedded in the binary, and each start-up runs migrate.NewMigrator with the PostgreSQL backend and Up, which applies only what the database lacks.CockroachDBThree replicas start at once and each runs migrate.NewMigrator with the CockroachDB backend and Up; its lock table lets one apply the migrations while the others wait and find nothing left.TimescaleDBThe migration files are embedded in the binary, and each start-up runs migrate.NewMigrator with the PostgreSQL backend and Up, which applies only what the database lacks.MySQLThe migration files are embedded in the binary, and each start-up runs migrate.NewMigrator with the MySQL backend and Up, which applies only what the database lacks.MariaDBThe migration files are embedded in the binary, and each start-up runs migrate.NewMigrator with the MySQL backend and Up, which applies only what the database lacks.DuckDBThe migration files are embedded in the binary, and each start-up runs migrate.NewMigrator with the DuckDB backend and Up, which applies only what the database lacks.