Compare · 006

JSON columns: 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: orders
4 - name: orders
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
12 json_tags:
12 json_tags:
13 case: camel
13 case: camel
14 omitempty: true
14 omitempty: true
15 struct_tags:
15 struct_tags:
16 db: "{name}"
16 db: "{name}"
17 empty_slices: true
17 empty_slices: true

Migrations

db/migrations/0001_orders.up.sqlidentical
CockroachDB
TimescaleDB
1CREATE TABLE orders (
1CREATE TABLE orders (
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3 customer TEXT NOT NULL,
3 customer TEXT NOT NULL,
4 basket JSONB NOT NULL,
4 basket JSONB NOT NULL,
5 delivery JSONB
5 delivery JSONB
6);
6);
db/migrations/0001_orders.down.sqlidentical
CockroachDB
TimescaleDB
1DROP TABLE orders;
1DROP TABLE orders;

Queries

PlaceOrderidentical
CockroachDB · db/queries/orders.sql
TimescaleDB · db/queries/orders.sql
1-- sgl.query(PlaceOrder, exec)
1-- sgl.query(PlaceOrder, exec)
2INSERT INTO orders (customer, basket, delivery) VALUES ($1, $2, $3);
2INSERT INTO orders (customer, basket, delivery) VALUES ($1, $2, $3);
GetOrderidentical
CockroachDB · db/queries/orders.sql
TimescaleDB · db/queries/orders.sql
1-- sgl.query(GetOrder, one)
1-- sgl.query(GetOrder, one)
2SELECT id, customer, basket, delivery FROM orders WHERE id = $1;
2SELECT id, customer, basket, delivery FROM orders WHERE id = $1;
OrdersWithFishidentical
CockroachDB · db/queries/orders.sql
TimescaleDB · db/queries/orders.sql
1-- sgl.query(OrdersWithFish, many)
1-- sgl.query(OrdersWithFish, many)
2-- $1 as sgl.param(fish, type: text)
2-- $1 as sgl.param(fish, type: text)
3SELECT
3SELECT
4 id,
4 id,
5 customer,
5 customer,
6 basket -> 'extras' AS extras,
6 basket -> 'extras' AS extras,
7 delivery ->> 'address' AS delivery_address
7 delivery ->> 'address' AS delivery_address
8FROM orders
8FROM orders
9WHERE basket ->> 'fish' = $1
9WHERE basket ->> 'fish' = $1
10ORDER BY id;
10ORDER BY id;
OrdersContainingidentical
CockroachDB · db/queries/orders.sql
TimescaleDB · db/queries/orders.sql
1-- sgl.query(OrdersContaining, many)
1-- sgl.query(OrdersContaining, many)
2-- $1 as sgl.param(subset)
2-- $1 as sgl.param(subset)
3SELECT id, customer
3SELECT id, customer
4FROM orders
4FROM orders
5WHERE basket @> $1::jsonb
5WHERE basket @> $1::jsonb
6ORDER BY id;
6ORDER BY id;

What sgl generates

PlaceOrderidentical
CockroachDB
TimescaleDB
1func (queries *Queries) PlaceOrder(ctx context.Context, params PlaceOrderParams) error
1func (queries *Queries) PlaceOrder(ctx context.Context, params PlaceOrderParams) error
2
2
3type PlaceOrderParams struct {
3type PlaceOrderParams struct {
4 Customer string
4 Customer string
5 Basket JSON
5 Basket JSON
6 Delivery JSON
6 Delivery JSON
7}
7}
8
8
9type JSON []byte
9type JSON []byte
GetOrderidentical
CockroachDB
TimescaleDB
1func (queries *Queries) GetOrder(ctx context.Context, id int64) (GetOrderRow, error)
1func (queries *Queries) GetOrder(ctx context.Context, id int64) (GetOrderRow, error)
2
2
3type GetOrderRow struct {
3type GetOrderRow struct {
4 ID int64 `json:"id,omitempty" db:"id"`
4 ID int64 `json:"id,omitempty" db:"id"`
5 Customer string `json:"customer,omitempty" db:"customer"`
5 Customer string `json:"customer,omitempty" db:"customer"`
6 Basket JSON `json:"basket,omitempty" db:"basket"`
6 Basket JSON `json:"basket,omitempty" db:"basket"`
7 Delivery JSON `json:"delivery,omitempty" db:"delivery"`
7 Delivery JSON `json:"delivery,omitempty" db:"delivery"`
8}
8}
9
9
10type JSON []byte
10type JSON []byte
OrdersWithFishidentical
CockroachDB
TimescaleDB
1func (queries *Queries) OrdersWithFish(ctx context.Context, fish string) ([]OrdersWithFishRow, error)
1func (queries *Queries) OrdersWithFish(ctx context.Context, fish string) ([]OrdersWithFishRow, error)
2
2
3type OrdersWithFishRow struct {
3type OrdersWithFishRow struct {
4 ID int64 `json:"id,omitempty" db:"id"`
4 ID int64 `json:"id,omitempty" db:"id"`
5 Customer string `json:"customer,omitempty" db:"customer"`
5 Customer string `json:"customer,omitempty" db:"customer"`
6 Extras JSON `json:"extras,omitempty" db:"extras"`
6 Extras JSON `json:"extras,omitempty" db:"extras"`
7 DeliveryAddress *string `json:"deliveryAddress,omitempty" db:"delivery_address"`
7 DeliveryAddress *string `json:"deliveryAddress,omitempty" db:"delivery_address"`
8}
8}
9
9
10type JSON []byte
10type JSON []byte
OrdersContainingidentical
CockroachDB
TimescaleDB
1func (queries *Queries) OrdersContaining(ctx context.Context, subset JSON) ([]OrdersContainingRow, error)
1func (queries *Queries) OrdersContaining(ctx context.Context, subset JSON) ([]OrdersContainingRow, error)
2
2
3type JSON []byte
3type JSON []byte
4
4
5type OrdersContainingRow struct {
5type OrdersContainingRow struct {
6 ID int64 `json:"id,omitempty" db:"id"`
6 ID int64 `json:"id,omitempty" db:"id"`
7 Customer string `json:"customer,omitempty" db:"customer"`
7 Customer string `json:"customer,omitempty" db:"customer"`
8}
8}

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 "encoding/json"
6 "encoding/json"
7 "fmt"
7 "fmt"
8 "io"
8 "io"
9 "reflect"
9 "reflect"
10 "strings"
10 "strings"
11
11
12 "seagull.sh/seagull/examples/postgres/006_json/internal/store"
12 "seagull.sh/seagull/examples/postgres/006_json/internal/store"
13)
13)
14
14
15// basket is the document an order's basket column holds.
15// basket is the document an order's basket column holds.
16type basket struct {
16type basket struct {
17 // Fish is the fish the order is for.
17 // Fish is the fish the order is for.
18 Fish string `json:"fish"`
18 Fish string `json:"fish"`
19
19
20 // Portions is how many portions of fish and chips.
20 // Portions is how many portions of fish and chips.
21 Portions int `json:"portions"`
21 Portions int `json:"portions"`
22
22
23 // Extras lists the sides.
23 // Extras lists the sides.
24 Extras []string `json:"extras"`
24 Extras []string `json:"extras"`
25}
25}
26
26
27// delivery is the document an order's delivery column holds, when the order is delivered.
27// delivery is the document an order's delivery column holds, when the order is delivered.
28type delivery struct {
28type delivery struct {
29 // Address is where the order goes.
29 // Address is where the order goes.
30 Address string `json:"address"`
30 Address string `json:"address"`
31}
31}
32
32
33// run places three chip shop orders and reads them back. A JSONB column becomes the
33// run places three chip shop orders and reads them back. A JSONB column becomes the
34// generated store.JSON type, which holds the raw document as bytes: encode a value into
34// generated store.JSON type, which holds the raw document as bytes: encode a value into
35// it with json.Marshal and decode it with json.Unmarshal. A nil store.JSON is SQL NULL.
35// it with json.Marshal and decode it with json.Unmarshal. A nil store.JSON is SQL NULL.
36//
36//
37// JSONB stores a parsed document, not the text it was sent, and writes it back in its own
37// JSONB stores a parsed document, not the text it was sent, and writes it back in its own
38// form: a space after each comma and colon, keys reordered and duplicate keys dropped, so
38// form: a space after each comma and colon, keys reordered and duplicate keys dropped, so
39// compare documents by decoding them, not by their bytes.
39// compare documents by decoding them, not by their bytes.
40//
40//
41// OrdersWithFish filters with ->>, which extracts a value as text, and selects with ->,
41// OrdersWithFish filters with ->>, which extracts a value as text, and selects with ->,
42// which extracts a JSONB fragment, so Extras is store.JSON again. DeliveryAddress is
42// which extracts a JSONB fragment, so Extras is store.JSON again. DeliveryAddress is
43// *string because ->> gives NULL when the delivery column or its address is missing. The
43// *string because ->> gives NULL when the delivery column or its address is missing. The
44// ->> comparison's parameter is pinned with sgl.param(fish, type: text), as inference
44// ->> comparison's parameter is pinned with sgl.param(fish, type: text), as inference
45// leaves it as any. OrdersContaining uses @>, which matches a document containing the one
45// leaves it as any. OrdersContaining uses @>, which matches a document containing the one
46// bound to $1::jsonb.
46// bound to $1::jsonb.
47//
47//
48// The rows go straight out as JSON, as an HTTP handler would send them, so sgl.yaml
48// The rows go straight out as JSON, as an HTTP handler would send them, so sgl.yaml
49// shapes the generated structs for that: go.json_tags writes each field's json tag in
49// shapes the generated structs for that: go.json_tags writes each field's json tag in
50// camel case with omitempty, so delivery_address is "deliveryAddress" and a nil one is
50// camel case with omitempty, so delivery_address is "deliveryAddress" and a nil one is
51// left out; go.struct_tags adds a db tag holding the column's name, {name}, for libraries
51// left out; go.struct_tags adds a db tag holding the column's name, {name}, for libraries
52// that map columns by tag; and go.empty_slices makes a many method return an empty slice
52// that map columns by tag; and go.empty_slices makes a many method return an empty slice
53// when no row matches, which encodes as [] rather than null.
53// when no row matches, which encodes as [] rather than null.
54//
54//
55// Takes database (*sql.DB) which is a migrated database.
55// Takes database (*sql.DB) which is a migrated database.
56// Takes output (io.Writer) which receives the decoded orders and the searches.
56// Takes output (io.Writer) which receives the decoded orders and the searches.
57//
57//
58// Returns error when a query fails or a document does not encode or decode.
58// Returns error when a query fails or a document does not encode or decode.
59func run(ctx context.Context, database *sql.DB, output io.Writer) error {
59func run(ctx context.Context, database *sql.DB, output io.Writer) error {
60 queries := store.New(database)
60 queries := store.New(database)
61
61
62 orders := []struct {
62 orders := []struct {
63 customer string
63 customer string
64 basket basket
64 basket basket
65 delivery *delivery
65 delivery *delivery
66 }{
66 }{
67 {customer: "Steven", basket: basket{Fish: "cod", Portions: 2, Extras: []string{"mushy peas", "curry sauce"}}},
67 {customer: "Steven", basket: basket{Fish: "cod", Portions: 2, Extras: []string{"mushy peas", "curry sauce"}}},
68 {customer: "Gwen", basket: basket{Fish: "haddock", Portions: 1, Extras: []string{}}},
68 {customer: "Gwen", basket: basket{Fish: "haddock", Portions: 1, Extras: []string{}}},
69 {customer: "Morwenna", basket: basket{Fish: "cod", Portions: 1, Extras: []string{"pickled egg"}}, delivery: &delivery{Address: "4 Harbour Row"}},
69 {customer: "Morwenna", basket: basket{Fish: "cod", Portions: 1, Extras: []string{"pickled egg"}}, delivery: &delivery{Address: "4 Harbour Row"}},
70 }
70 }
71 for _, order := range orders {
71 for _, order := range orders {
72 encodedBasket, err := json.Marshal(order.basket)
72 encodedBasket, err := json.Marshal(order.basket)
73 if err != nil {
73 if err != nil {
74 return fmt.Errorf("encoding %s's basket: %w", order.customer, err)
74 return fmt.Errorf("encoding %s's basket: %w", order.customer, err)
75 }
75 }
76 parameters := store.PlaceOrderParams{Customer: order.customer, Basket: encodedBasket, Delivery: nil}
76 parameters := store.PlaceOrderParams{Customer: order.customer, Basket: encodedBasket, Delivery: nil}
77 if order.delivery != nil {
77 if order.delivery != nil {
78 encodedDelivery, err := json.Marshal(order.delivery)
78 encodedDelivery, err := json.Marshal(order.delivery)
79 if err != nil {
79 if err != nil {
80 return fmt.Errorf("encoding %s's delivery: %w", order.customer, err)
80 return fmt.Errorf("encoding %s's delivery: %w", order.customer, err)
81 }
81 }
82 parameters.Delivery = encodedDelivery
82 parameters.Delivery = encodedDelivery
83 }
83 }
84 if err := queries.PlaceOrder(ctx, parameters); err != nil {
84 if err := queries.PlaceOrder(ctx, parameters); err != nil {
85 return fmt.Errorf("placing %s's order: %w", order.customer, err)
85 return fmt.Errorf("placing %s's order: %w", order.customer, err)
86 }
86 }
87 }
87 }
88
88
89 first, err := queries.GetOrder(ctx, 1)
89 first, err := queries.GetOrder(ctx, 1)
90 if err != nil {
90 if err != nil {
91 return fmt.Errorf("getting order 1: %w", err)
91 return fmt.Errorf("getting order 1: %w", err)
92 }
92 }
93 var decoded basket
93 var decoded basket
94 if err := json.Unmarshal(first.Basket, &decoded); err != nil {
94 if err := json.Unmarshal(first.Basket, &decoded); err != nil {
95 return fmt.Errorf("decoding order 1's basket: %w", err)
95 return fmt.Errorf("decoding order 1's basket: %w", err)
96 }
96 }
97 fmt.Fprintf(output, "order 1 decoded: %d x %s with %v, delivery is NULL: %t\n", decoded.Portions, decoded.Fish, decoded.Extras, first.Delivery == nil)
97 fmt.Fprintf(output, "order 1 decoded: %d x %s with %v, delivery is NULL: %t\n", decoded.Portions, decoded.Fish, decoded.Extras, first.Delivery == nil)
98
98
99 cod, err := queries.OrdersWithFish(ctx, "cod")
99 cod, err := queries.OrdersWithFish(ctx, "cod")
100 if err != nil {
100 if err != nil {
101 return fmt.Errorf("finding cod orders: %w", err)
101 return fmt.Errorf("finding cod orders: %w", err)
102 }
102 }
103 fmt.Fprintln(output, "\ncod orders:")
103 fmt.Fprintln(output, "\ncod orders:")
104 for _, order := range cod {
104 for _, order := range cod {
105 address := "collecting"
105 address := "collecting"
106 if order.DeliveryAddress != nil {
106 if order.DeliveryAddress != nil {
107 address = "to " + *order.DeliveryAddress
107 address = "to " + *order.DeliveryAddress
108 }
108 }
109 fmt.Fprintf(output, " order %d %-9s extras %-30s %s\n", order.ID, order.Customer, order.Extras, address)
109 fmt.Fprintf(output, " order %d %-9s extras %-30s %s\n", order.ID, order.Customer, order.Extras, address)
110 }
110 }
111 if err := writeJSON(output, "cod orders as JSON", cod); err != nil {
111 if err := writeJSON(output, "cod orders as JSON", cod); err != nil {
112 return err
112 return err
113 }
113 }
114
114
115 plaice, err := queries.OrdersWithFish(ctx, "plaice")
115 plaice, err := queries.OrdersWithFish(ctx, "plaice")
116 if err != nil {
116 if err != nil {
117 return fmt.Errorf("finding plaice orders: %w", err)
117 return fmt.Errorf("finding plaice orders: %w", err)
118 }
118 }
119 if err := writeJSON(output, "plaice orders as JSON", plaice); err != nil {
119 if err := writeJSON(output, "plaice orders as JSON", plaice); err != nil {
120 return err
120 return err
121 }
121 }
122 field, _ := reflect.TypeFor[store.OrdersWithFishRow]().FieldByName("DeliveryAddress")
122 field, _ := reflect.TypeFor[store.OrdersWithFishRow]().FieldByName("DeliveryAddress")
123 fmt.Fprintf(output, "DeliveryAddress is tagged %s\n", field.Tag)
123 fmt.Fprintf(output, "DeliveryAddress is tagged %s\n", field.Tag)
124
124
125 subset := store.JSON(`{"extras": ["pickled egg"]}`)
125 subset := store.JSON(`{"extras": ["pickled egg"]}`)
126 containing, err := queries.OrdersContaining(ctx, subset)
126 containing, err := queries.OrdersContaining(ctx, subset)
127 if err != nil {
127 if err != nil {
128 return fmt.Errorf("finding orders containing %s: %w", subset, err)
128 return fmt.Errorf("finding orders containing %s: %w", subset, err)
129 }
129 }
130 customers := make([]string, 0, len(containing))
130 customers := make([]string, 0, len(containing))
131 for _, order := range containing {
131 for _, order := range containing {
132 customers = append(customers, order.Customer)
132 customers = append(customers, order.Customer)
133 }
133 }
134 fmt.Fprintf(output, "\norders containing %s: %s\n", subset, strings.Join(customers, ", "))
134 fmt.Fprintf(output, "\norders containing %s: %s\n", subset, strings.Join(customers, ", "))
135 return nil
135 return nil
136}
136}
137
137
138// writeJSON writes rows encoded as JSON, as an HTTP handler would send them, after a
138// writeJSON writes rows encoded as JSON, as an HTTP handler would send them, after a
139// label.
139// label.
140//
140//
141// Takes output (io.Writer) which receives the label and the document.
141// Takes output (io.Writer) which receives the label and the document.
142// Takes label (string) which says what the rows are.
142// Takes label (string) which says what the rows are.
143// Takes rows (any) which are the rows to encode.
143// Takes rows (any) which are the rows to encode.
144//
144//
145// Returns error when the rows do not encode.
145// Returns error when the rows do not encode.
146func writeJSON(output io.Writer, label string, rows any) error {
146func writeJSON(output io.Writer, label string, rows any) error {
147 encoded, err := json.Marshal(rows)
147 encoded, err := json.Marshal(rows)
148 if err != nil {
148 if err != nil {
149 return fmt.Errorf("encoding %s: %w", label, err)
149 return fmt.Errorf("encoding %s: %w", label, err)
150 }
150 }
151 fmt.Fprintf(output, "%s: %s\n", label, encoded)
151 fmt.Fprintf(output, "%s: %s\n", label, encoded)
152 return nil
152 return nil
153}
153}