Compare · 020

Rows back from INSERT, UPDATE and DELETE: CockroachDB and PostgreSQL

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

Compare CockroachDB withSQLitePostgreSQLTimescaleDBMariaDBDuckDB

Configuration

sgl.yamlidentical
CockroachDB
PostgreSQL
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 migrate:
12 migrate:
13 dsn: ${env:DATABASE_URL}
13 dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_orders.up.sqlidentical
CockroachDB
PostgreSQL
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 item TEXT NOT NULL,
3 item TEXT NOT NULL,
4 quantity BIGINT NOT NULL DEFAULT 1,
4 quantity BIGINT NOT NULL DEFAULT 1,
5 status TEXT NOT NULL DEFAULT 'waiting'
5 status TEXT NOT NULL DEFAULT 'waiting'
6);
6);
db/migrations/0001_orders.down.sqlidentical
CockroachDB
PostgreSQL
1DROP TABLE orders;
1DROP TABLE orders;

Queries

PlaceOrderidentical
CockroachDB · db/queries/orders.sql
PostgreSQL · db/queries/orders.sql
1-- sgl.query(PlaceOrder, one)
1-- sgl.query(PlaceOrder, one)
2INSERT INTO orders (item, quantity) VALUES ($1, $2)
2INSERT INTO orders (item, quantity) VALUES ($1, $2)
3RETURNING id, status, quantity * 250 AS price_pence;
3RETURNING id, status, quantity * 250 AS price_pence;
ServeWaitingOrdersidentical
CockroachDB · db/queries/orders.sql
PostgreSQL · db/queries/orders.sql
1-- sgl.query(ServeWaitingOrders, many, row: OrderLine)
1-- sgl.query(ServeWaitingOrders, many, row: OrderLine)
2UPDATE orders SET status = 'served' WHERE status = 'waiting'
2UPDATE orders SET status = 'served' WHERE status = 'waiting'
3RETURNING id, item, quantity;
3RETURNING id, item, quantity;
CancelOrderidentical
CockroachDB · db/queries/orders.sql
PostgreSQL · db/queries/orders.sql
1-- sgl.query(CancelOrder, one, row: OrderLine)
1-- sgl.query(CancelOrder, one, row: OrderLine)
2DELETE FROM orders WHERE id = $1 AND status = 'waiting'
2DELETE FROM orders WHERE id = $1 AND status = 'waiting'
3RETURNING id, item, quantity;
3RETURNING id, item, quantity;
ReopenOrderidentical
CockroachDB · db/queries/orders.sql
PostgreSQL · db/queries/orders.sql
1-- sgl.query(ReopenOrder, one, model: orders)
1-- sgl.query(ReopenOrder, one, model: orders)
2UPDATE orders SET status = 'waiting' WHERE id = $1
2UPDATE orders SET status = 'waiting' WHERE id = $1
3RETURNING *;
3RETURNING *;

What sgl generates

PlaceOrderidentical
CockroachDB
PostgreSQL
1func (queries *Queries) PlaceOrder(ctx context.Context, params PlaceOrderParams) (PlaceOrderRow, error)
1func (queries *Queries) PlaceOrder(ctx context.Context, params PlaceOrderParams) (PlaceOrderRow, error)
2
2
3type PlaceOrderParams struct {
3type PlaceOrderParams struct {
4 Item string
4 Item string
5 Quantity int64
5 Quantity int64
6}
6}
7
7
8type PlaceOrderRow struct {
8type PlaceOrderRow struct {
9 ID int64 `json:"id"`
9 ID int64 `json:"id"`
10 Status string `json:"status"`
10 Status string `json:"status"`
11 PricePence int64 `json:"price_pence"`
11 PricePence int64 `json:"price_pence"`
12}
12}
ServeWaitingOrdersidentical
CockroachDB
PostgreSQL
1func (queries *Queries) ServeWaitingOrders(ctx context.Context) ([]OrderLine, error)
1func (queries *Queries) ServeWaitingOrders(ctx context.Context) ([]OrderLine, error)
2
2
3type OrderLine struct {
3type OrderLine struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 Item string `json:"item"`
5 Item string `json:"item"`
6 Quantity int64 `json:"quantity"`
6 Quantity int64 `json:"quantity"`
7}
7}
CancelOrderidentical
CockroachDB
PostgreSQL
1func (queries *Queries) CancelOrder(ctx context.Context, id int64) (OrderLine, error)
1func (queries *Queries) CancelOrder(ctx context.Context, id int64) (OrderLine, error)
2
2
3type OrderLine struct {
3type OrderLine struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 Item string `json:"item"`
5 Item string `json:"item"`
6 Quantity int64 `json:"quantity"`
6 Quantity int64 `json:"quantity"`
7}
7}
ReopenOrderidentical
CockroachDB
PostgreSQL
1func (queries *Queries) ReopenOrder(ctx context.Context, id int64) (Orders, error)
1func (queries *Queries) ReopenOrder(ctx context.Context, id int64) (Orders, error)
2
2
3type Orders struct {
3type Orders struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 Item string `json:"item"`
5 Item string `json:"item"`
6 Quantity int64 `json:"quantity"`
6 Quantity int64 `json:"quantity"`
7 Status string `json:"status"`
7 Status string `json:"status"`
8}
8}

The program

example.goidentical
CockroachDB
PostgreSQL
1package main
1package main
2
2
3import (
3import (
4 "cmp"
4 "cmp"
5 "context"
5 "context"
6 "database/sql"
6 "database/sql"
7 "errors"
7 "errors"
8 "fmt"
8 "fmt"
9 "io"
9 "io"
10 "slices"
10 "slices"
11
11
12 "seagull.sh/seagull/examples/postgres/020_returning/internal/store"
12 "seagull.sh/seagull/examples/postgres/020_returning/internal/store"
13)
13)
14
14
15// run places three orders, cancels one, serves the rest, tries to cancel an order that
15// run places three orders, cancels one, serves the rest, tries to cancel an order that
16// has already been served and reopens one. Every write uses RETURNING, so the rows it
16// has already been served and reopens one. Every write uses RETURNING, so the rows it
17// touched come back from the same statement.
17// touched come back from the same statement.
18//
18//
19// A query reads its rows into a struct of its own, such as PlaceOrderRow, unless its
19// A query reads its rows into a struct of its own, such as PlaceOrderRow, unless its
20// header names another. ServeWaitingOrders and CancelOrder return the same three columns,
20// header names another. ServeWaitingOrders and CancelOrder return the same three columns,
21// so both name the row type OrderLine with row: OrderLine, and the generated code
21// so both name the row type OrderLine with row: OrderLine, and the generated code
22// declares that struct once for the two. ReopenOrder returns the whole order with
22// declares that struct once for the two. ReopenOrder returns the whole order with
23// RETURNING *, so model: orders reads it into Orders, the table's model in models.go,
23// RETURNING *, so model: orders reads it into Orders, the table's model in models.go,
24// rather than a ReopenOrderRow with the same fields.
24// rather than a ReopenOrderRow with the same fields.
25//
25//
26// Takes database (*sql.DB) which is a migrated database.
26// Takes database (*sql.DB) which is a migrated database.
27// Takes output (io.Writer) which receives what each write returned.
27// Takes output (io.Writer) which receives what each write returned.
28//
28//
29// Returns error when a query fails.
29// Returns error when a query fails.
30func run(ctx context.Context, database *sql.DB, output io.Writer) error {
30func run(ctx context.Context, database *sql.DB, output io.Writer) error {
31 queries := store.New(database)
31 queries := store.New(database)
32
32
33 // PlaceOrder is an INSERT declared as one: the id the identity column assigned, the
33 // PlaceOrder is an INSERT declared as one: the id the identity column assigned, the
34 // default status and a computed price all come back in a PlaceOrderRow.
34 // default status and a computed price all come back in a PlaceOrderRow.
35 orders := []store.PlaceOrderParams{
35 orders := []store.PlaceOrderParams{
36 {Item: "chips", Quantity: 2},
36 {Item: "chips", Quantity: 2},
37 {Item: "battered cod", Quantity: 1},
37 {Item: "battered cod", Quantity: 1},
38 {Item: "mushy peas", Quantity: 1},
38 {Item: "mushy peas", Quantity: 1},
39 }
39 }
40 for _, order := range orders {
40 for _, order := range orders {
41 placed, err := queries.PlaceOrder(ctx, order)
41 placed, err := queries.PlaceOrder(ctx, order)
42 if err != nil {
42 if err != nil {
43 return fmt.Errorf("placing an order for %s: %w", order.Item, err)
43 return fmt.Errorf("placing an order for %s: %w", order.Item, err)
44 }
44 }
45 fmt.Fprintf(output, "placed #%d %-12s %s, %dp\n", placed.ID, order.Item, placed.Status, placed.PricePence)
45 fmt.Fprintf(output, "placed #%d %-12s %s, %dp\n", placed.ID, order.Item, placed.Status, placed.PricePence)
46 }
46 }
47
47
48 // CancelOrder is a DELETE declared as one, so it returns the row it removed.
48 // CancelOrder is a DELETE declared as one, so it returns the row it removed.
49 cancelled, err := queries.CancelOrder(ctx, 2)
49 cancelled, err := queries.CancelOrder(ctx, 2)
50 if err != nil {
50 if err != nil {
51 return fmt.Errorf("cancelling order 2: %w", err)
51 return fmt.Errorf("cancelling order 2: %w", err)
52 }
52 }
53 fmt.Fprintf(output, "cancelled #2 %-12s x%d\n", cancelled.Item, cancelled.Quantity)
53 fmt.Fprintf(output, "cancelled #2 %-12s x%d\n", cancelled.Item, cancelled.Quantity)
54
54
55 // ServeWaitingOrders is an UPDATE declared as many: one row for every order it changed.
55 // ServeWaitingOrders is an UPDATE declared as many: one row for every order it changed.
56 // PostgreSQL does not promise an order for RETURNING rows, so sort them.
56 // PostgreSQL does not promise an order for RETURNING rows, so sort them.
57 served, err := queries.ServeWaitingOrders(ctx)
57 served, err := queries.ServeWaitingOrders(ctx)
58 if err != nil {
58 if err != nil {
59 return fmt.Errorf("serving orders: %w", err)
59 return fmt.Errorf("serving orders: %w", err)
60 }
60 }
61 slices.SortFunc(served, func(a, b store.OrderLine) int { return cmp.Compare(a.ID, b.ID) })
61 slices.SortFunc(served, func(a, b store.OrderLine) int { return cmp.Compare(a.ID, b.ID) })
62 for _, order := range served {
62 for _, order := range served {
63 fmt.Fprintf(output, "served #%d %-12s x%d\n", order.ID, order.Item, order.Quantity)
63 fmt.Fprintf(output, "served #%d %-12s x%d\n", order.ID, order.Item, order.Quantity)
64 }
64 }
65
65
66 // A one query that matches no row returns sql.ErrNoRows, writes included: order 1 has
66 // A one query that matches no row returns sql.ErrNoRows, writes included: order 1 has
67 // been served, so the DELETE's WHERE clause no longer matches it.
67 // been served, so the DELETE's WHERE clause no longer matches it.
68 _, err = queries.CancelOrder(ctx, 1)
68 _, err = queries.CancelOrder(ctx, 1)
69 switch {
69 switch {
70 case errors.Is(err, sql.ErrNoRows):
70 case errors.Is(err, sql.ErrNoRows):
71 fmt.Fprintln(output, "cannot cancel #1: it is no longer waiting")
71 fmt.Fprintln(output, "cannot cancel #1: it is no longer waiting")
72 case err != nil:
72 case err != nil:
73 return fmt.Errorf("cancelling order 1: %w", err)
73 return fmt.Errorf("cancelling order 1: %w", err)
74 default:
74 default:
75 return errors.New("cancelled an order that had already been served")
75 return errors.New("cancelled an order that had already been served")
76 }
76 }
77
77
78 reopened, err := queries.ReopenOrder(ctx, 3)
78 reopened, err := queries.ReopenOrder(ctx, 3)
79 if err != nil {
79 if err != nil {
80 return fmt.Errorf("reopening order 3: %w", err)
80 return fmt.Errorf("reopening order 3: %w", err)
81 }
81 }
82 fmt.Fprintf(output, "reopened #%d %-12s x%d, %s\n", reopened.ID, reopened.Item, reopened.Quantity, reopened.Status)
82 fmt.Fprintf(output, "reopened #%d %-12s x%d, %s\n", reopened.ID, reopened.Item, reopened.Quantity, reopened.Status)
83 return nil
83 return nil
84}
84}