Compare · 013

Pagination with defaults and limits: SQLite and MariaDB

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

Compare SQLite withPostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDBClickHouse

Configuration

sgl.yamldiffers
SQLite
MariaDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: rockpools
4 - name: rockpools
5 dialect: sqlite
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_finds.up.sqldiffers
SQLite
MariaDB
1CREATE TABLE finds (
1CREATE TABLE finds (
2 id INTEGER PRIMARY KEY,
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3 creature TEXT NOT NULL,
3 creature VARCHAR(50) NOT NULL,
4 pool TEXT NOT NULL,
4 pool VARCHAR(20) NOT NULL,
5 size_mm INTEGER NOT NULL
5 size_mm INT NOT NULL
6);
6);
db/migrations/0001_finds.down.sqlidentical
SQLite
MariaDB
1DROP TABLE finds;
1DROP TABLE finds;

Queries

LogFindidentical
SQLite · db/queries/finds.sql
MariaDB · 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 (creature, pool, size_mm) VALUES (?, ?, ?);
ListFindsidentical
SQLite · db/queries/finds.sql
MariaDB · 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-- ?1 as sgl.param(page_size, default: 3, max: 5)
3-- ?2 as sgl.param(page_offset)
3-- ?2 as sgl.param(page_offset)
4SELECT id, creature, pool, size_mm
4SELECT id, creature, pool, size_mm
5FROM finds
5FROM finds
6ORDER BY id
6ORDER BY id
7LIMIT ?1 OFFSET ?2;
7LIMIT ?1 OFFSET ?2;
SearchFindsidentical
SQLite · db/queries/finds.sql
MariaDB · db/queries/finds.sql
1-- sgl.query(SearchFinds, many)
1-- sgl.query(SearchFinds, many)
2-- ?1 as sgl.param(creature, optional: true)
2-- ?1 as sgl.param(creature, optional: true)
3-- ?2 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)
4-- ?3 as sgl.param(page_offset)
5SELECT id, creature, pool, size_mm
5SELECT id, creature, pool, size_mm
6FROM finds
6FROM finds
7WHERE (?1 IS NULL OR creature = ?1)
7WHERE (?1 IS NULL OR creature = ?1)
8ORDER BY size_mm DESC, id
8ORDER BY size_mm DESC, id
9LIMIT ?2 OFFSET ?3;
9LIMIT ?2 OFFSET ?3;

What sgl generates

LogFinddiffers
SQLite
MariaDB
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 Creature string
4 Creature string
5 Pool string
5 Pool string
6 SizeMm int64
6 SizeMm int32
7}
7}
ListFindsdiffers
SQLite
MariaDB
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 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 int64 `json:"size_mm"`
12 SizeMm int32 `json:"size_mm"`
13}
13}
SearchFindsdiffers
SQLite
MariaDB
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 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 int64 `json:"size_mm"`
13 SizeMm int32 `json:"size_mm"`
14}
14}

The program

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

Go type differences

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

QueryFieldSQLiteMariaDB
LogFindparameter size_mmint64int32
ListFindscolumn size_mmint64int32
SearchFindscolumn size_mmint64int32