Compare · 016

Parents with child slices: CockroachDB and TimescaleDB

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

Compare CockroachDB withSQLite PostgreSQL TimescaleDB MySQL MariaDB DuckDB

Configuration

sgl.yamlidentical

CockroachDB
TimescaleDB
1version: 1
1version: 1
2
2
3databases:
3databases:
- name: chippy
- name: chippy
  dialect: postgres
  dialect: postgres
  migrations: db/migrations
  migrations: db/migrations
  queries: db/queries
  queries: db/queries
  go:
  go:
    package: store
    package: store
    out: internal/store
    out: internal/store
    driver: database/sql
    driver: database/sql
  migrate:
  migrate:
    dsn: ${env:DATABASE_URL}
    dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_orders.up.sqlidentical

CockroachDB
TimescaleDB
1CREATE TABLE orders (
1CREATE TABLE orders (
2    id       BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
2    id       BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3    customer TEXT   NOT NULL
3    customer TEXT   NOT NULL
4);
4);
5
5
6CREATE TABLE order_lines (
6CREATE TABLE order_lines (
7    id       BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
7    id       BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
8    order_id BIGINT NOT NULL REFERENCES orders (id),
8    order_id BIGINT NOT NULL REFERENCES orders (id),
9    dish     TEXT   NOT NULL,
9    dish     TEXT   NOT NULL,
10    quantity BIGINT NOT NULL
10    quantity BIGINT NOT NULL
11);
11);

db/migrations/0001_orders.down.sqlidentical

CockroachDB
TimescaleDB
1DROP TABLE order_lines;
1DROP TABLE order_lines;
2DROP TABLE orders;
2DROP TABLE orders;

Queries

OpenOrderidentical

CockroachDB · db/queries/orders.sql
TimescaleDB · db/queries/orders.sql
1-- sgl.query(OpenOrder, exec)
1-- sgl.query(OpenOrder, exec)
2INSERT INTO orders (customer) VALUES ($1);
2INSERT INTO orders (customer) VALUES ($1);

AddLineidentical

CockroachDB · db/queries/orders.sql
TimescaleDB · db/queries/orders.sql
1-- sgl.query(AddLine, exec)
1-- sgl.query(AddLine, exec)
2INSERT INTO order_lines (order_id, dish, quantity) VALUES ($1, $2, $3);
2INSERT INTO order_lines (order_id, dish, quantity) VALUES ($1, $2, $3);

ListOrdersidentical

CockroachDB · db/queries/orders.sql
TimescaleDB · db/queries/orders.sql
1-- sgl.query(ListOrders, many, group_by: orders.id)
1-- sgl.query(ListOrders, many, group_by: orders.id)
2-- sgl.embed(orders)
2-- sgl.embed(orders)
3-- sgl.embed(order_lines)
3-- sgl.embed(order_lines)
4SELECT orders.id, orders.customer, order_lines.id, order_lines.dish, order_lines.quantity
4SELECT orders.id, orders.customer, order_lines.id, order_lines.dish, order_lines.quantity
5FROM orders
5FROM orders
6LEFT JOIN order_lines ON order_lines.order_id = orders.id
6LEFT JOIN order_lines ON order_lines.order_id = orders.id
7ORDER BY orders.id, order_lines.id;
7ORDER BY orders.id, order_lines.id;

What sgl generates

OpenOrderidentical

CockroachDB
TimescaleDB
1func (queries *Queries) OpenOrder(ctx context.Context, customer string) error
1func (queries *Queries) OpenOrder(ctx context.Context, customer string) error

AddLineidentical

CockroachDB
TimescaleDB
1func (queries *Queries) AddLine(ctx context.Context, params AddLineParams) error
1func (queries *Queries) AddLine(ctx context.Context, params AddLineParams) error
2
2
3type AddLineParams struct {
3type AddLineParams struct {
4	OrderID  int64
4	OrderID  int64
5	Dish     string
5	Dish     string
6	Quantity int64
6	Quantity int64
7}
7}

ListOrdersidentical

CockroachDB
TimescaleDB
1func (queries *Queries) ListOrders(ctx context.Context) ([]ListOrdersRow, error)
1func (queries *Queries) ListOrders(ctx context.Context) ([]ListOrdersRow, error)
2
2
3type ListOrdersRow struct {
3type ListOrdersRow struct {
4	Orders     ListOrdersOrders
4	Orders     ListOrdersOrders
5	OrderLines []ListOrdersOrderLines
5	OrderLines []ListOrdersOrderLines
6}
6}
7
7
8type ListOrdersOrders struct {
8type ListOrdersOrders struct {
9	ID       int64  `json:"id"`
9	ID       int64  `json:"id"`
10	Customer string `json:"customer"`
10	Customer string `json:"customer"`
11}
11}
12
12
13type ListOrdersOrderLines struct {
13type ListOrdersOrderLines struct {
14	ID       *int64  `json:"id"`
14	ID       *int64  `json:"id"`
15	Dish     *string `json:"dish"`
15	Dish     *string `json:"dish"`
16	Quantity *int64  `json:"quantity"`
16	Quantity *int64  `json:"quantity"`
17}
17}

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/016_one_to_many/internal/store"
9	"seagull.sh/seagull/examples/postgres/016_one_to_many/internal/store"
10)
10)
11
11
12// run takes three chip shop orders and lists them with their lines. The join in
12// run takes three chip shop orders and lists them with their lines. The join in
13// ListOrders returns one row per order line, repeating the order's columns each time.
13// ListOrders returns one row per order line, repeating the order's columns each time.
14// group_by: orders.id collapses those rows: each distinct orders.id becomes one
14// group_by: orders.id collapses those rows: each distinct orders.id becomes one
15// ListOrdersRow, holding the order's columns in Orders and every matching line in the
15// ListOrdersRow, holding the order's columns in Orders and every matching line in the
16// OrderLines slice. Groups keep the order the rows arrive in.
16// OrderLines slice. Groups keep the order the rows arrive in.
17//
17//
18// It is a LEFT JOIN, so an order with no lines still appears, with an empty slice, and
18// It is a LEFT JOIN, so an order with no lines still appears, with an empty slice, and
19// the line fields are pointers. sgl vet checks the directive: group_by must name a column
19// the line fields are pointers. sgl vet checks the directive: group_by must name a column
20// the query returns (Q014), the other tables need sgl.embed (Q015), and the command must
20// the query returns (Q014), the other tables need sgl.embed (Q015), and the command must
21// be many (Q016).
21// be many (Q016).
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 order and its lines.
24// Takes output (io.Writer) which receives each order and its lines.
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	for _, customer := range []string{"Steven", "Gwen", "Jago"} {
30	for _, customer := range []string{"Steven", "Gwen", "Jago"} {
31		if err := queries.OpenOrder(ctx, customer); err != nil {
31		if err := queries.OpenOrder(ctx, customer); err != nil {
32			return fmt.Errorf("opening %s's order: %w", customer, err)
32			return fmt.Errorf("opening %s's order: %w", customer, err)
33		}
33		}
34	}
34	}
35	lines := []store.AddLineParams{
35	lines := []store.AddLineParams{
36		{OrderID: 1, Dish: "cod and chips", Quantity: 2},
36		{OrderID: 1, Dish: "cod and chips", Quantity: 2},
37		{OrderID: 1, Dish: "mushy peas", Quantity: 1},
37		{OrderID: 1, Dish: "mushy peas", Quantity: 1},
38		{OrderID: 1, Dish: "pickled egg", Quantity: 3},
38		{OrderID: 1, Dish: "pickled egg", Quantity: 3},
39		{OrderID: 2, Dish: "chips", Quantity: 1},
39		{OrderID: 2, Dish: "chips", Quantity: 1},
40	}
40	}
41	for _, line := range lines {
41	for _, line := range lines {
42		if err := queries.AddLine(ctx, line); err != nil {
42		if err := queries.AddLine(ctx, line); err != nil {
43			return fmt.Errorf("adding %s to order %d: %w", line.Dish, line.OrderID, err)
43			return fmt.Errorf("adding %s to order %d: %w", line.Dish, line.OrderID, err)
44		}
44		}
45	}
45	}
46
46
47	orders, err := queries.ListOrders(ctx)
47	orders, err := queries.ListOrders(ctx)
48	if err != nil {
48	if err != nil {
49		return fmt.Errorf("listing orders: %w", err)
49		return fmt.Errorf("listing orders: %w", err)
50	}
50	}
51	for _, order := range orders {
51	for _, order := range orders {
52		fmt.Fprintf(output, "order %d for %s\n", order.Orders.ID, order.Orders.Customer)
52		fmt.Fprintf(output, "order %d for %s\n", order.Orders.ID, order.Orders.Customer)
53		if len(order.OrderLines) == 0 {
53		if len(order.OrderLines) == 0 {
54			fmt.Fprintln(output, "  nothing ordered yet")
54			fmt.Fprintln(output, "  nothing ordered yet")
55		}
55		}
56		for _, line := range order.OrderLines {
56		for _, line := range order.OrderLines {
57			fmt.Fprintf(output, "  %d x %s\n", *line.Quantity, *line.Dish)
57			fmt.Fprintf(output, "  %d x %s\n", *line.Quantity, *line.Dish)
58		}
58		}
59	}
59	}
60	return nil
60	return nil
61}
61}

Go type differences

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

Query	Field	CockroachDB	TimescaleDB
ListOrders	column `id`	`*int64`	`int64`