Compare · 021

Insert or update: SQLite and MySQL

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

Compare SQLite withPostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDB

Configuration

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

Queries

ReceiveDeliverydiffers
SQLite · db/queries/stock.sql
MySQL · db/queries/stock.sql
1-- sgl.query(ReceiveDelivery, one)
1-- sgl.query(ReceiveDelivery, execrows)
2INSERT INTO stock (item, quantity, deliveries) VALUES (?, ?, 1)
2INSERT INTO stock (item, quantity, deliveries) VALUES (?, ?, 1)
3ON CONFLICT (item) DO UPDATE SET
3ON DUPLICATE KEY UPDATE
4 quantity = stock.quantity + excluded.quantity,
4 quantity = quantity + VALUES(quantity),
5 deliveries = stock.deliveries + 1
5 deliveries = deliveries + 1;
6RETURNING item, quantity, deliveries;
AddItemdiffers
SQLite · 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 CONFLICT (item) DO NOTHING;
3ON DUPLICATE KEY UPDATE item = item;
ListStockidentical
SQLite · 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;
GetStockonly MySQL
SQLite · not in this version
MySQL · db/queries/stock.sql
1-- sgl.query(GetStock, one)
2SELECT item, quantity, deliveries FROM stock WHERE item = ?;

What sgl generates

ReceiveDeliverydiffers
SQLite
MySQL
1func (queries *Queries) ReceiveDelivery(ctx context.Context, params ReceiveDeliveryParams) (ReceiveDeliveryRow, 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 int64
5 Quantity int32
6}
7
8type ReceiveDeliveryRow struct {
9 Item string `json:"item"`
10 Quantity int64 `json:"quantity"`
11 Deliveries int64 `json:"deliveries"`
12}
6}
AddItemidentical
SQLite
MySQL
1func (queries *Queries) AddItem(ctx context.Context, item string) (int64, error)
1func (queries *Queries) AddItem(ctx context.Context, item string) (int64, error)
ListStockdiffers
SQLite
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 int64 `json:"quantity"`
5 Quantity int32 `json:"quantity"`
6 Deliveries int64 `json:"deliveries"`
6 Deliveries int32 `json:"deliveries"`
7}
7}
GetStockonly MySQL
SQLite · not in this version
MySQL
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}

The program

example.godiffers
SQLite
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/sqlite/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 // excluded.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.
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(),
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.
25 deliveries := []store.ReceiveDeliveryParams{
31 deliveries := []store.ReceiveDeliveryParams{
26 {Item: "cod", Quantity: 10},
32 {Item: "cod", Quantity: 10},
27 {Item: "potatoes", Quantity: 40},
33 {Item: "potatoes", Quantity: 40},
28 {Item: "cod", Quantity: 5},
34 {Item: "cod", Quantity: 5},
29 }
35 }
30 for _, delivery := range deliveries {
36 for _, delivery := range deliveries {
31 row, err := queries.ReceiveDelivery(ctx, delivery)
37 affected, err := queries.ReceiveDelivery(ctx, delivery)
32 if err != nil {
38 if err != nil {
33 return fmt.Errorf("receiving %s: %w", delivery.Item, err)
39 return fmt.Errorf("receiving %s: %w", delivery.Item, err)
34 }
40 }
35 fmt.Fprintf(output, "%-8s delivery %d: +%d, now %d in stock\n",
41 row, err := queries.GetStock(ctx, delivery.Item)
36 row.Item, row.Deliveries, delivery.Quantity, row.Quantity)
42 if err != nil {
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)
37 }
51 }
38
52
39 // AddItem uses DO NOTHING, and execrows reports whether the row went in: 1 for a new
53 // AddItem sets item = item on a duplicate, which changes nothing, so MySQL counts 0
40 // item, 0 when the item was already there and the insert was skipped.
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
56 // value too long for its column.
41 for _, item := range []string{"peas", "cod"} {
57 for _, item := range []string{"peas", "cod"} {
42 added, err := queries.AddItem(ctx, item)
58 added, err := queries.AddItem(ctx, item)
43 if err != nil {
59 if err != nil {
44 return fmt.Errorf("adding %s: %w", item, err)
60 return fmt.Errorf("adding %s: %w", item, err)
45 }
61 }
46 if added == 0 {
62 if added == 0 {
47 fmt.Fprintf(output, "%s is already stocked, nothing changed\n", item)
63 fmt.Fprintf(output, "%s is already stocked, nothing changed\n", item)
48 continue
64 continue
49 }
65 }
50 fmt.Fprintf(output, "added %s with no stock yet\n", item)
66 fmt.Fprintf(output, "added %s with no stock yet\n", item)
51 }
67 }
52
68
53 stock, err := queries.ListStock(ctx)
69 stock, err := queries.ListStock(ctx)
54 if err != nil {
70 if err != nil {
55 return fmt.Errorf("listing stock: %w", err)
71 return fmt.Errorf("listing stock: %w", err)
56 }
72 }
57 for _, row := range stock {
73 for _, row := range stock {
58 fmt.Fprintf(output, "%-8s %2d\n", row.Item, row.Quantity)
74 fmt.Fprintf(output, "%-8s %2d\n", row.Item, row.Quantity)
59 }
75 }
60 return nil
76 return nil
61}
77}

Go type differences

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

QueryFieldSQLiteMySQL
ReceiveDeliveryparameter quantityint64int32
ListStockcolumn quantityint64int32
ListStockcolumn deliveriesint64int32