Compare · 002

Choosing a command: one, many, exec, execrows, execresult: MySQL and MariaDB

MySQL and MariaDB run the same files for this example: the SQL and the Go are identical.

Compare MySQL withSQLitePostgreSQLCockroachDBTimescaleDBMariaDB

Configuration

sgl.yamlidentical
MySQL
MariaDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: harbour
4 - name: harbour
5 dialect: mysql
5 dialect: mysql
6 migrations: db/migrations
6 migrations: db/migrations
7 queries: db/queries
7 queries: db/queries
8 go:
8 go:
9 package: store
9 package: store
10 out: internal/store
10 out: internal/store

Migrations

db/migrations/0001_boats.up.sqlidentical
MySQL
MariaDB
1CREATE TABLE boats (
1CREATE TABLE boats (
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3 name VARCHAR(100) NOT NULL,
3 name VARCHAR(100) NOT NULL,
4 berth INT NOT NULL,
4 berth INT NOT NULL,
5 at_sea BOOLEAN NOT NULL DEFAULT FALSE
5 at_sea BOOLEAN NOT NULL DEFAULT FALSE
6);
6);
db/migrations/0001_boats.down.sqlidentical
MySQL
MariaDB
1DROP TABLE boats;
1DROP TABLE boats;

Queries

MoorBoatidentical
MySQL · db/queries/boats.sql
MariaDB · db/queries/boats.sql
1-- sgl.query(MoorBoat, execresult)
1-- sgl.query(MoorBoat, execresult)
2INSERT INTO boats (name, berth) VALUES (?, ?);
2INSERT INTO boats (name, berth) VALUES (?, ?);
GetBoatidentical
MySQL · db/queries/boats.sql
MariaDB · db/queries/boats.sql
1-- sgl.query(GetBoat, one)
1-- sgl.query(GetBoat, one)
2SELECT id, name, berth, at_sea FROM boats WHERE id = ?;
2SELECT id, name, berth, at_sea FROM boats WHERE id = ?;
ListBoatsidentical
MySQL · db/queries/boats.sql
MariaDB · db/queries/boats.sql
1-- sgl.query(ListBoats, many)
1-- sgl.query(ListBoats, many)
2SELECT id, name, berth, at_sea FROM boats ORDER BY berth;
2SELECT id, name, berth, at_sea FROM boats ORDER BY berth;
PutToSeaidentical
MySQL · db/queries/boats.sql
MariaDB · db/queries/boats.sql
1-- sgl.query(PutToSea, execrows)
1-- sgl.query(PutToSea, execrows)
2UPDATE boats SET at_sea = TRUE WHERE at_sea = FALSE;
2UPDATE boats SET at_sea = TRUE WHERE at_sea = FALSE;
ScrapBoatidentical
MySQL · db/queries/boats.sql
MariaDB · db/queries/boats.sql
1-- sgl.query(ScrapBoat, exec)
1-- sgl.query(ScrapBoat, exec)
2DELETE FROM boats WHERE id = ?;
2DELETE FROM boats WHERE id = ?;

What sgl generates

MoorBoatidentical
MySQL
MariaDB
1func (queries *Queries) MoorBoat(ctx context.Context, params MoorBoatParams) (sql.Result, error)
1func (queries *Queries) MoorBoat(ctx context.Context, params MoorBoatParams) (sql.Result, error)
2
2
3type MoorBoatParams struct {
3type MoorBoatParams struct {
4 Name string
4 Name string
5 Berth int32
5 Berth int32
6}
6}
GetBoatidentical
MySQL
MariaDB
1func (queries *Queries) GetBoat(ctx context.Context, id int64) (GetBoatRow, error)
1func (queries *Queries) GetBoat(ctx context.Context, id int64) (GetBoatRow, error)
2
2
3type GetBoatRow struct {
3type GetBoatRow struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 Name string `json:"name"`
5 Name string `json:"name"`
6 Berth int32 `json:"berth"`
6 Berth int32 `json:"berth"`
7 AtSea bool `json:"at_sea"`
7 AtSea bool `json:"at_sea"`
8}
8}
ListBoatsidentical
MySQL
MariaDB
1func (queries *Queries) ListBoats(ctx context.Context) ([]ListBoatsRow, error)
1func (queries *Queries) ListBoats(ctx context.Context) ([]ListBoatsRow, error)
2
2
3type ListBoatsRow struct {
3type ListBoatsRow struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 Name string `json:"name"`
5 Name string `json:"name"`
6 Berth int32 `json:"berth"`
6 Berth int32 `json:"berth"`
7 AtSea bool `json:"at_sea"`
7 AtSea bool `json:"at_sea"`
8}
8}
PutToSeaidentical
MySQL
MariaDB
1func (queries *Queries) PutToSea(ctx context.Context) (int64, error)
1func (queries *Queries) PutToSea(ctx context.Context) (int64, error)
ScrapBoatidentical
MySQL
MariaDB
1func (queries *Queries) ScrapBoat(ctx context.Context, id int64) error
1func (queries *Queries) ScrapBoat(ctx context.Context, id int64) error

The program

example.goidentical
MySQL
MariaDB
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "database/sql"
6 "errors"
6 "errors"
7 "fmt"
7 "fmt"
8 "io"
8 "io"
9
9
10 "seagull.sh/seagull/examples/mysql/002_commands/internal/store"
10 "seagull.sh/seagull/examples/mysql/002_commands/internal/store"
11)
11)
12
12
13// run moors three boats, looks one up, scraps one, puts the rest to sea and lists the
13// run moors three boats, looks one up, scraps one, puts the rest to sea and lists the
14// harbour. Each query in db/queries/boats.sql names a command, and the command decides
14// harbour. Each query in db/queries/boats.sql names a command, and the command decides
15// what its generated method returns:
15// what its generated method returns:
16//
16//
17// - execresult returns sql.Result, which carries the new row's AUTO_INCREMENT id;
17// - execresult returns sql.Result, which carries the new row's AUTO_INCREMENT id;
18// - one returns a single row, or sql.ErrNoRows when nothing matches;
18// - one returns a single row, or sql.ErrNoRows when nothing matches;
19// - many returns a slice of rows;
19// - many returns a slice of rows;
20// - execrows returns how many rows the statement changed;
20// - execrows returns how many rows the statement changed;
21// - exec returns only an error.
21// - exec returns only an error.
22//
22//
23// The command has to fit the statement: exec on a SELECT, or one on an INSERT, makes sgl
23// The command has to fit the statement: exec on a SELECT, or one on an INSERT, makes sgl
24// vet report Q009. MySQL has no RETURNING to turn a write into a one query, and sgl vet
24// vet report Q009. MySQL has no RETURNING to turn a write into a one query, and sgl vet
25// refuses one with Q052, so execresult is how an INSERT hands back its id.
25// refuses one with Q052, so execresult is how an INSERT hands back its id.
26//
26//
27// Takes database (*sql.DB) which is a migrated database.
27// Takes database (*sql.DB) which is a migrated database.
28// Takes output (io.Writer) which receives what each call returned.
28// Takes output (io.Writer) which receives what each call returned.
29//
29//
30// Returns error when a query fails.
30// Returns error when a query fails.
31func run(ctx context.Context, database *sql.DB, output io.Writer) error {
31func run(ctx context.Context, database *sql.DB, output io.Writer) error {
32 queries := store.New(database)
32 queries := store.New(database)
33
33
34 arrivals := []store.MoorBoatParams{
34 arrivals := []store.MoorBoatParams{
35 {Name: "Kittiwake", Berth: 1},
35 {Name: "Kittiwake", Berth: 1},
36 {Name: "Salty Chip", Berth: 2},
36 {Name: "Salty Chip", Berth: 2},
37 {Name: "Gull Wing", Berth: 5},
37 {Name: "Gull Wing", Berth: 5},
38 }
38 }
39 for _, arrival := range arrivals {
39 for _, arrival := range arrivals {
40 result, err := queries.MoorBoat(ctx, arrival)
40 result, err := queries.MoorBoat(ctx, arrival)
41 if err != nil {
41 if err != nil {
42 return fmt.Errorf("mooring %s: %w", arrival.Name, err)
42 return fmt.Errorf("mooring %s: %w", arrival.Name, err)
43 }
43 }
44 id, err := result.LastInsertId()
44 id, err := result.LastInsertId()
45 if err != nil {
45 if err != nil {
46 return fmt.Errorf("reading %s's ID: %w", arrival.Name, err)
46 return fmt.Errorf("reading %s's ID: %w", arrival.Name, err)
47 }
47 }
48 fmt.Fprintf(output, "execresult moored %s as boat %d\n", arrival.Name, id)
48 fmt.Fprintf(output, "execresult moored %s as boat %d\n", arrival.Name, id)
49 }
49 }
50
50
51 boat, err := queries.GetBoat(ctx, 2)
51 boat, err := queries.GetBoat(ctx, 2)
52 if err != nil {
52 if err != nil {
53 return fmt.Errorf("getting boat 2: %w", err)
53 return fmt.Errorf("getting boat 2: %w", err)
54 }
54 }
55 fmt.Fprintf(output, "one boat 2 is %s, at berth %d\n", boat.Name, boat.Berth)
55 fmt.Fprintf(output, "one boat 2 is %s, at berth %d\n", boat.Name, boat.Berth)
56
56
57 if err := queries.ScrapBoat(ctx, 3); err != nil {
57 if err := queries.ScrapBoat(ctx, 3); err != nil {
58 return fmt.Errorf("scrapping boat 3: %w", err)
58 return fmt.Errorf("scrapping boat 3: %w", err)
59 }
59 }
60 fmt.Fprintln(output, "exec scrapped boat 3")
60 fmt.Fprintln(output, "exec scrapped boat 3")
61
61
62 _, err = queries.GetBoat(ctx, 3)
62 _, err = queries.GetBoat(ctx, 3)
63 if err == nil {
63 if err == nil {
64 return errors.New("getting scrapped boat 3: got a boat, want sql.ErrNoRows")
64 return errors.New("getting scrapped boat 3: got a boat, want sql.ErrNoRows")
65 }
65 }
66 if !errors.Is(err, sql.ErrNoRows) {
66 if !errors.Is(err, sql.ErrNoRows) {
67 return fmt.Errorf("getting scrapped boat 3: got %w, want sql.ErrNoRows", err)
67 return fmt.Errorf("getting scrapped boat 3: got %w, want sql.ErrNoRows", err)
68 }
68 }
69 fmt.Fprintln(output, "one boat 3 is gone, so GetBoat returns sql.ErrNoRows")
69 fmt.Fprintln(output, "one boat 3 is gone, so GetBoat returns sql.ErrNoRows")
70
70
71 // PutToSea only changes boats still in harbour, so a second run changes none. MySQL
71 // PutToSea only changes boats still in harbour, so a second run changes none. MySQL
72 // counts the rows a statement changed, not the rows its WHERE matched, unless the DSN
72 // counts the rows a statement changed, not the rows its WHERE matched, unless the DSN
73 // sets clientFoundRows=true.
73 // sets clientFoundRows=true.
74 for range 2 {
74 for range 2 {
75 sailed, err := queries.PutToSea(ctx)
75 sailed, err := queries.PutToSea(ctx)
76 if err != nil {
76 if err != nil {
77 return fmt.Errorf("putting boats to sea: %w", err)
77 return fmt.Errorf("putting boats to sea: %w", err)
78 }
78 }
79 fmt.Fprintf(output, "execrows %d boats put to sea\n", sailed)
79 fmt.Fprintf(output, "execrows %d boats put to sea\n", sailed)
80 }
80 }
81
81
82 boats, err := queries.ListBoats(ctx)
82 boats, err := queries.ListBoats(ctx)
83 if err != nil {
83 if err != nil {
84 return fmt.Errorf("listing boats: %w", err)
84 return fmt.Errorf("listing boats: %w", err)
85 }
85 }
86 for _, boat := range boats {
86 for _, boat := range boats {
87 fmt.Fprintf(output, "many berth %d %-10s at sea: %t\n", boat.Berth, boat.Name, boat.AtSea)
87 fmt.Fprintf(output, "many berth %d %-10s at sea: %t\n", boat.Berth, boat.Name, boat.AtSea)
88 }
88 }
89 return nil
89 return nil
90}
90}