Compare · 018

Missing rows without errors: SQLite and CockroachDB

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

Compare SQLite withPostgreSQLCockroachDBTimescaleDBDuckDB

Configuration

sgl.yamldiffers
SQLite
CockroachDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: ringing
4 - name: ringing
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_gulls.up.sqldiffers
SQLite
CockroachDB
1CREATE TABLE gulls (
1CREATE TABLE gulls (
2 id INTEGER PRIMARY KEY,
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 ring TEXT NOT NULL UNIQUE,
3 ring TEXT NOT NULL UNIQUE,
4 species TEXT NOT NULL
4 species TEXT NOT NULL
5);
5);
db/migrations/0001_gulls.down.sqlidentical
SQLite
CockroachDB
1DROP TABLE gulls;
1DROP TABLE gulls;

Queries

RingGulldiffers
SQLite · db/queries/gulls.sql
CockroachDB · 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
CockroachDB · 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
CockroachDB
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
CockroachDB
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
CockroachDB
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/postgres/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// PostgreSQL takes the next id from the identity column's sequence before it checks for
22// the conflict, and a skipped insert does not hand it back. The black-headed gull that is
23// turned away and then ringed GB1003 becomes gull 4, not gull 3.
24//
21// Takes database (*sql.DB) which is a migrated database.
25// Takes database (*sql.DB) which is a migrated database.
22// Takes output (io.Writer) which receives each ringing and lookup.
26// Takes output (io.Writer) which receives each ringing and lookup.
23//
27//
24// Returns error when a query fails.
28// Returns error when a query fails.
25func run(ctx context.Context, database *sql.DB, output io.Writer) error {
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
26 queries := store.New(database)
30 queries := store.New(database)
27
31
28 attempts := []store.RingGullParams{
32 attempts := []store.RingGullParams{
29 {Ring: "GB1001", Species: "herring gull"},
33 {Ring: "GB1001", Species: "herring gull"},
30 {Ring: "GB1002", Species: "kittiwake"},
34 {Ring: "GB1002", Species: "kittiwake"},
31 {Ring: "GB1001", Species: "black-headed gull"},
35 {Ring: "GB1001", Species: "black-headed gull"},
36 {Ring: "GB1003", Species: "black-headed gull"},
32 }
37 }
33 for _, attempt := range attempts {
38 for _, attempt := range attempts {
34 ringed, ok, err := queries.RingGull(ctx, attempt)
39 ringed, ok, err := queries.RingGull(ctx, attempt)
35 if err != nil {
40 if err != nil {
36 return fmt.Errorf("ringing %s: %w", attempt.Ring, err)
41 return fmt.Errorf("ringing %s: %w", attempt.Ring, err)
37 }
42 }
38 if !ok {
43 if !ok {
39 fmt.Fprintf(output, "ring %s is already taken, so the %s was not ringed\n", attempt.Ring, attempt.Species)
44 fmt.Fprintf(output, "ring %s is already taken, so the %s was not ringed\n", attempt.Ring, attempt.Species)
40 continue
45 continue
41 }
46 }
42 fmt.Fprintf(output, "ringed a %s as %s, gull %d\n", attempt.Species, ringed.Ring, ringed.ID)
47 fmt.Fprintf(output, "ringed a %s as %s, gull %d\n", attempt.Species, ringed.Ring, ringed.ID)
43 }
48 }
44
49
45 for _, ring := range []string{"GB1002", "GB9999"} {
50 for _, ring := range []string{"GB1002", "GB9999"} {
46 gull, ok, err := queries.FindGull(ctx, ring)
51 gull, ok, err := queries.FindGull(ctx, ring)
47 if err != nil {
52 if err != nil {
48 return fmt.Errorf("finding %s: %w", ring, err)
53 return fmt.Errorf("finding %s: %w", ring, err)
49 }
54 }
50 if !ok {
55 if !ok {
51 fmt.Fprintf(output, "no gull wears ring %s\n", ring)
56 fmt.Fprintf(output, "no gull wears ring %s\n", ring)
52 continue
57 continue
53 }
58 }
54 fmt.Fprintf(output, "ring %s is on gull %d, a %s\n", ring, gull.ID, gull.Species)
59 fmt.Fprintf(output, "ring %s is on gull %d, a %s\n", ring, gull.ID, gull.Species)
55 }
60 }
56 return nil
61 return nil
57}
62}