Compare · 021

Insert or update: PostgreSQL and SQLite

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

Compare PostgreSQL withSQLite CockroachDB TimescaleDB MySQL MariaDB DuckDB

Configuration

sgl.yamldiffers

PostgreSQL
SQLite
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: stock
4  - name: stock
5    dialect: postgres
5    dialect: sqlite
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

PostgreSQL
SQLite
1CREATE TABLE stock (
1CREATE TABLE stock (
2    item       TEXT   PRIMARY KEY,
2    item       TEXT    NOT NULL PRIMARY KEY,
3    quantity   BIGINT NOT NULL,
3    quantity   INTEGER NOT NULL,
4    deliveries BIGINT NOT NULL DEFAULT 0
4    deliveries INTEGER NOT NULL DEFAULT 0
5);
5);

db/migrations/0001_stock.down.sqlidentical

PostgreSQL
SQLite
1DROP TABLE stock;
1DROP TABLE stock;

Queries

ReceiveDeliverydiffers

PostgreSQL · db/queries/stock.sql
SQLite · db/queries/stock.sql
1-- sgl.query(ReceiveDelivery, one)
1-- sgl.query(ReceiveDelivery, one)
2INSERT INTO stock (item, quantity, deliveries) VALUES ($1, $2, 1)
2INSERT INTO stock (item, quantity, deliveries) VALUES (?, ?, 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

PostgreSQL · db/queries/stock.sql
SQLite · db/queries/stock.sql
1-- sgl.query(AddItem, execrows)
1-- sgl.query(AddItem, execrows)
2INSERT INTO stock (item, quantity) VALUES ($1, 0)
2INSERT INTO stock (item, quantity) VALUES (?, 0)
3ON CONFLICT (item) DO NOTHING;
3ON CONFLICT (item) DO NOTHING;

ListStockidentical

PostgreSQL · db/queries/stock.sql
SQLite · 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

PostgreSQL
SQLite
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

PostgreSQL
SQLite
1func (queries *Queries) AddItem(ctx context.Context, item string) (int64, error)
1func (queries *Queries) AddItem(ctx context.Context, item string) (int64, error)

ListStockidentical

PostgreSQL
SQLite
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

PostgreSQL
SQLite
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/postgres/021_upserts/internal/store"
9	"seagull.sh/seagull/examples/sqlite/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}