Compare · 020

Rows back from INSERT, UPDATE and DELETE: SQLite and DuckDB

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

Compare SQLite withPostgreSQLCockroachDBTimescaleDBMariaDBDuckDB

Configuration

sgl.yamldiffers
SQLite
DuckDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: orders
4 - name: orders
5 dialect: sqlite
5 dialect: duckdb
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_orders.up.sqldiffers
SQLite
DuckDB
1CREATE SEQUENCE order_ids;
2
1CREATE TABLE orders (
3CREATE TABLE orders (
2 id INTEGER PRIMARY KEY,
4 id BIGINT PRIMARY KEY DEFAULT nextval('order_ids'),
3 item TEXT NOT NULL,
5 item VARCHAR NOT NULL,
4 quantity INTEGER NOT NULL DEFAULT 1,
6 quantity INTEGER NOT NULL DEFAULT 1,
5 status TEXT NOT NULL DEFAULT 'waiting'
7 status VARCHAR NOT NULL DEFAULT 'waiting'
6);
8);
db/migrations/0001_orders.down.sqldiffers
SQLite
DuckDB
1DROP TABLE orders;
1DROP TABLE orders;
2DROP SEQUENCE order_ids;

Queries

PlaceOrderdiffers
SQLite · db/queries/orders.sql
DuckDB · db/queries/orders.sql
1-- sgl.query(PlaceOrder, one)
1-- sgl.query(PlaceOrder, one)
2INSERT INTO orders (item, quantity) VALUES (?, ?)
2INSERT INTO orders (item, quantity) VALUES ($1, $2)
3RETURNING id, status, quantity * 250 AS price_pence;
3RETURNING id, status, quantity * 250 AS price_pence;
ServeWaitingOrdersidentical
SQLite · db/queries/orders.sql
DuckDB · db/queries/orders.sql
1-- sgl.query(ServeWaitingOrders, many)
1-- sgl.query(ServeWaitingOrders, many)
2UPDATE orders SET status = 'served' WHERE status = 'waiting'
2UPDATE orders SET status = 'served' WHERE status = 'waiting'
3RETURNING id, item, quantity;
3RETURNING id, item, quantity;
CancelOrderdiffers
SQLite · db/queries/orders.sql
DuckDB · db/queries/orders.sql
1-- sgl.query(CancelOrder, one)
1-- sgl.query(CancelOrder, one)
2DELETE FROM orders WHERE id = ? AND status = 'waiting'
2DELETE FROM orders WHERE id = $1 AND status = 'waiting'
3RETURNING item, quantity;
3RETURNING item, quantity;

What sgl generates

PlaceOrderdiffers
SQLite
DuckDB
1func (queries *Queries) PlaceOrder(ctx context.Context, params PlaceOrderParams) (PlaceOrderRow, error)
1func (queries *Queries) PlaceOrder(ctx context.Context, params PlaceOrderParams) (PlaceOrderRow, error)
2
2
3type PlaceOrderParams struct {
3type PlaceOrderParams struct {
4 Item string
4 Item string
5 Quantity int64
5 Quantity int32
6}
6}
7
7
8type PlaceOrderRow struct {
8type PlaceOrderRow struct {
9 ID int64 `json:"id"`
9 ID int64 `json:"id"`
10 Status string `json:"status"`
10 Status string `json:"status"`
11 PricePence int64 `json:"price_pence"`
11 PricePence int32 `json:"price_pence"`
12}
12}
ServeWaitingOrdersdiffers
SQLite
DuckDB
1func (queries *Queries) ServeWaitingOrders(ctx context.Context) ([]ServeWaitingOrdersRow, error)
1func (queries *Queries) ServeWaitingOrders(ctx context.Context) ([]ServeWaitingOrdersRow, error)
2
2
3type ServeWaitingOrdersRow struct {
3type ServeWaitingOrdersRow struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 Item string `json:"item"`
5 Item string `json:"item"`
6 Quantity int64 `json:"quantity"`
6 Quantity int32 `json:"quantity"`
7}
7}
CancelOrderdiffers
SQLite
DuckDB
1func (queries *Queries) CancelOrder(ctx context.Context, id int64) (CancelOrderRow, error)
1func (queries *Queries) CancelOrder(ctx context.Context, id int64) (CancelOrderRow, error)
2
2
3type CancelOrderRow struct {
3type CancelOrderRow struct {
4 Item string `json:"item"`
4 Item string `json:"item"`
5 Quantity int64 `json:"quantity"`
5 Quantity int32 `json:"quantity"`
6}
6}

The program

example.godiffers
SQLite
DuckDB
1package main
1package main
2
2
3import (
3import (
4 "cmp"
4 "cmp"
5 "context"
5 "context"
6 "database/sql"
6 "database/sql"
7 "errors"
7 "errors"
8 "fmt"
8 "fmt"
9 "io"
9 "io"
10 "slices"
10 "slices"
11
11
12 "seagull.sh/seagull/examples/sqlite/020_returning/internal/store"
12 "seagull.sh/seagull/examples/duckdb/020_returning/internal/store"
13)
13)
14
14
15// run places three orders, cancels one, serves the rest and then tries to cancel an order
15// run places three orders, cancels one, serves the rest and then tries to cancel an order
16// that has already been served. Every write uses RETURNING, so the rows it touched come
16// that has already been served. Every write uses RETURNING, so the rows it touched come
17// back from the same statement.
17// back from the same statement.
18//
18//
19// Takes database (*sql.DB) which is a migrated database.
19// Takes database (*sql.DB) which is a migrated database.
20// Takes output (io.Writer) which receives what each write returned.
20// Takes output (io.Writer) which receives what each write returned.
21//
21//
22// Returns error when a query fails.
22// Returns error when a query fails.
23func run(ctx context.Context, database *sql.DB, output io.Writer) error {
23func run(ctx context.Context, database *sql.DB, output io.Writer) error {
24 queries := store.New(database)
24 queries := store.New(database)
25
25
26 // PlaceOrder is an INSERT declared as one: the id SQLite assigned, the default status
26 // PlaceOrder is an INSERT declared as one: the id the sequence assigned, the default
27 // and a computed price all come back in a PlaceOrderRow.
27 // status and a computed price all come back in a PlaceOrderRow.
28 orders := []store.PlaceOrderParams{
28 orders := []store.PlaceOrderParams{
29 {Item: "chips", Quantity: 2},
29 {Item: "chips", Quantity: 2},
30 {Item: "battered cod", Quantity: 1},
30 {Item: "battered cod", Quantity: 1},
31 {Item: "mushy peas", Quantity: 1},
31 {Item: "mushy peas", Quantity: 1},
32 }
32 }
33 for _, order := range orders {
33 for _, order := range orders {
34 placed, err := queries.PlaceOrder(ctx, order)
34 placed, err := queries.PlaceOrder(ctx, order)
35 if err != nil {
35 if err != nil {
36 return fmt.Errorf("placing an order for %s: %w", order.Item, err)
36 return fmt.Errorf("placing an order for %s: %w", order.Item, err)
37 }
37 }
38 fmt.Fprintf(output, "placed #%d %-12s %s, %dp\n", placed.ID, order.Item, placed.Status, placed.PricePence)
38 fmt.Fprintf(output, "placed #%d %-12s %s, %dp\n", placed.ID, order.Item, placed.Status, placed.PricePence)
39 }
39 }
40
40
41 // CancelOrder is a DELETE declared as one, so it returns the row it removed.
41 // CancelOrder is a DELETE declared as one, so it returns the row it removed.
42 cancelled, err := queries.CancelOrder(ctx, 2)
42 cancelled, err := queries.CancelOrder(ctx, 2)
43 if err != nil {
43 if err != nil {
44 return fmt.Errorf("cancelling order 2: %w", err)
44 return fmt.Errorf("cancelling order 2: %w", err)
45 }
45 }
46 fmt.Fprintf(output, "cancelled #2 %-12s x%d\n", cancelled.Item, cancelled.Quantity)
46 fmt.Fprintf(output, "cancelled #2 %-12s x%d\n", cancelled.Item, cancelled.Quantity)
47
47
48 // ServeWaitingOrders is an UPDATE declared as many: one row for every order it changed.
48 // ServeWaitingOrders is an UPDATE declared as many: one row for every order it changed.
49 // SQLite does not promise an order for RETURNING rows, so sort them.
49 // DuckDB does not promise an order for RETURNING rows, so sort them.
50 served, err := queries.ServeWaitingOrders(ctx)
50 served, err := queries.ServeWaitingOrders(ctx)
51 if err != nil {
51 if err != nil {
52 return fmt.Errorf("serving orders: %w", err)
52 return fmt.Errorf("serving orders: %w", err)
53 }
53 }
54 slices.SortFunc(served, func(a, b store.ServeWaitingOrdersRow) int { return cmp.Compare(a.ID, b.ID) })
54 slices.SortFunc(served, func(a, b store.ServeWaitingOrdersRow) int { return cmp.Compare(a.ID, b.ID) })
55 for _, order := range served {
55 for _, order := range served {
56 fmt.Fprintf(output, "served #%d %-12s x%d\n", order.ID, order.Item, order.Quantity)
56 fmt.Fprintf(output, "served #%d %-12s x%d\n", order.ID, order.Item, order.Quantity)
57 }
57 }
58
58
59 // A one query that matches no row returns sql.ErrNoRows, writes included: order 1 has
59 // A one query that matches no row returns sql.ErrNoRows, writes included: order 1 has
60 // been served, so the DELETE's WHERE clause no longer matches it.
60 // been served, so the DELETE's WHERE clause no longer matches it.
61 _, err = queries.CancelOrder(ctx, 1)
61 _, err = queries.CancelOrder(ctx, 1)
62 if errors.Is(err, sql.ErrNoRows) {
62 if errors.Is(err, sql.ErrNoRows) {
63 fmt.Fprintln(output, "cannot cancel #1: it is no longer waiting")
63 fmt.Fprintln(output, "cannot cancel #1: it is no longer waiting")
64 return nil
64 return nil
65 }
65 }
66 if err != nil {
66 if err != nil {
67 return fmt.Errorf("cancelling order 1: %w", err)
67 return fmt.Errorf("cancelling order 1: %w", err)
68 }
68 }
69 return errors.New("cancelled an order that had already been served")
69 return errors.New("cancelled an order that had already been served")
70}
70}

Go type differences

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

QueryFieldSQLiteDuckDB
PlaceOrderparameter quantityint64int32
PlaceOrdercolumn price_penceint64int32
ServeWaitingOrderscolumn quantityint64int32
CancelOrdercolumn quantityint64int32