Compare · 011

Optional filters: MySQL and ClickHouse

Every file of the MySQL and ClickHouse 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
ClickHouse
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: trips
4 - name: trips
5 dialect: mysql
5 dialect: clickhouse
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_trips.up.sqldiffers
MySQL
ClickHouse
1CREATE TABLE trips (
1CREATE TABLE trips (
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
2 id UInt64,
3 name VARCHAR(50) NOT NULL,
3 name String,
4 destination VARCHAR(50) NOT NULL,
4 destination String,
5 price_pence INT NOT NULL,
5 price_pence UInt32,
6 dogs_welcome BOOLEAN NOT NULL
6 dogs_welcome Bool
7);
7) ENGINE = MergeTree() ORDER BY id;
db/migrations/0001_trips.down.sqlidentical
MySQL
ClickHouse
1DROP TABLE trips;
1DROP TABLE trips;

Queries

AddTripdiffers
MySQL · db/queries/trips.sql
ClickHouse · db/queries/trips.sql
1-- sgl.query(AddTrip, exec)
1-- sgl.query(AddTrip, exec)
2INSERT INTO trips (name, destination, price_pence, dogs_welcome) VALUES (?, ?, ?, ?);
2INSERT INTO trips (id, name, destination, price_pence, dogs_welcome)
3VALUES ({id:UInt64}, {name:String}, {destination:String}, {price_pence:UInt32}, {dogs_welcome:Bool});
SearchTripsdiffers
MySQL · db/queries/trips.sql
ClickHouse · db/queries/trips.sql
1-- sgl.query(SearchTrips, many)
1-- sgl.query(SearchTrips, many)
2-- ?1 as sgl.param(destination, optional: true)
2-- {destination:Nullable(String)} as sgl.param(destination, optional: true)
3-- ?2 as sgl.param(max_price, optional: true)
3-- {max_price:Nullable(UInt32)} as sgl.param(max_price, optional: true)
4-- ?3 as sgl.param(dogs_welcome, optional: true)
4-- {dogs_welcome:Nullable(Bool)} as sgl.param(dogs_welcome, optional: true)
5SELECT id, name, destination, price_pence, dogs_welcome
5SELECT id, name, destination, price_pence, dogs_welcome
6FROM trips
6FROM trips
7WHERE (?1 IS NULL OR destination = ?1)
7WHERE ({destination:Nullable(String)} IS NULL OR destination = {destination:Nullable(String)})
8 AND (?2 IS NULL OR price_pence <= ?2)
8 AND ({max_price:Nullable(UInt32)} IS NULL OR price_pence <= {max_price:Nullable(UInt32)})
9 AND (?3 IS NULL OR dogs_welcome = ?3)
9 AND ({dogs_welcome:Nullable(Bool)} IS NULL OR dogs_welcome = {dogs_welcome:Nullable(Bool)})
10ORDER BY price_pence, id;
10ORDER BY price_pence, id;

What sgl generates

AddTripdiffers
MySQL
ClickHouse
1func (queries *Queries) AddTrip(ctx context.Context, params AddTripParams) error
1func (queries *Queries) AddTrip(ctx context.Context, params AddTripParams) error
2
2
3type AddTripParams struct {
3type AddTripParams struct {
4 ID uint64
4 Name string
5 Name string
5 Destination string
6 Destination string
6 PricePence int32
7 PricePence uint32
7 DogsWelcome bool
8 DogsWelcome bool
8}
9}
SearchTripsdiffers
MySQL
ClickHouse
1func (queries *Queries) SearchTrips(ctx context.Context, params SearchTripsParams) ([]SearchTripsRow, error)
1func (queries *Queries) SearchTrips(ctx context.Context, params SearchTripsParams) ([]SearchTripsRow, error)
2
2
3type SearchTripsParams struct {
3type SearchTripsParams struct {
4 Destination *string
4 Destination *string
5 MaxPrice *int32
5 MaxPrice *uint32
6 DogsWelcome *bool
6 DogsWelcome *bool
7}
7}
8
8
9type SearchTripsRow struct {
9type SearchTripsRow struct {
10 ID int64 `json:"id"`
10 ID uint64 `json:"id"`
11 Name string `json:"name"`
11 Name string `json:"name"`
12 Destination string `json:"destination"`
12 Destination string `json:"destination"`
13 PricePence int32 `json:"price_pence"`
13 PricePence uint32 `json:"price_pence"`
14 DogsWelcome bool `json:"dogs_welcome"`
14 DogsWelcome bool `json:"dogs_welcome"`
15}
15}

The program

example.godiffers
MySQL
ClickHouse
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 "strings"
8 "strings"
9
9
10 "seagull.sh/seagull/examples/mysql/011_optional_filters/internal/store"
10 "seagull.sh/seagull/examples/clickhouse/011_optional_filters/internal/store"
11)
11)
12
12
13// run searches boat trips four ways with one query. Each filter in SearchTrips is written
13// run searches boat trips four ways with one query. Each filter in SearchTrips is written
14// as (?1 IS NULL OR destination = ?1), and its binding says optional: true, which makes
14// as ({destination:Nullable(String)} IS NULL OR destination =
15// the Params field a pointer. A nil pointer is sent as NULL, so ?1 IS NULL is true and
15// {destination:Nullable(String)}), and its binding line says optional: true, which makes
16// that filter matches every row; a set pointer applies the filter. The SQL never changes,
16// the Params field a pointer. A nil pointer is sent as NULL, so the IS NULL test is true
17// only the values bound to it.
17// and that filter matches every row; a set pointer applies the filter. The SQL never
18// changes, only the values bound to it.
18//
19//
20// The placeholder's type must be Nullable(T). With {destination:String} the server reads
21// the NULL marker as text, the IS NULL test is false, and a search left open quietly
22// matches nothing. Nullable(T) alone already makes the field a pointer; optional: true
23// says the filter may be left out on purpose.
24//
19// Takes database (*sql.DB) which is a migrated database.
25// Takes database (*sql.DB) which is a migrated database.
20// Takes output (io.Writer) which receives each search's trips.
26// Takes output (io.Writer) which receives each search's trips.
21//
27//
22// Returns error when a query fails.
28// Returns error when a query fails.
23func run(ctx context.Context, database *sql.DB, output io.Writer) error {
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
24 queries := store.New(database)
30 queries := store.New(database)
25
31
26 trips := []store.AddTripParams{
32 trips := []store.AddTripParams{
27 {Name: "Seal spotting", Destination: "Gull Rock", PricePence: 1500, DogsWelcome: true},
33 {ID: 1, Name: "Seal spotting", Destination: "Gull Rock", PricePence: 1500, DogsWelcome: true},
28 {Name: "Puffin cruise", Destination: "Puffin Island", PricePence: 2500, DogsWelcome: false},
34 {ID: 2, Name: "Puffin cruise", Destination: "Puffin Island", PricePence: 2500, DogsWelcome: false},
29 {Name: "Mackerel fishing", Destination: "Gull Rock", PricePence: 2000, DogsWelcome: false},
35 {ID: 3, Name: "Mackerel fishing", Destination: "Gull Rock", PricePence: 2000, DogsWelcome: false},
30 {Name: "Sunset sail", Destination: "the bay", PricePence: 1200, DogsWelcome: true},
36 {ID: 4, Name: "Sunset sail", Destination: "the bay", PricePence: 1200, DogsWelcome: true},
31 }
37 }
32 for _, trip := range trips {
38 for _, trip := range trips {
33 if err := queries.AddTrip(ctx, trip); err != nil {
39 if err := queries.AddTrip(ctx, trip); err != nil {
34 return fmt.Errorf("adding %s: %w", trip.Name, err)
40 return fmt.Errorf("adding %s: %w", trip.Name, err)
35 }
41 }
36 }
42 }
37
43
38 searches := []struct {
44 searches := []struct {
39 label string
45 label string
40 parameters store.SearchTripsParams
46 parameters store.SearchTripsParams
41 }{
47 }{
42 {label: "no filters", parameters: store.SearchTripsParams{}},
48 {label: "no filters", parameters: store.SearchTripsParams{}},
43 {label: "to Gull Rock", parameters: store.SearchTripsParams{Destination: new("Gull Rock")}},
49 {label: "to Gull Rock", parameters: store.SearchTripsParams{Destination: new("Gull Rock")}},
44 {label: "£15 or less", parameters: store.SearchTripsParams{MaxPrice: new(int32(1500))}},
50 {label: "£15 or less", parameters: store.SearchTripsParams{MaxPrice: new(uint32(1500))}},
45 {label: "to Gull Rock with a dog", parameters: store.SearchTripsParams{Destination: new("Gull Rock"), DogsWelcome: new(true)}},
51 {label: "to Gull Rock with a dog", parameters: store.SearchTripsParams{Destination: new("Gull Rock"), DogsWelcome: new(true)}},
46 }
52 }
47 for _, search := range searches {
53 for _, search := range searches {
48 found, err := queries.SearchTrips(ctx, search.parameters)
54 found, err := queries.SearchTrips(ctx, search.parameters)
49 if err != nil {
55 if err != nil {
50 return fmt.Errorf("searching %s: %w", search.label, err)
56 return fmt.Errorf("searching %s: %w", search.label, err)
51 }
57 }
52 names := make([]string, 0, len(found))
58 names := make([]string, 0, len(found))
53 for _, trip := range found {
59 for _, trip := range found {
54 names = append(names, trip.Name)
60 names = append(names, trip.Name)
55 }
61 }
56 fmt.Fprintf(output, "%-24s %s\n", search.label+":", strings.Join(names, ", "))
62 fmt.Fprintf(output, "%-24s %s\n", search.label+":", strings.Join(names, ", "))
57 }
63 }
58 return nil
64 return nil
59}
65}

Go type differences

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

QueryFieldMySQLClickHouse
AddTripparameter price_penceint32uint32
SearchTripsparameter max_price*int32*uint32
SearchTripscolumn idint64uint64
SearchTripscolumn price_penceint32uint32