005 From SQL types to Go types on PostgreSQL

On PostgreSQL:
$1 placeholders
Every integer type maps by its width: SMALLINT, INTEGER and BIGINT give int16, int32 and int64
REAL is single precision and maps to float32; DOUBLE PRECISION maps to float64
NUMERIC maps to apd.Decimal and UUID to uuid.UUID, so example.go imports both packages
DATE and TIMESTAMPTZ map to time.Time; TIMESTAMPTZ arrives in the connection's time zone
INET has no mapping and arrives as any, which pgx's database/sql driver fills with a string

Seagull reads each column's declared type from your migrations and picks the Go type that holds it without loss. Integers keep the width and sign the engine stores, floats their precision, exact decimals become a decimal type rather than a float, dates and times become time.Time, and a column that can be NULL becomes a pointer. You never choose a Go type for a column by hand, and you never scan a NUMERIC into a float64 by accident.

This example records one landing of fish, with a column of each common kind, reads it back, and prints the Go type every column became.

The schema

db/migrations/0001_landings.up.sql

CREATE TABLE landings (
    id         BIGINT           GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    catch_id   UUID             NOT NULL,
    species    TEXT             NOT NULL,
    boxes      SMALLINT         NOT NULL,
    fish       INTEGER          NOT NULL,
    weight_g   BIGINT           NOT NULL,
    length_cm  REAL             NOT NULL,
    sea_temp   DOUBLE PRECISION NOT NULL,
    price      NUMERIC(8, 2)    NOT NULL,
    on_ice     BOOLEAN          NOT NULL,
    landed_on  DATE             NOT NULL,
    landed_at  TIMESTAMPTZ      NOT NULL,
    harbour_ip INET             NOT NULL,
    tag        BYTEA            NOT NULL,
    notes      TEXT
);

What sgl generates

The row type for GetLanding is the mapping, field by field:

internal/store

type GetLandingRow struct {
	ID        int64       `json:"id"`
	CatchID   uuid.UUID   `json:"catch_id"`
	Species   string      `json:"species"`
	Boxes     int16       `json:"boxes"`
	Fish      int32       `json:"fish"`
	WeightG   int64       `json:"weight_g"`
	LengthCm  float32     `json:"length_cm"`
	SeaTemp   float64     `json:"sea_temp"`
	Price     apd.Decimal `json:"price"`
	OnIce     bool        `json:"on_ice"`
	LandedOn  time.Time   `json:"landed_on"`
	LandedAt  time.Time   `json:"landed_at"`
	HarbourIP any         `json:"harbour_ip"`
	Tag       []byte      `json:"tag"`
	Notes     *string     `json:"notes"`
}

func (queries *Queries) GetLanding(ctx context.Context, id int64) (GetLandingRow, error) {
	var row GetLandingRow
	err := queries.reader.QueryRowContext(ctx, getLandingSQL, id).Scan(&row.ID, &row.CatchID, &row.Species, &row.Boxes, &row.Fish, &row.WeightG, &row.LengthCm, &row.SeaTemp, &row.Price, &row.OnIce, &row.LandedOn, &row.LandedAt, &row.HarbourIP, &row.Tag, &row.Notes)
	if err != nil {
		return GetLandingRow{}, err
	}
	return row, nil
}

SMALLINT, INTEGER and BIGINT give int16, int32 and int64.
REAL is single precision, so it gives float32; DOUBLE PRECISION gives float64.
NUMERIC gives apd.Decimal from github.com/cockroachdb/apd/v3, which keeps money exact: the price comes back as 412.80, with its trailing zero.
UUID gives uuid.UUID from github.com/google/uuid.
DATE and TIMESTAMPTZ give time.Time. A TIMESTAMPTZ arrives in the connection's time zone, so the program prints it in UTC.
BYTEA gives []byte, and the nullable notes gives *string.
INET is a type Seagull has no mapping for, so HarbourIP is any, and the driver decides what it holds: pgx's database/sql driver hands back a string. A go_type override pins a column like this to a type of your choosing.

Parameters follow the same rules, so RecordLanding takes the same Go types it hands back.

Using it

The program records a landing and reads it back, then prints each column's name, the Go type of its field (with %T) and its value. %T prints []byte as []uint8, since byte is an alias for uint8.

example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"
	"time"

	"github.com/cockroachdb/apd/v3"
	"github.com/google/uuid"

	"seagull.sh/seagull/examples/postgres/005_type_mapping/internal/store"
)

// run records a landing of fish and reads it back, printing the Go type sgl chose for
// each column of db/migrations. PostgreSQL's types are exact, so each maps by name:
//
//   - SMALLINT, INTEGER and BIGINT give int16, int32 and int64;
//   - REAL is single precision and gives float32, while DOUBLE PRECISION gives float64;
//   - NUMERIC gives apd.Decimal from github.com/cockroachdb/apd/v3, which keeps money
//     exact, and UUID gives uuid.UUID from github.com/google/uuid;
//   - DATE and TIMESTAMPTZ give time.Time, and BYTEA gives []byte;
//   - a type Seagull has no mapping for, such as INET, gives any, and the driver decides
//     what it holds: pgx's database/sql driver hands back the address as a string. Give
//     such a column a go_type override to pin it down.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives one line per column.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{
		CatchID:   uuid.MustParse("6f1c0b8e-4b2a-4c55-9d7e-2a3f5e8c1d40"),
		Species:   "mackerel",
		Boxes:     12,
		Fish:      480,
		WeightG:   163_200,
		LengthCm:  31.5,
		SeaTemp:   12.25,
		Price:     *apd.New(41_280, -2),
		OnIce:     true,
		LandedOn:  time.Date(2026, time.May, 1, 0, 0, 0, 0, time.UTC),
		LandedAt:  time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
		HarbourIP: "192.0.2.17",
		Tag:       []byte{0x04, 0xa2, 0x19, 0xf3},
		Notes:     new("landed at Newlyn"),
	})
	if err != nil {
		return fmt.Errorf("recording a landing: %w", err)
	}

	landing, err := queries.GetLanding(ctx, recorded.ID)
	if err != nil {
		return fmt.Errorf("getting landing %d: %w", recorded.ID, err)
	}
	fmt.Fprintf(output, "id          %-12T %d\n", landing.ID, landing.ID)
	fmt.Fprintf(output, "catch_id    %-12T %s\n", landing.CatchID, landing.CatchID)
	fmt.Fprintf(output, "species     %-12T %s\n", landing.Species, landing.Species)
	fmt.Fprintf(output, "boxes       %-12T %d\n", landing.Boxes, landing.Boxes)
	fmt.Fprintf(output, "fish        %-12T %d\n", landing.Fish, landing.Fish)
	fmt.Fprintf(output, "weight_g    %-12T %d\n", landing.WeightG, landing.WeightG)
	fmt.Fprintf(output, "length_cm   %-12T %.1f\n", landing.LengthCm, landing.LengthCm)
	fmt.Fprintf(output, "sea_temp    %-12T %.2f\n", landing.SeaTemp, landing.SeaTemp)
	fmt.Fprintf(output, "price       %-12T %s\n", landing.Price, landing.Price.String())
	fmt.Fprintf(output, "on_ice      %-12T %t\n", landing.OnIce, landing.OnIce)
	fmt.Fprintf(output, "landed_on   %-12T %s\n", landing.LandedOn, landing.LandedOn.Format(time.DateOnly))
	// TIMESTAMPTZ arrives in the connection's time zone, so print it in UTC.
	fmt.Fprintf(output, "landed_at   %-12T %s\n", landing.LandedAt, landing.LandedAt.UTC().Format(time.RFC3339))
	fmt.Fprintf(output, "harbour_ip  %-12T %v\n", landing.HarbourIP, landing.HarbourIP)
	// %T prints []byte as []uint8, since byte is an alias for uint8.
	fmt.Fprintf(output, "tag         %-12T % x\n", landing.Tag, landing.Tag)
	fmt.Fprintf(output, "notes       %-12T %s\n", landing.Notes, *landing.Notes)
	return nil
}

What it prints

id          int64        1
catch_id    uuid.UUID    6f1c0b8e-4b2a-4c55-9d7e-2a3f5e8c1d40
species     string       mackerel
boxes       int16        12
fish        int32        480
weight_g    int64        163200
length_cm   float32      31.5
sea_temp    float64      12.25
price       apd.Decimal  412.80
on_ice      bool         true
landed_on   time.Time    2026-05-01
landed_at   time.Time    2026-05-01T05:30:00Z
harbour_ip  string       192.0.2.17
tag         []uint8      04 a2 19 f3
notes       *string      landed at Newlyn

Changing a type

The defaults are a starting point. A go_type directive on a column, or an override in sgl.yaml, swaps in a type of your own, such as a named Pence type for money; Type overrides shows how. The type mapping reference lists every default, and the order in which overrides apply.

With pgx

The landing read back through pgx, which decodes the same Row struct from PostgreSQL's binary formats, INTERVAL into time.Duration and an unmapped INET into a netip.Prefix.

What changes:

$1 placeholders
The Row and Params types are the same as database/sql's; pgx decodes the values itself
INTERVAL maps to time.Duration, which pgx decodes itself, counting a month as 30 days
INET has no mapping and arrives as any, which pgx fills with a netip.Prefix rather than a string
NUMERIC maps to apd.Decimal and UUID to uuid.UUID, which pgx fills through their Scan methods

db/queries/landings.sql

-- sgl.query(RecordLanding, one)
INSERT INTO landings (catch_id, species, boxes, fish, weight_g, length_cm, sea_temp, price, on_ice, landed_on, landed_at, at_sea, harbour_ip, tag, notes)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
RETURNING id;

-- sgl.query(GetLanding, one)
SELECT id, catch_id, species, boxes, fish, weight_g, length_cm, sea_temp, price, on_ice, landed_on, landed_at, at_sea, harbour_ip, tag, notes
FROM landings
WHERE id = $1;

internal/store

type GetLandingRow struct {
	ID        int64         `json:"id"`
	CatchID   uuid.UUID     `json:"catch_id"`
	Species   string        `json:"species"`
	Boxes     int16         `json:"boxes"`
	Fish      int32         `json:"fish"`
	WeightG   int64         `json:"weight_g"`
	LengthCm  float32       `json:"length_cm"`
	SeaTemp   float64       `json:"sea_temp"`
	Price     apd.Decimal   `json:"price"`
	OnIce     bool          `json:"on_ice"`
	LandedOn  time.Time     `json:"landed_on"`
	LandedAt  time.Time     `json:"landed_at"`
	AtSea     time.Duration `json:"at_sea"`
	HarbourIP any           `json:"harbour_ip"`
	Tag       []byte        `json:"tag"`
	Notes     *string       `json:"notes"`
}

func (queries *Queries) GetLanding(ctx context.Context, id int64) (GetLandingRow, error) {
	var row GetLandingRow
	err := queries.db.QueryRow(ctx, getLandingSQL, id).Scan(&row.ID, &row.CatchID, &row.Species, &row.Boxes, &row.Fish, &row.WeightG, &row.LengthCm, &row.SeaTemp, &row.Price, &row.OnIce, &row.LandedOn, &row.LandedAt, &row.AtSea, &row.HarbourIP, &row.Tag, &row.Notes)
	if err != nil {
		return GetLandingRow{}, err
	}
	return row, nil
}

example.go

package main

import (
	"context"
	"fmt"
	"io"
	"time"

	"github.com/cockroachdb/apd/v3"
	"github.com/google/uuid"
	"github.com/jackc/pgx/v5/pgxpool"

	"seagull.sh/seagull/examples/postgres/005_type_mapping_pgx/internal/store"
)

// run records a landing of fish and reads it back through pgx, printing the Go type sgl
// chose for each column of db/migrations. Most field types match the database/sql
// emitter's, but the native pgx emitter maps INTERVAL to time.Duration rather than
// string. pgx reads PostgreSQL's binary formats itself:
//
//   - SMALLINT, INTEGER and BIGINT give int16, int32 and int64; REAL gives float32 and
//     DOUBLE PRECISION gives float64;
//   - NUMERIC gives apd.Decimal and UUID gives uuid.UUID, which pgx fills through their
//     Scan methods;
//   - DATE and TIMESTAMPTZ give time.Time, and INTERVAL gives time.Duration, which pgx
//     decodes itself, counting a month as 30 days;
//   - INET has no mapping in Seagull and gives any, which pgx fills with a netip.Prefix,
//     where database/sql would give a string.
//
// Takes pool (*pgxpool.Pool) which is a pool on a migrated database.
// Takes output (io.Writer) which receives one line per column.
//
// Returns error when a query fails.
func run(ctx context.Context, pool *pgxpool.Pool, output io.Writer) error {
	queries := store.New(pool)

	recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{
		CatchID:   uuid.MustParse("6f1c0b8e-4b2a-4c55-9d7e-2a3f5e8c1d40"),
		Species:   "mackerel",
		Boxes:     12,
		Fish:      480,
		WeightG:   163_200,
		LengthCm:  31.5,
		SeaTemp:   12.25,
		Price:     *apd.New(41_280, -2),
		OnIce:     true,
		LandedOn:  time.Date(2026, time.May, 1, 0, 0, 0, 0, time.UTC),
		LandedAt:  time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
		AtSea:     14*time.Hour + 45*time.Minute,
		HarbourIP: "192.0.2.17",
		Tag:       []byte{0x04, 0xa2, 0x19, 0xf3},
		Notes:     new("landed at Newlyn"),
	})
	if err != nil {
		return fmt.Errorf("recording a landing: %w", err)
	}

	landing, err := queries.GetLanding(ctx, recorded.ID)
	if err != nil {
		return fmt.Errorf("getting landing %d: %w", recorded.ID, err)
	}
	fmt.Fprintf(output, "id          %-14T %d\n", landing.ID, landing.ID)
	fmt.Fprintf(output, "catch_id    %-14T %s\n", landing.CatchID, landing.CatchID)
	fmt.Fprintf(output, "species     %-14T %s\n", landing.Species, landing.Species)
	fmt.Fprintf(output, "boxes       %-14T %d\n", landing.Boxes, landing.Boxes)
	fmt.Fprintf(output, "fish        %-14T %d\n", landing.Fish, landing.Fish)
	fmt.Fprintf(output, "weight_g    %-14T %d\n", landing.WeightG, landing.WeightG)
	fmt.Fprintf(output, "length_cm   %-14T %.1f\n", landing.LengthCm, landing.LengthCm)
	fmt.Fprintf(output, "sea_temp    %-14T %.2f\n", landing.SeaTemp, landing.SeaTemp)
	fmt.Fprintf(output, "price       %-14T %s\n", landing.Price, landing.Price.String())
	fmt.Fprintf(output, "on_ice      %-14T %t\n", landing.OnIce, landing.OnIce)
	fmt.Fprintf(output, "landed_on   %-14T %s\n", landing.LandedOn, landing.LandedOn.Format(time.DateOnly))
	// TIMESTAMPTZ arrives in the connection's time zone, so print it in UTC.
	fmt.Fprintf(output, "landed_at   %-14T %s\n", landing.LandedAt, landing.LandedAt.UTC().Format(time.RFC3339))
	fmt.Fprintf(output, "at_sea      %-14T %s\n", landing.AtSea, landing.AtSea)
	fmt.Fprintf(output, "harbour_ip  %-14T %v\n", landing.HarbourIP, landing.HarbourIP)
	// %T prints []byte as []uint8, since byte is an alias for uint8.
	fmt.Fprintf(output, "tag         %-14T % x\n", landing.Tag, landing.Tag)
	fmt.Fprintf(output, "notes       %-14T %s\n", landing.Notes, *landing.Notes)
	return nil
}

Across engines

Only these Go types of GetLanding differ between engines; the rest are the same everywhere.

	PostgreSQL	SQLite	MySQL	DuckDB	ClickHouse
`id` parameter	`int64`	`int64`	`int64`	`int64`	`uint64`
`id` column	`int64`	`int64`	`int64`	`int64`	`uint64`
`boxes` column	`int16`	`int64`	`int16`	`int16`	`uint16`
`fish` column	`int32`	`int64`	`int32`	`int32`	`uint32`
`length_cm` column	`float32`	`float64`	`float64`	`float64`	`float32`
`tag` column	`[]byte`	`[]byte`	`[]byte`	`[]byte`	`string`
`catch_id` column	`uuid.UUID`	·	·	`uuid.UUID`	`uuid.UUID`
`sea_temp` column	`float64`	·	·	·	·
`price` column	`apd.Decimal`	·	·	·	`decimal.Decimal`
`landed_on` column	`time.Time`	·	·	`time.Time`	·
`harbour_ip` column	`any`	·	·	·	·
`berth` column	·	·	`uint8`	·	·
`price_per_kg` column	·	·	`apd.Decimal`	·	·
`quay` column	·	·	·	`uint8`	·
`water_temp` column	·	·	·	`float32`	·
`auction_lot` column	·	·	·	`*big.Int`	·

A · marks a parameter or column that version of the example does not have.

TimescaleDB runs the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

Compare the whole example side by side with SQLite, MySQL, DuckDB and ClickHouse.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/postgres && go run ./005_type_mapping

Its test runs it too, and checks what it prints against output.txt:

cd seagull/examples/postgres && SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./005_type_mapping

Needs a running PostgreSQL server: set DATABASE_URL, or pass -dsn, to the address of an empty database.

The files

Every file of the example, as it is in the Seagull repository.

sgl.yaml

# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.

version: 1

databases:
  - name: landings
    dialect: postgres
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      driver: database/sql

db/migrations/0001_landings.up.sql

CREATE TABLE landings (
    id         BIGINT           GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    catch_id   UUID             NOT NULL,
    species    TEXT             NOT NULL,
    boxes      SMALLINT         NOT NULL,
    fish       INTEGER          NOT NULL,
    weight_g   BIGINT           NOT NULL,
    length_cm  REAL             NOT NULL,
    sea_temp   DOUBLE PRECISION NOT NULL,
    price      NUMERIC(8, 2)    NOT NULL,
    on_ice     BOOLEAN          NOT NULL,
    landed_on  DATE             NOT NULL,
    landed_at  TIMESTAMPTZ      NOT NULL,
    harbour_ip INET             NOT NULL,
    tag        BYTEA            NOT NULL,
    notes      TEXT
);

db/migrations/0001_landings.down.sql

DROP TABLE landings;

db/queries/landings.sql

-- sgl.query(RecordLanding, one)
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)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
RETURNING id;

-- sgl.query(GetLanding, one)
SELECT id, catch_id, species, boxes, fish, weight_g, length_cm, sea_temp, price, on_ice, landed_on, landed_at, harbour_ip, tag, notes
FROM landings
WHERE id = $1;

example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"
	"time"

	"github.com/cockroachdb/apd/v3"
	"github.com/google/uuid"

	"seagull.sh/seagull/examples/postgres/005_type_mapping/internal/store"
)

// run records a landing of fish and reads it back, printing the Go type sgl chose for
// each column of db/migrations. PostgreSQL's types are exact, so each maps by name:
//
//   - SMALLINT, INTEGER and BIGINT give int16, int32 and int64;
//   - REAL is single precision and gives float32, while DOUBLE PRECISION gives float64;
//   - NUMERIC gives apd.Decimal from github.com/cockroachdb/apd/v3, which keeps money
//     exact, and UUID gives uuid.UUID from github.com/google/uuid;
//   - DATE and TIMESTAMPTZ give time.Time, and BYTEA gives []byte;
//   - a type Seagull has no mapping for, such as INET, gives any, and the driver decides
//     what it holds: pgx's database/sql driver hands back the address as a string. Give
//     such a column a go_type override to pin it down.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives one line per column.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{
		CatchID:   uuid.MustParse("6f1c0b8e-4b2a-4c55-9d7e-2a3f5e8c1d40"),
		Species:   "mackerel",
		Boxes:     12,
		Fish:      480,
		WeightG:   163_200,
		LengthCm:  31.5,
		SeaTemp:   12.25,
		Price:     *apd.New(41_280, -2),
		OnIce:     true,
		LandedOn:  time.Date(2026, time.May, 1, 0, 0, 0, 0, time.UTC),
		LandedAt:  time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
		HarbourIP: "192.0.2.17",
		Tag:       []byte{0x04, 0xa2, 0x19, 0xf3},
		Notes:     new("landed at Newlyn"),
	})
	if err != nil {
		return fmt.Errorf("recording a landing: %w", err)
	}

	landing, err := queries.GetLanding(ctx, recorded.ID)
	if err != nil {
		return fmt.Errorf("getting landing %d: %w", recorded.ID, err)
	}
	fmt.Fprintf(output, "id          %-12T %d\n", landing.ID, landing.ID)
	fmt.Fprintf(output, "catch_id    %-12T %s\n", landing.CatchID, landing.CatchID)
	fmt.Fprintf(output, "species     %-12T %s\n", landing.Species, landing.Species)
	fmt.Fprintf(output, "boxes       %-12T %d\n", landing.Boxes, landing.Boxes)
	fmt.Fprintf(output, "fish        %-12T %d\n", landing.Fish, landing.Fish)
	fmt.Fprintf(output, "weight_g    %-12T %d\n", landing.WeightG, landing.WeightG)
	fmt.Fprintf(output, "length_cm   %-12T %.1f\n", landing.LengthCm, landing.LengthCm)
	fmt.Fprintf(output, "sea_temp    %-12T %.2f\n", landing.SeaTemp, landing.SeaTemp)
	fmt.Fprintf(output, "price       %-12T %s\n", landing.Price, landing.Price.String())
	fmt.Fprintf(output, "on_ice      %-12T %t\n", landing.OnIce, landing.OnIce)
	fmt.Fprintf(output, "landed_on   %-12T %s\n", landing.LandedOn, landing.LandedOn.Format(time.DateOnly))
	// TIMESTAMPTZ arrives in the connection's time zone, so print it in UTC.
	fmt.Fprintf(output, "landed_at   %-12T %s\n", landing.LandedAt, landing.LandedAt.UTC().Format(time.RFC3339))
	fmt.Fprintf(output, "harbour_ip  %-12T %v\n", landing.HarbourIP, landing.HarbourIP)
	// %T prints []byte as []uint8, since byte is an alias for uint8.
	fmt.Fprintf(output, "tag         %-12T % x\n", landing.Tag, landing.Tag)
	fmt.Fprintf(output, "notes       %-12T %s\n", landing.Notes, *landing.Notes)
	return nil
}

main.go

// Command 005_type_mapping applies its migrations to a PostgreSQL database, then runs the
// example in example.go: one landing of fish is recorded and read back, printing the Go
// type of each column.
//
// Run it with `go run . -dsn postgres://user:password@localhost:5432/example`, or set
// DATABASE_URL instead of passing -dsn. Give it an empty database of its own, as the
// migrations create its tables and the example adds rows each time it runs.
package main

import (
	"context"
	"database/sql"
	"embed"
	"errors"
	"flag"
	"fmt"
	"log"
	"os"

	_ "github.com/jackc/pgx/v5/stdlib"

	"seagull.sh/seagull/sdk/migrate"
	"seagull.sh/seagull/sdk/migrate/migrate_backend_postgres"
)

// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS

func main() {
	dsn := flag.String("dsn", os.Getenv("DATABASE_URL"), "PostgreSQL connection string; defaults to $DATABASE_URL")
	flag.Parse()
	if err := execute(context.Background(), *dsn); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database at dsn, and runs the example against it.
//
// Takes dsn (string) which is the PostgreSQL connection string.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, dsn string) error {
	if dsn == "" {
		return errors.New("set -dsn or DATABASE_URL to a PostgreSQL connection string")
	}
	database, err := sql.Open("pgx", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.New(), migrations, "db/migrations")
	if err != nil {
		return err
	}
	if _, err := migrator.Up(ctx); err != nil {
		return fmt.Errorf("migrating: %w", err)
	}
	return run(ctx, database, os.Stdout)
}

internal/store/landings.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"time"

	"github.com/cockroachdb/apd/v3"
	"github.com/google/uuid"
)

const getLandingSQL = `SELECT id, catch_id, species, boxes, fish, weight_g, length_cm, sea_temp, price, on_ice, landed_on, landed_at, harbour_ip, tag, notes
FROM landings
WHERE id = $1;`

type GetLandingRow struct {
	ID        int64       `json:"id"`
	CatchID   uuid.UUID   `json:"catch_id"`
	Species   string      `json:"species"`
	Boxes     int16       `json:"boxes"`
	Fish      int32       `json:"fish"`
	WeightG   int64       `json:"weight_g"`
	LengthCm  float32     `json:"length_cm"`
	SeaTemp   float64     `json:"sea_temp"`
	Price     apd.Decimal `json:"price"`
	OnIce     bool        `json:"on_ice"`
	LandedOn  time.Time   `json:"landed_on"`
	LandedAt  time.Time   `json:"landed_at"`
	HarbourIP any         `json:"harbour_ip"`
	Tag       []byte      `json:"tag"`
	Notes     *string     `json:"notes"`
}

func (queries *Queries) GetLanding(ctx context.Context, id int64) (GetLandingRow, error) {
	var row GetLandingRow
	err := queries.reader.QueryRowContext(ctx, getLandingSQL, id).Scan(&row.ID, &row.CatchID, &row.Species, &row.Boxes, &row.Fish, &row.WeightG, &row.LengthCm, &row.SeaTemp, &row.Price, &row.OnIce, &row.LandedOn, &row.LandedAt, &row.HarbourIP, &row.Tag, &row.Notes)
	if err != nil {
		return GetLandingRow{}, err
	}
	return row, nil
}

const recordLandingSQL = `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)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
RETURNING id;`

type RecordLandingParams struct {
	CatchID   uuid.UUID
	Species   string
	Boxes     int16
	Fish      int32
	WeightG   int64
	LengthCm  float32
	SeaTemp   float64
	Price     apd.Decimal
	OnIce     bool
	LandedOn  time.Time
	LandedAt  time.Time
	HarbourIP any
	Tag       []byte
	Notes     *string
}

type RecordLandingRow struct {
	ID int64 `json:"id"`
}

func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) (RecordLandingRow, error) {
	var row RecordLandingRow
	err := queries.writer.QueryRowContext(ctx, recordLandingSQL, params.CatchID, params.Species, params.Boxes, params.Fish, params.WeightG, params.LengthCm, params.SeaTemp, params.Price, params.OnIce, params.LandedOn, params.LandedAt, params.HarbourIP, params.Tag, params.Notes).Scan(&row.ID)
	if err != nil {
		return RecordLandingRow{}, err
	}
	return row, nil
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"time"

	"github.com/cockroachdb/apd/v3"
	"github.com/google/uuid"
)

type Landings struct {
	ID        int64       `json:"id"`
	CatchID   uuid.UUID   `json:"catch_id"`
	Species   string      `json:"species"`
	Boxes     int16       `json:"boxes"`
	Fish      int32       `json:"fish"`
	WeightG   int64       `json:"weight_g"`
	LengthCm  float32     `json:"length_cm"`
	SeaTemp   float64     `json:"sea_temp"`
	Price     apd.Decimal `json:"price"`
	OnIce     bool        `json:"on_ice"`
	LandedOn  time.Time   `json:"landed_on"`
	LandedAt  time.Time   `json:"landed_at"`
	HarbourIP any         `json:"harbour_ip"`
	Tag       []byte      `json:"tag"`
	Notes     *string     `json:"notes"`
}

internal/store/querier.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type dbtxWrapper interface {
	WrapDBTX(inner any) any
}

type Queries struct {
	reader  DBTX
	writer  DBTX
	wrapper dbtxWrapper
}

func New(db DBTX) *Queries {
	wrapper, _ := db.(dbtxWrapper)
	return &Queries{reader: db, writer: db, wrapper: wrapper}
}

func NewWithReplica(writer DBTX, reader DBTX) *Queries {
	wrapper, _ := writer.(dbtxWrapper)
	return &Queries{reader: reader, writer: writer, wrapper: wrapper}
}

func (queries *Queries) WithTx(transaction *sql.Tx) *Queries {
	if queries == nil {
		return &Queries{reader: transaction, writer: transaction, wrapper: nil}
	}
	db := queries.wrapDBTX(transaction)
	return &Queries{reader: db, writer: db, wrapper: queries.wrapper}
}

func (queries *Queries) wrapDBTX(db DBTX) DBTX {
	if queries.wrapper == nil {
		return db
	}
	wrapped, ok := queries.wrapper.WrapDBTX(db).(DBTX)
	if !ok {
		return db
	}
	return wrapped
}

func (queries *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error {
	transaction, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer transaction.Rollback()
	if err = fn(queries.WithTx(transaction)); err != nil {
		return err
	}
	return transaction.Commit()
}

output.txt

id          int64        1
catch_id    uuid.UUID    6f1c0b8e-4b2a-4c55-9d7e-2a3f5e8c1d40
species     string       mackerel
boxes       int16        12
fish        int32        480
weight_g    int64        163200
length_cm   float32      31.5
sea_temp    float64      12.25
price       apd.Decimal  412.80
on_ice      bool         true
landed_on   time.Time    2026-05-01
landed_at   time.Time    2026-05-01T05:30:00Z
harbour_ip  string       192.0.2.17
tag         []uint8      04 a2 19 f3
notes       *string      landed at Newlyn

From SQL types to Go types

The schema

What sgl generates

Using it

What it prints

Changing a type

With pgx

Across engines

Run it locally

The files

Also on other engines