Compare · 016

Parents with child slices: PostgreSQL and MariaDB

Every file of the PostgreSQL and MariaDB versions side by side, with the lines that differ marked. 3 parameter or column types differ in Go.

Compare PostgreSQL withSQLiteCockroachDBTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
PostgreSQL
MariaDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: chippy
4 - name: chippy
5 dialect: postgres
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
11 driver: database/sql
12 migrate:
13 dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_orders.up.sqldiffers
PostgreSQL
MariaDB
1CREATE TABLE orders (
1CREATE TABLE orders (
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3 customer TEXT NOT NULL
3 customer VARCHAR(50) 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 NOT NULL AUTO_INCREMENT PRIMARY KEY,
8 order_id BIGINT NOT NULL REFERENCES orders (id),
8 order_id BIGINT NOT NULL,
9 dish TEXT NOT NULL,
9 dish VARCHAR(50) NOT NULL,
10 quantity BIGINT NOT NULL
10 quantity INT NOT NULL,
11 FOREIGN KEY (order_id) REFERENCES orders (id)
11);
12);
db/migrations/0001_orders.down.sqlidentical
PostgreSQL
MariaDB
1DROP TABLE order_lines;
1DROP TABLE order_lines;
2DROP TABLE orders;
2DROP TABLE orders;

Queries

OpenOrderdiffers
PostgreSQL · db/queries/orders.sql
MariaDB · 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 (?);
AddLinediffers
PostgreSQL · db/queries/orders.sql
MariaDB · 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 (?, ?, ?);
ListOrdersidentical
PostgreSQL · db/queries/orders.sql
MariaDB · 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
PostgreSQL
MariaDB
1func (queries *Queries) OpenOrder(ctx context.Context, customer string) error
1func (queries *Queries) OpenOrder(ctx context.Context, customer string) error
AddLinediffers
PostgreSQL
MariaDB
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 int32
7}
7}
ListOrdersdiffers
PostgreSQL
MariaDB
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 *int32 `json:"quantity"`
17}
17}

The program

example.godiffers
PostgreSQL
MariaDB
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/mysql/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.

QueryFieldPostgreSQLMariaDB
AddLineparameter quantityint64int32
ListOrderscolumn id*int64int64
ListOrderscolumn quantity*int64*int32