Compare · 014

The runtime query builder: ClickHouse and MySQL

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

Compare ClickHouse withSQLitePostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
ClickHouse
MySQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: pier
4 - name: pier
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_lost_property.up.sqldiffers
ClickHouse
MySQL
1CREATE TABLE lost_property (
1CREATE TABLE lost_property (
2 id UInt64,
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3 item String,
3 item VARCHAR(50) NOT NULL,
4 colour String,
4 colour VARCHAR(20) NOT NULL,
5 found_at String,
5 found_at VARCHAR(50) NOT NULL,
6 days_held UInt32,
6 days_held INT NOT NULL,
7 claimed Bool DEFAULT false,
7 claimed BOOLEAN NOT NULL DEFAULT FALSE,
8 owner_phone Nullable(String)
8 owner_phone VARCHAR(20)
9) ENGINE = MergeTree() ORDER BY id;
9);
db/migrations/0001_lost_property.down.sqlidentical
ClickHouse
MySQL
1DROP TABLE lost_property;
1DROP TABLE lost_property;

Queries

HandIndiffers
ClickHouse · db/queries/lost_property.sql
MySQL · db/queries/lost_property.sql
1-- sgl.query(HandIn, exec)
1-- sgl.query(HandIn, exec)
2INSERT INTO lost_property (id, item, colour, found_at, days_held)
2INSERT INTO lost_property (item, colour, found_at, days_held) VALUES (?, ?, ?, ?);
3VALUES ({id:UInt64}, {item:String}, {colour:String}, {found_at:String}, {days_held:UInt32});
Claimdiffers
ClickHouse · db/queries/lost_property.sql
MySQL · db/queries/lost_property.sql
1-- sgl.query(Claim, asyncexec)
1-- sgl.query(Claim, execrows)
2ALTER TABLE lost_property
2UPDATE lost_property SET claimed = TRUE, owner_phone = ? WHERE id = ?;
3UPDATE claimed = true, owner_phone = {owner_phone:Nullable(String)}
4WHERE id = {id:UInt64};
SearchUnclaimeddiffers
ClickHouse · db/queries/lost_property.sql
MySQL · db/queries/lost_property.sql
1-- sgl.query(SearchUnclaimed, many, dynamic: runtime)
1-- sgl.query(SearchUnclaimed, many, dynamic: runtime)
2SELECT id, item, colour, found_at, days_held
2SELECT id, item, colour, found_at, days_held
3FROM lost_property
3FROM lost_property
4WHERE claimed = false
4WHERE claimed = FALSE

What sgl generates

HandIndiffers
ClickHouse
MySQL
1func (queries *Queries) HandIn(ctx context.Context, params HandInParams) error
1func (queries *Queries) HandIn(ctx context.Context, params HandInParams) error
2
2
3type HandInParams struct {
3type HandInParams struct {
4 ID uint64
5 Item string
4 Item string
6 Colour string
5 Colour string
7 FoundAt string
6 FoundAt string
8 DaysHeld uint32
7 DaysHeld int32
9}
8}
Claimdiffers
ClickHouse
MySQL
1func (queries *Queries) Claim(ctx context.Context, params ClaimParams) error
1func (queries *Queries) Claim(ctx context.Context, params ClaimParams) (int64, error)
2
2
3type ClaimParams struct {
3type ClaimParams struct {
4 OwnerPhone *string
4 OwnerPhone *string
5 ID uint64
5 ID int64
6}
6}
SearchUnclaimeddiffers
ClickHouse
MySQL
1func (queries *Queries) SearchUnclaimed() *SearchUnclaimedBuilder
1func (queries *Queries) SearchUnclaimed() *SearchUnclaimedBuilder
2
2
3type SearchUnclaimedRow struct {
3type SearchUnclaimedRow struct {
4 ID uint64 `json:"id"`
4 ID int64 `json:"id"`
5 Item string `json:"item"`
5 Item string `json:"item"`
6 Colour string `json:"colour"`
6 Colour string `json:"colour"`
7 FoundAt string `json:"found_at"`
7 FoundAt string `json:"found_at"`
8 DaysHeld uint32 `json:"days_held"`
8 DaysHeld int32 `json:"days_held"`
9}
9}

The program

example.godiffers
ClickHouse
MySQL
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/clickhouse/014_runtime_builder/internal/store"
10 "seagull.sh/seagull/examples/mysql/014_runtime_builder/internal/store"
11)
11)
12
12
13// run searches the pier's unclaimed lost property. dynamic: runtime turns SearchUnclaimed
13// run searches the pier's unclaimed lost property. dynamic: runtime turns SearchUnclaimed
14// into a builder: Where, OrderBy, Limit and Offset add to the SQL, and All, One or Count
14// into a builder: Where, OrderBy, Limit and Offset add to the SQL, and All, One or Count
15// run it. The base query already has WHERE claimed = false, so each Where is joined with
15// run it. The base query already has WHERE claimed = FALSE, so each Where is joined with
16// AND, which sgl vet points out as hint Q022. The builder inserts its filters, order and
16// AND, which sgl vet points out as hint Q022. The builder inserts its filters, order and
17// limit at the places the query leaves for them, before any LIMIT or trailing semicolon,
17// limit at the places the query leaves for them, before any LIMIT or trailing semicolon,
18// and Count replaces the SELECT list with COUNT(*), wrapping the whole query instead when
18// and Count replaces the SELECT list with COUNT(*), wrapping the whole query instead when
19// it groups or is DISTINCT (Q023).
19// it groups or is DISTINCT (Q023).
20//
20//
21// The builder is safe with untrusted input. Where and OrderBy accept only the columns the
21// The builder is safe with untrusted input. Where and OrderBy accept only the columns the
22// query selects, so owner_phone, which the table has but the query leaves out, cannot be
22// query selects, so owner_phone, which the table has but the query leaves out, cannot be
23// searched. Operators and directions come from fixed allow-lists, and every value is
23// searched. Operators and directions come from fixed allow-lists, and every value is
24// bound as a parameter, never written into the SQL. A refused input does not panic: the
24// bound as a parameter, never written into the SQL. A refused input does not panic: the
25// terminal returns ErrSglUnknownColumn, ErrSglUnknownOperator or ErrSglUnknownDirection.
25// terminal returns ErrSglUnknownColumn, ErrSglUnknownOperator or ErrSglUnknownDirection.
26//
26//
27// Claim is an ALTER TABLE ... UPDATE mutation, so it is asyncexec; the connection sets
28// mutations_sync=2, so the claimed phone is gone from the searches that follow.
29//
30// Takes database (*sql.DB) which is a migrated database.
27// Takes database (*sql.DB) which is a migrated database.
31// Takes output (io.Writer) which receives the searches and the refusals.
28// Takes output (io.Writer) which receives the searches and the refusals.
32//
29//
33// Returns error when a query fails or an unsafe input is not refused.
30// Returns error when a query fails or an unsafe input is not refused.
34func run(ctx context.Context, database *sql.DB, output io.Writer) error {
31func run(ctx context.Context, database *sql.DB, output io.Writer) error {
35 queries := store.New(database)
32 queries := store.New(database)
36
33
37 items := []store.HandInParams{
34 items := []store.HandInParams{
38 {ID: 1, Item: "umbrella", Colour: "red", FoundAt: "the pier", DaysHeld: 40},
35 {Item: "umbrella", Colour: "red", FoundAt: "the pier", DaysHeld: 40},
39 {ID: 2, Item: "sunglasses", Colour: "black", FoundAt: "the beach", DaysHeld: 12},
36 {Item: "sunglasses", Colour: "black", FoundAt: "the beach", DaysHeld: 12},
40 {ID: 3, Item: "bucket and spade", Colour: "red", FoundAt: "the beach", DaysHeld: 3},
37 {Item: "bucket and spade", Colour: "red", FoundAt: "the beach", DaysHeld: 3},
41 {ID: 4, Item: "umbrella", Colour: "blue", FoundAt: "the cafe", DaysHeld: 33},
38 {Item: "umbrella", Colour: "blue", FoundAt: "the cafe", DaysHeld: 33},
42 {ID: 5, Item: "phone", Colour: "black", FoundAt: "the pier", DaysHeld: 1},
39 {Item: "phone", Colour: "black", FoundAt: "the pier", DaysHeld: 1},
43 }
40 }
44 for _, item := range items {
41 for _, item := range items {
45 if err := queries.HandIn(ctx, item); err != nil {
42 if err := queries.HandIn(ctx, item); err != nil {
46 return fmt.Errorf("handing in a %s: %w", item.Item, err)
43 return fmt.Errorf("handing in a %s: %w", item.Item, err)
47 }
44 }
48 }
45 }
49 if err := queries.Claim(ctx, store.ClaimParams{OwnerPhone: new("07700 900123"), ID: 5}); err != nil {
46 if _, err := queries.Claim(ctx, store.ClaimParams{OwnerPhone: new("07700 900123"), ID: 5}); err != nil {
50 return fmt.Errorf("claiming the phone: %w", err)
47 return fmt.Errorf("claiming the phone: %w", err)
51 }
48 }
52
49
53 red, err := queries.SearchUnclaimed().Where("colour", "=", "red").OrderBy("id", "ASC").All(ctx)
50 red, err := queries.SearchUnclaimed().Where("colour", "=", "red").All(ctx)
54 if err != nil {
51 if err != nil {
55 return fmt.Errorf("finding red items: %w", err)
52 return fmt.Errorf("finding red items: %w", err)
56 }
53 }
57 fmt.Fprintln(output, "red:")
54 fmt.Fprintln(output, "red:")
58 printItems(output, red)
55 printItems(output, red)
59
56
60 longest, err := queries.SearchUnclaimed().
57 longest, err := queries.SearchUnclaimed().
61 Where("days_held", ">=", 30).
58 Where("days_held", ">=", 30).
62 OrderBy("days_held", "DESC").
59 OrderBy("days_held", "DESC").
63 Limit(2).
60 Limit(2).
64 All(ctx)
61 All(ctx)
65 if err != nil {
62 if err != nil {
66 return fmt.Errorf("finding long-held items: %w", err)
63 return fmt.Errorf("finding long-held items: %w", err)
67 }
64 }
68 fmt.Fprintln(output, "held 30 days or more, longest first:")
65 fmt.Fprintln(output, "held 30 days or more, longest first:")
69 printItems(output, longest)
66 printItems(output, longest)
70
67
71 umbrellas, err := queries.SearchUnclaimed().Where("item", "=", "umbrella").Count(ctx)
68 umbrellas, err := queries.SearchUnclaimed().Where("item", "=", "umbrella").Count(ctx)
72 if err != nil {
69 if err != nil {
73 return fmt.Errorf("counting umbrellas: %w", err)
70 return fmt.Errorf("counting umbrellas: %w", err)
74 }
71 }
75 fmt.Fprintf(output, "unclaimed umbrellas: %d\n", umbrellas)
72 fmt.Fprintf(output, "unclaimed umbrellas: %d\n", umbrellas)
76
73
77 tautology, err := queries.SearchUnclaimed().Where("item", "=", "' OR '1'='1").All(ctx)
74 tautology, err := queries.SearchUnclaimed().Where("item", "=", "' OR '1'='1").All(ctx)
78 if err != nil {
75 if err != nil {
79 return fmt.Errorf("searching for a quoted value: %w", err)
76 return fmt.Errorf("searching for a quoted value: %w", err)
80 }
77 }
81 fmt.Fprintf(output, "items named ' OR '1'='1: %d, as the value is bound, not run\n", len(tautology))
78 fmt.Fprintf(output, "items named ' OR '1'='1: %d, as the value is bound, not run\n", len(tautology))
82
79
83 attempts := []struct {
80 attempts := []struct {
84 label string
81 label string
85 search *store.SearchUnclaimedBuilder
82 search *store.SearchUnclaimedBuilder
86 }{
83 }{
87 {label: "a column the query does not select", search: queries.SearchUnclaimed().Where("owner_phone", "IS NOT NULL", nil)},
84 {label: "a column the query does not select", search: queries.SearchUnclaimed().Where("owner_phone", "IS NOT NULL", nil)},
88 {label: "SQL in a column name", search: queries.SearchUnclaimed().OrderBy("id; DROP TABLE lost_property", "ASC")},
85 {label: "SQL in a column name", search: queries.SearchUnclaimed().OrderBy("id; DROP TABLE lost_property", "ASC")},
89 {label: "an operator outside the allow-list", search: queries.SearchUnclaimed().Where("colour", "= 'red' OR 1=1 --", "red")},
86 {label: "an operator outside the allow-list", search: queries.SearchUnclaimed().Where("colour", "= 'red' OR 1=1 --", "red")},
90 {label: "a direction outside the allow-list", search: queries.SearchUnclaimed().OrderBy("days_held", "DESC; DROP TABLE lost_property")},
87 {label: "a direction outside the allow-list", search: queries.SearchUnclaimed().OrderBy("days_held", "DESC; DROP TABLE lost_property")},
91 }
88 }
92 for _, attempt := range attempts {
89 for _, attempt := range attempts {
93 _, err := attempt.search.All(ctx)
90 _, err := attempt.search.All(ctx)
94 switch {
91 switch {
95 case errors.Is(err, store.ErrSglUnknownColumn), errors.Is(err, store.ErrSglUnknownOperator), errors.Is(err, store.ErrSglUnknownDirection):
92 case errors.Is(err, store.ErrSglUnknownColumn), errors.Is(err, store.ErrSglUnknownOperator), errors.Is(err, store.ErrSglUnknownDirection):
96 fmt.Fprintf(output, "refused %s: %v\n", attempt.label, err)
93 fmt.Fprintf(output, "refused %s: %v\n", attempt.label, err)
97 case err == nil:
94 case err == nil:
98 return fmt.Errorf("%s: got no error, want a refusal", attempt.label)
95 return fmt.Errorf("%s: got no error, want a refusal", attempt.label)
99 default:
96 default:
100 return fmt.Errorf("%s: got %w, want a refusal", attempt.label, err)
97 return fmt.Errorf("%s: got %w, want a refusal", attempt.label, err)
101 }
98 }
102 }
99 }
103 return nil
100 return nil
104}
101}
105
102
106// printItems writes one line per item.
103// printItems writes one line per item.
107//
104//
108// Takes output (io.Writer) which receives the lines.
105// Takes output (io.Writer) which receives the lines.
109// Takes items ([]store.SearchUnclaimedRow) which are the items to print.
106// Takes items ([]store.SearchUnclaimedRow) which are the items to print.
110func printItems(output io.Writer, items []store.SearchUnclaimedRow) {
107func printItems(output io.Writer, items []store.SearchUnclaimedRow) {
111 for _, item := range items {
108 for _, item := range items {
112 fmt.Fprintf(output, " #%d %s %s, found at %s, held %d days\n", item.ID, item.Colour, item.Item, item.FoundAt, item.DaysHeld)
109 fmt.Fprintf(output, " #%d %s %s, found at %s, held %d days\n", item.ID, item.Colour, item.Item, item.FoundAt, item.DaysHeld)
113 }
110 }
114}
111}

Go type differences

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

QueryFieldClickHouseMySQL
HandInparameter days_helduint32int32
Claimparameter iduint64int64
SearchUnclaimedcolumn iduint64int64
SearchUnclaimedcolumn days_helduint32int32