005 From SQL types to Go types on MariaDB

The same on MariaDB. MariaDB runs this example unchanged: the SQL and the Go that sgl generates are identical to the MySQL version. Only dialect: in sgl.yaml changes.

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           NOT NULL AUTO_INCREMENT PRIMARY KEY,
    species      VARCHAR(50)      NOT NULL,
    boxes        SMALLINT         NOT NULL,
    fish         INT              NOT NULL,
    weight_g     BIGINT           NOT NULL,
    length_cm    DOUBLE           NOT NULL,
    on_ice       BOOLEAN          NOT NULL,
    landed_at    DATETIME         NOT NULL,
    tag          VARBINARY(16)    NOT NULL,
    notes        TEXT,
    berth        TINYINT UNSIGNED NOT NULL,
    price_per_kg DECIMAL(6, 2)    NOT NULL
);

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      int16       `json:"boxes"`
	Fish       int32       `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"`
	Berth      uint8       `json:"berth"`
	PricePerKg apd.Decimal `json:"price_per_kg"`
}

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, &row.Berth, &row.PricePerKg)
	if err != nil {
		return GetLandingRow{}, err
	}
	return row, nil
}

SMALLINT, INT and BIGINT give int16, int32 and int64, and an UNSIGNED integer gives the unsigned Go type of the same width, so TINYINT UNSIGNED is uint8.
DOUBLE gives float64.
BOOLEAN is MariaDB's TINYINT(1), and gives bool.
DATETIME gives time.Time, which the driver returns only when the DSN sets parseTime=true.
VARBINARY gives []byte, and the nullable notes gives *string.
DECIMAL(6, 2) gives apd.Decimal from github.com/cockroachdb/apd/v3, which holds the exact value.

Parameters follow the same rules, so RecordLanding takes the same Go types it hands back. The one exception here is deliberate. The insert binds the price as text, with a binding line that overrides the parameter's SQL type, and MariaDB converts "4.20" to DECIMAL(6, 2) without rounding:

db/queries/landings.sql

-- sgl.query(RecordLanding, execresult)
-- ?11 as sgl.param(price_per_kg, type: text)
INSERT INTO landings (species, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes, berth, price_per_kg)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

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/mysql/005_type_mapping/internal/store"
)

// run records a landing of fish and reads it back, printing the Go type sgl chose for
// each column. Seagull maps the type each column declares in db/migrations: SMALLINT, INT
// and BIGINT give int16, int32 and int64, and an UNSIGNED integer gives the unsigned Go
// type of the same width, so TINYINT UNSIGNED is uint8. DOUBLE gives float64, BOOLEAN
// (MySQL's TINYINT(1)) gives bool, DATETIME gives time.Time when the DSN sets
// parseTime=true, and VARBINARY gives []byte.
//
// DECIMAL gives apd.Decimal from github.com/cockroachdb/apd/v3, which holds the exact
// value. The insert binds the price as text instead, through sgl.param(price_per_kg,
// type: text), and MySQL converts "4.20" to DECIMAL(6, 2) without rounding.
//
// 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)

	result, 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"),
		Berth:      7,
		PricePerKg: "4.20",
	})
	if err != nil {
		return fmt.Errorf("recording a landing: %w", err)
	}
	id, err := result.LastInsertId()
	if err != nil {
		return fmt.Errorf("reading the landing's id: %w", err)
	}

	landing, err := queries.GetLanding(ctx, id)
	if err != nil {
		return fmt.Errorf("getting landing %d: %w", id, err)
	}
	fmt.Fprintf(output, "id            %-12T %d\n", landing.ID, landing.ID)
	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, "on_ice        %-12T %t\n", landing.OnIce, landing.OnIce)
	fmt.Fprintf(output, "landed_at     %-12T %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           %-12T % x\n", landing.Tag, landing.Tag)
	fmt.Fprintf(output, "notes         %-12T %s\n", landing.Notes, *landing.Notes)
	fmt.Fprintf(output, "berth         %-12T %d\n", landing.Berth, landing.Berth)
	fmt.Fprintf(output, "price_per_kg  %-12T %s\n", landing.PricePerKg, landing.PricePerKg.String())
	return nil
}

What it prints

id            int64        1
species       string       mackerel
boxes         int16        12
fish          int32        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
berth         uint8        7
price_per_kg  apd.Decimal  4.20

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.

	MySQL	SQLite	PostgreSQL	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	`float64`	`float64`	`float32`	`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.

MariaDB runs the MySQL version unchanged.

TimescaleDB runs the PostgreSQL version unchanged.

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

Run it locally

From the directory you cloned the Seagull repository into:

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

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

cd seagull/examples/mysql && SGL_EXAMPLE_ENGINE=mariadb SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./005_type_mapping

Needs a running MariaDB 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: mysql
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_landings.up.sql

CREATE TABLE landings (
    id           BIGINT           NOT NULL AUTO_INCREMENT PRIMARY KEY,
    species      VARCHAR(50)      NOT NULL,
    boxes        SMALLINT         NOT NULL,
    fish         INT              NOT NULL,
    weight_g     BIGINT           NOT NULL,
    length_cm    DOUBLE           NOT NULL,
    on_ice       BOOLEAN          NOT NULL,
    landed_at    DATETIME         NOT NULL,
    tag          VARBINARY(16)    NOT NULL,
    notes        TEXT,
    berth        TINYINT UNSIGNED NOT NULL,
    price_per_kg DECIMAL(6, 2)    NOT NULL
);

db/migrations/0001_landings.down.sql

DROP TABLE landings;

db/queries/landings.sql

-- sgl.query(RecordLanding, execresult)
-- ?11 as sgl.param(price_per_kg, type: text)
INSERT INTO landings (species, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes, berth, price_per_kg)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

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

example.go

package main

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

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

// run records a landing of fish and reads it back, printing the Go type sgl chose for
// each column. Seagull maps the type each column declares in db/migrations: SMALLINT, INT
// and BIGINT give int16, int32 and int64, and an UNSIGNED integer gives the unsigned Go
// type of the same width, so TINYINT UNSIGNED is uint8. DOUBLE gives float64, BOOLEAN
// (MySQL's TINYINT(1)) gives bool, DATETIME gives time.Time when the DSN sets
// parseTime=true, and VARBINARY gives []byte.
//
// DECIMAL gives apd.Decimal from github.com/cockroachdb/apd/v3, which holds the exact
// value. The insert binds the price as text instead, through sgl.param(price_per_kg,
// type: text), and MySQL converts "4.20" to DECIMAL(6, 2) without rounding.
//
// 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)

	result, 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"),
		Berth:      7,
		PricePerKg: "4.20",
	})
	if err != nil {
		return fmt.Errorf("recording a landing: %w", err)
	}
	id, err := result.LastInsertId()
	if err != nil {
		return fmt.Errorf("reading the landing's id: %w", err)
	}

	landing, err := queries.GetLanding(ctx, id)
	if err != nil {
		return fmt.Errorf("getting landing %d: %w", id, err)
	}
	fmt.Fprintf(output, "id            %-12T %d\n", landing.ID, landing.ID)
	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, "on_ice        %-12T %t\n", landing.OnIce, landing.OnIce)
	fmt.Fprintf(output, "landed_at     %-12T %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           %-12T % x\n", landing.Tag, landing.Tag)
	fmt.Fprintf(output, "notes         %-12T %s\n", landing.Notes, *landing.Notes)
	fmt.Fprintf(output, "berth         %-12T %d\n", landing.Berth, landing.Berth)
	fmt.Fprintf(output, "price_per_kg  %-12T %s\n", landing.PricePerKg, landing.PricePerKg.String())
	return nil
}

main.go

// Command 005_type_mapping applies its migrations to a MySQL 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 . -dsn 'user:password@tcp(localhost:3306)/landings'`, 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/go-sql-driver/mysql"

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

// 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"), "MySQL data source name; defaults to $DATABASE_URL")
	flag.Parse()
	if err := execute(context.Background(), *dsn); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database dsn names, and runs the example against it.
//
// Takes dsn (string) which is the database's data source name.
//
// 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 MySQL data source name")
	}
	config, err := mysql.ParseDSN(dsn)
	if err != nil {
		return err
	}
	// Seagull maps DATETIME and TIMESTAMP columns to time.Time, which the driver returns
	// only when parseTime is set.
	config.ParseTime = true
	dsn = config.FormatDSN()

	database, err := sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_mysql.New(migrate.WithDriverMultiStatements(config.MultiStatements)), 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"
	"database/sql"
	"time"

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

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

type GetLandingRow struct {
	ID         int64       `json:"id"`
	Species    string      `json:"species"`
	Boxes      int16       `json:"boxes"`
	Fish       int32       `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"`
	Berth      uint8       `json:"berth"`
	PricePerKg apd.Decimal `json:"price_per_kg"`
}

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, &row.Berth, &row.PricePerKg)
	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, berth, price_per_kg)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);`

type RecordLandingParams struct {
	Species    string
	Boxes      int16
	Fish       int32
	WeightG    int64
	LengthCm   float64
	OnIce      bool
	LandedAt   time.Time
	Tag        []byte
	Notes      *string
	Berth      uint8
	PricePerKg string
}

func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) (sql.Result, error) {
	return queries.writer.ExecContext(ctx, recordLandingSQL, params.Species, params.Boxes, params.Fish, params.WeightG, params.LengthCm, params.OnIce, params.LandedAt, params.Tag, params.Notes, params.Berth, params.PricePerKg)
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"time"

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

type Landings struct {
	ID         int64       `json:"id"`
	Species    string      `json:"species"`
	Boxes      int16       `json:"boxes"`
	Fish       int32       `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"`
	Berth      uint8       `json:"berth"`
	PricePerKg apd.Decimal `json:"price_per_kg"`
}

internal/store/querier.go

// Code generated by sgl. DO NOT EDIT.

// Package store reads DATETIME, DATE and TIMESTAMP columns into time.Time, so the DSN it connects with must set parseTime=true.
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         int16        12
fish          int32        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
berth         uint8        7
price_per_kg  apd.Decimal  4.20

From SQL types to Go types