Compare · 005

From SQL types to Go types: SQLite and ClickHouse

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

Compare SQLite withPostgreSQLTimescaleDBMySQLMariaDBDuckDBClickHouse

Configuration

sgl.yamldiffers
SQLite
ClickHouse
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: landings
4 - name: landings
5 dialect: sqlite
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
SQLite
ClickHouse
1CREATE TABLE landings (
1CREATE TABLE landings (
2 id INTEGER PRIMARY KEY,
2 id UInt64,
3 species TEXT NOT NULL,
3 catch_id UUID,
4 boxes SMALLINT NOT NULL,
4 species String,
5 fish INTEGER NOT NULL,
5 boxes UInt16,
6 weight_g BIGINT NOT NULL,
6 fish UInt32,
7 length_cm REAL 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 BLOB NOT NULL,
10 on_ice Bool,
11 notes TEXT
11 landed_at DateTime('UTC'),
12);
12 tag FixedString(4),
13 notes Nullable(String)
14) ENGINE = MergeTree() ORDER BY id;
db/migrations/0001_landings.down.sqlidentical
SQLite
ClickHouse
1DROP TABLE landings;
1DROP TABLE landings;

Queries

RecordLandingdiffers
SQLite · db/queries/landings.sql
ClickHouse · db/queries/landings.sql
1-- sgl.query(RecordLanding, one)
1-- sgl.query(RecordLanding, exec)
2INSERT INTO landings (species, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes)
2INSERT INTO landings (id, catch_id, species, boxes, fish, weight_g, length_cm, price, on_ice, landed_at, tag, notes)
3VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
3VALUES (
4RETURNING id;
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
SQLite · 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
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
SQLite
ClickHouse
1func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) (RecordLandingRow, error)
1func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) error
2
2
3type RecordLandingParams struct {
3type RecordLandingParams struct {
4 ID uint64
5 CatchID string
4 Species string
6 Species string
5 Boxes int64
7 Boxes uint16
6 Fish int64
8 Fish uint32
7 WeightG int64
9 WeightG int64
8 LengthCm float64
10 LengthCm float32
11 Price string
9 OnIce bool
12 OnIce bool
10 LandedAt time.Time
13 LandedAt time.Time
11 Tag []byte
14 Tag string
12 Notes *string
15 Notes *string
13}
16}
14
15type RecordLandingRow struct {
16 ID int64 `json:"id"`
17}
GetLandingdiffers
SQLite
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 int64 `json:"boxes"`
6 Species string `json:"species"`
7 Fish int64 `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 Tag string `json:"tag"`
15 Notes *string `json:"notes"`
14}
16}

The program

example.godiffers
SQLite
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/sqlite/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. SQLite stores every value as one of five storage classes, so Seagull maps
14// each column. ClickHouse types are exact about width and sign, and Seagull follows them:
15// the type each column declares in db/migrations. SMALLINT, INTEGER and BIGINT all have
15// UInt16, UInt32 and UInt64 give uint16, uint32 and uint64, Int64 gives int64, Float32
16// INTEGER affinity, which SQLite stores in up to eight bytes whatever width the column
16// gives float32, and Bool gives bool. DateTime('UTC') gives time.Time; the generated code
17// declares, so each gives int64, as the INTEGER PRIMARY KEY rowid does. REAL gives
17// sends a time as Unix seconds, so the server keeps its moment whatever the zone, and the
18// float64, and DATETIME gives time.Time, which the driver parses back from the text it
18// column's time zone makes it read back in UTC. FixedString(4) and String both give
19// stored. SQLite has no exact decimal type, so money is best kept as INTEGER pence; a
19// string, which in ClickHouse holds any bytes, and Nullable(String) gives *string.
20// DECIMAL column maps to apd.Decimal from github.com/cockroachdb/apd/v3.
21//
20//
21// UUID gives uuid.UUID from github.com/google/uuid and Decimal(9, 2) gives
22// decimal.Decimal from github.com/shopspring/decimal, the types clickhouse-go scans them
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.
26//
22// Takes database (*sql.DB) which is a migrated database.
27// Takes database (*sql.DB) which is a migrated database.
23// Takes output (io.Writer) which receives one line per column.
28// Takes output (io.Writer) which receives one line per column.
24//
29//
25// Returns error when a query fails.
30// Returns error when a query fails.
26func run(ctx context.Context, database *sql.DB, output io.Writer) error {
31func run(ctx context.Context, database *sql.DB, output io.Writer) error {
27 queries := store.New(database)
32 queries := store.New(database)
28
33
29 recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{
34 if err := queries.RecordLanding(ctx, store.RecordLandingParams{
35 ID: 1,
36 CatchID: "5f0c6c7e-8d0e-4b77-9a51-2c4a6f9e3b10",
30 Species: "mackerel",
37 Species: "mackerel",
31 Boxes: 12,
38 Boxes: 12,
32 Fish: 480,
39 Fish: 480,
33 WeightG: 163_200,
40 WeightG: 163_200,
34 LengthCm: 31.5,
41 LengthCm: 31.5,
42 Price: "412.50",
35 OnIce: true,
43 OnIce: true,
36 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
44 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
37 Tag: []byte{0x04, 0xa2, 0x19, 0xf3},
45 Tag: string([]byte{0x04, 0xa2, 0x19, 0xf3}),
38 Notes: new("landed at Newlyn"),
46 Notes: new("landed at Newlyn"),
39 })
47 }); err != nil {
40 if err != nil {
41 return fmt.Errorf("recording a landing: %w", err)
48 return fmt.Errorf("recording a landing: %w", err)
42 }
49 }
43
50
44 landing, err := queries.GetLanding(ctx, recorded.ID)
51 landing, err := queries.GetLanding(ctx, 1)
45 if err != nil {
52 if err != nil {
46 return fmt.Errorf("getting landing %d: %w", recorded.ID, err)
53 return fmt.Errorf("getting landing 1: %w", err)
47 }
54 }
48 fmt.Fprintf(output, "id %-10T %d\n", landing.ID, landing.ID)
55 fmt.Fprintf(output, "id %-16T %d\n", landing.ID, landing.ID)
49 fmt.Fprintf(output, "species %-10T %s\n", landing.Species, landing.Species)
56 fmt.Fprintf(output, "catch_id %-16T %s\n", landing.CatchID, landing.CatchID)
50 fmt.Fprintf(output, "boxes %-10T %d\n", landing.Boxes, landing.Boxes)
57 fmt.Fprintf(output, "species %-16T %s\n", landing.Species, landing.Species)
51 fmt.Fprintf(output, "fish %-10T %d\n", landing.Fish, landing.Fish)
58 fmt.Fprintf(output, "boxes %-16T %d\n", landing.Boxes, landing.Boxes)
52 fmt.Fprintf(output, "weight_g %-10T %d\n", landing.WeightG, landing.WeightG)
59 fmt.Fprintf(output, "fish %-16T %d\n", landing.Fish, landing.Fish)
53 fmt.Fprintf(output, "length_cm %-10T %.1f\n", landing.LengthCm, landing.LengthCm)
60 fmt.Fprintf(output, "weight_g %-16T %d\n", landing.WeightG, landing.WeightG)
54 fmt.Fprintf(output, "on_ice %-10T %t\n", landing.OnIce, landing.OnIce)
61 fmt.Fprintf(output, "length_cm %-16T %.1f\n", landing.LengthCm, landing.LengthCm)
55 fmt.Fprintf(output, "landed_at %-10T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
62 fmt.Fprintf(output, "price %-16T %s\n", landing.Price, landing.Price.StringFixed(2))
56 // %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)
57 fmt.Fprintf(output, "tag %-10T % x\n", landing.Tag, landing.Tag)
64 fmt.Fprintf(output, "landed_at %-16T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
58 fmt.Fprintf(output, "notes %-10T %s\n", landing.Notes, *landing.Notes)
65 fmt.Fprintf(output, "tag %-16T % x\n", landing.Tag, landing.Tag)
66 fmt.Fprintf(output, "notes %-16T %s\n", landing.Notes, *landing.Notes)
59 return nil
67 return nil
60}
68}

Go type differences

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

QueryFieldSQLiteClickHouse
RecordLandingparameter boxesint64uint16
RecordLandingparameter fishint64uint32
RecordLandingparameter length_cmfloat64float32
RecordLandingparameter tag[]bytestring
GetLandingparameter idint64uint64
GetLandingcolumn idint64uint64
GetLandingcolumn boxesint64uint16
GetLandingcolumn fishint64uint32
GetLandingcolumn length_cmfloat64float32
GetLandingcolumn tag[]bytestring