005 From SQL types to Go types on SQLite

On SQLite:
Types follow each column's declared type, as SQLite itself has only five storage classes
Every type with INTEGER affinity, SMALLINT and BIGINT included, maps to int64, as SQLite stores any integer in up to eight bytes
REAL, DOUBLE and FLOAT all map to float64
DATETIME, DATE and TIMESTAMP map to time.Time, which modernc.org/sqlite parses from stored text
BOOLEAN maps to bool, stored as 0 and 1
No exact decimal type; DECIMAL maps to apd.Decimal, so the example keeps to integers

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

SQLite stores every value as one of five storage classes, so Seagull goes by the type name each column declares.

db/migrations/0001_landings.up.sql

CREATE TABLE landings (
    id         INTEGER  PRIMARY KEY,
    species    TEXT     NOT NULL,
    boxes      SMALLINT NOT NULL,
    fish       INTEGER  NOT NULL,
    weight_g   BIGINT   NOT NULL,
    length_cm  REAL     NOT NULL,
    on_ice     BOOLEAN  NOT NULL,
    landed_at  DATETIME NOT NULL,
    tag        BLOB     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"`
	Species  string    `json:"species"`
	Boxes    int64     `json:"boxes"`
	Fish     int64     `json:"fish"`
	WeightG  int64     `json:"weight_g"`
	LengthCm float64   `json:"length_cm"`
	OnIce    bool      `json:"on_ice"`
	LandedAt time.Time `json:"landed_at"`
	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.Species, &row.Boxes, &row.Fish, &row.WeightG, &row.LengthCm, &row.OnIce, &row.LandedAt, &row.Tag, &row.Notes)
	if err != nil {
		return GetLandingRow{}, err
	}
	return row, nil
}

SMALLINT, INTEGER and BIGINT all give int64. Each has SQLite's integer affinity, and SQLite stores any integer in up to eight bytes whatever width the column declares, so boxes is an int64 although it says SMALLINT. INTEGER PRIMARY KEY is the 64-bit rowid, so id is an int64 as well.
REAL gives float64.
BOOLEAN gives bool, stored as 0 and 1.
DATETIME gives time.Time, which the driver parses back from the text it stored.
BLOB gives []byte, and the nullable notes gives *string.

SQLite has no exact decimal type. Money is best kept as an integer number of pence, as this example keeps weight in whole grams; a column declared DECIMAL would map to apd.Decimal.

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"

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

// run records a landing of fish and reads it back, printing the Go type sgl chose for
// each column. SQLite stores every value as one of five storage classes, so Seagull maps
// the type each column declares in db/migrations. SMALLINT, INTEGER and BIGINT all have
// INTEGER affinity, which SQLite stores in up to eight bytes whatever width the column
// declares, so each gives int64, as the INTEGER PRIMARY KEY rowid does. REAL gives
// float64, and DATETIME gives time.Time, which the driver parses back from the text it
// stored. SQLite has no exact decimal type, so money is best kept as INTEGER pence; a
// DECIMAL column maps to apd.Decimal from github.com/cockroachdb/apd/v3.
//
// 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{
		Species:  "mackerel",
		Boxes:    12,
		Fish:     480,
		WeightG:  163_200,
		LengthCm: 31.5,
		OnIce:    true,
		LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
		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         %-10T %d\n", landing.ID, landing.ID)
	fmt.Fprintf(output, "species    %-10T %s\n", landing.Species, landing.Species)
	fmt.Fprintf(output, "boxes      %-10T %d\n", landing.Boxes, landing.Boxes)
	fmt.Fprintf(output, "fish       %-10T %d\n", landing.Fish, landing.Fish)
	fmt.Fprintf(output, "weight_g   %-10T %d\n", landing.WeightG, landing.WeightG)
	fmt.Fprintf(output, "length_cm  %-10T %.1f\n", landing.LengthCm, landing.LengthCm)
	fmt.Fprintf(output, "on_ice     %-10T %t\n", landing.OnIce, landing.OnIce)
	fmt.Fprintf(output, "landed_at  %-10T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
	// %T prints []byte as []uint8, since byte is an alias for uint8.
	fmt.Fprintf(output, "tag        %-10T % x\n", landing.Tag, landing.Tag)
	fmt.Fprintf(output, "notes      %-10T %s\n", landing.Notes, *landing.Notes)
	return nil
}

What it prints

id         int64      1
species    string     mackerel
boxes      int64      12
fish       int64      480
weight_g   int64      163200
length_cm  float64    31.5
on_ice     bool       true
landed_at  time.Time  2026-05-01T05:30:00Z
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.

Across engines

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

	SQLite	PostgreSQL	MySQL	DuckDB	ClickHouse
`id` parameter	`int64`	`int64`	`int64`	`int64`	`uint64`
`id` column	`int64`	`int64`	`int64`	`int64`	`uint64`
`boxes` column	`int64`	`int16`	`int16`	`int16`	`uint16`
`fish` column	`int64`	`int32`	`int32`	`int32`	`uint32`
`length_cm` column	`float64`	`float32`	`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 PostgreSQL, MySQL, DuckDB and ClickHouse.

Run it locally

From the directory you cloned the Seagull repository into:

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

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

cd seagull/examples/sqlite && go test ./005_type_mapping

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: sqlite
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_landings.up.sql

CREATE TABLE landings (
    id         INTEGER  PRIMARY KEY,
    species    TEXT     NOT NULL,
    boxes      SMALLINT NOT NULL,
    fish       INTEGER  NOT NULL,
    weight_g   BIGINT   NOT NULL,
    length_cm  REAL     NOT NULL,
    on_ice     BOOLEAN  NOT NULL,
    landed_at  DATETIME NOT NULL,
    tag        BLOB     NOT NULL,
    notes      TEXT
);

db/migrations/0001_landings.down.sql

DROP TABLE landings;

db/queries/landings.sql

-- sgl.query(RecordLanding, one)
INSERT INTO landings (species, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
RETURNING id;

-- sgl.query(GetLanding, one)
SELECT id, species, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes
FROM landings
WHERE id = ?;

example.go

package main

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

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

// run records a landing of fish and reads it back, printing the Go type sgl chose for
// each column. SQLite stores every value as one of five storage classes, so Seagull maps
// the type each column declares in db/migrations. SMALLINT, INTEGER and BIGINT all have
// INTEGER affinity, which SQLite stores in up to eight bytes whatever width the column
// declares, so each gives int64, as the INTEGER PRIMARY KEY rowid does. REAL gives
// float64, and DATETIME gives time.Time, which the driver parses back from the text it
// stored. SQLite has no exact decimal type, so money is best kept as INTEGER pence; a
// DECIMAL column maps to apd.Decimal from github.com/cockroachdb/apd/v3.
//
// 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{
		Species:  "mackerel",
		Boxes:    12,
		Fish:     480,
		WeightG:  163_200,
		LengthCm: 31.5,
		OnIce:    true,
		LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
		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         %-10T %d\n", landing.ID, landing.ID)
	fmt.Fprintf(output, "species    %-10T %s\n", landing.Species, landing.Species)
	fmt.Fprintf(output, "boxes      %-10T %d\n", landing.Boxes, landing.Boxes)
	fmt.Fprintf(output, "fish       %-10T %d\n", landing.Fish, landing.Fish)
	fmt.Fprintf(output, "weight_g   %-10T %d\n", landing.WeightG, landing.WeightG)
	fmt.Fprintf(output, "length_cm  %-10T %.1f\n", landing.LengthCm, landing.LengthCm)
	fmt.Fprintf(output, "on_ice     %-10T %t\n", landing.OnIce, landing.OnIce)
	fmt.Fprintf(output, "landed_at  %-10T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
	// %T prints []byte as []uint8, since byte is an alias for uint8.
	fmt.Fprintf(output, "tag        %-10T % x\n", landing.Tag, landing.Tag)
	fmt.Fprintf(output, "notes      %-10T %s\n", landing.Notes, *landing.Notes)
	return nil
}

main.go

// Command 005_type_mapping applies its migrations to a SQLite database, then runs the
// example in example.go: one landing of fish is recorded and read back, printing each
// column's Go type and value.
//
// Run it with `go run .`, or `go run . -db landings.db` to keep the database.
package main

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

	_ "modernc.org/sqlite"

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

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

func main() {
	path := flag.String("db", "", "SQLite database file; a temporary one when empty")
	flag.Parse()
	if err := execute(context.Background(), *path); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database at path, or a temporary one when path is empty,
// and runs the example against it.
//
// Takes path (string) which is the database file, or empty.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, path string) error {
	if path == "" {
		directory, err := os.MkdirTemp("", "seagull-example-")
		if err != nil {
			return err
		}
		defer func() { _ = os.RemoveAll(directory) }()
		path = filepath.Join(directory, "example.db")
	}
	database, err := sql.Open("sqlite", "file:"+path)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_sqlite.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"
)

const getLandingSQL = `SELECT id, species, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes
FROM landings
WHERE id = ?1;`

type GetLandingRow struct {
	ID       int64     `json:"id"`
	Species  string    `json:"species"`
	Boxes    int64     `json:"boxes"`
	Fish     int64     `json:"fish"`
	WeightG  int64     `json:"weight_g"`
	LengthCm float64   `json:"length_cm"`
	OnIce    bool      `json:"on_ice"`
	LandedAt time.Time `json:"landed_at"`
	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.Species, &row.Boxes, &row.Fish, &row.WeightG, &row.LengthCm, &row.OnIce, &row.LandedAt, &row.Tag, &row.Notes)
	if err != nil {
		return GetLandingRow{}, err
	}
	return row, nil
}

const recordLandingSQL = `INSERT INTO landings (species, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)
RETURNING id;`

type RecordLandingParams struct {
	Species  string
	Boxes    int64
	Fish     int64
	WeightG  int64
	LengthCm float64
	OnIce    bool
	LandedAt time.Time
	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.Species, params.Boxes, params.Fish, params.WeightG, params.LengthCm, params.OnIce, params.LandedAt, 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"

type Landings struct {
	ID       int64     `json:"id"`
	Species  string    `json:"species"`
	Boxes    int64     `json:"boxes"`
	Fish     int64     `json:"fish"`
	WeightG  int64     `json:"weight_g"`
	LengthCm float64   `json:"length_cm"`
	OnIce    bool      `json:"on_ice"`
	LandedAt time.Time `json:"landed_at"`
	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
species    string     mackerel
boxes      int64      12
fish       int64      480
weight_g   int64      163200
length_cm  float64    31.5
on_ice     bool       true
landed_at  time.Time  2026-05-01T05:30:00Z
tag        []uint8    04 a2 19 f3
notes      *string    landed at Newlyn

From SQL types to Go types