Compare · 013

Pagination with defaults and limits: ClickHouse and SQLite

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

Compare ClickHouse withSQLitePostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
ClickHouse
SQLite
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: rockpools
4 - name: rockpools
5 dialect: clickhouse
5 dialect: sqlite
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
ClickHouse
SQLite
1CREATE TABLE finds (
1CREATE TABLE finds (
2 id UInt64,
2 id INTEGER PRIMARY KEY,
3 creature String,
3 creature TEXT NOT NULL,
4 pool String,
4 pool TEXT NOT NULL,
5 size_mm UInt32
5 size_mm INTEGER NOT NULL
6) ENGINE = MergeTree() ORDER BY id;
6);
db/migrations/0001_finds.down.sqlidentical
ClickHouse
SQLite
1DROP TABLE finds;
1DROP TABLE finds;

Queries

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

What sgl generates

LogFinddiffers
ClickHouse
SQLite
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
5 Creature string
4 Creature string
6 Pool string
5 Pool string
7 SizeMm uint32
6 SizeMm int64
8}
7}
ListFindsdiffers
ClickHouse
SQLite
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 uint64 `json:"id"`
9 ID int64 `json:"id"`
10 Creature string `json:"creature"`
10 Creature string `json:"creature"`
11 Pool string `json:"pool"`
11 Pool string `json:"pool"`
12 SizeMm uint32 `json:"size_mm"`
12 SizeMm int64 `json:"size_mm"`
13}
13}
SearchFindsdiffers
ClickHouse
SQLite
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 uint64 `json:"id"`
10 ID int64 `json:"id"`
11 Creature string `json:"creature"`
11 Creature string `json:"creature"`
12 Pool string `json:"pool"`
12 Pool string `json:"pool"`
13 SizeMm uint32 `json:"size_mm"`
13 SizeMm int64 `json:"size_mm"`
14}
14}

The program

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

Go type differences

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

QueryFieldClickHouseSQLite
LogFindparameter size_mmuint32int64
ListFindscolumn iduint64int64
ListFindscolumn size_mmuint32int64
SearchFindscolumn iduint64int64
SearchFindscolumn size_mmuint32int64