Compare · 005

From SQL types to Go types: ClickHouse and DuckDB

Every file of the ClickHouse and DuckDB versions side by side, with the lines that differ marked. 11 parameter or column types differ in Go.

Compare ClickHouse withSQLitePostgreSQLTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
ClickHouse
DuckDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: landings
4 - name: harbour
5 dialect: clickhouse
5 dialect: duckdb
6 migrations: db/migrations
6 migrations: db/migrations
7 queries: db/queries
7 queries: db/queries
8 go:
8 go:
9 package: store
9 package: store
10 out: internal/store
10 out: internal/store

Migrations

db/migrations/0001_landings.up.sqldiffers
ClickHouse
DuckDB
1CREATE SEQUENCE landing_ids;
2
1CREATE TABLE landings (
3CREATE TABLE landings (
2 id UInt64,
4 id BIGINT PRIMARY KEY DEFAULT nextval('landing_ids'),
3 catch_id UUID,
5 species VARCHAR NOT NULL,
4 species String,
6 quay UTINYINT NOT NULL,
5 boxes UInt16,
7 boxes SMALLINT NOT NULL,
6 fish UInt32,
8 fish INTEGER NOT NULL,
7 weight_g Int64,
9 weight_g BIGINT NOT NULL,
8 length_cm Float32,
10 length_cm DOUBLE NOT NULL,
9 price Decimal(9, 2),
11 water_temp REAL NOT NULL,
10 on_ice Bool,
12 on_ice BOOLEAN NOT NULL,
11 landed_at DateTime('UTC'),
13 landed_at TIMESTAMP NOT NULL,
12 tag FixedString(4),
14 landed_on DATE NOT NULL,
13 notes Nullable(String)
15 catch_id UUID NOT NULL,
14) ENGINE = MergeTree() ORDER BY id;
16 auction_lot HUGEINT NOT NULL,
17 tag BLOB NOT NULL,
18 notes VARCHAR
19);
db/migrations/0001_landings.down.sqldiffers
ClickHouse
DuckDB
1DROP TABLE landings;
1DROP TABLE landings;
2DROP SEQUENCE landing_ids;

Queries

RecordLandingdiffers
ClickHouse · db/queries/landings.sql
DuckDB · db/queries/landings.sql
1-- sgl.query(RecordLanding, exec)
1-- sgl.query(RecordLanding, one)
2INSERT INTO landings (id, catch_id, species, boxes, fish, weight_g, length_cm, price, on_ice, landed_at, tag, notes)
2INSERT INTO landings (species, quay, boxes, fish, weight_g, length_cm, water_temp, on_ice, landed_at, landed_on, catch_id, auction_lot, tag, notes)
3VALUES (
3VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
4 {id:UInt64},
4RETURNING id;
5 toUUID({catch_id:String}),
6 {species:String},
7 {boxes:UInt16},
8 {fish:UInt32},
9 {weight_g:Int64},
10 {length_cm:Float32},
11 toDecimal32({price:String}, 2),
12 {on_ice:Bool},
13 {landed_at:DateTime('UTC')},
14 {tag:FixedString(4)},
15 {notes:Nullable(String)}
16);
GetLandingdiffers
ClickHouse · db/queries/landings.sql
DuckDB · db/queries/landings.sql
1-- sgl.query(GetLanding, one)
1-- sgl.query(GetLanding, one)
2SELECT id, catch_id, species, boxes, fish, weight_g, length_cm, price, on_ice, landed_at, tag, notes
2SELECT id, species, quay, boxes, fish, weight_g, length_cm, water_temp, on_ice, landed_at, landed_on, catch_id, auction_lot, tag, notes
3FROM landings
3FROM landings
4WHERE id = {id:UInt64};
4WHERE id = $1;

What sgl generates

RecordLandingdiffers
ClickHouse
DuckDB
1func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) error
1func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) (RecordLandingRow, error)
2
2
3type RecordLandingParams struct {
3type RecordLandingParams struct {
4 ID uint64
4 Species string
5 CatchID string
5 Quay uint8
6 Species string
6 Boxes int16
7 Boxes uint16
7 Fish int32
8 Fish uint32
8 WeightG int64
9 WeightG int64
9 LengthCm float64
10 LengthCm float32
10 WaterTemp float32
11 Price string
11 OnIce bool
12 OnIce bool
12 LandedAt time.Time
13 LandedAt time.Time
13 LandedOn time.Time
14 Tag string
14 CatchID uuid.UUID
15 Notes *string
15 AuctionLot *big.Int
16 Tag []byte
17 Notes *string
16}
18}
19
20type RecordLandingRow struct {
21 ID int64 `json:"id"`
22}
GetLandingdiffers
ClickHouse
DuckDB
1func (queries *Queries) GetLanding(ctx context.Context, id uint64) (GetLandingRow, error)
1func (queries *Queries) GetLanding(ctx context.Context, id int64) (GetLandingRow, error)
2
2
3type GetLandingRow struct {
3type GetLandingRow struct {
4 ID uint64 `json:"id"`
4 ID int64 `json:"id"`
5 CatchID uuid.UUID `json:"catch_id"`
5 Species string `json:"species"`
6 Species string `json:"species"`
6 Quay uint8 `json:"quay"`
7 Boxes uint16 `json:"boxes"`
7 Boxes int16 `json:"boxes"`
8 Fish uint32 `json:"fish"`
8 Fish int32 `json:"fish"`
9 WeightG int64 `json:"weight_g"`
9 WeightG int64 `json:"weight_g"`
10 LengthCm float32 `json:"length_cm"`
10 LengthCm float64 `json:"length_cm"`
11 Price decimal.Decimal `json:"price"`
11 WaterTemp float32 `json:"water_temp"`
12 OnIce bool `json:"on_ice"`
12 OnIce bool `json:"on_ice"`
13 LandedAt time.Time `json:"landed_at"`
13 LandedAt time.Time `json:"landed_at"`
14 Tag string `json:"tag"`
14 LandedOn time.Time `json:"landed_on"`
15 Notes *string `json:"notes"`
15 CatchID uuid.UUID `json:"catch_id"`
16 AuctionLot *big.Int `json:"auction_lot"`
17 Tag []byte `json:"tag"`
18 Notes *string `json:"notes"`
16}
19}

The program

example.godiffers
ClickHouse
DuckDB
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "database/sql"
6 "errors"
6 "fmt"
7 "fmt"
7 "io"
8 "io"
9 "math/big"
8 "time"
10 "time"
9
11
10 "seagull.sh/seagull/examples/clickhouse/005_type_mapping/internal/store"
12 "github.com/google/uuid"
13
14 "seagull.sh/seagull/examples/duckdb/005_type_mapping/internal/store"
11)
15)
12
16
13// run records a landing of fish and reads it back, printing the Go type sgl chose for
17// run records a landing of fish and reads it back, printing the Go type sgl chose for
14// each column. ClickHouse types are exact about width and sign, and Seagull follows them:
18// each column. DuckDB declares every column's type exactly, and Seagull maps each one:
15// UInt16, UInt32 and UInt64 give uint16, uint32 and uint64, Int64 gives int64, Float32
16// gives float32, and Bool gives bool. DateTime('UTC') gives time.Time; the generated code
17// sends a time as Unix seconds, so the server keeps its moment whatever the zone, and the
18// column's time zone makes it read back in UTC. FixedString(4) and String both give
19// string, which in ClickHouse holds any bytes, and Nullable(String) gives *string.
20//
19//
21// UUID gives uuid.UUID from github.com/google/uuid and Decimal(9, 2) gives
20// - SMALLINT, INTEGER and BIGINT give int16, int32 and int64, and the unsigned UTINYINT
22// decimal.Decimal from github.com/shopspring/decimal, the types clickhouse-go scans them
21// gives uint8;
23// into. A {price:Decimal(9, 2)} placeholder would make the parameter a decimal.Decimal
22// - DOUBLE gives float64, but REAL is DuckDB's 4-byte float, so it gives float32;
24// too; this example passes the price and catch id as text and converts them in SQL with
23// - TIMESTAMP and DATE give time.Time, and UUID gives uuid.UUID from
25// toDecimal32 and toUUID, so it needs nothing beyond the standard library.
24// github.com/google/uuid;
25// - HUGEINT is a 128-bit integer with no Go equivalent, so it gives *big.Int, the type
26// duckdb-go returns, even when the column is NOT NULL;
27// - BLOB gives []byte, and a nullable VARCHAR gives *string.
26//
28//
27// Takes database (*sql.DB) which is a migrated database.
29// Takes database (*sql.DB) which is a migrated database.
28// Takes output (io.Writer) which receives one line per column.
30// Takes output (io.Writer) which receives one line per column.
29//
31//
30// Returns error when a query fails.
32// Returns error when a query fails.
31func run(ctx context.Context, database *sql.DB, output io.Writer) error {
33func run(ctx context.Context, database *sql.DB, output io.Writer) error {
32 queries := store.New(database)
34 queries := store.New(database)
33
35
34 if err := queries.RecordLanding(ctx, store.RecordLandingParams{
36 // The auction lot number has 20 digits, too many for int64 or uint64.
35 ID: 1,
37 lot, ok := new(big.Int).SetString("20260501000000000001", 10)
36 CatchID: "5f0c6c7e-8d0e-4b77-9a51-2c4a6f9e3b10",
38 if !ok {
37 Species: "mackerel",
39 return errors.New("parsing the auction lot number")
38 Boxes: 12,
40 }
39 Fish: 480,
41 recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{
40 WeightG: 163_200,
42 Species: "mackerel",
41 LengthCm: 31.5,
43 Quay: 3,
42 Price: "412.50",
44 Boxes: 12,
43 OnIce: true,
45 Fish: 480,
44 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
46 WeightG: 163_200,
45 Tag: string([]byte{0x04, 0xa2, 0x19, 0xf3}),
47 LengthCm: 31.5,
46 Notes: new("landed at Newlyn"),
48 WaterTemp: 11.25,
47 }); err != nil {
49 OnIce: true,
50 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
51 LandedOn: time.Date(2026, time.May, 1, 0, 0, 0, 0, time.UTC),
52 CatchID: uuid.MustParse("6f1c2a5e-4d3b-4c2a-9e8f-1a2b3c4d5e6f"),
53 AuctionLot: lot,
54 Tag: []byte{0x04, 0xa2, 0x19, 0xf3},
55 Notes: new("landed at Newlyn"),
56 })
57 if err != nil {
48 return fmt.Errorf("recording a landing: %w", err)
58 return fmt.Errorf("recording a landing: %w", err)
49 }
59 }
50
60
51 landing, err := queries.GetLanding(ctx, 1)
61 landing, err := queries.GetLanding(ctx, recorded.ID)
52 if err != nil {
62 if err != nil {
53 return fmt.Errorf("getting landing 1: %w", err)
63 return fmt.Errorf("getting landing %d: %w", recorded.ID, err)
54 }
64 }
55 fmt.Fprintf(output, "id %-16T %d\n", landing.ID, landing.ID)
65 fmt.Fprintf(output, "id %-10T %d\n", landing.ID, landing.ID)
56 fmt.Fprintf(output, "catch_id %-16T %s\n", landing.CatchID, landing.CatchID)
66 fmt.Fprintf(output, "species %-10T %s\n", landing.Species, landing.Species)
57 fmt.Fprintf(output, "species %-16T %s\n", landing.Species, landing.Species)
67 fmt.Fprintf(output, "quay %-10T %d\n", landing.Quay, landing.Quay)
58 fmt.Fprintf(output, "boxes %-16T %d\n", landing.Boxes, landing.Boxes)
68 fmt.Fprintf(output, "boxes %-10T %d\n", landing.Boxes, landing.Boxes)
59 fmt.Fprintf(output, "fish %-16T %d\n", landing.Fish, landing.Fish)
69 fmt.Fprintf(output, "fish %-10T %d\n", landing.Fish, landing.Fish)
60 fmt.Fprintf(output, "weight_g %-16T %d\n", landing.WeightG, landing.WeightG)
70 fmt.Fprintf(output, "weight_g %-10T %d\n", landing.WeightG, landing.WeightG)
61 fmt.Fprintf(output, "length_cm %-16T %.1f\n", landing.LengthCm, landing.LengthCm)
71 fmt.Fprintf(output, "length_cm %-10T %.1f\n", landing.LengthCm, landing.LengthCm)
62 fmt.Fprintf(output, "price %-16T %s\n", landing.Price, landing.Price.StringFixed(2))
72 fmt.Fprintf(output, "water_temp %-10T %.2f\n", landing.WaterTemp, landing.WaterTemp)
63 fmt.Fprintf(output, "on_ice %-16T %t\n", landing.OnIce, landing.OnIce)
73 fmt.Fprintf(output, "on_ice %-10T %t\n", landing.OnIce, landing.OnIce)
64 fmt.Fprintf(output, "landed_at %-16T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
74 fmt.Fprintf(output, "landed_at %-10T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
65 fmt.Fprintf(output, "tag %-16T % x\n", landing.Tag, landing.Tag)
75 fmt.Fprintf(output, "landed_on %-10T %s\n", landing.LandedOn, landing.LandedOn.Format(time.DateOnly))
66 fmt.Fprintf(output, "notes %-16T %s\n", landing.Notes, *landing.Notes)
76 fmt.Fprintf(output, "catch_id %-10T %s\n", landing.CatchID, landing.CatchID)
77 fmt.Fprintf(output, "auction_lot %-10T %s\n", landing.AuctionLot, landing.AuctionLot)
78 // %T prints []byte as []uint8, since byte is an alias for uint8.
79 fmt.Fprintf(output, "tag %-10T % x\n", landing.Tag, landing.Tag)
80 fmt.Fprintf(output, "notes %-10T %s\n", landing.Notes, *landing.Notes)
67 return nil
81 return nil
68}
82}

Go type differences

The same SQL types map to different Go types, because each database reports them differently.

QueryFieldClickHouseDuckDB
RecordLandingparameter catch_idstringuuid.UUID
RecordLandingparameter boxesuint16int16
RecordLandingparameter fishuint32int32
RecordLandingparameter length_cmfloat32float64
RecordLandingparameter tagstring[]byte
GetLandingparameter iduint64int64
GetLandingcolumn iduint64int64
GetLandingcolumn boxesuint16int16
GetLandingcolumn fishuint32int32
GetLandingcolumn length_cmfloat32float64
GetLandingcolumn tagstring[]byte