Compare · 006

JSON columns: DuckDB and MySQL

Every file of the DuckDB and MySQL versions side by side, with the lines that differ marked.

Compare DuckDB withSQLitePostgreSQLCockroachDBTimescaleDBMySQLMariaDBClickHouse

Configuration

sgl.yamldiffers
DuckDB
MySQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: chippy
4 - name: orders
5 dialect: duckdb
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_orders.up.sqldiffers
DuckDB
MySQL
1CREATE SEQUENCE order_ids;
2
3CREATE TABLE orders (
1CREATE TABLE orders (
4 id BIGINT PRIMARY KEY DEFAULT nextval('order_ids'),
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
5 customer VARCHAR NOT NULL,
3 customer VARCHAR(50) NOT NULL,
6 basket JSON NOT NULL,
4 basket JSON NOT NULL,
7 delivery JSON
5 delivery JSON
8);
6);
db/migrations/0001_orders.down.sqldiffers
DuckDB
MySQL
1DROP TABLE orders;
1DROP TABLE orders;
2DROP SEQUENCE order_ids;

Queries

PlaceOrderdiffers
DuckDB · db/queries/orders.sql
MySQL · 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 (?, ?, ?);
GetOrderdiffers
DuckDB · db/queries/orders.sql
MySQL · 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 = ?;
OrdersWithFishdiffers
DuckDB · db/queries/orders.sql
MySQL · db/queries/orders.sql
1-- sgl.query(OrdersWithFish, many)
1-- sgl.query(OrdersWithFish, many)
2-- $1 as sgl.param(fish, type: varchar)
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 address
7 delivery ->> '$.address' AS address
8FROM orders
8FROM orders
9WHERE basket ->> '$.fish' = $1
9WHERE basket ->> '$.fish' = ?1
10ORDER BY id;
10ORDER BY id;

What sgl generates

PlaceOrderidentical
DuckDB
MySQL
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
DuckDB
MySQL
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"`
4 ID int64 `json:"id"`
5 Customer string `json:"customer"`
5 Customer string `json:"customer"`
6 Basket JSON `json:"basket"`
6 Basket JSON `json:"basket"`
7 Delivery JSON `json:"delivery"`
7 Delivery JSON `json:"delivery"`
8}
8}
9
9
10type JSON []byte
10type JSON []byte
OrdersWithFishidentical
DuckDB
MySQL
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"`
4 ID int64 `json:"id"`
5 Customer string `json:"customer"`
5 Customer string `json:"customer"`
6 Extras JSON `json:"extras"`
6 Extras JSON `json:"extras"`
7 Address *string `json:"address"`
7 Address *string `json:"address"`
8}
8}
9
9
10type JSON []byte
10type JSON []byte

The program

example.godiffers
DuckDB
MySQL
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
9
10 "seagull.sh/seagull/examples/duckdb/006_json/internal/store"
10 "seagull.sh/seagull/examples/mysql/006_json/internal/store"
11)
11)
12
12
13// basket is the document an order's basket column holds.
13// basket is the document an order's basket column holds.
14type basket struct {
14type basket struct {
15 // Fish is the fish the order is for.
15 // Fish is the fish the order is for.
16 Fish string `json:"fish"`
16 Fish string `json:"fish"`
17
17
18 // Portions is how many portions of fish and chips.
18 // Portions is how many portions of fish and chips.
19 Portions int `json:"portions"`
19 Portions int `json:"portions"`
20
20
21 // Extras lists the sides.
21 // Extras lists the sides.
22 Extras []string `json:"extras"`
22 Extras []string `json:"extras"`
23}
23}
24
24
25// delivery is the document an order's delivery column holds, when the order is delivered.
25// delivery is the document an order's delivery column holds, when the order is delivered.
26type delivery struct {
26type delivery struct {
27 // Address is where the order goes.
27 // Address is where the order goes.
28 Address string `json:"address"`
28 Address string `json:"address"`
29}
29}
30
30
31// run places three chip shop orders and reads them back. A JSON column read by a query
31// run places three chip shop orders and reads them back. A JSON column becomes the
32// becomes the generated store.JSON type, which holds the raw document as bytes: decode it
32// generated store.JSON type, which holds the raw document as bytes: encode a value into
33// with json.Unmarshal. A nil store.JSON is SQL NULL.
33// it with json.Marshal and decode it with json.Unmarshal. A nil store.JSON is SQL NULL.
34//
35// The generated SQL casts each JSON column it reads to VARCHAR, so store.JSON holds the
36// document as DuckDB stored it, the keys in the order they were written.
37//
34//
38// PlaceOrder takes its documents as store.JSON too, and the generated code hands
35// MySQL checks each document as it is stored and keeps it in a binary form, so what comes
39// duckdb-go their text; DuckDB's JSON type is VARCHAR underneath and checks the text as
36// back is MySQL's own text for it: keys reordered, with a space after each colon and
40// it is stored.
37// comma. Decoding gives the same values either way.
41//
38//
42// OrdersWithFish filters with ->>, which extracts a value as text, and selects with ->,
39// OrdersWithFish filters with ->>, which extracts a value as SQL text, and selects with
43// which extracts a JSON fragment, so Extras is store.JSON again. Address is *string
40// ->, which extracts a JSON fragment, so Extras is store.JSON again. Address is *string
44// because ->> gives NULL when the delivery column or its address is missing.
41// because ->> gives NULL when the delivery column or its address is missing. They are
42// MySQL's shorthand for JSON_EXTRACT and JSON_UNQUOTE(JSON_EXTRACT(...)).
45//
43//
46// Takes database (*sql.DB) which is a migrated database.
44// Takes database (*sql.DB) which is a migrated database.
47// Takes output (io.Writer) which receives the decoded orders and the search.
45// Takes output (io.Writer) which receives the decoded orders and the search.
48//
46//
49// Returns error when a query fails or a document does not encode or decode.
47// Returns error when a query fails or a document does not encode or decode.
50func run(ctx context.Context, database *sql.DB, output io.Writer) error {
48func run(ctx context.Context, database *sql.DB, output io.Writer) error {
51 queries := store.New(database)
49 queries := store.New(database)
52
50
53 orders := []struct {
51 orders := []struct {
54 customer string
52 customer string
55 basket basket
53 basket basket
56 delivery *delivery
54 delivery *delivery
57 }{
55 }{
58 {customer: "Steven", basket: basket{Fish: "cod", Portions: 2, Extras: []string{"mushy peas", "curry sauce"}}},
56 {customer: "Steven", basket: basket{Fish: "cod", Portions: 2, Extras: []string{"mushy peas", "curry sauce"}}},
59 {customer: "Gwen", basket: basket{Fish: "haddock", Portions: 1, Extras: []string{}}},
57 {customer: "Gwen", basket: basket{Fish: "haddock", Portions: 1, Extras: []string{}}},
60 {customer: "Morwenna", basket: basket{Fish: "cod", Portions: 1, Extras: []string{"pickled egg"}}, delivery: &delivery{Address: "4 Harbour Row"}},
58 {customer: "Morwenna", basket: basket{Fish: "cod", Portions: 1, Extras: []string{"pickled egg"}}, delivery: &delivery{Address: "4 Harbour Row"}},
61 }
59 }
62 for _, order := range orders {
60 for _, order := range orders {
63 encodedBasket, err := json.Marshal(order.basket)
61 encodedBasket, err := json.Marshal(order.basket)
64 if err != nil {
62 if err != nil {
65 return fmt.Errorf("encoding %s's basket: %w", order.customer, err)
63 return fmt.Errorf("encoding %s's basket: %w", order.customer, err)
66 }
64 }
67 parameters := store.PlaceOrderParams{Customer: order.customer, Basket: encodedBasket, Delivery: nil}
65 parameters := store.PlaceOrderParams{Customer: order.customer, Basket: encodedBasket, Delivery: nil}
68 if order.delivery != nil {
66 if order.delivery != nil {
69 encodedDelivery, err := json.Marshal(order.delivery)
67 encodedDelivery, err := json.Marshal(order.delivery)
70 if err != nil {
68 if err != nil {
71 return fmt.Errorf("encoding %s's delivery: %w", order.customer, err)
69 return fmt.Errorf("encoding %s's delivery: %w", order.customer, err)
72 }
70 }
73 parameters.Delivery = encodedDelivery
71 parameters.Delivery = encodedDelivery
74 }
72 }
75 if err := queries.PlaceOrder(ctx, parameters); err != nil {
73 if err := queries.PlaceOrder(ctx, parameters); err != nil {
76 return fmt.Errorf("placing %s's order: %w", order.customer, err)
74 return fmt.Errorf("placing %s's order: %w", order.customer, err)
77 }
75 }
78 }
76 }
79
77
80 first, err := queries.GetOrder(ctx, 1)
78 first, err := queries.GetOrder(ctx, 1)
81 if err != nil {
79 if err != nil {
82 return fmt.Errorf("getting order 1: %w", err)
80 return fmt.Errorf("getting order 1: %w", err)
83 }
81 }
84 var decoded basket
82 var decoded basket
85 if err := json.Unmarshal(first.Basket, &decoded); err != nil {
83 if err := json.Unmarshal(first.Basket, &decoded); err != nil {
86 return fmt.Errorf("decoding order 1's basket: %w", err)
84 return fmt.Errorf("decoding order 1's basket: %w", err)
87 }
85 }
88 fmt.Fprintf(output, "order 1 stored %s\n", first.Basket)
86 fmt.Fprintf(output, "order 1 stored %s\n", first.Basket)
89 fmt.Fprintf(output, "decoded: %d x %s with %v, delivery is NULL: %t\n", decoded.Portions, decoded.Fish, decoded.Extras, first.Delivery == nil)
87 fmt.Fprintf(output, "decoded: %d x %s with %v, delivery is NULL: %t\n", decoded.Portions, decoded.Fish, decoded.Extras, first.Delivery == nil)
90
88
91 cod, err := queries.OrdersWithFish(ctx, "cod")
89 cod, err := queries.OrdersWithFish(ctx, "cod")
92 if err != nil {
90 if err != nil {
93 return fmt.Errorf("finding cod orders: %w", err)
91 return fmt.Errorf("finding cod orders: %w", err)
94 }
92 }
95 fmt.Fprintln(output, "\ncod orders:")
93 fmt.Fprintln(output, "\ncod orders:")
96 for _, order := range cod {
94 for _, order := range cod {
97 address := "collecting"
95 address := "collecting"
98 if order.Address != nil {
96 if order.Address != nil {
99 address = "to " + *order.Address
97 address = "to " + *order.Address
100 }
98 }
101 fmt.Fprintf(output, " order %d %-9s extras %-30s %s\n", order.ID, order.Customer, order.Extras, address)
99 fmt.Fprintf(output, " order %d %-9s extras %-30s %s\n", order.ID, order.Customer, order.Extras, address)
102 }
100 }
103 return nil
101 return nil
104}
102}