From SQL types to Go types: TimescaleDB and PostgreSQL
TimescaleDB and PostgreSQL run the same files for this example: the SQL and the Go are identical.
Configuration
sgl.yamlidentical
TimescaleDB
PostgreSQL
1
version: 11
version: 12
2
3
databases:3
databases:4
- name: landings4
- name: landings5
dialect: postgres5
dialect: postgres6
migrations: db/migrations6
migrations: db/migrations7
queries: db/queries7
queries: db/queries8
go:8
go:9
package: store9
package: store10
out: internal/store10
out: internal/store11
driver: database/sql11
driver: database/sqlMigrations
db/migrations/0001_landings.up.sqlidentical
TimescaleDB
PostgreSQL
1
CREATE TABLE landings (1
CREATE TABLE landings (2
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,2
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,3
catch_id UUID NOT NULL,3
catch_id UUID NOT NULL,4
species TEXT NOT NULL,4
species TEXT NOT NULL,5
boxes SMALLINT NOT NULL,5
boxes SMALLINT NOT NULL,6
fish INTEGER NOT NULL,6
fish INTEGER NOT NULL,7
weight_g BIGINT NOT NULL,7
weight_g BIGINT NOT NULL,8
length_cm REAL NOT NULL,8
length_cm REAL NOT NULL,9
sea_temp DOUBLE PRECISION NOT NULL,9
sea_temp DOUBLE PRECISION NOT NULL,10
price NUMERIC(8, 2) NOT NULL,10
price NUMERIC(8, 2) NOT NULL,11
on_ice BOOLEAN NOT NULL,11
on_ice BOOLEAN NOT NULL,12
landed_on DATE NOT NULL,12
landed_on DATE NOT NULL,13
landed_at TIMESTAMPTZ NOT NULL,13
landed_at TIMESTAMPTZ NOT NULL,14
harbour_ip INET NOT NULL,14
harbour_ip INET NOT NULL,15
tag BYTEA NOT NULL,15
tag BYTEA NOT NULL,16
notes TEXT16
notes TEXT17
);17
);db/migrations/0001_landings.down.sqlidentical
TimescaleDB
PostgreSQL
1
DROP TABLE landings;1
DROP TABLE landings;Queries
RecordLandingidentical
TimescaleDB · db/queries/landings.sql
PostgreSQL · db/queries/landings.sql
1
-- sgl.query(RecordLanding, one)1
-- sgl.query(RecordLanding, one)2
INSERT INTO landings (catch_id, species, boxes, fish, weight_g, length_cm, sea_temp, price, on_ice, landed_on, landed_at, harbour_ip, tag, notes)2
INSERT INTO landings (catch_id, species, boxes, fish, weight_g, length_cm, sea_temp, price, on_ice, landed_on, landed_at, harbour_ip, tag, notes)3
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)3
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)4
RETURNING id;4
RETURNING id;GetLandingidentical
TimescaleDB · db/queries/landings.sql
PostgreSQL · db/queries/landings.sql
1
-- sgl.query(GetLanding, one)1
-- sgl.query(GetLanding, one)2
SELECT id, catch_id, species, boxes, fish, weight_g, length_cm, sea_temp, price, on_ice, landed_on, landed_at, harbour_ip, tag, notes2
SELECT id, catch_id, species, boxes, fish, weight_g, length_cm, sea_temp, price, on_ice, landed_on, landed_at, harbour_ip, tag, notes3
FROM landings3
FROM landings4
WHERE id = $1;4
WHERE id = $1;What sgl generates
RecordLandingidentical
TimescaleDB
PostgreSQL
1
func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) (RecordLandingRow, error)1
func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) (RecordLandingRow, error)2
2
3
type RecordLandingParams struct {3
type RecordLandingParams struct {4
CatchID uuid.UUID4
CatchID uuid.UUID5
Species string5
Species string6
Boxes int166
Boxes int167
Fish int327
Fish int328
WeightG int648
WeightG int649
LengthCm float329
LengthCm float3210
SeaTemp float6410
SeaTemp float6411
Price apd.Decimal11
Price apd.Decimal12
OnIce bool12
OnIce bool13
LandedOn time.Time13
LandedOn time.Time14
LandedAt time.Time14
LandedAt time.Time15
HarbourIP any15
HarbourIP any16
Tag []byte16
Tag []byte17
Notes *string17
Notes *string18
}18
}19
19
20
type RecordLandingRow struct {20
type RecordLandingRow struct {21
ID int64 `json:"id"`21
ID int64 `json:"id"`22
}22
}GetLandingidentical
TimescaleDB
PostgreSQL
1
func (queries *Queries) GetLanding(ctx context.Context, id int64) (GetLandingRow, error)1
func (queries *Queries) GetLanding(ctx context.Context, id int64) (GetLandingRow, error)2
2
3
type GetLandingRow struct {3
type GetLandingRow struct {4
ID int64 `json:"id"`4
ID int64 `json:"id"`5
CatchID uuid.UUID `json:"catch_id"`5
CatchID uuid.UUID `json:"catch_id"`6
Species string `json:"species"`6
Species string `json:"species"`7
Boxes int16 `json:"boxes"`7
Boxes int16 `json:"boxes"`8
Fish int32 `json:"fish"`8
Fish int32 `json:"fish"`9
WeightG int64 `json:"weight_g"`9
WeightG int64 `json:"weight_g"`10
LengthCm float32 `json:"length_cm"`10
LengthCm float32 `json:"length_cm"`11
SeaTemp float64 `json:"sea_temp"`11
SeaTemp float64 `json:"sea_temp"`12
Price apd.Decimal `json:"price"`12
Price apd.Decimal `json:"price"`13
OnIce bool `json:"on_ice"`13
OnIce bool `json:"on_ice"`14
LandedOn time.Time `json:"landed_on"`14
LandedOn time.Time `json:"landed_on"`15
LandedAt time.Time `json:"landed_at"`15
LandedAt time.Time `json:"landed_at"`16
HarbourIP any `json:"harbour_ip"`16
HarbourIP any `json:"harbour_ip"`17
Tag []byte `json:"tag"`17
Tag []byte `json:"tag"`18
Notes *string `json:"notes"`18
Notes *string `json:"notes"`19
}19
}The program
example.goidentical
TimescaleDB
PostgreSQL
1
package main1
package main2
2
3
import (3
import (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
"github.com/cockroachdb/apd/v3"11
"github.com/google/uuid"11
"github.com/google/uuid"12
12
13
"seagull.sh/seagull/examples/postgres/005_type_mapping/internal/store"13
"seagull.sh/seagull/examples/postgres/005_type_mapping/internal/store"14
)14
)15
15
16
// run records a landing of fish and reads it back, printing the Go type sgl chose for16
// run records a landing of fish and reads it back, printing the Go type sgl chose for17
// each column of db/migrations. PostgreSQL's types are exact, so each maps by name:17
// each column of db/migrations. PostgreSQL's types are exact, so each maps by name:18
//18
//19
// - SMALLINT, INTEGER and BIGINT give int16, int32 and int64;19
// - SMALLINT, INTEGER and BIGINT give int16, int32 and int64;20
// - REAL is single precision and gives float32, while DOUBLE PRECISION gives float64;20
// - REAL is single precision and gives float32, while DOUBLE PRECISION gives float64;21
// - NUMERIC gives apd.Decimal from github.com/cockroachdb/apd/v3, which keeps money21
// - NUMERIC gives apd.Decimal from github.com/cockroachdb/apd/v3, which keeps money22
// exact, and UUID gives uuid.UUID from github.com/google/uuid;22
// exact, and UUID gives uuid.UUID from github.com/google/uuid;23
// - DATE and TIMESTAMPTZ give time.Time, and BYTEA gives []byte;23
// - DATE and TIMESTAMPTZ give time.Time, and BYTEA gives []byte;24
// - a type Seagull has no mapping for, such as INET, gives any, and the driver decides24
// - a type Seagull has no mapping for, such as INET, gives any, and the driver decides25
// what it holds: pgx's database/sql driver hands back the address as a string. Give25
// what it holds: pgx's database/sql driver hands back the address as a string. Give26
// such a column a go_type override to pin it down.26
// such a column a go_type override to pin it down.27
//27
//28
// Takes database (*sql.DB) which is a migrated database.28
// Takes database (*sql.DB) which is a migrated database.29
// Takes output (io.Writer) which receives one line per column.29
// Takes output (io.Writer) which receives one line per column.30
//30
//31
// Returns error when a query fails.31
// Returns error when a query fails.32
func run(ctx context.Context, database *sql.DB, output io.Writer) error {32
func run(ctx context.Context, database *sql.DB, output io.Writer) error {33
queries := store.New(database)33
queries := store.New(database)34
34
35
recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{35
recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{36
CatchID: uuid.MustParse("6f1c0b8e-4b2a-4c55-9d7e-2a3f5e8c1d40"),36
CatchID: uuid.MustParse("6f1c0b8e-4b2a-4c55-9d7e-2a3f5e8c1d40"),37
Species: "mackerel",37
Species: "mackerel",38
Boxes: 12,38
Boxes: 12,39
Fish: 480,39
Fish: 480,40
WeightG: 163_200,40
WeightG: 163_200,41
LengthCm: 31.5,41
LengthCm: 31.5,42
SeaTemp: 12.25,42
SeaTemp: 12.25,43
Price: *apd.New(41_280, -2),43
Price: *apd.New(41_280, -2),44
OnIce: true,44
OnIce: true,45
LandedOn: time.Date(2026, time.May, 1, 0, 0, 0, 0, time.UTC),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),46
LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),47
HarbourIP: "192.0.2.17",47
HarbourIP: "192.0.2.17",48
Tag: []byte{0x04, 0xa2, 0x19, 0xf3},48
Tag: []byte{0x04, 0xa2, 0x19, 0xf3},49
Notes: new("landed at Newlyn"),49
Notes: new("landed at Newlyn"),50
})50
})51
if err != nil {51
if err != nil {52
return fmt.Errorf("recording a landing: %w", err)52
return fmt.Errorf("recording a landing: %w", err)53
}53
}54
54
55
landing, err := queries.GetLanding(ctx, recorded.ID)55
landing, err := queries.GetLanding(ctx, recorded.ID)56
if err != nil {56
if err != nil {57
return fmt.Errorf("getting landing %d: %w", recorded.ID, err)57
return fmt.Errorf("getting landing %d: %w", recorded.ID, err)58
}58
}59
fmt.Fprintf(output, "id %-12T %d\n", landing.ID, landing.ID)59
fmt.Fprintf(output, "id %-12T %d\n", landing.ID, landing.ID)60
fmt.Fprintf(output, "catch_id %-12T %s\n", landing.CatchID, landing.CatchID)60
fmt.Fprintf(output, "catch_id %-12T %s\n", landing.CatchID, landing.CatchID)61
fmt.Fprintf(output, "species %-12T %s\n", landing.Species, landing.Species)61
fmt.Fprintf(output, "species %-12T %s\n", landing.Species, landing.Species)62
fmt.Fprintf(output, "boxes %-12T %d\n", landing.Boxes, landing.Boxes)62
fmt.Fprintf(output, "boxes %-12T %d\n", landing.Boxes, landing.Boxes)63
fmt.Fprintf(output, "fish %-12T %d\n", landing.Fish, landing.Fish)63
fmt.Fprintf(output, "fish %-12T %d\n", landing.Fish, landing.Fish)64
fmt.Fprintf(output, "weight_g %-12T %d\n", landing.WeightG, landing.WeightG)64
fmt.Fprintf(output, "weight_g %-12T %d\n", landing.WeightG, landing.WeightG)65
fmt.Fprintf(output, "length_cm %-12T %.1f\n", landing.LengthCm, landing.LengthCm)65
fmt.Fprintf(output, "length_cm %-12T %.1f\n", landing.LengthCm, landing.LengthCm)66
fmt.Fprintf(output, "sea_temp %-12T %.2f\n", landing.SeaTemp, landing.SeaTemp)66
fmt.Fprintf(output, "sea_temp %-12T %.2f\n", landing.SeaTemp, landing.SeaTemp)67
fmt.Fprintf(output, "price %-12T %s\n", landing.Price, landing.Price.String())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)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))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.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))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)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.73
// %T prints []byte as []uint8, since byte is an alias for uint8.74
fmt.Fprintf(output, "tag %-12T % x\n", landing.Tag, landing.Tag)74
fmt.Fprintf(output, "tag %-12T % x\n", landing.Tag, landing.Tag)75
fmt.Fprintf(output, "notes %-12T %s\n", landing.Notes, *landing.Notes)75
fmt.Fprintf(output, "notes %-12T %s\n", landing.Notes, *landing.Notes)76
return nil76
return nil77
}77
}