Compare · 021

Insert or update: MariaDB and MySQL

MariaDB and MySQL run the same files for this example: the SQL and the Go are identical.

Compare MariaDB withSQLitePostgreSQLCockroachDBTimescaleDBMySQLDuckDB

Configuration

sgl.yamlidentical
MariaDB
MySQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: stock
4 - name: stock
5 dialect: mysql
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

Migrations

db/migrations/0001_stock.up.sqlidentical
MariaDB
MySQL
1CREATE TABLE stock (
1CREATE TABLE stock (
2 item VARCHAR(50) NOT NULL PRIMARY KEY,
2 item VARCHAR(50) NOT NULL PRIMARY KEY,
3 quantity INT NOT NULL,
3 quantity INT NOT NULL,
4 deliveries INT NOT NULL DEFAULT 0
4 deliveries INT NOT NULL DEFAULT 0
5);
5);
db/migrations/0001_stock.down.sqlidentical
MariaDB
MySQL
1DROP TABLE stock;
1DROP TABLE stock;

Queries

ReceiveDeliveryidentical
MariaDB · db/queries/stock.sql
MySQL · db/queries/stock.sql
1-- sgl.query(ReceiveDelivery, execrows)
1-- sgl.query(ReceiveDelivery, execrows)
2INSERT INTO stock (item, quantity, deliveries) VALUES (?, ?, 1)
2INSERT INTO stock (item, quantity, deliveries) VALUES (?, ?, 1)
3ON DUPLICATE KEY UPDATE
3ON DUPLICATE KEY UPDATE
4 quantity = quantity + VALUES(quantity),
4 quantity = quantity + VALUES(quantity),
5 deliveries = deliveries + 1;
5 deliveries = deliveries + 1;
GetStockidentical
MariaDB · db/queries/stock.sql
MySQL · db/queries/stock.sql
1-- sgl.query(GetStock, one)
1-- sgl.query(GetStock, one)
2SELECT item, quantity, deliveries FROM stock WHERE item = ?;
2SELECT item, quantity, deliveries FROM stock WHERE item = ?;
AddItemidentical
MariaDB · db/queries/stock.sql
MySQL · 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 (?, 0)
3ON DUPLICATE KEY UPDATE item = item;
3ON DUPLICATE KEY UPDATE item = item;
ListStockidentical
MariaDB · db/queries/stock.sql
MySQL · 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

ReceiveDeliveryidentical
MariaDB
MySQL
1func (queries *Queries) ReceiveDelivery(ctx context.Context, params ReceiveDeliveryParams) (int64, error)
1func (queries *Queries) ReceiveDelivery(ctx context.Context, params ReceiveDeliveryParams) (int64, error)
2
2
3type ReceiveDeliveryParams struct {
3type ReceiveDeliveryParams struct {
4 Item string
4 Item string
5 Quantity int32
5 Quantity int32
6}
6}
GetStockidentical
MariaDB
MySQL
1func (queries *Queries) GetStock(ctx context.Context, item string) (GetStockRow, error)
1func (queries *Queries) GetStock(ctx context.Context, item string) (GetStockRow, error)
2
2
3type GetStockRow struct {
3type GetStockRow struct {
4 Item string `json:"item"`
4 Item string `json:"item"`
5 Quantity int32 `json:"quantity"`
5 Quantity int32 `json:"quantity"`
6 Deliveries int32 `json:"deliveries"`
6 Deliveries int32 `json:"deliveries"`
7}
7}
AddItemidentical
MariaDB
MySQL
1func (queries *Queries) AddItem(ctx context.Context, item string) (int64, error)
1func (queries *Queries) AddItem(ctx context.Context, item string) (int64, error)
ListStockidentical
MariaDB
MySQL
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 int32 `json:"quantity"`
6 Deliveries int32 `json:"deliveries"`
6 Deliveries int32 `json:"deliveries"`
7}
7}

The program

example.goidentical
MariaDB
MySQL
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/mysql/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 // VALUES(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. MySQL 8.0.19 and later also accept a row alias, VALUES (?,
25 // ?, 1) AS new with new.quantity, and deprecate VALUES(); MariaDB accepts only VALUES(),
25 // ?, 1) AS new with new.quantity, and deprecate VALUES(); MariaDB accepts only VALUES(),
26 // so the example keeps to it.
26 // so the example keeps to it.
27 //
27 //
28 // MySQL has no RETURNING, so the method is execrows, and MySQL's count says what
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
29 // happened: 1 for a row inserted, 2 for a row updated. GetStock then reads the row as it
30 // ended up.
30 // ended up.
31 deliveries := []store.ReceiveDeliveryParams{
31 deliveries := []store.ReceiveDeliveryParams{
32 {Item: "cod", Quantity: 10},
32 {Item: "cod", Quantity: 10},
33 {Item: "potatoes", Quantity: 40},
33 {Item: "potatoes", Quantity: 40},
34 {Item: "cod", Quantity: 5},
34 {Item: "cod", Quantity: 5},
35 }
35 }
36 for _, delivery := range deliveries {
36 for _, delivery := range deliveries {
37 affected, err := queries.ReceiveDelivery(ctx, delivery)
37 affected, err := queries.ReceiveDelivery(ctx, delivery)
38 if err != nil {
38 if err != nil {
39 return fmt.Errorf("receiving %s: %w", delivery.Item, err)
39 return fmt.Errorf("receiving %s: %w", delivery.Item, err)
40 }
40 }
41 row, err := queries.GetStock(ctx, delivery.Item)
41 row, err := queries.GetStock(ctx, delivery.Item)
42 if err != nil {
42 if err != nil {
43 return fmt.Errorf("reading %s: %w", delivery.Item, err)
43 return fmt.Errorf("reading %s: %w", delivery.Item, err)
44 }
44 }
45 outcome := "inserted"
45 outcome := "inserted"
46 if affected == 2 {
46 if affected == 2 {
47 outcome = "updated"
47 outcome = "updated"
48 }
48 }
49 fmt.Fprintf(output, "%-8s delivery %d: +%d, now %d in stock (%s)\n",
49 fmt.Fprintf(output, "%-8s delivery %d: +%d, now %d in stock (%s)\n",
50 row.Item, row.Deliveries, delivery.Quantity, row.Quantity, outcome)
50 row.Item, row.Deliveries, delivery.Quantity, row.Quantity, outcome)
51 }
51 }
52
52
53 // AddItem sets item = item on a duplicate, which changes nothing, so MySQL counts 0
53 // AddItem sets item = item on a duplicate, which changes nothing, so MySQL counts 0
54 // rows: execrows reports 1 for a new item and 0 when the item was already there. It is
54 // rows: execrows reports 1 for a new item and 0 when the item was already there. It is
55 // MySQL's form of ON CONFLICT DO NOTHING; INSERT IGNORE would also hide errors such as a
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.
56 // value too long for its column.
57 for _, item := range []string{"peas", "cod"} {
57 for _, item := range []string{"peas", "cod"} {
58 added, err := queries.AddItem(ctx, item)
58 added, err := queries.AddItem(ctx, item)
59 if err != nil {
59 if err != nil {
60 return fmt.Errorf("adding %s: %w", item, err)
60 return fmt.Errorf("adding %s: %w", item, err)
61 }
61 }
62 if added == 0 {
62 if added == 0 {
63 fmt.Fprintf(output, "%s is already stocked, nothing changed\n", item)
63 fmt.Fprintf(output, "%s is already stocked, nothing changed\n", item)
64 continue
64 continue
65 }
65 }
66 fmt.Fprintf(output, "added %s with no stock yet\n", item)
66 fmt.Fprintf(output, "added %s with no stock yet\n", item)
67 }
67 }
68
68
69 stock, err := queries.ListStock(ctx)
69 stock, err := queries.ListStock(ctx)
70 if err != nil {
70 if err != nil {
71 return fmt.Errorf("listing stock: %w", err)
71 return fmt.Errorf("listing stock: %w", err)
72 }
72 }
73 for _, row := range stock {
73 for _, row := range stock {
74 fmt.Fprintf(output, "%-8s %2d\n", row.Item, row.Quantity)
74 fmt.Fprintf(output, "%-8s %2d\n", row.Item, row.Quantity)
75 }
75 }
76 return nil
76 return nil
77}
77}