Compare · 018

Missing rows without errors: SQLite and DuckDB

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

Compare SQLite withPostgreSQLCockroachDBTimescaleDBDuckDB

Configuration

sgl.yamldiffers
SQLite
DuckDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: ringing
4 - name: colony
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_gulls.up.sqldiffers
SQLite
DuckDB
1CREATE SEQUENCE gull_ids;
2
1CREATE TABLE gulls (
3CREATE TABLE gulls (
2 id INTEGER PRIMARY KEY,
4 id BIGINT PRIMARY KEY DEFAULT nextval('gull_ids'),
3 ring TEXT NOT NULL UNIQUE,
5 ring VARCHAR NOT NULL UNIQUE,
4 species TEXT NOT NULL
6 species VARCHAR NOT NULL
5);
7);
db/migrations/0001_gulls.down.sqldiffers
SQLite
DuckDB
1DROP TABLE gulls;
1DROP TABLE gulls;
2DROP SEQUENCE gull_ids;

Queries

RingGulldiffers
SQLite · db/queries/gulls.sql
DuckDB · db/queries/gulls.sql
1-- sgl.query(RingGull, one, optional: true)
1-- sgl.query(RingGull, one, optional: true)
2INSERT INTO gulls (ring, species) VALUES (?, ?)
2INSERT INTO gulls (ring, species) VALUES ($1, $2)
3ON CONFLICT (ring) DO NOTHING
3ON CONFLICT (ring) DO NOTHING
4RETURNING id, ring;
4RETURNING id, ring;
FindGulldiffers
SQLite · db/queries/gulls.sql
DuckDB · db/queries/gulls.sql
1-- sgl.query(FindGull, one, optional: true)
1-- sgl.query(FindGull, one, optional: true)
2SELECT id, ring, species FROM gulls WHERE ring = ?;
2SELECT id, ring, species FROM gulls WHERE ring = $1;

What sgl generates

RingGullidentical
SQLite
DuckDB
1func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) (RingGullRow, bool, error)
1func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) (RingGullRow, bool, error)
2
2
3type RingGullParams struct {
3type RingGullParams struct {
4 Ring string
4 Ring string
5 Species string
5 Species string
6}
6}
7
7
8type RingGullRow struct {
8type RingGullRow struct {
9 ID int64 `json:"id"`
9 ID int64 `json:"id"`
10 Ring string `json:"ring"`
10 Ring string `json:"ring"`
11}
11}
FindGullidentical
SQLite
DuckDB
1func (queries *Queries) FindGull(ctx context.Context, ring string) (FindGullRow, bool, error)
1func (queries *Queries) FindGull(ctx context.Context, ring string) (FindGullRow, bool, error)
2
2
3type FindGullRow struct {
3type FindGullRow struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 Ring string `json:"ring"`
5 Ring string `json:"ring"`
6 Species string `json:"species"`
6 Species string `json:"species"`
7}
7}

The program

example.godiffers
SQLite
DuckDB
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/018_optional_one/internal/store"
9 "seagull.sh/seagull/examples/duckdb/018_optional_one/internal/store"
10)
10)
11
11
12// run rings gulls and looks them up. Both queries are one queries with optional: true, so
12// run rings gulls and looks them up. Both queries are one queries with optional: true, so
13// they return (row, ok, error): a missing row gives ok false and a nil error instead of
13// they return (row, ok, error): a missing row gives ok false and a nil error instead of
14// sql.ErrNoRows, and error is left for real failures.
14// sql.ErrNoRows, and error is left for real failures.
15//
15//
16// RingGull inserts with ON CONFLICT (ring) DO NOTHING RETURNING. A ring that is already
16// RingGull inserts with ON CONFLICT (ring) DO NOTHING RETURNING. A ring that is already
17// taken skips the insert, so RETURNING has no row and ok is false. Without optional: true
17// taken skips the insert, so RETURNING has no row and ok is false. Without optional: true
18// sgl vet warns about this (Q046), as the call would fail with sql.ErrNoRows. optional
18// sgl vet warns about this (Q046), as the call would fail with sql.ErrNoRows. optional
19// applies only to a one query; on any other command sgl vet refuses it (Q047).
19// applies only to a one query; on any other command sgl vet refuses it (Q047).
20//
20//
21// Takes database (*sql.DB) which is a migrated database.
21// Takes database (*sql.DB) which is a migrated database.
22// Takes output (io.Writer) which receives each ringing and lookup.
22// Takes output (io.Writer) which receives each ringing and lookup.
23//
23//
24// Returns error when a query fails.
24// Returns error when a query fails.
25func run(ctx context.Context, database *sql.DB, output io.Writer) error {
25func run(ctx context.Context, database *sql.DB, output io.Writer) error {
26 queries := store.New(database)
26 queries := store.New(database)
27
27
28 attempts := []store.RingGullParams{
28 attempts := []store.RingGullParams{
29 {Ring: "GB1001", Species: "herring gull"},
29 {Ring: "GB1001", Species: "herring gull"},
30 {Ring: "GB1002", Species: "kittiwake"},
30 {Ring: "GB1002", Species: "kittiwake"},
31 {Ring: "GB1001", Species: "black-headed gull"},
31 {Ring: "GB1001", Species: "black-headed gull"},
32 }
32 }
33 for _, attempt := range attempts {
33 for _, attempt := range attempts {
34 ringed, ok, err := queries.RingGull(ctx, attempt)
34 ringed, ok, err := queries.RingGull(ctx, attempt)
35 if err != nil {
35 if err != nil {
36 return fmt.Errorf("ringing %s: %w", attempt.Ring, err)
36 return fmt.Errorf("ringing %s: %w", attempt.Ring, err)
37 }
37 }
38 if !ok {
38 if !ok {
39 fmt.Fprintf(output, "ring %s is already taken, so the %s was not ringed\n", attempt.Ring, attempt.Species)
39 fmt.Fprintf(output, "ring %s is already taken, so the %s was not ringed\n", attempt.Ring, attempt.Species)
40 continue
40 continue
41 }
41 }
42 fmt.Fprintf(output, "ringed a %s as %s, gull %d\n", attempt.Species, ringed.Ring, ringed.ID)
42 fmt.Fprintf(output, "ringed a %s as %s, gull %d\n", attempt.Species, ringed.Ring, ringed.ID)
43 }
43 }
44
44
45 for _, ring := range []string{"GB1002", "GB9999"} {
45 for _, ring := range []string{"GB1002", "GB9999"} {
46 gull, ok, err := queries.FindGull(ctx, ring)
46 gull, ok, err := queries.FindGull(ctx, ring)
47 if err != nil {
47 if err != nil {
48 return fmt.Errorf("finding %s: %w", ring, err)
48 return fmt.Errorf("finding %s: %w", ring, err)
49 }
49 }
50 if !ok {
50 if !ok {
51 fmt.Fprintf(output, "no gull wears ring %s\n", ring)
51 fmt.Fprintf(output, "no gull wears ring %s\n", ring)
52 continue
52 continue
53 }
53 }
54 fmt.Fprintf(output, "ring %s is on gull %d, a %s\n", ring, gull.ID, gull.Species)
54 fmt.Fprintf(output, "ring %s is on gull %d, a %s\n", ring, gull.ID, gull.Species)
55 }
55 }
56 return nil
56 return nil
57}
57}