Compare · 021

Insert or update: MySQL and CockroachDB

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

Compare MySQL withSQLitePostgreSQLCockroachDBTimescaleDBMariaDBDuckDB

Configuration

sgl.yamldiffers
MySQL
CockroachDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: stock
4 - name: stock
5 dialect: mysql
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_stock.up.sqldiffers
MySQL
CockroachDB
1CREATE TABLE stock (
1CREATE TABLE stock (
2 item VARCHAR(50) NOT NULL PRIMARY KEY,
2 item TEXT PRIMARY KEY,
3 quantity INT NOT NULL,
3 quantity BIGINT NOT NULL,
4 deliveries INT NOT NULL DEFAULT 0
4 deliveries BIGINT NOT NULL DEFAULT 0
5);
5);
db/migrations/0001_stock.down.sqlidentical
MySQL
CockroachDB
1DROP TABLE stock;
1DROP TABLE stock;

Queries

ReceiveDeliverydiffers
MySQL · db/queries/stock.sql
CockroachDB · db/queries/stock.sql
1-- sgl.query(ReceiveDelivery, execrows)
1-- sgl.query(ReceiveDelivery, one)
2INSERT INTO stock (item, quantity, deliveries) VALUES (?, ?, 1)
2INSERT INTO stock (item, quantity, deliveries) VALUES ($1, $2, 1)
3ON DUPLICATE KEY UPDATE
3ON CONFLICT (item) DO UPDATE SET
4 quantity = quantity + VALUES(quantity),
4 quantity = stock.quantity + EXCLUDED.quantity,
5 deliveries = deliveries + 1;
5 deliveries = stock.deliveries + 1
6RETURNING item, quantity, deliveries;
GetStockonly MySQL
MySQL · db/queries/stock.sql
CockroachDB · not in this version
1-- sgl.query(GetStock, one)
2SELECT item, quantity, deliveries FROM stock WHERE item = ?;
AddItemdiffers
MySQL · db/queries/stock.sql
CockroachDB · db/queries/stock.sql
1-- sgl.query(AddItem, execrows)
1-- sgl.query(AddItem, execrows)
2INSERT INTO stock (item, quantity) VALUES (?, 0)
2INSERT INTO stock (item, quantity) VALUES ($1, 0)
3ON DUPLICATE KEY UPDATE item = item;
3ON CONFLICT (item) DO NOTHING;
ListStockidentical
MySQL · db/queries/stock.sql
CockroachDB · db/queries/stock.sql
1-- sgl.query(ListStock, many)
1-- sgl.query(ListStock, many)
2SELECT item, quantity, deliveries FROM stock ORDER BY item;
2SELECT item, quantity, deliveries FROM stock ORDER BY item;

What sgl generates

ReceiveDeliverydiffers
MySQL
CockroachDB
1func (queries *Queries) ReceiveDelivery(ctx context.Context, params ReceiveDeliveryParams) (int64, error)
1func (queries *Queries) ReceiveDelivery(ctx context.Context, params ReceiveDeliveryParams) (ReceiveDeliveryRow, error)
2
2
3type ReceiveDeliveryParams struct {
3type ReceiveDeliveryParams struct {
4 Item string
4 Item string
5 Quantity int32
5 Quantity int64
6}
6}
7
8type ReceiveDeliveryRow struct {
9 Item string `json:"item"`
10 Quantity int64 `json:"quantity"`
11 Deliveries int64 `json:"deliveries"`
12}
GetStockonly MySQL
MySQL
CockroachDB · not in this version
1func (queries *Queries) GetStock(ctx context.Context, item string) (GetStockRow, error)
2
3type GetStockRow struct {
4 Item string `json:"item"`
5 Quantity int32 `json:"quantity"`
6 Deliveries int32 `json:"deliveries"`
7}
AddItemidentical
MySQL
CockroachDB
1func (queries *Queries) AddItem(ctx context.Context, item string) (int64, error)
1func (queries *Queries) AddItem(ctx context.Context, item string) (int64, error)
ListStockdiffers
MySQL
CockroachDB
1func (queries *Queries) ListStock(ctx context.Context) ([]ListStockRow, error)
1func (queries *Queries) ListStock(ctx context.Context) ([]ListStockRow, error)
2
2
3type ListStockRow struct {
3type ListStockRow struct {
4 Item string `json:"item"`
4 Item string `json:"item"`
5 Quantity int32 `json:"quantity"`
5 Quantity int64 `json:"quantity"`
6 Deliveries int32 `json:"deliveries"`
6 Deliveries int64 `json:"deliveries"`
7}
7}

The program

example.godiffers
MySQL
CockroachDB
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/mysql/021_upserts/internal/store"
9 "seagull.sh/seagull/examples/postgres/021_upserts/internal/store"
10)
10)
11
11
12// run books three deliveries into the stock table, two of them for the same item, then
12// run books three deliveries into the stock table, two of them for the same item, then
13// adds two items only if they are new, and prints the stock that results.
13// adds two items only if they are new, and prints the stock that results.
14//
14//
15// Takes database (*sql.DB) which is a migrated database.
15// Takes database (*sql.DB) which is a migrated database.
16// Takes output (io.Writer) which receives each upsert's outcome and the final stock.
16// Takes output (io.Writer) which receives each upsert's outcome and the final stock.
17//
17//
18// Returns error when a query fails.
18// Returns error when a query fails.
19func run(ctx context.Context, database *sql.DB, output io.Writer) error {
19func run(ctx context.Context, database *sql.DB, output io.Writer) error {
20 queries := store.New(database)
20 queries := store.New(database)
21
21
22 // ReceiveDelivery inserts a new item, or adds to the quantity of one already in stock.
22 // ReceiveDelivery inserts a new item, or adds to the quantity of one already in stock.
23 // VALUES(quantity) is the value the INSERT tried to write, so the parameters stay plain
23 // EXCLUDED.quantity is the value the INSERT tried to write, so the parameters stay plain
24 // Item and Quantity fields. MySQL 8.0.19 and later also accept a row alias, VALUES (?,
24 // Item and Quantity fields.
25 // ?, 1) AS new with new.quantity, and deprecate VALUES(); MariaDB accepts only VALUES(),
26 // so the example keeps to it.
27 //
28 // MySQL has no RETURNING, so the method is execrows, and MySQL's count says what
29 // happened: 1 for a row inserted, 2 for a row updated. GetStock then reads the row as it
30 // ended up.
31 deliveries := []store.ReceiveDeliveryParams{
25 deliveries := []store.ReceiveDeliveryParams{
32 {Item: "cod", Quantity: 10},
26 {Item: "cod", Quantity: 10},
33 {Item: "potatoes", Quantity: 40},
27 {Item: "potatoes", Quantity: 40},
34 {Item: "cod", Quantity: 5},
28 {Item: "cod", Quantity: 5},
35 }
29 }
36 for _, delivery := range deliveries {
30 for _, delivery := range deliveries {
37 affected, err := queries.ReceiveDelivery(ctx, delivery)
31 row, err := queries.ReceiveDelivery(ctx, delivery)
38 if err != nil {
32 if err != nil {
39 return fmt.Errorf("receiving %s: %w", delivery.Item, err)
33 return fmt.Errorf("receiving %s: %w", delivery.Item, err)
40 }
34 }
41 row, err := queries.GetStock(ctx, delivery.Item)
35 fmt.Fprintf(output, "%-8s delivery %d: +%d, now %d in stock\n",
42 if err != nil {
36 row.Item, row.Deliveries, delivery.Quantity, row.Quantity)
43 return fmt.Errorf("reading %s: %w", delivery.Item, err)
44 }
45 outcome := "inserted"
46 if affected == 2 {
47 outcome = "updated"
48 }
49 fmt.Fprintf(output, "%-8s delivery %d: +%d, now %d in stock (%s)\n",
50 row.Item, row.Deliveries, delivery.Quantity, row.Quantity, outcome)
51 }
37 }
52
38
53 // AddItem sets item = item on a duplicate, which changes nothing, so MySQL counts 0
39 // AddItem uses DO NOTHING, and execrows reports whether the row went in: 1 for a new
54 // rows: execrows reports 1 for a new item and 0 when the item was already there. It is
40 // item, 0 when the item was already there and the insert was skipped.
55 // MySQL's form of ON CONFLICT DO NOTHING; INSERT IGNORE would also hide errors such as a
56 // value too long for its column.
57 for _, item := range []string{"peas", "cod"} {
41 for _, item := range []string{"peas", "cod"} {
58 added, err := queries.AddItem(ctx, item)
42 added, err := queries.AddItem(ctx, item)
59 if err != nil {
43 if err != nil {
60 return fmt.Errorf("adding %s: %w", item, err)
44 return fmt.Errorf("adding %s: %w", item, err)
61 }
45 }
62 if added == 0 {
46 if added == 0 {
63 fmt.Fprintf(output, "%s is already stocked, nothing changed\n", item)
47 fmt.Fprintf(output, "%s is already stocked, nothing changed\n", item)
64 continue
48 continue
65 }
49 }
66 fmt.Fprintf(output, "added %s with no stock yet\n", item)
50 fmt.Fprintf(output, "added %s with no stock yet\n", item)
67 }
51 }
68
52
69 stock, err := queries.ListStock(ctx)
53 stock, err := queries.ListStock(ctx)
70 if err != nil {
54 if err != nil {
71 return fmt.Errorf("listing stock: %w", err)
55 return fmt.Errorf("listing stock: %w", err)
72 }
56 }
73 for _, row := range stock {
57 for _, row := range stock {
74 fmt.Fprintf(output, "%-8s %2d\n", row.Item, row.Quantity)
58 fmt.Fprintf(output, "%-8s %2d\n", row.Item, row.Quantity)
75 }
59 }
76 return nil
60 return nil
77}
61}

Go type differences

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

QueryFieldMySQLCockroachDB
ReceiveDeliveryparameter quantityint32int64
ListStockcolumn quantityint32int64
ListStockcolumn deliveriesint32int64