Compare · 005

From SQL types to Go types: ClickHouse and MariaDB

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

Compare ClickHouse withSQLitePostgreSQLTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
ClickHouse
MariaDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: landings
4 - name: landings
5 dialect: clickhouse
5 dialect: mysql
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
MariaDB
1CREATE TABLE landings (
1CREATE TABLE landings (
2 id UInt64,
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3 catch_id UUID,
3 species VARCHAR(50) NOT NULL,
4 species String,
4 boxes SMALLINT NOT NULL,
5 boxes UInt16,
5 fish INT NOT NULL,
6 fish UInt32,
6 weight_g BIGINT NOT NULL,
7 weight_g Int64,
7 length_cm DOUBLE NOT NULL,
8 length_cm Float32,
8 on_ice BOOLEAN NOT NULL,
9 price Decimal(9, 2),
9 landed_at DATETIME NOT NULL,
10 on_ice Bool,
10 tag VARBINARY(16) NOT NULL,
11 landed_at DateTime('UTC'),
11 notes TEXT,
12 tag FixedString(4),
12 berth TINYINT UNSIGNED NOT NULL,
13 notes Nullable(String)
13 price_per_kg DECIMAL(6, 2) NOT NULL
14) ENGINE = MergeTree() ORDER BY id;
14);
db/migrations/0001_landings.down.sqlidentical
ClickHouse
MariaDB
1DROP TABLE landings;
1DROP TABLE landings;

Queries

RecordLandingdiffers
ClickHouse · db/queries/landings.sql
MariaDB · db/queries/landings.sql
1-- sgl.query(RecordLanding, exec)
1-- sgl.query(RecordLanding, execresult)
2INSERT INTO landings (id, catch_id, species, boxes, fish, weight_g, length_cm, price, on_ice, landed_at, tag, notes)
2-- ?11 as sgl.param(price_per_kg, type: text)
3VALUES (
3INSERT INTO landings (species, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes, berth, price_per_kg)
4 {id:UInt64},
4VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
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
MariaDB · 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, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes, berth, price_per_kg
3FROM landings
3FROM landings
4WHERE id = {id:UInt64};
4WHERE id = ?;

What sgl generates

RecordLandingdiffers
ClickHouse
MariaDB
1func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) error
1func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) (sql.Result, error)
2
2
3type RecordLandingParams struct {
3type RecordLandingParams struct {
4 ID uint64
4 Species string
5 CatchID string
5 Boxes int16
6 Species string
6 Fish int32
7 Boxes uint16
7 WeightG int64
8 Fish uint32
8 LengthCm float64
9 WeightG int64
9 OnIce bool
10 LengthCm float32
10 LandedAt time.Time
11 Price string
11 Tag []byte
12 OnIce bool
12 Notes *string
13 LandedAt time.Time
13 Berth uint8
14 Tag string
14 PricePerKg string
15 Notes *string
16}
15}
GetLandingdiffers
ClickHouse
MariaDB
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 Boxes int16 `json:"boxes"`
7 Boxes uint16 `json:"boxes"`
7 Fish int32 `json:"fish"`
8 Fish uint32 `json:"fish"`
8 WeightG int64 `json:"weight_g"`
9 WeightG int64 `json:"weight_g"`
9 LengthCm float64 `json:"length_cm"`
10 LengthCm float32 `json:"length_cm"`
10 OnIce bool `json:"on_ice"`
11 Price decimal.Decimal `json:"price"`
11 LandedAt time.Time `json:"landed_at"`
12 OnIce bool `json:"on_ice"`
12 Tag []byte `json:"tag"`
13 LandedAt time.Time `json:"landed_at"`
13 Notes *string `json:"notes"`
14 Tag string `json:"tag"`
14 Berth uint8 `json:"berth"`
15 Notes *string `json:"notes"`
15 PricePerKg apd.Decimal `json:"price_per_kg"`
16}
16}

The program

example.godiffers
ClickHouse
MariaDB
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "database/sql"
6 "fmt"
6 "fmt"
7 "io"
7 "io"
8 "time"
8 "time"
9
9
10 "seagull.sh/seagull/examples/clickhouse/005_type_mapping/internal/store"
10 "seagull.sh/seagull/examples/mysql/005_type_mapping/internal/store"
11)
11)
12
12
13// run records a landing of fish and reads it back, printing the Go type sgl chose for
13// 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:
14// each column. Seagull maps the type each column declares in db/migrations: SMALLINT, INT
15// UInt16, UInt32 and UInt64 give uint16, uint32 and uint64, Int64 gives int64, Float32
15// and BIGINT give int16, int32 and int64, and an UNSIGNED integer gives the unsigned Go
16// gives float32, and Bool gives bool. DateTime('UTC') gives time.Time; the generated code
16// type of the same width, so TINYINT UNSIGNED is uint8. DOUBLE gives float64, BOOLEAN
17// sends a time as Unix seconds, so the server keeps its moment whatever the zone, and the
17// (MySQL's TINYINT(1)) gives bool, DATETIME gives time.Time when the DSN sets
18// column's time zone makes it read back in UTC. FixedString(4) and String both give
18// parseTime=true, and VARBINARY gives []byte.
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// DECIMAL gives apd.Decimal from github.com/cockroachdb/apd/v3, which holds the exact
22// decimal.Decimal from github.com/shopspring/decimal, the types clickhouse-go scans them
21// value. The insert binds the price as text instead, through sgl.param(price_per_kg,
23// into. A {price:Decimal(9, 2)} placeholder would make the parameter a decimal.Decimal
22// type: text), and MySQL converts "4.20" to DECIMAL(6, 2) without rounding.
24// too; this example passes the price and catch id as text and converts them in SQL with
25// toDecimal32 and toUUID, so it needs nothing beyond the standard library.
26//
23//
27// Takes database (*sql.DB) which is a migrated database.
24// Takes database (*sql.DB) which is a migrated database.
28// Takes output (io.Writer) which receives one line per column.
25// Takes output (io.Writer) which receives one line per column.
29//
26//
30// Returns error when a query fails.
27// Returns error when a query fails.
31func run(ctx context.Context, database *sql.DB, output io.Writer) error {
28func run(ctx context.Context, database *sql.DB, output io.Writer) error {
32 queries := store.New(database)
29 queries := store.New(database)
33
30
34 if err := queries.RecordLanding(ctx, store.RecordLandingParams{
31 result, err := queries.RecordLanding(ctx, store.RecordLandingParams{
35 ID: 1,
32 Species: "mackerel",
36 CatchID: "5f0c6c7e-8d0e-4b77-9a51-2c4a6f9e3b10",
33 Boxes: 12,
37 Species: "mackerel",
34 Fish: 480,
38 Boxes: 12,
35 WeightG: 163_200,
39 Fish: 480,
36 LengthCm: 31.5,
40 WeightG: 163_200,
37 OnIce: true,
41 LengthCm: 31.5,
38 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
42 Price: "412.50",
39 Tag: []byte{0x04, 0xa2, 0x19, 0xf3},
43 OnIce: true,
40 Notes: new("landed at Newlyn"),
44 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
41 Berth: 7,
45 Tag: string([]byte{0x04, 0xa2, 0x19, 0xf3}),
42 PricePerKg: "4.20",
46 Notes: new("landed at Newlyn"),
43 })
47 }); err != nil {
44 if err != nil {
48 return fmt.Errorf("recording a landing: %w", err)
45 return fmt.Errorf("recording a landing: %w", err)
49 }
46 }
47 id, err := result.LastInsertId()
48 if err != nil {
49 return fmt.Errorf("reading the landing's id: %w", err)
50 }
50
51
51 landing, err := queries.GetLanding(ctx, 1)
52 landing, err := queries.GetLanding(ctx, id)
52 if err != nil {
53 if err != nil {
53 return fmt.Errorf("getting landing 1: %w", err)
54 return fmt.Errorf("getting landing %d: %w", id, err)
54 }
55 }
55 fmt.Fprintf(output, "id %-16T %d\n", landing.ID, landing.ID)
56 fmt.Fprintf(output, "id %-12T %d\n", landing.ID, landing.ID)
56 fmt.Fprintf(output, "catch_id %-16T %s\n", landing.CatchID, landing.CatchID)
57 fmt.Fprintf(output, "species %-12T %s\n", landing.Species, landing.Species)
57 fmt.Fprintf(output, "species %-16T %s\n", landing.Species, landing.Species)
58 fmt.Fprintf(output, "boxes %-12T %d\n", landing.Boxes, landing.Boxes)
58 fmt.Fprintf(output, "boxes %-16T %d\n", landing.Boxes, landing.Boxes)
59 fmt.Fprintf(output, "fish %-12T %d\n", landing.Fish, landing.Fish)
59 fmt.Fprintf(output, "fish %-16T %d\n", landing.Fish, landing.Fish)
60 fmt.Fprintf(output, "weight_g %-12T %d\n", landing.WeightG, landing.WeightG)
60 fmt.Fprintf(output, "weight_g %-16T %d\n", landing.WeightG, landing.WeightG)
61 fmt.Fprintf(output, "length_cm %-12T %.1f\n", landing.LengthCm, landing.LengthCm)
61 fmt.Fprintf(output, "length_cm %-16T %.1f\n", landing.LengthCm, landing.LengthCm)
62 fmt.Fprintf(output, "on_ice %-12T %t\n", landing.OnIce, landing.OnIce)
62 fmt.Fprintf(output, "price %-16T %s\n", landing.Price, landing.Price.StringFixed(2))
63 fmt.Fprintf(output, "landed_at %-12T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
63 fmt.Fprintf(output, "on_ice %-16T %t\n", landing.OnIce, landing.OnIce)
64 // %T prints []byte as []uint8, since byte is an alias for uint8.
64 fmt.Fprintf(output, "landed_at %-16T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
65 fmt.Fprintf(output, "tag %-12T % x\n", landing.Tag, landing.Tag)
65 fmt.Fprintf(output, "tag %-16T % x\n", landing.Tag, landing.Tag)
66 fmt.Fprintf(output, "notes %-12T %s\n", landing.Notes, *landing.Notes)
66 fmt.Fprintf(output, "notes %-16T %s\n", landing.Notes, *landing.Notes)
67 fmt.Fprintf(output, "berth %-12T %d\n", landing.Berth, landing.Berth)
68 fmt.Fprintf(output, "price_per_kg %-12T %s\n", landing.PricePerKg, landing.PricePerKg.String())
67 return nil
69 return nil
68}
70}

Go type differences

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

QueryFieldClickHouseMariaDB
RecordLandingparameter boxesuint16int16
RecordLandingparameter fishuint32int32
RecordLandingparameter length_cmfloat32float64
RecordLandingparameter tagstring[]byte
GetLandingparameter iduint64int64
GetLandingcolumn iduint64int64
GetLandingcolumn boxesuint16int16
GetLandingcolumn fishuint32int32
GetLandingcolumn length_cmfloat32float64
GetLandingcolumn tagstring[]byte