Compare · 005

From SQL types to Go types: DuckDB and SQLite

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

Compare DuckDB withSQLite PostgreSQL TimescaleDB MySQL MariaDB ClickHouse

Configuration

sgl.yamldiffers

DuckDB
SQLite
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: harbour
4  - name: landings
5    dialect: duckdb
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

DuckDB
SQLite
1CREATE SEQUENCE landing_ids;
2
3CREATE TABLE landings (
1CREATE TABLE landings (
  id          BIGINT    PRIMARY KEY DEFAULT nextval('landing_ids'),
  id         INTEGER  PRIMARY KEY,
  species     VARCHAR   NOT NULL,
  species    TEXT     NOT NULL,
  quay        UTINYINT  NOT NULL,
  boxes      SMALLINT NOT NULL,
  boxes       SMALLINT  NOT NULL,
  fish       INTEGER  NOT NULL,
  fish        INTEGER   NOT NULL,
  weight_g   BIGINT   NOT NULL,
  weight_g    BIGINT    NOT NULL,
  length_cm  REAL     NOT NULL,
  length_cm   DOUBLE    NOT NULL,
  on_ice     BOOLEAN  NOT NULL,
  water_temp  REAL      NOT NULL,
  landed_at  DATETIME NOT NULL,
  on_ice      BOOLEAN   NOT NULL,
  tag        BLOB     NOT NULL,
  landed_at   TIMESTAMP NOT NULL,
  notes      TEXT
  landed_on   DATE      NOT NULL,
  catch_id    UUID      NOT NULL,
  auction_lot HUGEINT   NOT NULL,
  tag         BLOB      NOT NULL,
  notes       VARCHAR
19);
12);

db/migrations/0001_landings.down.sqldiffers

DuckDB
SQLite
1DROP TABLE landings;
1DROP TABLE landings;
2DROP SEQUENCE landing_ids;

Queries

RecordLandingdiffers

DuckDB · db/queries/landings.sql
SQLite · db/queries/landings.sql
1-- sgl.query(RecordLanding, one)
1-- sgl.query(RecordLanding, one)
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)
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

DuckDB · db/queries/landings.sql
SQLite · db/queries/landings.sql
1-- sgl.query(GetLanding, one)
1-- sgl.query(GetLanding, one)
2SELECT id, species, quay, boxes, fish, weight_g, length_cm, water_temp, on_ice, landed_at, landed_on, catch_id, auction_lot, 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

DuckDB
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	Species    string
4	Species  string
5	Quay       uint8
5	Boxes    int64
6	Boxes      int16
6	Fish     int64
7	Fish       int32
7	WeightG  int64
8	WeightG    int64
8	LengthCm float64
9	LengthCm   float64
9	OnIce    bool
10	WaterTemp  float32
10	LandedAt time.Time
11	OnIce      bool
11	Tag      []byte
12	LandedAt   time.Time
12	Notes    *string
13	LandedOn   time.Time
14	CatchID    uuid.UUID
15	AuctionLot *big.Int
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

DuckDB
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 {
ID         int64     `json:"id"`
ID       int64     `json:"id"`
Species    string    `json:"species"`
Species  string    `json:"species"`
Quay       uint8     `json:"quay"`
Boxes    int64     `json:"boxes"`
Boxes      int16     `json:"boxes"`
Fish     int64     `json:"fish"`
Fish       int32     `json:"fish"`
WeightG  int64     `json:"weight_g"`
WeightG    int64     `json:"weight_g"`
LengthCm float64   `json:"length_cm"`
LengthCm   float64   `json:"length_cm"`
OnIce    bool      `json:"on_ice"`
WaterTemp  float32   `json:"water_temp"`
LandedAt time.Time `json:"landed_at"`
OnIce      bool      `json:"on_ice"`
Tag      []byte    `json:"tag"`
LandedAt   time.Time `json:"landed_at"`
Notes    *string   `json:"notes"`
LandedOn   time.Time `json:"landed_on"`
CatchID    uuid.UUID `json:"catch_id"`
AuctionLot *big.Int  `json:"auction_lot"`
Tag        []byte    `json:"tag"`
Notes      *string   `json:"notes"`
19}
14}

The program

example.godiffers

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

Go type differences

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

Query	Field	DuckDB	SQLite
RecordLanding	parameter `boxes`	`int16`	`int64`
RecordLanding	parameter `fish`	`int32`	`int64`
GetLanding	column `boxes`	`int16`	`int64`
GetLanding	column `fish`	`int32`	`int64`