Compare · 020

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

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

Compare MariaDB withSQLite PostgreSQL CockroachDB TimescaleDB DuckDB

Configuration

sgl.yamldiffers

MariaDB
PostgreSQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: orders
4  - name: orders
5    dialect: mariadb
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
12    migrate:
13      dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_orders.up.sqldiffers

MariaDB
PostgreSQL
1CREATE TABLE orders (
1CREATE TABLE orders (
2    id       BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
2    id       BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3    item     VARCHAR(50) NOT NULL,
3    item     TEXT   NOT NULL,
4    quantity INT         NOT NULL DEFAULT 1,
4    quantity BIGINT NOT NULL DEFAULT 1,
5    status   VARCHAR(20) NOT NULL DEFAULT 'waiting'
5    status   TEXT   NOT NULL DEFAULT 'waiting'
6);
6);

db/migrations/0001_orders.down.sqlidentical

MariaDB
PostgreSQL
1DROP TABLE orders;
1DROP TABLE orders;

Queries

PlaceOrderdiffers

MariaDB · 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 (?, ?)
2INSERT INTO orders (item, quantity) VALUES ($1, $2)
3RETURNING id, status, quantity * 250 AS price_pence;
3RETURNING id, status, quantity * 250 AS price_pence;

ServeWaitingOrdersdiffers

MariaDB · db/queries/orders.sql
PostgreSQL · db/queries/orders.sql
1-- sgl.query(ServeWaitingOrders, execrows)
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;

ServedOrdersonly MariaDB

MariaDB · db/queries/orders.sql
PostgreSQL · not in this version
1-- sgl.query(ServedOrders, many)
2SELECT id, item, quantity FROM orders WHERE status = 'served' ORDER BY id;

CancelOrderdiffers

MariaDB · db/queries/orders.sql
PostgreSQL · db/queries/orders.sql
1-- sgl.query(CancelOrder, one)
1-- sgl.query(CancelOrder, one, row: OrderLine)
2DELETE FROM orders WHERE id = ? AND status = 'waiting'
2DELETE FROM orders WHERE id = $1 AND status = 'waiting'
3RETURNING item, quantity;
3RETURNING id, item, quantity;

ReopenOrderonly PostgreSQL

MariaDB · not in this version
PostgreSQL · db/queries/orders.sql
1-- sgl.query(ReopenOrder, one, model: orders)
2UPDATE orders SET status = 'waiting' WHERE id = $1
3RETURNING *;

What sgl generates

PlaceOrderdiffers

MariaDB
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 int32
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 int32  `json:"price_pence"`
11	PricePence int64  `json:"price_pence"`
12}
12}

ServeWaitingOrdersdiffers

MariaDB
PostgreSQL
1func (queries *Queries) ServeWaitingOrders(ctx context.Context) (int64, error)
1func (queries *Queries) ServeWaitingOrders(ctx context.Context) ([]OrderLine, error)
2
3type OrderLine struct {
4	ID       int64  `json:"id"`
5	Item     string `json:"item"`
6	Quantity int64  `json:"quantity"`
7}

ServedOrdersonly MariaDB

MariaDB
PostgreSQL · not in this version
1func (queries *Queries) ServedOrders(ctx context.Context) ([]ServedOrdersRow, error)
2
3type ServedOrdersRow struct {
4	ID       int64  `json:"id"`
5	Item     string `json:"item"`
6	Quantity int32  `json:"quantity"`
7}

CancelOrderdiffers

MariaDB
PostgreSQL
1func (queries *Queries) CancelOrder(ctx context.Context, id int64) (CancelOrderRow, error)
1func (queries *Queries) CancelOrder(ctx context.Context, id int64) (OrderLine, error)
2
2
3type CancelOrderRow struct {
3type OrderLine struct {
4	ID       int64  `json:"id"`
4	Item     string `json:"item"`
5	Item     string `json:"item"`
5	Quantity int32  `json:"quantity"`
6	Quantity int64  `json:"quantity"`
6}
7}

ReopenOrderonly PostgreSQL

MariaDB · not in this version
PostgreSQL
1func (queries *Queries) ReopenOrder(ctx context.Context, id int64) (Orders, error)
2
3type Orders struct {
4	ID       int64  `json:"id"`
5	Item     string `json:"item"`
6	Quantity int64  `json:"quantity"`
7	Status   string `json:"status"`
8}

The program

example.godiffers

MariaDB
PostgreSQL
1package main
1package main
2
2
3import (
3import (
4	"cmp"
4	"context"
5	"context"
5	"database/sql"
6	"database/sql"
6	"errors"
7	"errors"
7	"fmt"
8	"fmt"
8	"io"
9	"io"
10	"slices"
9
11
10	"seagull.sh/seagull/examples/mariadb/020_returning/internal/store"
12	"seagull.sh/seagull/examples/postgres/020_returning/internal/store"
11)
13)
12
14
13// run places three orders, cancels one, serves the rest and then tries to cancel an order
15// run places three orders, cancels one, serves the rest, tries to cancel an order that
14// that has already been served. The INSERT and the DELETE use RETURNING, so the rows they
16// has already been served and reopens one. Every write uses RETURNING, so the rows it
15// touched come back from the same statement.
17// touched come back from the same statement.
16//
18//
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,
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
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.
25//
17// Takes database (*sql.DB) which is a migrated database.
26// Takes database (*sql.DB) which is a migrated database.
18// Takes output (io.Writer) which receives what each write returned.
27// Takes output (io.Writer) which receives what each write returned.
19//
28//
20// Returns error when a query fails.
29// Returns error when a query fails.
21func run(ctx context.Context, database *sql.DB, output io.Writer) error {
30func run(ctx context.Context, database *sql.DB, output io.Writer) error {
22	queries := store.New(database)
31	queries := store.New(database)
23
32
24	// PlaceOrder is an INSERT declared as one: the id MariaDB assigned, the default status
33	// PlaceOrder is an INSERT declared as one: the id the identity column assigned, the
25	// and a computed price all come back in a PlaceOrderRow.
34	// default status and a computed price all come back in a PlaceOrderRow.
26	orders := []store.PlaceOrderParams{
35	orders := []store.PlaceOrderParams{
27		{Item: "chips", Quantity: 2},
36		{Item: "chips", Quantity: 2},
28		{Item: "battered cod", Quantity: 1},
37		{Item: "battered cod", Quantity: 1},
29		{Item: "mushy peas", Quantity: 1},
38		{Item: "mushy peas", Quantity: 1},
30	}
39	}
31	for _, order := range orders {
40	for _, order := range orders {
32		placed, err := queries.PlaceOrder(ctx, order)
41		placed, err := queries.PlaceOrder(ctx, order)
33		if err != nil {
42		if err != nil {
34			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)
35		}
44		}
36		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)
37	}
46	}
38
47
39	// 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.
40	cancelled, err := queries.CancelOrder(ctx, 2)
49	cancelled, err := queries.CancelOrder(ctx, 2)
41	if err != nil {
50	if err != nil {
42		return fmt.Errorf("cancelling order 2: %w", err)
51		return fmt.Errorf("cancelling order 2: %w", err)
43	}
52	}
44	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)
45
54
46	// MariaDB has RETURNING on INSERT, REPLACE and DELETE, but not on UPDATE, so
55	// ServeWaitingOrders is an UPDATE declared as many: one row for every order it changed.
47	// ServeWaitingOrders is execrows and ServedOrders reads the changed rows back.
56	// PostgreSQL does not promise an order for RETURNING rows, so sort them.
48	if _, err := queries.ServeWaitingOrders(ctx); err != nil {
57	served, err := queries.ServeWaitingOrders(ctx)
49		return fmt.Errorf("serving orders: %w", err)
50	}
51	served, err := queries.ServedOrders(ctx)
52	if err != nil {
58	if err != nil {
53		return fmt.Errorf("listing served orders: %w", err)
59		return fmt.Errorf("serving orders: %w", err)
54	}
60	}
61	slices.SortFunc(served, func(a, b store.OrderLine) int { return cmp.Compare(a.ID, b.ID) })
55	for _, order := range served {
62	for _, order := range served {
56		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)
57	}
64	}
58
65
59	// 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
60	// 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.
61	_, err = queries.CancelOrder(ctx, 1)
68	_, err = queries.CancelOrder(ctx, 1)
62	if errors.Is(err, sql.ErrNoRows) {
69	switch {
70	case errors.Is(err, sql.ErrNoRows):
63		fmt.Fprintln(output, "cannot cancel #1: it is no longer waiting")
71		fmt.Fprintln(output, "cannot cancel #1: it is no longer waiting")
64		return nil
72	case err != nil:
73		return fmt.Errorf("cancelling order 1: %w", err)
74	default:
75		return errors.New("cancelled an order that had already been served")
65	}
76	}
77
78	reopened, err := queries.ReopenOrder(ctx, 3)
66	if err != nil {
79	if err != nil {
67		return fmt.Errorf("cancelling order 1: %w", err)
80		return fmt.Errorf("reopening order 3: %w", err)
68	}
81	}
69	return errors.New("cancelled an order that had already been served")
82	fmt.Fprintf(output, "reopened  #%d %-12s x%d, %s\n", reopened.ID, reopened.Item, reopened.Quantity, reopened.Status)
83	return nil
70}
84}

Go type differences

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

Query	Field	MariaDB	PostgreSQL
PlaceOrder	parameter `quantity`	`int32`	`int64`
PlaceOrder	column `price_pence`	`int32`	`int64`
CancelOrder	column `quantity`	`int32`	`int64`