Compare · 021

Insert or update: SQLite and TimescaleDB

Every file of the SQLite and TimescaleDB versions side by side, with the lines that differ marked.

Compare SQLite withPostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDB

Configuration

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

Queries

ReceiveDeliverydiffers
SQLite · db/queries/stock.sql
TimescaleDB · db/queries/stock.sql
1-- sgl.query(ReceiveDelivery, one)
1-- sgl.query(ReceiveDelivery, one)
2INSERT INTO stock (item, quantity, deliveries) VALUES (?, ?, 1)
2INSERT INTO stock (item, quantity, deliveries) VALUES ($1, $2, 1)
3ON CONFLICT (item) DO UPDATE SET
3ON CONFLICT (item) DO UPDATE SET
4 quantity = stock.quantity + excluded.quantity,
4 quantity = stock.quantity + EXCLUDED.quantity,
5 deliveries = stock.deliveries + 1
5 deliveries = stock.deliveries + 1
6RETURNING item, quantity, deliveries;
6RETURNING item, quantity, deliveries;
AddItemdiffers
SQLite · db/queries/stock.sql
TimescaleDB · 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 CONFLICT (item) DO NOTHING;
3ON CONFLICT (item) DO NOTHING;
ListStockidentical
SQLite · db/queries/stock.sql
TimescaleDB · 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
SQLite
TimescaleDB
1func (queries *Queries) ReceiveDelivery(ctx context.Context, params ReceiveDeliveryParams) (ReceiveDeliveryRow, 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 int64
5 Quantity int64
6}
6}
7
7
8type ReceiveDeliveryRow struct {
8type ReceiveDeliveryRow struct {
9 Item string `json:"item"`
9 Item string `json:"item"`
10 Quantity int64 `json:"quantity"`
10 Quantity int64 `json:"quantity"`
11 Deliveries int64 `json:"deliveries"`
11 Deliveries int64 `json:"deliveries"`
12}
12}
AddItemidentical
SQLite
TimescaleDB
1func (queries *Queries) AddItem(ctx context.Context, item string) (int64, error)
1func (queries *Queries) AddItem(ctx context.Context, item string) (int64, error)
ListStockidentical
SQLite
TimescaleDB
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 int64 `json:"quantity"`
6 Deliveries int64 `json:"deliveries"`
6 Deliveries int64 `json:"deliveries"`
7}
7}

The program

example.godiffers
SQLite
TimescaleDB
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/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 // excluded.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.
24 // Item and Quantity fields.
25 deliveries := []store.ReceiveDeliveryParams{
25 deliveries := []store.ReceiveDeliveryParams{
26 {Item: "cod", Quantity: 10},
26 {Item: "cod", Quantity: 10},
27 {Item: "potatoes", Quantity: 40},
27 {Item: "potatoes", Quantity: 40},
28 {Item: "cod", Quantity: 5},
28 {Item: "cod", Quantity: 5},
29 }
29 }
30 for _, delivery := range deliveries {
30 for _, delivery := range deliveries {
31 row, err := queries.ReceiveDelivery(ctx, delivery)
31 row, err := queries.ReceiveDelivery(ctx, delivery)
32 if err != nil {
32 if err != nil {
33 return fmt.Errorf("receiving %s: %w", delivery.Item, err)
33 return fmt.Errorf("receiving %s: %w", delivery.Item, err)
34 }
34 }
35 fmt.Fprintf(output, "%-8s delivery %d: +%d, now %d in stock\n",
35 fmt.Fprintf(output, "%-8s delivery %d: +%d, now %d in stock\n",
36 row.Item, row.Deliveries, delivery.Quantity, row.Quantity)
36 row.Item, row.Deliveries, delivery.Quantity, row.Quantity)
37 }
37 }
38
38
39 // AddItem uses DO NOTHING, and execrows reports whether the row went in: 1 for a new
39 // AddItem uses DO NOTHING, and execrows reports whether the row went in: 1 for a new
40 // item, 0 when the item was already there and the insert was skipped.
40 // item, 0 when the item was already there and the insert was skipped.
41 for _, item := range []string{"peas", "cod"} {
41 for _, item := range []string{"peas", "cod"} {
42 added, err := queries.AddItem(ctx, item)
42 added, err := queries.AddItem(ctx, item)
43 if err != nil {
43 if err != nil {
44 return fmt.Errorf("adding %s: %w", item, err)
44 return fmt.Errorf("adding %s: %w", item, err)
45 }
45 }
46 if added == 0 {
46 if added == 0 {
47 fmt.Fprintf(output, "%s is already stocked, nothing changed\n", item)
47 fmt.Fprintf(output, "%s is already stocked, nothing changed\n", item)
48 continue
48 continue
49 }
49 }
50 fmt.Fprintf(output, "added %s with no stock yet\n", item)
50 fmt.Fprintf(output, "added %s with no stock yet\n", item)
51 }
51 }
52
52
53 stock, err := queries.ListStock(ctx)
53 stock, err := queries.ListStock(ctx)
54 if err != nil {
54 if err != nil {
55 return fmt.Errorf("listing stock: %w", err)
55 return fmt.Errorf("listing stock: %w", err)
56 }
56 }
57 for _, row := range stock {
57 for _, row := range stock {
58 fmt.Fprintf(output, "%-8s %2d\n", row.Item, row.Quantity)
58 fmt.Fprintf(output, "%-8s %2d\n", row.Item, row.Quantity)
59 }
59 }
60 return nil
60 return nil
61}
61}