Compare · 008

Arrays and lists as typed slices: ClickHouse and CockroachDB

Every file of the ClickHouse and CockroachDB versions side by side, with the lines that differ marked.

Compare ClickHouse withPostgreSQLCockroachDBTimescaleDBDuckDB

Configuration

sgl.yamldiffers
ClickHouse
CockroachDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: quay
4 - name: voyages
5 dialect: clickhouse
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

Migrations

db/migrations/0001_hauls.up.sqldiffers
ClickHouse
CockroachDB · db/migrations/0001_voyages.up.sql
1CREATE TABLE hauls (
1CREATE TABLE voyages (
2 id UInt64,
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3 boat String,
3 boat TEXT NOT NULL,
4 species Array(String),
4 ports TEXT[] NOT NULL,
5 weights_kg Array(UInt32)
5 catch_kg BIGINT[] NOT NULL,
6) ENGINE = MergeTree() ORDER BY id;
6 sightings TEXT[]
7);
8-- sgl.column(voyages.ports, non_null_elements: true)
db/migrations/0001_hauls.down.sqldiffers
ClickHouse
CockroachDB · db/migrations/0001_voyages.down.sql
1DROP TABLE hauls;
1DROP TABLE voyages;

Queries

LandHaulonly ClickHouse
ClickHouse · db/queries/hauls.sql
CockroachDB · not in this version
1-- sgl.query(LandHaul, exec)
2INSERT INTO hauls (id, boat, species, weights_kg)
3VALUES ({id:UInt64}, {boat:String}, {species:Array(String)}, {weights_kg:Array(UInt32)});
GetHaulonly ClickHouse
ClickHouse · db/queries/hauls.sql
CockroachDB · not in this version
1-- sgl.query(GetHaul, one)
2SELECT id, boat, species, weights_kg FROM hauls WHERE id = {id:UInt64};
HaulsWithonly ClickHouse
ClickHouse · db/queries/hauls.sql
CockroachDB · not in this version
1-- sgl.query(HaulsWith, many)
2SELECT id, boat, species, weights_kg
3FROM hauls
4WHERE has(species, {fish:String})
5ORDER BY id;
LogVoyageonly CockroachDB
ClickHouse · not in this version
CockroachDB · db/queries/voyages.sql
1-- sgl.query(LogVoyage, exec)
2INSERT INTO voyages (boat, ports, catch_kg, sightings) VALUES ($1, $2, $3, $4);
ListVoyagesonly CockroachDB
ClickHouse · not in this version
CockroachDB · db/queries/voyages.sql
1-- sgl.query(ListVoyages, many)
2SELECT id, boat, ports, catch_kg, sightings FROM voyages ORDER BY id;
VoyagesCallingAtonly CockroachDB
ClickHouse · not in this version
CockroachDB · db/queries/voyages.sql
1-- sgl.query(VoyagesCallingAt, many)
2-- $1 as sgl.param(port)
3SELECT boat, ports[1] AS first_port, cardinality(ports) AS stops
4FROM voyages
5WHERE $1::text = ANY(ports)
6ORDER BY id;
VoyagesPerBoatonly CockroachDB
ClickHouse · not in this version
CockroachDB · db/queries/voyages.sql
1-- sgl.query(VoyagesPerBoat, many)
2SELECT boat, array_agg(id ORDER BY id) AS voyages
3FROM voyages
4GROUP BY boat
5ORDER BY boat;

What sgl generates

LandHaulonly ClickHouse
ClickHouse
CockroachDB · not in this version
1func (queries *Queries) LandHaul(ctx context.Context, params LandHaulParams) error
2
3type LandHaulParams struct {
4 ID uint64
5 Boat string
6 Species []string
7 WeightsKg []uint32
8}
GetHaulonly ClickHouse
ClickHouse
CockroachDB · not in this version
1func (queries *Queries) GetHaul(ctx context.Context, id uint64) (GetHaulRow, error)
2
3type GetHaulRow struct {
4 ID uint64 `json:"id"`
5 Boat string `json:"boat"`
6 Species []string `json:"species"`
7 WeightsKg []uint32 `json:"weights_kg"`
8}
HaulsWithonly ClickHouse
ClickHouse
CockroachDB · not in this version
1func (queries *Queries) HaulsWith(ctx context.Context, fish string) ([]HaulsWithRow, error)
2
3type HaulsWithRow struct {
4 ID uint64 `json:"id"`
5 Boat string `json:"boat"`
6 Species []string `json:"species"`
7 WeightsKg []uint32 `json:"weights_kg"`
8}
LogVoyageonly CockroachDB
ClickHouse · not in this version
CockroachDB
1func (queries *Queries) LogVoyage(ctx context.Context, params LogVoyageParams) error
2
3type LogVoyageParams struct {
4 Boat string
5 Ports []string
6 CatchKg []*int64
7 Sightings []*string
8}
ListVoyagesonly CockroachDB
ClickHouse · not in this version
CockroachDB
1func (queries *Queries) ListVoyages(ctx context.Context) ([]ListVoyagesRow, error)
2
3type ListVoyagesRow struct {
4 ID int64 `json:"id"`
5 Boat string `json:"boat"`
6 Ports []string `json:"ports"`
7 CatchKg []*int64 `json:"catch_kg"`
8 Sightings []*string `json:"sightings"`
9}
VoyagesCallingAtonly CockroachDB
ClickHouse · not in this version
CockroachDB
1func (queries *Queries) VoyagesCallingAt(ctx context.Context, port string) ([]VoyagesCallingAtRow, error)
2
3type VoyagesCallingAtRow struct {
4 Boat string `json:"boat"`
5 FirstPort *string `json:"first_port"`
6 Stops int32 `json:"stops"`
7}
VoyagesPerBoatonly CockroachDB
ClickHouse · not in this version
CockroachDB
1func (queries *Queries) VoyagesPerBoat(ctx context.Context) ([]VoyagesPerBoatRow, error)
2
3type VoyagesPerBoatRow struct {
4 Boat string `json:"boat"`
5 Voyages []int64 `json:"voyages"`
6}

The program

example.godiffers
ClickHouse
CockroachDB
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/008_arrays/internal/store"
9 "seagull.sh/seagull/examples/postgres/008_arrays/internal/store"
10)
10)
11
11
12// run lands four hauls of fish and reads them back. An Array(T) column arrives as a Go
12// run logs three fishing voyages and reads them back. An array column becomes a typed Go
13// slice of T's type, so species, an Array(String), is []string and weights_kg, an
13// slice: TEXT[] gives []*string and BIGINT[] gives []*int64, and array_agg gives a slice
14// Array(UInt32), is []uint32, in result rows and in parameters alike: LandHaul takes the
14// of its argument's type, here []int64: the id column it collects is NOT NULL, so the
15// slices and binds each as one {species:Array(String)} value.
15// array can hold no NULL and its elements are plain values. A slice parameter is sent as
16// a PostgreSQL array, and a nil slice is sent as NULL.
16//
17//
17// HaulsWith filters with has(species, {fish:String}), which looks inside the array on the
18// The elements are pointers because PostgreSQL can hold NULL in any array, whatever the
18// server. An Array column cannot be Nullable in ClickHouse, so an empty haul is an empty
19// column's type says, so a NULL element reads as nil, which elements prints as NULL.
19// array, never NULL.
20// Parameters take the same pointers, so LogVoyage takes []*int64 for catch_kg and
21// []*string for sightings, and a nil element writes NULL; values builds them from plain
22// values. When the data never holds a NULL element, non_null_elements: true opts out, in
23// an sgl.column line of a query or migration or as go.types.non_null_elements in
24// sgl.yaml: the elements then read and bind as plain values, and a NULL one fails the
25// read. The migration declares it for ports, as a voyage never calls at a NULL port, so
26// Ports is a plain []string in the model, the row and the parameters alike.
20//
27//
28// database/sql cannot scan a PostgreSQL array, so the generated SQL wraps each array
29// column in to_json() and decodes the JSON into the slice; open internal/store to see it.
30// A NULL array therefore scans to a nil slice, and an empty array to an empty one.
31//
32// VoyagesCallingAt searches inside an array with $1::text = ANY(ports). The cast gives
33// the parameter its type, for Seagull, which would otherwise leave it as any, and for
34// CockroachDB, which cannot work it out from ANY. ports[1] reads one element, and is
35// *string because a missing element is NULL.
36//
21// Takes database (*sql.DB) which is a migrated database.
37// Takes database (*sql.DB) which is a migrated database.
22// Takes output (io.Writer) which receives the hauls.
38// Takes output (io.Writer) which receives the voyages and the searches.
23//
39//
24// Returns error when a query fails.
40// Returns error when a query fails.
25func run(ctx context.Context, database *sql.DB, output io.Writer) error {
41func run(ctx context.Context, database *sql.DB, output io.Writer) error {
26 queries := store.New(database)
42 queries := store.New(database)
27
43
28 hauls := []store.LandHaulParams{
44 voyages := []store.LogVoyageParams{
29 {ID: 1, Boat: "Kittiwake", Species: []string{"mackerel", "pollock"}, WeightsKg: []uint32{120, 45}},
45 {Boat: "Kittiwake", Ports: []string{"Newlyn", "St Ives", "Mousehole"}, CatchKg: values[int64](120, 85), Sightings: values("dolphin", "basking shark")},
30 {ID: 2, Boat: "Salty Chip", Species: []string{"crab", "lobster"}, WeightsKg: []uint32{30, 12}},
46 {Boat: "Salty Chip", Ports: []string{"Penzance", "Mousehole"}, CatchKg: values[int64](40), Sightings: nil},
31 {ID: 3, Boat: "Gull Wing", Species: []string{"mackerel"}, WeightsKg: []uint32{80}},
47 {Boat: "Kittiwake", Ports: []string{"Newlyn", "Lamorna"}, CatchKg: values[int64](60, 30, 15), Sightings: []*string{}},
32 {ID: 4, Boat: "Driftwood", Species: []string{}, WeightsKg: []uint32{}},
33 }
48 }
34 for _, haul := range hauls {
49 for _, voyage := range voyages {
35 if err := queries.LandHaul(ctx, haul); err != nil {
50 if err := queries.LogVoyage(ctx, voyage); err != nil {
36 return fmt.Errorf("landing haul %d: %w", haul.ID, err)
51 return fmt.Errorf("logging %s's voyage: %w", voyage.Boat, err)
37 }
52 }
38 }
53 }
39
54
40 first, err := queries.GetHaul(ctx, 1)
55 logged, err := queries.ListVoyages(ctx)
41 if err != nil {
56 if err != nil {
42 return fmt.Errorf("getting haul 1: %w", err)
57 return fmt.Errorf("listing voyages: %w", err)
43 }
58 }
44 fmt.Fprintf(output, "haul 1: species %T %v, weights_kg %T %v\n", first.Species, first.Species, first.WeightsKg, first.WeightsKg)
59 for _, voyage := range logged {
60 sightings := elements(voyage.Sightings)
61 if voyage.Sightings == nil {
62 sightings = "NULL"
63 }
64 fmt.Fprintf(output, "voyage %d %-10s ports %s, catch %s kg, sightings %s\n", voyage.ID, voyage.Boat, voyage.Ports, elements(voyage.CatchKg), sightings)
65 }
45
66
46 mackerel, err := queries.HaulsWith(ctx, "mackerel")
67 calling, err := queries.VoyagesCallingAt(ctx, "Mousehole")
47 if err != nil {
68 if err != nil {
48 return fmt.Errorf("finding mackerel: %w", err)
69 return fmt.Errorf("finding voyages calling at Mousehole: %w", err)
49 }
70 }
50 fmt.Fprintln(output, "hauls with mackerel:")
71 fmt.Fprintln(output, "\ncalling at Mousehole:")
51 for _, haul := range mackerel {
72 for _, voyage := range calling {
52 fmt.Fprintf(output, " #%d %-10s %v %v kg\n", haul.ID, haul.Boat, haul.Species, haul.WeightsKg)
73 fmt.Fprintf(output, " %-10s from %s, %d stops\n", voyage.Boat, *voyage.FirstPort, voyage.Stops)
53 }
74 }
54
75
55 empty, err := queries.GetHaul(ctx, 4)
76 perBoat, err := queries.VoyagesPerBoat(ctx)
56 if err != nil {
77 if err != nil {
57 return fmt.Errorf("getting haul 4: %w", err)
78 return fmt.Errorf("grouping voyages by boat: %w", err)
58 }
79 }
59 fmt.Fprintf(output, "haul 4 landed %d species, an empty array: %v\n", len(empty.Species), empty.Species)
80 fmt.Fprintln(output, "\nvoyages per boat:")
81 for _, boat := range perBoat {
82 fmt.Fprintf(output, " %-10s %v\n", boat.Boat, boat.Voyages)
83 }
60 return nil
84 return nil
61}
85}
86
87// elements returns an array's elements as fmt prints a slice, with NULL for each element
88// the database holds NULL in.
89//
90// Takes array ([]*T) which holds the elements, nil for a NULL one.
91//
92// Returns string which is the elements in brackets, separated by spaces.
93func elements[T any](array []*T) string {
94 texts := make([]string, 0, len(array))
95 for _, element := range array {
96 if element == nil {
97 texts = append(texts, "NULL")
98 continue
99 }
100 texts = append(texts, fmt.Sprint(*element))
101 }
102 return fmt.Sprint(texts)
103}
104
105// values returns pointers to copies of plain values, as an array parameter whose elements
106// may be NULL takes them.
107//
108// Takes items (...T) which are the elements.
109//
110// Returns []*T which points at the copies, none of them nil.
111func values[T any](items ...T) []*T {
112 pointed := make([]*T, len(items))
113 for index := range items {
114 pointed[index] = &items[index]
115 }
116 return pointed
117}