Compare · 005

From SQL types to Go types: SQLite and DuckDB

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

Compare SQLite withPostgreSQLTimescaleDBMySQLMariaDBDuckDBClickHouse

Configuration

sgl.yamldiffers
SQLite
DuckDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: landings
4 - name: harbour
5 dialect: sqlite
5 dialect: duckdb
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
DuckDB
1CREATE SEQUENCE landing_ids;
2
1CREATE TABLE landings (
3CREATE TABLE landings (
2 id INTEGER PRIMARY KEY,
4 id BIGINT PRIMARY KEY DEFAULT nextval('landing_ids'),
3 species TEXT NOT NULL,
5 species VARCHAR NOT NULL,
4 boxes SMALLINT NOT NULL,
6 quay UTINYINT NOT NULL,
5 fish INTEGER NOT NULL,
7 boxes SMALLINT NOT NULL,
6 weight_g BIGINT NOT NULL,
8 fish INTEGER NOT NULL,
7 length_cm REAL NOT NULL,
9 weight_g BIGINT NOT NULL,
8 on_ice BOOLEAN NOT NULL,
10 length_cm DOUBLE NOT NULL,
9 landed_at DATETIME NOT NULL,
11 water_temp REAL NOT NULL,
10 tag BLOB NOT NULL,
12 on_ice BOOLEAN NOT NULL,
11 notes TEXT
13 landed_at TIMESTAMP NOT NULL,
14 landed_on DATE NOT NULL,
15 catch_id UUID NOT NULL,
16 auction_lot HUGEINT NOT NULL,
17 tag BLOB NOT NULL,
18 notes VARCHAR
12);
19);
db/migrations/0001_landings.down.sqldiffers
SQLite
DuckDB
1DROP TABLE landings;
1DROP TABLE landings;
2DROP SEQUENCE landing_ids;

Queries

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

What sgl generates

RecordLandingdiffers
SQLite
DuckDB
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 Species string
4 Species string
5 Boxes int64
5 Quay uint8
6 Fish int64
6 Boxes int16
7 WeightG int64
7 Fish int32
8 LengthCm float64
8 WeightG int64
9 OnIce bool
9 LengthCm float64
10 LandedAt time.Time
10 WaterTemp float32
11 Tag []byte
11 OnIce bool
12 Notes *string
12 LandedAt time.Time
13 LandedOn time.Time
14 CatchID uuid.UUID
15 AuctionLot *big.Int
16 Tag []byte
17 Notes *string
13}
18}
14
19
15type RecordLandingRow struct {
20type RecordLandingRow struct {
16 ID int64 `json:"id"`
21 ID int64 `json:"id"`
17}
22}
GetLandingdiffers
SQLite
DuckDB
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 Species string `json:"species"`
5 Species string `json:"species"`
6 Boxes int64 `json:"boxes"`
6 Quay uint8 `json:"quay"`
7 Fish int64 `json:"fish"`
7 Boxes int16 `json:"boxes"`
8 WeightG int64 `json:"weight_g"`
8 Fish int32 `json:"fish"`
9 LengthCm float64 `json:"length_cm"`
9 WeightG int64 `json:"weight_g"`
10 OnIce bool `json:"on_ice"`
10 LengthCm float64 `json:"length_cm"`
11 LandedAt time.Time `json:"landed_at"`
11 WaterTemp float32 `json:"water_temp"`
12 Tag []byte `json:"tag"`
12 OnIce bool `json:"on_ice"`
13 Notes *string `json:"notes"`
13 LandedAt time.Time `json:"landed_at"`
14 LandedOn time.Time `json:"landed_on"`
15 CatchID uuid.UUID `json:"catch_id"`
16 AuctionLot *big.Int `json:"auction_lot"`
17 Tag []byte `json:"tag"`
18 Notes *string `json:"notes"`
14}
19}

The program

example.godiffers
SQLite
DuckDB
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "database/sql"
6 "errors"
6 "fmt"
7 "fmt"
7 "io"
8 "io"
9 "math/big"
8 "time"
10 "time"
9
11
10 "seagull.sh/seagull/examples/sqlite/005_type_mapping/internal/store"
12 "github.com/google/uuid"
13
14 "seagull.sh/seagull/examples/duckdb/005_type_mapping/internal/store"
11)
15)
12
16
13// run records a landing of fish and reads it back, printing the Go type sgl chose for
17// 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
18// each column. DuckDB declares every column's type exactly, and Seagull maps each one:
15// the type each column declares in db/migrations. SMALLINT, INTEGER and BIGINT all have
16// INTEGER affinity, which SQLite stores in up to eight bytes whatever width the column
17// declares, so each gives int64, as the INTEGER PRIMARY KEY rowid does. REAL gives
18// float64, and DATETIME gives time.Time, which the driver parses back from the text it
19// stored. SQLite has no exact decimal type, so money is best kept as INTEGER pence; a
20// DECIMAL column maps to apd.Decimal from github.com/cockroachdb/apd/v3.
21//
19//
20// - SMALLINT, INTEGER and BIGINT give int16, int32 and int64, and the unsigned UTINYINT
21// gives uint8;
22// - DOUBLE gives float64, but REAL is DuckDB's 4-byte float, so it gives float32;
23// - TIMESTAMP and DATE give time.Time, and UUID gives uuid.UUID from
24// github.com/google/uuid;
25// - HUGEINT is a 128-bit integer with no Go equivalent, so it gives *big.Int, the type
26// duckdb-go returns, even when the column is NOT NULL;
27// - BLOB gives []byte, and a nullable VARCHAR gives *string.
28//
22// Takes database (*sql.DB) which is a migrated database.
29// Takes database (*sql.DB) which is a migrated database.
23// Takes output (io.Writer) which receives one line per column.
30// Takes output (io.Writer) which receives one line per column.
24//
31//
25// Returns error when a query fails.
32// Returns error when a query fails.
26func run(ctx context.Context, database *sql.DB, output io.Writer) error {
33func run(ctx context.Context, database *sql.DB, output io.Writer) error {
27 queries := store.New(database)
34 queries := store.New(database)
28
35
36 // The auction lot number has 20 digits, too many for int64 or uint64.
37 lot, ok := new(big.Int).SetString("20260501000000000001", 10)
38 if !ok {
39 return errors.New("parsing the auction lot number")
40 }
29 recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{
41 recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{
30 Species: "mackerel",
42 Species: "mackerel",
31 Boxes: 12,
43 Quay: 3,
32 Fish: 480,
44 Boxes: 12,
33 WeightG: 163_200,
45 Fish: 480,
34 LengthCm: 31.5,
46 WeightG: 163_200,
35 OnIce: true,
47 LengthCm: 31.5,
36 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
48 WaterTemp: 11.25,
37 Tag: []byte{0x04, 0xa2, 0x19, 0xf3},
49 OnIce: true,
38 Notes: new("landed at Newlyn"),
50 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
51 LandedOn: time.Date(2026, time.May, 1, 0, 0, 0, 0, time.UTC),
52 CatchID: uuid.MustParse("6f1c2a5e-4d3b-4c2a-9e8f-1a2b3c4d5e6f"),
53 AuctionLot: lot,
54 Tag: []byte{0x04, 0xa2, 0x19, 0xf3},
55 Notes: new("landed at Newlyn"),
39 })
56 })
40 if err != nil {
57 if err != nil {
41 return fmt.Errorf("recording a landing: %w", err)
58 return fmt.Errorf("recording a landing: %w", err)
42 }
59 }
43
60
44 landing, err := queries.GetLanding(ctx, recorded.ID)
61 landing, err := queries.GetLanding(ctx, recorded.ID)
45 if err != nil {
62 if err != nil {
46 return fmt.Errorf("getting landing %d: %w", recorded.ID, err)
63 return fmt.Errorf("getting landing %d: %w", recorded.ID, err)
47 }
64 }
48 fmt.Fprintf(output, "id %-10T %d\n", landing.ID, landing.ID)
65 fmt.Fprintf(output, "id %-10T %d\n", landing.ID, landing.ID)
49 fmt.Fprintf(output, "species %-10T %s\n", landing.Species, landing.Species)
66 fmt.Fprintf(output, "species %-10T %s\n", landing.Species, landing.Species)
50 fmt.Fprintf(output, "boxes %-10T %d\n", landing.Boxes, landing.Boxes)
67 fmt.Fprintf(output, "quay %-10T %d\n", landing.Quay, landing.Quay)
51 fmt.Fprintf(output, "fish %-10T %d\n", landing.Fish, landing.Fish)
68 fmt.Fprintf(output, "boxes %-10T %d\n", landing.Boxes, landing.Boxes)
52 fmt.Fprintf(output, "weight_g %-10T %d\n", landing.WeightG, landing.WeightG)
69 fmt.Fprintf(output, "fish %-10T %d\n", landing.Fish, landing.Fish)
53 fmt.Fprintf(output, "length_cm %-10T %.1f\n", landing.LengthCm, landing.LengthCm)
70 fmt.Fprintf(output, "weight_g %-10T %d\n", landing.WeightG, landing.WeightG)
54 fmt.Fprintf(output, "on_ice %-10T %t\n", landing.OnIce, landing.OnIce)
71 fmt.Fprintf(output, "length_cm %-10T %.1f\n", landing.LengthCm, landing.LengthCm)
55 fmt.Fprintf(output, "landed_at %-10T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
72 fmt.Fprintf(output, "water_temp %-10T %.2f\n", landing.WaterTemp, landing.WaterTemp)
73 fmt.Fprintf(output, "on_ice %-10T %t\n", landing.OnIce, landing.OnIce)
74 fmt.Fprintf(output, "landed_at %-10T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
75 fmt.Fprintf(output, "landed_on %-10T %s\n", landing.LandedOn, landing.LandedOn.Format(time.DateOnly))
76 fmt.Fprintf(output, "catch_id %-10T %s\n", landing.CatchID, landing.CatchID)
77 fmt.Fprintf(output, "auction_lot %-10T %s\n", landing.AuctionLot, landing.AuctionLot)
56 // %T prints []byte as []uint8, since byte is an alias for uint8.
78 // %T prints []byte as []uint8, since byte is an alias for uint8.
57 fmt.Fprintf(output, "tag %-10T % x\n", landing.Tag, landing.Tag)
79 fmt.Fprintf(output, "tag %-10T % x\n", landing.Tag, landing.Tag)
58 fmt.Fprintf(output, "notes %-10T %s\n", landing.Notes, *landing.Notes)
80 fmt.Fprintf(output, "notes %-10T %s\n", landing.Notes, *landing.Notes)
59 return nil
81 return nil
60}
82}

Go type differences

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

QueryFieldSQLiteDuckDB
RecordLandingparameter boxesint64int16
RecordLandingparameter fishint64int32
GetLandingcolumn boxesint64int16
GetLandingcolumn fishint64int32