Compare · 003

Placeholders, inferred names and Params structs: MySQL and CockroachDB

Every file of the MySQL and CockroachDB versions side by side, with the lines that differ marked. 4 parameter or column types differ in Go.

Compare MySQL withSQLitePostgreSQLCockroachDBTimescaleDBMariaDBDuckDBClickHouse

Configuration

sgl.yamldiffers
MySQL
CockroachDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: ferries
4 - name: ferries
5 dialect: mysql
5 dialect: postgres
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
11 driver: database/sql

Migrations

db/migrations/0001_sailings.up.sqldiffers
MySQL
CockroachDB
1CREATE TABLE sailings (
1CREATE TABLE sailings (
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3 origin VARCHAR(100) NOT NULL,
3 origin TEXT NOT NULL,
4 destination VARCHAR(100) NOT NULL,
4 destination TEXT NOT NULL,
5 departs CHAR(5) NOT NULL,
5 departs TEXT NOT NULL,
6 seats_free INT NOT NULL
6 seats_free BIGINT NOT NULL
7);
7);
db/migrations/0001_sailings.down.sqlidentical
MySQL
CockroachDB
1DROP TABLE sailings;
1DROP TABLE sailings;

Queries

AddSailingdiffers
MySQL · db/queries/sailings.sql
CockroachDB · db/queries/sailings.sql
1-- sgl.query(AddSailing, exec)
1-- sgl.query(AddSailing, exec)
2INSERT INTO sailings (origin, destination, departs, seats_free) VALUES (?, ?, ?, ?);
2INSERT INTO sailings (origin, destination, departs, seats_free) VALUES ($1, $2, $3, $4);
FindSailingsdiffers
MySQL · db/queries/sailings.sql
CockroachDB · db/queries/sailings.sql
1-- sgl.query(FindSailings, many)
1-- sgl.query(FindSailings, many)
2SELECT id, origin, destination, departs, seats_free
2SELECT id, origin, destination, departs, seats_free
3FROM sailings
3FROM sailings
4WHERE origin = ? AND destination = ?
4WHERE origin = $1 AND destination = $2
5ORDER BY departs;
5ORDER BY departs;
SailingsDepartingdiffers
MySQL · db/queries/sailings.sql
CockroachDB · db/queries/sailings.sql
1-- sgl.query(SailingsDeparting, many)
1-- sgl.query(SailingsDeparting, many)
2-- ?1 as sgl.param(earliest)
2-- $1 as sgl.param(earliest)
3-- ?2 as sgl.param(latest)
3-- $2 as sgl.param(latest)
4SELECT id, origin, destination, departs, seats_free
4SELECT id, origin, destination, departs, seats_free
5FROM sailings
5FROM sailings
6WHERE departs >= ?1 AND departs < ?2
6WHERE departs >= $1 AND departs < $2
7ORDER BY departs;
7ORDER BY departs;
BookSeatsdiffers
MySQL · db/queries/sailings.sql
CockroachDB · db/queries/sailings.sql
1-- sgl.query(BookSeats, execrows)
1-- sgl.query(BookSeats, execrows)
2-- ?1 as sgl.param(seats)
3-- ?2 as sgl.param(sailing_id)
4UPDATE sailings
2UPDATE sailings
5SET seats_free = seats_free - ?1
3SET seats_free = seats_free - :seats
6WHERE id = ?2 AND seats_free >= ?1;
4WHERE id = :sailing_id AND seats_free >= :seats;

What sgl generates

AddSailingdiffers
MySQL
CockroachDB
1func (queries *Queries) AddSailing(ctx context.Context, params AddSailingParams) error
1func (queries *Queries) AddSailing(ctx context.Context, params AddSailingParams) error
2
2
3type AddSailingParams struct {
3type AddSailingParams struct {
4 Origin string
4 Origin string
5 Destination string
5 Destination string
6 Departs string
6 Departs string
7 SeatsFree int32
7 SeatsFree int64
8}
8}
FindSailingsdiffers
MySQL
CockroachDB
1func (queries *Queries) FindSailings(ctx context.Context, params FindSailingsParams) ([]FindSailingsRow, error)
1func (queries *Queries) FindSailings(ctx context.Context, params FindSailingsParams) ([]FindSailingsRow, error)
2
2
3type FindSailingsParams struct {
3type FindSailingsParams struct {
4 Origin string
4 Origin string
5 Destination string
5 Destination string
6}
6}
7
7
8type FindSailingsRow struct {
8type FindSailingsRow struct {
9 ID int64 `json:"id"`
9 ID int64 `json:"id"`
10 Origin string `json:"origin"`
10 Origin string `json:"origin"`
11 Destination string `json:"destination"`
11 Destination string `json:"destination"`
12 Departs string `json:"departs"`
12 Departs string `json:"departs"`
13 SeatsFree int32 `json:"seats_free"`
13 SeatsFree int64 `json:"seats_free"`
14}
14}
SailingsDepartingdiffers
MySQL
CockroachDB
1func (queries *Queries) SailingsDeparting(ctx context.Context, params SailingsDepartingParams) ([]SailingsDepartingRow, error)
1func (queries *Queries) SailingsDeparting(ctx context.Context, params SailingsDepartingParams) ([]SailingsDepartingRow, error)
2
2
3type SailingsDepartingParams struct {
3type SailingsDepartingParams struct {
4 Earliest string
4 Earliest string
5 Latest string
5 Latest string
6}
6}
7
7
8type SailingsDepartingRow struct {
8type SailingsDepartingRow struct {
9 ID int64 `json:"id"`
9 ID int64 `json:"id"`
10 Origin string `json:"origin"`
10 Origin string `json:"origin"`
11 Destination string `json:"destination"`
11 Destination string `json:"destination"`
12 Departs string `json:"departs"`
12 Departs string `json:"departs"`
13 SeatsFree int32 `json:"seats_free"`
13 SeatsFree int64 `json:"seats_free"`
14}
14}
BookSeatsdiffers
MySQL
CockroachDB
1func (queries *Queries) BookSeats(ctx context.Context, params BookSeatsParams) (int64, error)
1func (queries *Queries) BookSeats(ctx context.Context, params BookSeatsParams) (int64, error)
2
2
3type BookSeatsParams struct {
3type BookSeatsParams struct {
4 Seats int32
4 Seats int64
5 SailingID int64
5 SailingID int64
6}
6}

The program

example.godiffers
MySQL
CockroachDB
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "database/sql"
6 "fmt"
6 "fmt"
7 "io"
7 "io"
8
8
9 "seagull.sh/seagull/examples/mysql/003_parameters/internal/store"
9 "seagull.sh/seagull/examples/postgres/003_parameters/internal/store"
10)
10)
11
11
12// run fills a ferry timetable, searches it two ways and books seats. Every query has two
12// run fills a ferry timetable, searches it two ways and books seats. Every query has two
13// or more parameters, so each method takes a Params struct, and the struct's field names
13// or more parameters, so each method takes a Params struct, and the struct's field names
14// come from the SQL:
14// come from the SQL:
15//
15//
16// - AddSailing and FindSailings name each ? after the column it is inserted into or
16// - AddSailing and FindSailings name each $N after the column it is inserted into or
17// compared with: Origin, Destination, Departs, SeatsFree;
17// compared with: Origin, Destination, Departs, SeatsFree;
18// - SailingsDeparting compares departs twice, which would give Departs and Departs2, so
18// - SailingsDeparting compares departs twice, which would give Departs and Departs2, so
19// its binding lines (?1 as sgl.param(earliest)) choose better names;
19// its binding lines ($1 as sgl.param(earliest)) choose better names;
20// - BookSeats numbers its placeholders: ?1 appears twice but is one Seats field.
20// - BookSeats uses named placeholders; :seats appears twice but is one field. The
21//
21// generated SQL numbers them, so :seats becomes $1 in both places, as PostgreSQL
22// MySQL itself takes only ?, so the generated SQL has a plain ? for each use, and the
22// binds a reused number once.
23// method passes Seats once for each.
24//
23//
25// Takes database (*sql.DB) which is a migrated database.
24// Takes database (*sql.DB) which is a migrated database.
26// Takes output (io.Writer) which receives the search results and bookings.
25// Takes output (io.Writer) which receives the search results and bookings.
27//
26//
28// Returns error when a query fails.
27// Returns error when a query fails.
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
28func run(ctx context.Context, database *sql.DB, output io.Writer) error {
30 queries := store.New(database)
29 queries := store.New(database)
31
30
32 timetable := []store.AddSailingParams{
31 timetable := []store.AddSailingParams{
33 {Origin: "Penzance", Destination: "St Mary's", Departs: "09:15", SeatsFree: 40},
32 {Origin: "Penzance", Destination: "St Mary's", Departs: "09:15", SeatsFree: 40},
34 {Origin: "St Mary's", Destination: "Tresco", Departs: "11:00", SeatsFree: 12},
33 {Origin: "St Mary's", Destination: "Tresco", Departs: "11:00", SeatsFree: 12},
35 {Origin: "Penzance", Destination: "St Mary's", Departs: "14:30", SeatsFree: 4},
34 {Origin: "Penzance", Destination: "St Mary's", Departs: "14:30", SeatsFree: 4},
36 {Origin: "St Mary's", Destination: "Penzance", Departs: "16:30", SeatsFree: 60},
35 {Origin: "St Mary's", Destination: "Penzance", Departs: "16:30", SeatsFree: 60},
37 }
36 }
38 for _, sailing := range timetable {
37 for _, sailing := range timetable {
39 if err := queries.AddSailing(ctx, sailing); err != nil {
38 if err := queries.AddSailing(ctx, sailing); err != nil {
40 return fmt.Errorf("adding the %s sailing: %w", sailing.Departs, err)
39 return fmt.Errorf("adding the %s sailing: %w", sailing.Departs, err)
41 }
40 }
42 }
41 }
43
42
44 route, err := queries.FindSailings(ctx, store.FindSailingsParams{
43 route, err := queries.FindSailings(ctx, store.FindSailingsParams{
45 Origin: "Penzance",
44 Origin: "Penzance",
46 Destination: "St Mary's",
45 Destination: "St Mary's",
47 })
46 })
48 if err != nil {
47 if err != nil {
49 return fmt.Errorf("finding sailings: %w", err)
48 return fmt.Errorf("finding sailings: %w", err)
50 }
49 }
51 fmt.Fprintln(output, "Penzance to St Mary's:")
50 fmt.Fprintln(output, "Penzance to St Mary's:")
52 for _, sailing := range route {
51 for _, sailing := range route {
53 fmt.Fprintf(output, " sailing %d at %s, %d seats free\n", sailing.ID, sailing.Departs, sailing.SeatsFree)
52 fmt.Fprintf(output, " sailing %d at %s, %d seats free\n", sailing.ID, sailing.Departs, sailing.SeatsFree)
54 }
53 }
55
54
56 midday, err := queries.SailingsDeparting(ctx, store.SailingsDepartingParams{
55 midday, err := queries.SailingsDeparting(ctx, store.SailingsDepartingParams{
57 Earliest: "10:00",
56 Earliest: "10:00",
58 Latest: "15:00",
57 Latest: "15:00",
59 })
58 })
60 if err != nil {
59 if err != nil {
61 return fmt.Errorf("finding midday sailings: %w", err)
60 return fmt.Errorf("finding midday sailings: %w", err)
62 }
61 }
63 fmt.Fprintln(output, "Departing between 10:00 and 15:00:")
62 fmt.Fprintln(output, "Departing between 10:00 and 15:00:")
64 for _, sailing := range midday {
63 for _, sailing := range midday {
65 fmt.Fprintf(output, " sailing %d at %s, %s to %s\n", sailing.ID, sailing.Departs, sailing.Origin, sailing.Destination)
64 fmt.Fprintf(output, " sailing %d at %s, %s to %s\n", sailing.ID, sailing.Departs, sailing.Origin, sailing.Destination)
66 }
65 }
67
66
68 // The same booking twice: the first takes 3 of the 4 free seats, so the second changes
67 // The same booking twice: the first takes 3 of the 4 free seats, so the second changes
69 // no row because seats_free >= ?1 is now false.
68 // no row because seats_free >= :seats is now false.
70 booking := store.BookSeatsParams{Seats: 3, SailingID: 3}
69 booking := store.BookSeatsParams{Seats: 3, SailingID: 3}
71 for range 2 {
70 for range 2 {
72 booked, err := queries.BookSeats(ctx, booking)
71 booked, err := queries.BookSeats(ctx, booking)
73 if err != nil {
72 if err != nil {
74 return fmt.Errorf("booking sailing %d: %w", booking.SailingID, err)
73 return fmt.Errorf("booking sailing %d: %w", booking.SailingID, err)
75 }
74 }
76 if booked == 0 {
75 if booked == 0 {
77 fmt.Fprintf(output, "sailing %d has fewer than %d seats free\n", booking.SailingID, booking.Seats)
76 fmt.Fprintf(output, "sailing %d has fewer than %d seats free\n", booking.SailingID, booking.Seats)
78 continue
77 continue
79 }
78 }
80 fmt.Fprintf(output, "booked %d seats on sailing %d\n", booking.Seats, booking.SailingID)
79 fmt.Fprintf(output, "booked %d seats on sailing %d\n", booking.Seats, booking.SailingID)
81 }
80 }
82 return nil
81 return nil
83}
82}

Go type differences

The same SQL types map to different Go types, because each database reports them differently.

QueryFieldMySQLCockroachDB
AddSailingparameter seats_freeint32int64
FindSailingscolumn seats_freeint32int64
SailingsDepartingcolumn seats_freeint32int64
BookSeatsparameter seatsint32int64