Compare · 005

From SQL types to Go types: TimescaleDB and SQLite

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

Compare TimescaleDB withSQLitePostgreSQLMySQLMariaDBDuckDBClickHouse

Configuration

sgl.yamldiffers
TimescaleDB
SQLite
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: landings
4 - name: landings
5 dialect: postgres
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
11 driver: database/sql

Migrations

db/migrations/0001_landings.up.sqldiffers
TimescaleDB
SQLite
1CREATE TABLE landings (
1CREATE TABLE landings (
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
2 id INTEGER PRIMARY KEY,
3 catch_id UUID NOT NULL,
3 species TEXT NOT NULL,
4 species TEXT NOT NULL,
4 boxes SMALLINT NOT NULL,
5 boxes SMALLINT NOT NULL,
5 fish INTEGER NOT NULL,
6 fish INTEGER NOT NULL,
6 weight_g BIGINT NOT NULL,
7 weight_g BIGINT NOT NULL,
7 length_cm REAL NOT NULL,
8 length_cm REAL NOT NULL,
8 on_ice BOOLEAN NOT NULL,
9 sea_temp DOUBLE PRECISION NOT NULL,
9 landed_at DATETIME NOT NULL,
10 price NUMERIC(8, 2) NOT NULL,
10 tag BLOB NOT NULL,
11 on_ice BOOLEAN NOT NULL,
12 landed_on DATE NOT NULL,
13 landed_at TIMESTAMPTZ NOT NULL,
14 harbour_ip INET NOT NULL,
15 tag BYTEA NOT NULL,
16 notes TEXT
11 notes TEXT
17);
12);
db/migrations/0001_landings.down.sqlidentical
TimescaleDB
SQLite
1DROP TABLE landings;
1DROP TABLE landings;

Queries

RecordLandingdiffers
TimescaleDB · db/queries/landings.sql
SQLite · db/queries/landings.sql
1-- sgl.query(RecordLanding, one)
1-- sgl.query(RecordLanding, one)
2INSERT INTO landings (catch_id, species, boxes, fish, weight_g, length_cm, sea_temp, price, on_ice, landed_on, landed_at, harbour_ip, tag, notes)
2INSERT INTO landings (species, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes)
3VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
3VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
4RETURNING id;
4RETURNING id;
GetLandingdiffers
TimescaleDB · 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, sea_temp, price, on_ice, landed_on, landed_at, harbour_ip, tag, notes
2SELECT id, species, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes
3FROM landings
3FROM landings
4WHERE id = $1;
4WHERE id = ?;

What sgl generates

RecordLandingdiffers
TimescaleDB
SQLite
1func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) (RecordLandingRow, error)
1func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) (RecordLandingRow, error)
2
2
3type RecordLandingParams struct {
3type RecordLandingParams struct {
4 CatchID uuid.UUID
4 Species string
5 Species string
5 Boxes int64
6 Boxes int16
6 Fish int64
7 Fish int32
7 WeightG int64
8 WeightG int64
8 LengthCm float64
9 LengthCm float32
9 OnIce bool
10 SeaTemp float64
10 LandedAt time.Time
11 Price apd.Decimal
11 Tag []byte
12 OnIce bool
12 Notes *string
13 LandedOn time.Time
14 LandedAt time.Time
15 HarbourIP any
16 Tag []byte
17 Notes *string
18}
13}
19
14
20type RecordLandingRow struct {
15type RecordLandingRow struct {
21 ID int64 `json:"id"`
16 ID int64 `json:"id"`
22}
17}
GetLandingdiffers
TimescaleDB
SQLite
1func (queries *Queries) GetLanding(ctx context.Context, id int64) (GetLandingRow, error)
1func (queries *Queries) GetLanding(ctx context.Context, id int64) (GetLandingRow, error)
2
2
3type GetLandingRow struct {
3type GetLandingRow struct {
4 ID int64 `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 int16 `json:"boxes"`
7 Fish int64 `json:"fish"`
8 Fish int32 `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 SeaTemp float64 `json:"sea_temp"`
11 LandedAt time.Time `json:"landed_at"`
12 Price apd.Decimal `json:"price"`
12 Tag []byte `json:"tag"`
13 OnIce bool `json:"on_ice"`
13 Notes *string `json:"notes"`
14 LandedOn time.Time `json:"landed_on"`
15 LandedAt time.Time `json:"landed_at"`
16 HarbourIP any `json:"harbour_ip"`
17 Tag []byte `json:"tag"`
18 Notes *string `json:"notes"`
19}
14}

The program

example.godiffers
TimescaleDB
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 "github.com/cockroachdb/apd/v3"
10 "seagull.sh/seagull/examples/sqlite/005_type_mapping/internal/store"
11 "github.com/google/uuid"
12
13 "seagull.sh/seagull/examples/postgres/005_type_mapping/internal/store"
14)
11)
15
12
16// 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
17// each column of db/migrations. PostgreSQL's types are exact, so each maps by name:
14// each column. SQLite stores every value as one of five storage classes, so Seagull maps
18//
15// the type each column declares in db/migrations. SMALLINT, INTEGER and BIGINT all have
19// - SMALLINT, INTEGER and BIGINT give int16, int32 and int64;
16// INTEGER affinity, which SQLite stores in up to eight bytes whatever width the column
20// - REAL is single precision and gives float32, while DOUBLE PRECISION gives float64;
17// declares, so each gives int64, as the INTEGER PRIMARY KEY rowid does. REAL gives
21// - NUMERIC gives apd.Decimal from github.com/cockroachdb/apd/v3, which keeps money
18// float64, and DATETIME gives time.Time, which the driver parses back from the text it
22// exact, and UUID gives uuid.UUID from github.com/google/uuid;
19// stored. SQLite has no exact decimal type, so money is best kept as INTEGER pence; a
23// - DATE and TIMESTAMPTZ give time.Time, and BYTEA gives []byte;
20// DECIMAL column maps to apd.Decimal from github.com/cockroachdb/apd/v3.
24// - a type Seagull has no mapping for, such as INET, gives any, and the driver decides
25// what it holds: pgx's database/sql driver hands back the address as a string. Give
26// such a column a go_type override to pin it down.
27//
21//
28// Takes database (*sql.DB) which is a migrated database.
22// Takes database (*sql.DB) which is a migrated database.
29// Takes output (io.Writer) which receives one line per column.
23// Takes output (io.Writer) which receives one line per column.
30//
24//
31// Returns error when a query fails.
25// Returns error when a query fails.
32func run(ctx context.Context, database *sql.DB, output io.Writer) error {
26func run(ctx context.Context, database *sql.DB, output io.Writer) error {
33 queries := store.New(database)
27 queries := store.New(database)
34
28
35 recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{
29 recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{
36 CatchID: uuid.MustParse("6f1c0b8e-4b2a-4c55-9d7e-2a3f5e8c1d40"),
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,
35 OnIce: true,
42 SeaTemp: 12.25,
36 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
43 Price: *apd.New(41_280, -2),
37 Tag: []byte{0x04, 0xa2, 0x19, 0xf3},
44 OnIce: true,
38 Notes: new("landed at Newlyn"),
45 LandedOn: time.Date(2026, time.May, 1, 0, 0, 0, 0, time.UTC),
46 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
47 HarbourIP: "192.0.2.17",
48 Tag: []byte{0x04, 0xa2, 0x19, 0xf3},
49 Notes: new("landed at Newlyn"),
50 })
39 })
51 if err != nil {
40 if err != nil {
52 return fmt.Errorf("recording a landing: %w", err)
41 return fmt.Errorf("recording a landing: %w", err)
53 }
42 }
54
43
55 landing, err := queries.GetLanding(ctx, recorded.ID)
44 landing, err := queries.GetLanding(ctx, recorded.ID)
56 if err != nil {
45 if err != nil {
57 return fmt.Errorf("getting landing %d: %w", recorded.ID, err)
46 return fmt.Errorf("getting landing %d: %w", recorded.ID, err)
58 }
47 }
59 fmt.Fprintf(output, "id %-12T %d\n", landing.ID, landing.ID)
48 fmt.Fprintf(output, "id %-10T %d\n", landing.ID, landing.ID)
60 fmt.Fprintf(output, "catch_id %-12T %s\n", landing.CatchID, landing.CatchID)
49 fmt.Fprintf(output, "species %-10T %s\n", landing.Species, landing.Species)
61 fmt.Fprintf(output, "species %-12T %s\n", landing.Species, landing.Species)
50 fmt.Fprintf(output, "boxes %-10T %d\n", landing.Boxes, landing.Boxes)
62 fmt.Fprintf(output, "boxes %-12T %d\n", landing.Boxes, landing.Boxes)
51 fmt.Fprintf(output, "fish %-10T %d\n", landing.Fish, landing.Fish)
63 fmt.Fprintf(output, "fish %-12T %d\n", landing.Fish, landing.Fish)
52 fmt.Fprintf(output, "weight_g %-10T %d\n", landing.WeightG, landing.WeightG)
64 fmt.Fprintf(output, "weight_g %-12T %d\n", landing.WeightG, landing.WeightG)
53 fmt.Fprintf(output, "length_cm %-10T %.1f\n", landing.LengthCm, landing.LengthCm)
65 fmt.Fprintf(output, "length_cm %-12T %.1f\n", landing.LengthCm, landing.LengthCm)
54 fmt.Fprintf(output, "on_ice %-10T %t\n", landing.OnIce, landing.OnIce)
66 fmt.Fprintf(output, "sea_temp %-12T %.2f\n", landing.SeaTemp, landing.SeaTemp)
55 fmt.Fprintf(output, "landed_at %-10T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
67 fmt.Fprintf(output, "price %-12T %s\n", landing.Price, landing.Price.String())
68 fmt.Fprintf(output, "on_ice %-12T %t\n", landing.OnIce, landing.OnIce)
69 fmt.Fprintf(output, "landed_on %-12T %s\n", landing.LandedOn, landing.LandedOn.Format(time.DateOnly))
70 // TIMESTAMPTZ arrives in the connection's time zone, so print it in UTC.
71 fmt.Fprintf(output, "landed_at %-12T %s\n", landing.LandedAt, landing.LandedAt.UTC().Format(time.RFC3339))
72 fmt.Fprintf(output, "harbour_ip %-12T %v\n", landing.HarbourIP, landing.HarbourIP)
73 // %T prints []byte as []uint8, since byte is an alias for uint8.
56 // %T prints []byte as []uint8, since byte is an alias for uint8.
74 fmt.Fprintf(output, "tag %-12T % x\n", landing.Tag, landing.Tag)
57 fmt.Fprintf(output, "tag %-10T % x\n", landing.Tag, landing.Tag)
75 fmt.Fprintf(output, "notes %-12T %s\n", landing.Notes, *landing.Notes)
58 fmt.Fprintf(output, "notes %-10T %s\n", landing.Notes, *landing.Notes)
76 return nil
59 return nil
77}
60}

Go type differences

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

QueryFieldTimescaleDBSQLite
RecordLandingparameter boxesint16int64
RecordLandingparameter fishint32int64
RecordLandingparameter length_cmfloat32float64
GetLandingcolumn boxesint16int64
GetLandingcolumn fishint32int64
GetLandingcolumn length_cmfloat32float64