From SQL types to Go types: MariaDB and MySQL
MariaDB and MySQL run the same files for this example: the SQL and the Go are identical.
Configuration
sgl.yamlidentical
MariaDB
MySQL
1
version: 11
version: 12
2
3
databases:3
databases:4
- name: landings4
- name: landings5
dialect: mysql5
dialect: mysql6
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/storeMigrations
db/migrations/0001_landings.up.sqlidentical
MariaDB
MySQL
1
CREATE TABLE landings (1
CREATE TABLE landings (2
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,2
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,3
species VARCHAR(50) NOT NULL,3
species VARCHAR(50) NOT NULL,4
boxes SMALLINT NOT NULL,4
boxes SMALLINT NOT NULL,5
fish INT NOT NULL,5
fish INT NOT NULL,6
weight_g BIGINT NOT NULL,6
weight_g BIGINT NOT NULL,7
length_cm DOUBLE NOT NULL,7
length_cm DOUBLE NOT NULL,8
on_ice BOOLEAN NOT NULL,8
on_ice BOOLEAN NOT NULL,9
landed_at DATETIME NOT NULL,9
landed_at DATETIME NOT NULL,10
tag VARBINARY(16) NOT NULL,10
tag VARBINARY(16) NOT NULL,11
notes TEXT,11
notes TEXT,12
berth TINYINT UNSIGNED NOT NULL,12
berth TINYINT UNSIGNED NOT NULL,13
price_per_kg DECIMAL(6, 2) NOT NULL13
price_per_kg DECIMAL(6, 2) NOT NULL14
);14
);db/migrations/0001_landings.down.sqlidentical
MariaDB
MySQL
1
DROP TABLE landings;1
DROP TABLE landings;Queries
RecordLandingidentical
MariaDB · db/queries/landings.sql
MySQL · db/queries/landings.sql
1
-- sgl.query(RecordLanding, execresult)1
-- sgl.query(RecordLanding, execresult)2
-- ?11 as sgl.param(price_per_kg, type: text)2
-- ?11 as sgl.param(price_per_kg, type: text)3
INSERT INTO landings (species, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes, berth, price_per_kg)3
INSERT INTO landings (species, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes, berth, price_per_kg)4
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);4
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);GetLandingidentical
MariaDB · db/queries/landings.sql
MySQL · db/queries/landings.sql
1
-- sgl.query(GetLanding, one)1
-- sgl.query(GetLanding, one)2
SELECT id, species, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes, berth, price_per_kg2
SELECT id, species, boxes, fish, weight_g, length_cm, on_ice, landed_at, tag, notes, berth, price_per_kg3
FROM landings3
FROM landings4
WHERE id = ?;4
WHERE id = ?;What sgl generates
RecordLandingidentical
MariaDB
MySQL
1
func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) (sql.Result, error)1
func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) (sql.Result, error)2
2
3
type RecordLandingParams struct {3
type RecordLandingParams struct {4
Species string4
Species string5
Boxes int165
Boxes int166
Fish int326
Fish int327
WeightG int647
WeightG int648
LengthCm float648
LengthCm float649
OnIce bool9
OnIce bool10
LandedAt time.Time10
LandedAt time.Time11
Tag []byte11
Tag []byte12
Notes *string12
Notes *string13
Berth uint813
Berth uint814
PricePerKg string14
PricePerKg string15
}15
}GetLandingidentical
MariaDB
MySQL
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
Species string `json:"species"`5
Species string `json:"species"`6
Boxes int16 `json:"boxes"`6
Boxes int16 `json:"boxes"`7
Fish int32 `json:"fish"`7
Fish int32 `json:"fish"`8
WeightG int64 `json:"weight_g"`8
WeightG int64 `json:"weight_g"`9
LengthCm float64 `json:"length_cm"`9
LengthCm float64 `json:"length_cm"`10
OnIce bool `json:"on_ice"`10
OnIce bool `json:"on_ice"`11
LandedAt time.Time `json:"landed_at"`11
LandedAt time.Time `json:"landed_at"`12
Tag []byte `json:"tag"`12
Tag []byte `json:"tag"`13
Notes *string `json:"notes"`13
Notes *string `json:"notes"`14
Berth uint8 `json:"berth"`14
Berth uint8 `json:"berth"`15
PricePerKg apd.Decimal `json:"price_per_kg"`15
PricePerKg apd.Decimal `json:"price_per_kg"`16
}16
}The program
example.goidentical
MariaDB
MySQL
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
"seagull.sh/seagull/examples/mysql/005_type_mapping/internal/store"10
"seagull.sh/seagull/examples/mysql/005_type_mapping/internal/store"11
)11
)12
12
13
// run records a landing of fish and reads it back, printing the Go type sgl chose for13
// run records a landing of fish and reads it back, printing the Go type sgl chose for14
// each column. Seagull maps the type each column declares in db/migrations: SMALLINT, INT14
// each column. Seagull maps the type each column declares in db/migrations: SMALLINT, INT15
// and BIGINT give int16, int32 and int64, and an UNSIGNED integer gives the unsigned Go15
// and BIGINT give int16, int32 and int64, and an UNSIGNED integer gives the unsigned Go16
// type of the same width, so TINYINT UNSIGNED is uint8. DOUBLE gives float64, BOOLEAN16
// type of the same width, so TINYINT UNSIGNED is uint8. DOUBLE gives float64, BOOLEAN17
// (MySQL's TINYINT(1)) gives bool, DATETIME gives time.Time when the DSN sets17
// (MySQL's TINYINT(1)) gives bool, DATETIME gives time.Time when the DSN sets18
// parseTime=true, and VARBINARY gives []byte.18
// parseTime=true, and VARBINARY gives []byte.19
//19
//20
// DECIMAL gives apd.Decimal from github.com/cockroachdb/apd/v3, which holds the exact20
// DECIMAL gives apd.Decimal from github.com/cockroachdb/apd/v3, which holds the exact21
// value. The insert binds the price as text instead, through sgl.param(price_per_kg,21
// value. The insert binds the price as text instead, through sgl.param(price_per_kg,22
// type: text), and MySQL converts "4.20" to DECIMAL(6, 2) without rounding.22
// type: text), and MySQL converts "4.20" to DECIMAL(6, 2) without rounding.23
//23
//24
// Takes database (*sql.DB) which is a migrated database.24
// Takes database (*sql.DB) which is a migrated database.25
// Takes output (io.Writer) which receives one line per column.25
// Takes output (io.Writer) which receives one line per column.26
//26
//27
// Returns error when a query fails.27
// Returns error when a query fails.28
func run(ctx context.Context, database *sql.DB, output io.Writer) error {28
func run(ctx context.Context, database *sql.DB, output io.Writer) error {29
queries := store.New(database)29
queries := store.New(database)30
30
31
result, err := queries.RecordLanding(ctx, store.RecordLandingParams{31
result, err := queries.RecordLanding(ctx, store.RecordLandingParams{32
Species: "mackerel",32
Species: "mackerel",33
Boxes: 12,33
Boxes: 12,34
Fish: 480,34
Fish: 480,35
WeightG: 163_200,35
WeightG: 163_200,36
LengthCm: 31.5,36
LengthCm: 31.5,37
OnIce: true,37
OnIce: true,38
LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),38
LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),39
Tag: []byte{0x04, 0xa2, 0x19, 0xf3},39
Tag: []byte{0x04, 0xa2, 0x19, 0xf3},40
Notes: new("landed at Newlyn"),40
Notes: new("landed at Newlyn"),41
Berth: 7,41
Berth: 7,42
PricePerKg: "4.20",42
PricePerKg: "4.20",43
})43
})44
if err != nil {44
if err != nil {45
return fmt.Errorf("recording a landing: %w", err)45
return fmt.Errorf("recording a landing: %w", err)46
}46
}47
id, err := result.LastInsertId()47
id, err := result.LastInsertId()48
if err != nil {48
if err != nil {49
return fmt.Errorf("reading the landing's id: %w", err)49
return fmt.Errorf("reading the landing's id: %w", err)50
}50
}51
51
52
landing, err := queries.GetLanding(ctx, id)52
landing, err := queries.GetLanding(ctx, id)53
if err != nil {53
if err != nil {54
return fmt.Errorf("getting landing %d: %w", id, err)54
return fmt.Errorf("getting landing %d: %w", id, err)55
}55
}56
fmt.Fprintf(output, "id %-12T %d\n", landing.ID, landing.ID)56
fmt.Fprintf(output, "id %-12T %d\n", landing.ID, landing.ID)57
fmt.Fprintf(output, "species %-12T %s\n", landing.Species, landing.Species)57
fmt.Fprintf(output, "species %-12T %s\n", landing.Species, landing.Species)58
fmt.Fprintf(output, "boxes %-12T %d\n", landing.Boxes, landing.Boxes)58
fmt.Fprintf(output, "boxes %-12T %d\n", landing.Boxes, landing.Boxes)59
fmt.Fprintf(output, "fish %-12T %d\n", landing.Fish, landing.Fish)59
fmt.Fprintf(output, "fish %-12T %d\n", landing.Fish, landing.Fish)60
fmt.Fprintf(output, "weight_g %-12T %d\n", landing.WeightG, landing.WeightG)60
fmt.Fprintf(output, "weight_g %-12T %d\n", landing.WeightG, landing.WeightG)61
fmt.Fprintf(output, "length_cm %-12T %.1f\n", landing.LengthCm, landing.LengthCm)61
fmt.Fprintf(output, "length_cm %-12T %.1f\n", landing.LengthCm, landing.LengthCm)62
fmt.Fprintf(output, "on_ice %-12T %t\n", landing.OnIce, landing.OnIce)62
fmt.Fprintf(output, "on_ice %-12T %t\n", landing.OnIce, landing.OnIce)63
fmt.Fprintf(output, "landed_at %-12T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))63
fmt.Fprintf(output, "landed_at %-12T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))64
// %T prints []byte as []uint8, since byte is an alias for uint8.64
// %T prints []byte as []uint8, since byte is an alias for uint8.65
fmt.Fprintf(output, "tag %-12T % x\n", landing.Tag, landing.Tag)65
fmt.Fprintf(output, "tag %-12T % x\n", landing.Tag, landing.Tag)66
fmt.Fprintf(output, "notes %-12T %s\n", landing.Notes, *landing.Notes)66
fmt.Fprintf(output, "notes %-12T %s\n", landing.Notes, *landing.Notes)67
fmt.Fprintf(output, "berth %-12T %d\n", landing.Berth, landing.Berth)67
fmt.Fprintf(output, "berth %-12T %d\n", landing.Berth, landing.Berth)68
fmt.Fprintf(output, "price_per_kg %-12T %s\n", landing.PricePerKg, landing.PricePerKg.String())68
fmt.Fprintf(output, "price_per_kg %-12T %s\n", landing.PricePerKg, landing.PricePerKg.String())69
return nil69
return nil70
}70
}