Compare · 005

From SQL types to Go types: ClickHouse and SQLite

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

Compare ClickHouse withSQLite PostgreSQL TimescaleDB MySQL MariaDB DuckDB

Configuration

sgl.yamldiffers

ClickHouse
SQLite
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: landings
4  - name: landings
5    dialect: clickhouse
5    dialect: sqlite
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
SQLite
1CREATE TABLE landings (
1CREATE TABLE landings (
  id        UInt64,
  id         INTEGER  PRIMARY KEY,
  catch_id  UUID,
  species    TEXT     NOT NULL,
  species   String,
  boxes      SMALLINT NOT NULL,
  boxes     UInt16,
  fish       INTEGER  NOT NULL,
  fish      UInt32,
  weight_g   BIGINT   NOT NULL,
  weight_g  Int64,
  length_cm  REAL     NOT NULL,
  length_cm Float32,
  on_ice     BOOLEAN  NOT NULL,
  price     Decimal(9, 2),
  landed_at  DATETIME NOT NULL,
  on_ice    Bool,
  tag        BLOB     NOT NULL,
  landed_at DateTime('UTC'),
  notes      TEXT
  tag       FixedString(4),
12);
  notes     Nullable(String)
14) ENGINE = MergeTree() ORDER BY id;

db/migrations/0001_landings.down.sqlidentical

ClickHouse
SQLite
1DROP TABLE landings;
1DROP TABLE landings;

Queries

RecordLandingdiffers

ClickHouse · db/queries/landings.sql
SQLite · 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, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes)
3VALUES (
3VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
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
SQLite · 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
3FROM landings
3FROM landings
4WHERE id = {id:UInt64};
4WHERE id = ?;

What sgl generates

RecordLandingdiffers

ClickHouse
SQLite
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
5	CatchID  string
6	Species  string
4	Species  string
7	Boxes    uint16
5	Boxes    int64
8	Fish     uint32
6	Fish     int64
9	WeightG  int64
7	WeightG  int64
10	LengthCm float32
8	LengthCm float64
11	Price    string
12	OnIce    bool
9	OnIce    bool
13	LandedAt time.Time
10	LandedAt time.Time
14	Tag      string
11	Tag      []byte
15	Notes    *string
12	Notes    *string
16}
13}
14
15type RecordLandingRow struct {
16	ID int64 `json:"id"`
17}

GetLandingdiffers

ClickHouse
SQLite
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    int64     `json:"boxes"`
7	Boxes    uint16          `json:"boxes"`
7	Fish     int64     `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"`
15	Notes    *string         `json:"notes"`
16}
14}

The program

example.godiffers

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

Go type differences

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

Query	Field	ClickHouse	SQLite
RecordLanding	parameter `boxes`	`uint16`	`int64`
RecordLanding	parameter `fish`	`uint32`	`int64`
RecordLanding	parameter `length_cm`	`float32`	`float64`
RecordLanding	parameter `tag`	`string`	`[]byte`
GetLanding	parameter `id`	`uint64`	`int64`
GetLanding	column `id`	`uint64`	`int64`
GetLanding	column `boxes`	`uint16`	`int64`
GetLanding	column `fish`	`uint32`	`int64`
GetLanding	column `length_cm`	`float32`	`float64`
GetLanding	column `tag`	`string`	`[]byte`