Compare · 005

From SQL types to Go types: MySQL and ClickHouse

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

Compare MySQL withSQLitePostgreSQLTimescaleDBMariaDBDuckDBClickHouse

Configuration

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

Queries

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

What sgl generates

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

The program

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

Go type differences

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

QueryFieldMySQLClickHouse
RecordLandingparameter boxesint16uint16
RecordLandingparameter fishint32uint32
RecordLandingparameter length_cmfloat64float32
RecordLandingparameter tag[]bytestring
GetLandingparameter idint64uint64
GetLandingcolumn idint64uint64
GetLandingcolumn boxesint16uint16
GetLandingcolumn fishint32uint32
GetLandingcolumn length_cmfloat64float32
GetLandingcolumn tag[]bytestring