Compare · 010

IN lists from Go slices: CockroachDB and TimescaleDB

CockroachDB and TimescaleDB run the same files for this example: the SQL and the Go are identical.

Compare CockroachDB withSQLitePostgreSQLTimescaleDBMySQLMariaDBDuckDBClickHouse

Configuration

sgl.yamlidentical
CockroachDB
TimescaleDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: huts
4 - name: huts
5 dialect: postgres
5 dialect: postgres
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
11 driver: database/sql
11 driver: database/sql

Migrations

db/migrations/0001_huts.up.sqlidentical
CockroachDB
TimescaleDB
1CREATE TABLE huts (
1CREATE TABLE huts (
2 number BIGINT PRIMARY KEY,
2 number BIGINT PRIMARY KEY,
3 beach TEXT NOT NULL,
3 beach TEXT NOT NULL,
4 colour TEXT NOT NULL,
4 colour TEXT NOT NULL,
5 booked BOOLEAN NOT NULL DEFAULT false
5 booked BOOLEAN NOT NULL DEFAULT false
6);
6);
db/migrations/0001_huts.down.sqlidentical
CockroachDB
TimescaleDB
1DROP TABLE huts;
1DROP TABLE huts;

Queries

AddHutidentical
CockroachDB · db/queries/huts.sql
TimescaleDB · db/queries/huts.sql
1-- sgl.query(AddHut, exec)
1-- sgl.query(AddHut, exec)
2INSERT INTO huts (number, beach, colour) VALUES ($1, $2, $3);
2INSERT INTO huts (number, beach, colour) VALUES ($1, $2, $3);
HutsOnBeachesidentical
CockroachDB · db/queries/huts.sql
TimescaleDB · db/queries/huts.sql
1-- sgl.query(HutsOnBeaches, many)
1-- sgl.query(HutsOnBeaches, many)
2-- $1 as sgl.param(beaches, kind: slice)
2-- $1 as sgl.param(beaches, kind: slice)
3SELECT number, beach, colour, booked
3SELECT number, beach, colour, booked
4FROM huts
4FROM huts
5WHERE beach IN ($1)
5WHERE beach IN ($1)
6ORDER BY number;
6ORDER BY number;
BookHutsidentical
CockroachDB · db/queries/huts.sql
TimescaleDB · db/queries/huts.sql
1-- sgl.query(BookHuts, execrows)
1-- sgl.query(BookHuts, execrows)
2-- $1 as sgl.param(numbers, kind: slice)
2-- $1 as sgl.param(numbers, kind: slice)
3UPDATE huts SET booked = true WHERE number IN ($1) AND NOT booked;
3UPDATE huts SET booked = true WHERE number IN ($1) AND NOT booked;
FreeHutsInColoursidentical
CockroachDB · db/queries/huts.sql
TimescaleDB · db/queries/huts.sql
1-- sgl.query(FreeHutsInColours, many)
1-- sgl.query(FreeHutsInColours, many)
2-- $1 as sgl.param(beach)
2-- $1 as sgl.param(beach)
3-- $2 as sgl.param(colours, kind: slice)
3-- $2 as sgl.param(colours, kind: slice)
4SELECT number, colour
4SELECT number, colour
5FROM huts
5FROM huts
6WHERE beach = $1 AND colour IN ($2) AND NOT booked
6WHERE beach = $1 AND colour IN ($2) AND NOT booked
7ORDER BY number;
7ORDER BY number;

What sgl generates

AddHutidentical
CockroachDB
TimescaleDB
1func (queries *Queries) AddHut(ctx context.Context, params AddHutParams) error
1func (queries *Queries) AddHut(ctx context.Context, params AddHutParams) error
2
2
3type AddHutParams struct {
3type AddHutParams struct {
4 Number int64
4 Number int64
5 Beach string
5 Beach string
6 Colour string
6 Colour string
7}
7}
HutsOnBeachesidentical
CockroachDB
TimescaleDB
1func (queries *Queries) HutsOnBeaches(ctx context.Context, params HutsOnBeachesParams) ([]HutsOnBeachesRow, error)
1func (queries *Queries) HutsOnBeaches(ctx context.Context, params HutsOnBeachesParams) ([]HutsOnBeachesRow, error)
2
2
3type HutsOnBeachesParams struct {
3type HutsOnBeachesParams struct {
4 Beaches []string
4 Beaches []string
5}
5}
6
6
7type HutsOnBeachesRow struct {
7type HutsOnBeachesRow struct {
8 Number int64 `json:"number"`
8 Number int64 `json:"number"`
9 Beach string `json:"beach"`
9 Beach string `json:"beach"`
10 Colour string `json:"colour"`
10 Colour string `json:"colour"`
11 Booked bool `json:"booked"`
11 Booked bool `json:"booked"`
12}
12}
BookHutsidentical
CockroachDB
TimescaleDB
1func (queries *Queries) BookHuts(ctx context.Context, params BookHutsParams) (int64, error)
1func (queries *Queries) BookHuts(ctx context.Context, params BookHutsParams) (int64, error)
2
2
3type BookHutsParams struct {
3type BookHutsParams struct {
4 Numbers []int64
4 Numbers []int64
5}
5}
FreeHutsInColoursidentical
CockroachDB
TimescaleDB
1func (queries *Queries) FreeHutsInColours(ctx context.Context, params FreeHutsInColoursParams) ([]FreeHutsInColoursRow, error)
1func (queries *Queries) FreeHutsInColours(ctx context.Context, params FreeHutsInColoursParams) ([]FreeHutsInColoursRow, error)
2
2
3type FreeHutsInColoursParams struct {
3type FreeHutsInColoursParams struct {
4 Beach string
4 Beach string
5 Colours []string
5 Colours []string
6}
6}
7
7
8type FreeHutsInColoursRow struct {
8type FreeHutsInColoursRow struct {
9 Number int64 `json:"number"`
9 Number int64 `json:"number"`
10 Colour string `json:"colour"`
10 Colour string `json:"colour"`
11}
11}

The program

example.goidentical
CockroachDB
TimescaleDB
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/postgres/010_slices/internal/store"
9 "seagull.sh/seagull/examples/postgres/010_slices/internal/store"
10)
10)
11
11
12// run lets out beach huts by passing Go slices where the SQL has an IN list. A binding
12// run lets out beach huts by passing Go slices where the SQL has an IN list. A binding
13// such as $1 as sgl.param(beaches, kind: slice) makes the parameter a slice, and the
13// such as $1 as sgl.param(beaches, kind: slice) makes the parameter a slice, and the
14// generated method rewrites IN ($1) to one placeholder per element at call time. A slice
14// generated method rewrites IN ($1) to one placeholder per element at call time. A slice
15// is numbered after every other parameter, so no other placeholder moves. An empty slice
15// is numbered after every other parameter, so no other placeholder moves. An empty slice
16// turns IN ($1) into = ANY('{}'), which matches nothing, and an expansion past
16// turns IN ($1) into = ANY('{}'), which matches nothing, and an expansion past
17// PostgreSQL's limit of 65535 bind variables returns an error before the statement is
17// PostgreSQL's limit of 65535 bind variables returns an error before the statement is
18// sent.
18// sent.
19//
19//
20// The rewrite happens before the statement runs, so sgl vet refuses a slice parameter on
20// The rewrite happens before the statement runs, so sgl vet refuses a slice parameter on
21// a batch or copyfrom query (Q017) or a dynamic: runtime query (Q018).
21// a batch or copyfrom query (Q017) or a dynamic: runtime query (Q018).
22//
22//
23// Takes database (*sql.DB) which is a migrated database.
23// Takes database (*sql.DB) which is a migrated database.
24// Takes output (io.Writer) which receives each search and booking.
24// Takes output (io.Writer) which receives each search and booking.
25//
25//
26// Returns error when a query fails.
26// Returns error when a query fails.
27func run(ctx context.Context, database *sql.DB, output io.Writer) error {
27func run(ctx context.Context, database *sql.DB, output io.Writer) error {
28 queries := store.New(database)
28 queries := store.New(database)
29
29
30 huts := []store.AddHutParams{
30 huts := []store.AddHutParams{
31 {Number: 1, Beach: "Polzeath", Colour: "blue"},
31 {Number: 1, Beach: "Polzeath", Colour: "blue"},
32 {Number: 2, Beach: "Polzeath", Colour: "yellow"},
32 {Number: 2, Beach: "Polzeath", Colour: "yellow"},
33 {Number: 3, Beach: "Polzeath", Colour: "red"},
33 {Number: 3, Beach: "Polzeath", Colour: "red"},
34 {Number: 4, Beach: "Watergate", Colour: "blue"},
34 {Number: 4, Beach: "Watergate", Colour: "blue"},
35 {Number: 5, Beach: "Watergate", Colour: "red"},
35 {Number: 5, Beach: "Watergate", Colour: "red"},
36 {Number: 6, Beach: "Fistral", Colour: "yellow"},
36 {Number: 6, Beach: "Fistral", Colour: "yellow"},
37 }
37 }
38 for _, hut := range huts {
38 for _, hut := range huts {
39 if err := queries.AddHut(ctx, hut); err != nil {
39 if err := queries.AddHut(ctx, hut); err != nil {
40 return fmt.Errorf("adding hut %d: %w", hut.Number, err)
40 return fmt.Errorf("adding hut %d: %w", hut.Number, err)
41 }
41 }
42 }
42 }
43
43
44 for _, beaches := range [][]string{{"Polzeath", "Fistral"}, {"Watergate"}, {}} {
44 for _, beaches := range [][]string{{"Polzeath", "Fistral"}, {"Watergate"}, {}} {
45 found, err := queries.HutsOnBeaches(ctx, store.HutsOnBeachesParams{Beaches: beaches})
45 found, err := queries.HutsOnBeaches(ctx, store.HutsOnBeachesParams{Beaches: beaches})
46 if err != nil {
46 if err != nil {
47 return fmt.Errorf("finding huts on %v: %w", beaches, err)
47 return fmt.Errorf("finding huts on %v: %w", beaches, err)
48 }
48 }
49 numbers := make([]int64, 0, len(found))
49 numbers := make([]int64, 0, len(found))
50 for _, hut := range found {
50 for _, hut := range found {
51 numbers = append(numbers, hut.Number)
51 numbers = append(numbers, hut.Number)
52 }
52 }
53 fmt.Fprintf(output, "huts on %v: %v\n", beaches, numbers)
53 fmt.Fprintf(output, "huts on %v: %v\n", beaches, numbers)
54 }
54 }
55
55
56 // Hut 3 is taken by the first booking, so the second books only hut 5.
56 // Hut 3 is taken by the first booking, so the second books only hut 5.
57 for _, numbers := range [][]int64{{1, 3, 4}, {3, 5}} {
57 for _, numbers := range [][]int64{{1, 3, 4}, {3, 5}} {
58 booked, err := queries.BookHuts(ctx, store.BookHutsParams{Numbers: numbers})
58 booked, err := queries.BookHuts(ctx, store.BookHutsParams{Numbers: numbers})
59 if err != nil {
59 if err != nil {
60 return fmt.Errorf("booking huts %v: %w", numbers, err)
60 return fmt.Errorf("booking huts %v: %w", numbers, err)
61 }
61 }
62 fmt.Fprintf(output, "booking huts %v booked %d\n", numbers, booked)
62 fmt.Fprintf(output, "booking huts %v booked %d\n", numbers, booked)
63 }
63 }
64
64
65 free, err := queries.FreeHutsInColours(ctx, store.FreeHutsInColoursParams{
65 free, err := queries.FreeHutsInColours(ctx, store.FreeHutsInColoursParams{
66 Beach: "Polzeath",
66 Beach: "Polzeath",
67 Colours: []string{"blue", "yellow"},
67 Colours: []string{"blue", "yellow"},
68 })
68 })
69 if err != nil {
69 if err != nil {
70 return fmt.Errorf("finding free huts: %w", err)
70 return fmt.Errorf("finding free huts: %w", err)
71 }
71 }
72 for _, hut := range free {
72 for _, hut := range free {
73 fmt.Fprintf(output, "hut %d at Polzeath is %s and free\n", hut.Number, hut.Colour)
73 fmt.Fprintf(output, "hut %d at Polzeath is %s and free\n", hut.Number, hut.Colour)
74 }
74 }
75 return nil
75 return nil
76}
76}