Compare · 003

Placeholders, inferred names and Params structs: ClickHouse and MariaDB

Every file of the ClickHouse and MariaDB versions side by side, with the lines that differ marked. 3 parameter or column types differ in Go.

Compare ClickHouse withSQLitePostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
ClickHouse
MariaDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: ferries
4 - name: ferries
5 dialect: clickhouse
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_sailings.up.sqldiffers
ClickHouse
MariaDB
1CREATE TABLE sailings (
1CREATE TABLE sailings (
2 id UInt64,
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3 origin String,
3 origin VARCHAR(100) NOT NULL,
4 destination String,
4 destination VARCHAR(100) NOT NULL,
5 departs String,
5 departs CHAR(5) NOT NULL,
6 seats_free Int32
6 seats_free INT NOT NULL
7) ENGINE = MergeTree() ORDER BY id;
7);
db/migrations/0001_sailings.down.sqlidentical
ClickHouse
MariaDB
1DROP TABLE sailings;
1DROP TABLE sailings;

Queries

AddSailingdiffers
ClickHouse · db/queries/sailings.sql
MariaDB · db/queries/sailings.sql
1-- sgl.query(AddSailing, exec)
1-- sgl.query(AddSailing, exec)
2INSERT INTO sailings (id, origin, destination, departs, seats_free)
2INSERT INTO sailings (origin, destination, departs, seats_free) VALUES (?, ?, ?, ?);
3VALUES ({id:UInt64}, {origin:String}, {destination:String}, {departs:String}, {seats_free:Int32});
FindSailingsdiffers
ClickHouse · db/queries/sailings.sql
MariaDB · 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 = {origin:String} AND destination = {destination:String}
4WHERE origin = ? AND destination = ?
5ORDER BY departs;
5ORDER BY departs;
SailingsDepartingdiffers
ClickHouse · db/queries/sailings.sql
MariaDB · db/queries/sailings.sql
1-- sgl.query(SailingsDeparting, many)
1-- sgl.query(SailingsDeparting, many)
2-- ?1 as sgl.param(earliest)
3-- ?2 as sgl.param(latest)
2SELECT id, origin, destination, departs, seats_free
4SELECT id, origin, destination, departs, seats_free
3FROM sailings
5FROM sailings
4WHERE departs >= {earliest:String} AND departs < {latest:String}
6WHERE departs >= ?1 AND departs < ?2
5ORDER BY departs;
7ORDER BY departs;
BookSeatsdiffers
ClickHouse · db/queries/sailings.sql
MariaDB · db/queries/sailings.sql
1-- sgl.query(BookSeats, asyncexec)
1-- sgl.query(BookSeats, execrows)
2ALTER TABLE sailings
2-- ?1 as sgl.param(seats)
3UPDATE seats_free = seats_free - {seats:Int32}
3-- ?2 as sgl.param(sailing_id)
4WHERE id = {sailing_id:UInt64} AND seats_free >= {seats:Int32};
4UPDATE sailings
5SET seats_free = seats_free - ?1
6WHERE id = ?2 AND seats_free >= ?1;
SeatsFreeonly ClickHouse
ClickHouse · db/queries/sailings.sql
MariaDB · not in this version
1-- sgl.query(SeatsFree, one)
2SELECT seats_free FROM sailings WHERE id = {sailing_id:UInt64};

What sgl generates

AddSailingdiffers
ClickHouse
MariaDB
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 ID uint64
5 Origin string
4 Origin string
6 Destination string
5 Destination string
7 Departs string
6 Departs string
8 SeatsFree int32
7 SeatsFree int32
9}
8}
FindSailingsdiffers
ClickHouse
MariaDB
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 uint64 `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 int32 `json:"seats_free"`
14}
14}
SailingsDepartingdiffers
ClickHouse
MariaDB
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 uint64 `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 int32 `json:"seats_free"`
14}
14}
BookSeatsdiffers
ClickHouse
MariaDB
1func (queries *Queries) BookSeats(ctx context.Context, params BookSeatsParams) 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 int32
5 SailingID uint64
5 SailingID int64
6}
6}
SeatsFreeonly ClickHouse
ClickHouse
MariaDB · not in this version
1func (queries *Queries) SeatsFree(ctx context.Context, sailingID uint64) (SeatsFreeRow, error)
2
3type SeatsFreeRow struct {
4 SeatsFree int32 `json:"seats_free"`
5}

The program

example.godiffers
ClickHouse
MariaDB
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/clickhouse/003_parameters/internal/store"
9 "seagull.sh/seagull/examples/mysql/003_parameters/internal/store"
10)
10)
11
11
12// run fills a ferry timetable, searches it two ways and books seats. A ClickHouse
12// run fills a ferry timetable, searches it two ways and books seats. Every query has two
13// placeholder carries its own name and type, as in {origin:String}, so each Params field
13// or more parameters, so each method takes a Params struct, and the struct's field names
14// is named after the placeholder and typed from it: String gives string, Int32 int32 and
14// come from the SQL:
15// UInt64 uint64. A query with two or more parameters takes a Params struct:
16//
17// - AddSailing and FindSailings name their fields ID, Origin, Destination, Departs and
18// SeatsFree from {id:UInt64}, {origin:String} and the rest;
19// - SailingsDeparting compares departs twice, so it names the two bounds itself with
20// {earliest:String} and {latest:String}, and needs no binding line;
21// - BookSeats uses {seats:Int32} twice, but it is one field, bound once.
22//
15//
23// Seagull sends each placeholder as p_<name>, as in {p_seats:Int32}, so a parameter may
16// - AddSailing and FindSailings name each ? after the column it is inserted into or
24// be called limit or offset without the server reading it as a keyword.
17// compared with: Origin, Destination, Departs, SeatsFree;
18// - SailingsDeparting compares departs twice, which would give Departs and Departs2, so
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.
25//
21//
26// ALTER TABLE ... UPDATE reports no changed rows, so BookSeats is asyncexec and the
22// MySQL itself takes only ?, so the generated SQL has a plain ? for each use, and the
27// booking reads seats_free before and after to see whether the guard let it through.
23// method passes Seats once for each.
28//
24//
29// Takes database (*sql.DB) which is a migrated database.
25// Takes database (*sql.DB) which is a migrated database.
30// Takes output (io.Writer) which receives the search results and bookings.
26// Takes output (io.Writer) which receives the search results and bookings.
31//
27//
32// Returns error when a query fails.
28// Returns error when a query fails.
33func run(ctx context.Context, database *sql.DB, output io.Writer) error {
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
34 queries := store.New(database)
30 queries := store.New(database)
35
31
36 timetable := []store.AddSailingParams{
32 timetable := []store.AddSailingParams{
37 {ID: 1, Origin: "Penzance", Destination: "St Mary's", Departs: "09:15", SeatsFree: 40},
33 {Origin: "Penzance", Destination: "St Mary's", Departs: "09:15", SeatsFree: 40},
38 {ID: 2, Origin: "St Mary's", Destination: "Tresco", Departs: "11:00", SeatsFree: 12},
34 {Origin: "St Mary's", Destination: "Tresco", Departs: "11:00", SeatsFree: 12},
39 {ID: 3, Origin: "Penzance", Destination: "St Mary's", Departs: "14:30", SeatsFree: 4},
35 {Origin: "Penzance", Destination: "St Mary's", Departs: "14:30", SeatsFree: 4},
40 {ID: 4, Origin: "St Mary's", Destination: "Penzance", Departs: "16:30", SeatsFree: 60},
36 {Origin: "St Mary's", Destination: "Penzance", Departs: "16:30", SeatsFree: 60},
41 }
37 }
42 for _, sailing := range timetable {
38 for _, sailing := range timetable {
43 if err := queries.AddSailing(ctx, sailing); err != nil {
39 if err := queries.AddSailing(ctx, sailing); err != nil {
44 return fmt.Errorf("adding the %s sailing: %w", sailing.Departs, err)
40 return fmt.Errorf("adding the %s sailing: %w", sailing.Departs, err)
45 }
41 }
46 }
42 }
47
43
48 route, err := queries.FindSailings(ctx, store.FindSailingsParams{
44 route, err := queries.FindSailings(ctx, store.FindSailingsParams{
49 Origin: "Penzance",
45 Origin: "Penzance",
50 Destination: "St Mary's",
46 Destination: "St Mary's",
51 })
47 })
52 if err != nil {
48 if err != nil {
53 return fmt.Errorf("finding sailings: %w", err)
49 return fmt.Errorf("finding sailings: %w", err)
54 }
50 }
55 fmt.Fprintln(output, "Penzance to St Mary's:")
51 fmt.Fprintln(output, "Penzance to St Mary's:")
56 for _, sailing := range route {
52 for _, sailing := range route {
57 fmt.Fprintf(output, " sailing %d at %s, %d seats free\n", sailing.ID, sailing.Departs, sailing.SeatsFree)
53 fmt.Fprintf(output, " sailing %d at %s, %d seats free\n", sailing.ID, sailing.Departs, sailing.SeatsFree)
58 }
54 }
59
55
60 midday, err := queries.SailingsDeparting(ctx, store.SailingsDepartingParams{
56 midday, err := queries.SailingsDeparting(ctx, store.SailingsDepartingParams{
61 Earliest: "10:00",
57 Earliest: "10:00",
62 Latest: "15:00",
58 Latest: "15:00",
63 })
59 })
64 if err != nil {
60 if err != nil {
65 return fmt.Errorf("finding midday sailings: %w", err)
61 return fmt.Errorf("finding midday sailings: %w", err)
66 }
62 }
67 fmt.Fprintln(output, "Departing between 10:00 and 15:00:")
63 fmt.Fprintln(output, "Departing between 10:00 and 15:00:")
68 for _, sailing := range midday {
64 for _, sailing := range midday {
69 fmt.Fprintf(output, " sailing %d at %s, %s to %s\n", sailing.ID, sailing.Departs, sailing.Origin, sailing.Destination)
65 fmt.Fprintf(output, " sailing %d at %s, %s to %s\n", sailing.ID, sailing.Departs, sailing.Origin, sailing.Destination)
70 }
66 }
71
67
72 // The same booking twice: the first takes 3 of the 4 free seats, so the second changes
68 // The same booking twice: the first takes 3 of the 4 free seats, so the second changes
73 // nothing because seats_free >= {seats:Int32} is now false.
69 // no row because seats_free >= ?1 is now false.
74 booking := store.BookSeatsParams{Seats: 3, SailingID: 3}
70 booking := store.BookSeatsParams{Seats: 3, SailingID: 3}
75 for range 2 {
71 for range 2 {
76 booked, err := bookSeats(ctx, queries, booking)
72 booked, err := queries.BookSeats(ctx, booking)
77 if err != nil {
73 if err != nil {
78 return err
74 return fmt.Errorf("booking sailing %d: %w", booking.SailingID, err)
79 }
75 }
80 if !booked {
76 if booked == 0 {
81 fmt.Fprintf(output, "sailing %d has fewer than %d seats free\n", booking.SailingID, booking.Seats)
77 fmt.Fprintf(output, "sailing %d has fewer than %d seats free\n", booking.SailingID, booking.Seats)
82 continue
78 continue
83 }
79 }
84 fmt.Fprintf(output, "booked %d seats on sailing %d\n", booking.Seats, booking.SailingID)
80 fmt.Fprintf(output, "booked %d seats on sailing %d\n", booking.Seats, booking.SailingID)
85 }
81 }
86 return nil
82 return nil
87}
83}
88
89// bookSeats runs BookSeats and reports whether it took the seats, by comparing seats_free
90// before and after. The connection sets mutations_sync=2, so the mutation has finished
91// when BookSeats returns.
92//
93// Takes queries (*store.Queries) which runs the queries.
94// Takes booking (store.BookSeatsParams) which is the sailing and the number of seats.
95//
96// Returns bool which is true when the seats were booked.
97// Returns error when a query fails.
98func bookSeats(ctx context.Context, queries *store.Queries, booking store.BookSeatsParams) (bool, error) {
99 before, err := queries.SeatsFree(ctx, booking.SailingID)
100 if err != nil {
101 return false, fmt.Errorf("reading seats on sailing %d: %w", booking.SailingID, err)
102 }
103 if err := queries.BookSeats(ctx, booking); err != nil {
104 return false, fmt.Errorf("booking sailing %d: %w", booking.SailingID, err)
105 }
106 after, err := queries.SeatsFree(ctx, booking.SailingID)
107 if err != nil {
108 return false, fmt.Errorf("reading seats on sailing %d: %w", booking.SailingID, err)
109 }
110 return after.SeatsFree != before.SeatsFree, nil
111}

Go type differences

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

QueryFieldClickHouseMariaDB
FindSailingscolumn iduint64int64
SailingsDepartingcolumn iduint64int64
BookSeatsparameter sailing_iduint64int64