Compare · 013

Pagination with defaults and limits: MySQL and ClickHouse

Every file of the MySQL and ClickHouse versions side by side, with the lines that differ marked. 5 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: rockpools
4 - name: rockpools
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_finds.up.sqldiffers
MySQL
ClickHouse
1CREATE TABLE finds (
1CREATE TABLE finds (
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
2 id UInt64,
3 creature VARCHAR(50) NOT NULL,
3 creature String,
4 pool VARCHAR(20) NOT NULL,
4 pool String,
5 size_mm INT NOT NULL
5 size_mm UInt32
6);
6) ENGINE = MergeTree() ORDER BY id;
db/migrations/0001_finds.down.sqlidentical
MySQL
ClickHouse
1DROP TABLE finds;
1DROP TABLE finds;

Queries

LogFinddiffers
MySQL · db/queries/finds.sql
ClickHouse · db/queries/finds.sql
1-- sgl.query(LogFind, exec)
1-- sgl.query(LogFind, exec)
2INSERT INTO finds (creature, pool, size_mm) VALUES (?, ?, ?);
2INSERT INTO finds (id, creature, pool, size_mm)
3VALUES ({id:UInt64}, {creature:String}, {pool:String}, {size_mm:UInt32});
ListFindsdiffers
MySQL · db/queries/finds.sql
ClickHouse · db/queries/finds.sql
1-- sgl.query(ListFinds, many)
1-- sgl.query(ListFinds, many)
2-- ?1 as sgl.param(page_size, default: 3, max: 5)
2-- {page_size:UInt32} as sgl.param(page_size, default: 3, max: 5)
3-- ?2 as sgl.param(page_offset)
4SELECT id, creature, pool, size_mm
3SELECT id, creature, pool, size_mm
5FROM finds
4FROM finds
6ORDER BY id
5ORDER BY id
7LIMIT ?1 OFFSET ?2;
6LIMIT {page_size:UInt32} OFFSET {page_offset:UInt32};
SearchFindsdiffers
MySQL · db/queries/finds.sql
ClickHouse · db/queries/finds.sql
1-- sgl.query(SearchFinds, many)
1-- sgl.query(SearchFinds, many)
2-- ?1 as sgl.param(creature, optional: true)
2-- {creature:Nullable(String)} as sgl.param(creature, optional: true)
3-- ?2 as sgl.param(page_size, default: 3, max: 5)
3-- {page_size:UInt32} as sgl.param(page_size, default: 3, max: 5)
4-- ?3 as sgl.param(page_offset)
5SELECT id, creature, pool, size_mm
4SELECT id, creature, pool, size_mm
6FROM finds
5FROM finds
7WHERE (?1 IS NULL OR creature = ?1)
6WHERE ({creature:Nullable(String)} IS NULL OR creature = {creature:Nullable(String)})
8ORDER BY size_mm DESC, id
7ORDER BY size_mm DESC, id
9LIMIT ?2 OFFSET ?3;
8LIMIT {page_size:UInt32} OFFSET {page_offset:UInt32};

What sgl generates

LogFinddiffers
MySQL
ClickHouse
1func (queries *Queries) LogFind(ctx context.Context, params LogFindParams) error
1func (queries *Queries) LogFind(ctx context.Context, params LogFindParams) error
2
2
3type LogFindParams struct {
3type LogFindParams struct {
4 ID uint64
4 Creature string
5 Creature string
5 Pool string
6 Pool string
6 SizeMm int32
7 SizeMm uint32
7}
8}
ListFindsdiffers
MySQL
ClickHouse
1func (queries *Queries) ListFinds(ctx context.Context, params ListFindsParams) ([]ListFindsRow, error)
1func (queries *Queries) ListFinds(ctx context.Context, params ListFindsParams) ([]ListFindsRow, error)
2
2
3type ListFindsParams struct {
3type ListFindsParams struct {
4 PageSize int
4 PageSize int
5 PageOffset int
5 PageOffset int
6}
6}
7
7
8type ListFindsRow struct {
8type ListFindsRow struct {
9 ID int64 `json:"id"`
9 ID uint64 `json:"id"`
10 Creature string `json:"creature"`
10 Creature string `json:"creature"`
11 Pool string `json:"pool"`
11 Pool string `json:"pool"`
12 SizeMm int32 `json:"size_mm"`
12 SizeMm uint32 `json:"size_mm"`
13}
13}
SearchFindsdiffers
MySQL
ClickHouse
1func (queries *Queries) SearchFinds(ctx context.Context, params SearchFindsParams) ([]SearchFindsRow, error)
1func (queries *Queries) SearchFinds(ctx context.Context, params SearchFindsParams) ([]SearchFindsRow, error)
2
2
3type SearchFindsParams struct {
3type SearchFindsParams struct {
4 Creature *string
4 Creature *string
5 PageSize int
5 PageSize int
6 PageOffset int
6 PageOffset int
7}
7}
8
8
9type SearchFindsRow struct {
9type SearchFindsRow struct {
10 ID int64 `json:"id"`
10 ID uint64 `json:"id"`
11 Creature string `json:"creature"`
11 Creature string `json:"creature"`
12 Pool string `json:"pool"`
12 Pool string `json:"pool"`
13 SizeMm int32 `json:"size_mm"`
13 SizeMm uint32 `json:"size_mm"`
14}
14}

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
8
9 "seagull.sh/seagull/examples/mysql/013_pagination/internal/store"
9 "seagull.sh/seagull/examples/clickhouse/013_pagination/internal/store"
10)
10)
11
11
12// run logs seven rock pool finds and pages through them. A parameter in a LIMIT or OFFSET
12// run logs seven rock pool finds and pages through them. A placeholder that is the whole
13// clause becomes a plain int. On page_size, default: 3 replaces a zero value and max: 5
13// LIMIT or OFFSET count becomes a plain int, whatever ClickHouse type it names, so
14// caps a larger one, before the query runs, so a caller cannot ask for every row at once;
14// {page_size:UInt32} gives an int field. On page_size, default: 3 replaces a zero value
15// a negative size or offset becomes 0.
15// and max: 5 caps a larger one, before the query runs, so a caller cannot ask for every
16// row at once; a negative size or offset becomes 0.
16//
17//
18// ClickHouse has no auto-increment, so each find's id is a parameter.
19//
17// SearchFinds is the shape of a search endpoint: an optional filter, a fixed order and a
20// SearchFinds is the shape of a search endpoint: an optional filter, a fixed order and a
18// page, all in one static query.
21// page, all in one static query.
19//
22//
20// Takes database (*sql.DB) which is a migrated database.
23// Takes database (*sql.DB) which is a migrated database.
21// Takes output (io.Writer) which receives each page.
24// Takes output (io.Writer) which receives each page.
22//
25//
23// Returns error when a query fails.
26// Returns error when a query fails.
24func run(ctx context.Context, database *sql.DB, output io.Writer) error {
27func run(ctx context.Context, database *sql.DB, output io.Writer) error {
25 queries := store.New(database)
28 queries := store.New(database)
26
29
27 finds := []store.LogFindParams{
30 finds := []store.LogFindParams{
28 {Creature: "crab", Pool: "north", SizeMm: 45},
31 {ID: 1, Creature: "crab", Pool: "north", SizeMm: 45},
29 {Creature: "starfish", Pool: "east", SizeMm: 80},
32 {ID: 2, Creature: "starfish", Pool: "east", SizeMm: 80},
30 {Creature: "crab", Pool: "east", SizeMm: 60},
33 {ID: 3, Creature: "crab", Pool: "east", SizeMm: 60},
31 {Creature: "blenny", Pool: "north", SizeMm: 70},
34 {ID: 4, Creature: "blenny", Pool: "north", SizeMm: 70},
32 {Creature: "crab", Pool: "south", SizeMm: 30},
35 {ID: 5, Creature: "crab", Pool: "south", SizeMm: 30},
33 {Creature: "prawn", Pool: "north", SizeMm: 25},
36 {ID: 6, Creature: "prawn", Pool: "north", SizeMm: 25},
34 {Creature: "crab", Pool: "north", SizeMm: 55},
37 {ID: 7, Creature: "crab", Pool: "north", SizeMm: 55},
35 }
38 }
36 for _, find := range finds {
39 for _, find := range finds {
37 if err := queries.LogFind(ctx, find); err != nil {
40 if err := queries.LogFind(ctx, find); err != nil {
38 return fmt.Errorf("logging a %s: %w", find.Creature, err)
41 return fmt.Errorf("logging a %s: %w", find.Creature, err)
39 }
42 }
40 }
43 }
41
44
42 pages := []struct {
45 pages := []struct {
43 label string
46 label string
44 parameters store.ListFindsParams
47 parameters store.ListFindsParams
45 }{
48 }{
46 {label: "size 0 uses the default of 3", parameters: store.ListFindsParams{PageSize: 0, PageOffset: 0}},
49 {label: "size 0 uses the default of 3", parameters: store.ListFindsParams{PageSize: 0, PageOffset: 0}},
47 {label: "the next page, offset 3", parameters: store.ListFindsParams{PageSize: 3, PageOffset: 3}},
50 {label: "the next page, offset 3", parameters: store.ListFindsParams{PageSize: 3, PageOffset: 3}},
48 {label: "size 50 is capped at 5", parameters: store.ListFindsParams{PageSize: 50, PageOffset: 0}},
51 {label: "size 50 is capped at 5", parameters: store.ListFindsParams{PageSize: 50, PageOffset: 0}},
49 }
52 }
50 for _, page := range pages {
53 for _, page := range pages {
51 listed, err := queries.ListFinds(ctx, page.parameters)
54 listed, err := queries.ListFinds(ctx, page.parameters)
52 if err != nil {
55 if err != nil {
53 return fmt.Errorf("listing finds, %s: %w", page.label, err)
56 return fmt.Errorf("listing finds, %s: %w", page.label, err)
54 }
57 }
55 fmt.Fprintf(output, "%s:\n", page.label)
58 fmt.Fprintf(output, "%s:\n", page.label)
56 for _, find := range listed {
59 for _, find := range listed {
57 fmt.Fprintf(output, " #%d %s in the %s pool\n", find.ID, find.Creature, find.Pool)
60 fmt.Fprintf(output, " #%d %s in the %s pool\n", find.ID, find.Creature, find.Pool)
58 }
61 }
59 }
62 }
60
63
61 for _, offset := range []int{0, 2} {
64 for _, offset := range []int{0, 2} {
62 crabs, err := queries.SearchFinds(ctx, store.SearchFindsParams{
65 crabs, err := queries.SearchFinds(ctx, store.SearchFindsParams{
63 Creature: new("crab"),
66 Creature: new("crab"),
64 PageSize: 2,
67 PageSize: 2,
65 PageOffset: offset,
68 PageOffset: offset,
66 })
69 })
67 if err != nil {
70 if err != nil {
68 return fmt.Errorf("searching crabs from %d: %w", offset, err)
71 return fmt.Errorf("searching crabs from %d: %w", offset, err)
69 }
72 }
70 fmt.Fprintf(output, "biggest crabs, offset %d:\n", offset)
73 fmt.Fprintf(output, "biggest crabs, offset %d:\n", offset)
71 for _, crab := range crabs {
74 for _, crab := range crabs {
72 fmt.Fprintf(output, " #%d %dmm\n", crab.ID, crab.SizeMm)
75 fmt.Fprintf(output, " #%d %dmm\n", crab.ID, crab.SizeMm)
73 }
76 }
74 }
77 }
75 return nil
78 return nil
76}
79}

Go type differences

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

QueryFieldMySQLClickHouse
LogFindparameter size_mmint32uint32
ListFindscolumn idint64uint64
ListFindscolumn size_mmint32uint32
SearchFindscolumn idint64uint64
SearchFindscolumn size_mmint32uint32