Compare · 018

Missing rows without errors: CockroachDB and SQLite

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

Compare CockroachDB withSQLitePostgreSQLTimescaleDBDuckDB

Configuration

sgl.yamldiffers
CockroachDB
SQLite
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: ringing
4 - name: ringing
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_gulls.up.sqldiffers
CockroachDB
SQLite
1CREATE TABLE gulls (
1CREATE TABLE gulls (
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
2 id INTEGER 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
CockroachDB
SQLite
1DROP TABLE gulls;
1DROP TABLE gulls;

Queries

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

What sgl generates

RingGullidentical
CockroachDB
SQLite
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
CockroachDB
SQLite
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
CockroachDB
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/018_optional_one/internal/store"
9 "seagull.sh/seagull/examples/sqlite/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//
25// Takes database (*sql.DB) which is a migrated database.
21// Takes database (*sql.DB) which is a migrated database.
26// Takes output (io.Writer) which receives each ringing and lookup.
22// Takes output (io.Writer) which receives each ringing and lookup.
27//
23//
28// Returns error when a query fails.
24// Returns error when a query fails.
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
25func run(ctx context.Context, database *sql.DB, output io.Writer) error {
30 queries := store.New(database)
26 queries := store.New(database)
31
27
32 attempts := []store.RingGullParams{
28 attempts := []store.RingGullParams{
33 {Ring: "GB1001", Species: "herring gull"},
29 {Ring: "GB1001", Species: "herring gull"},
34 {Ring: "GB1002", Species: "kittiwake"},
30 {Ring: "GB1002", Species: "kittiwake"},
35 {Ring: "GB1001", Species: "black-headed gull"},
31 {Ring: "GB1001", Species: "black-headed gull"},
36 {Ring: "GB1003", Species: "black-headed gull"},
37 }
32 }
38 for _, attempt := range attempts {
33 for _, attempt := range attempts {
39 ringed, ok, err := queries.RingGull(ctx, attempt)
34 ringed, ok, err := queries.RingGull(ctx, attempt)
40 if err != nil {
35 if err != nil {
41 return fmt.Errorf("ringing %s: %w", attempt.Ring, err)
36 return fmt.Errorf("ringing %s: %w", attempt.Ring, err)
42 }
37 }
43 if !ok {
38 if !ok {
44 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)
45 continue
40 continue
46 }
41 }
47 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)
48 }
43 }
49
44
50 for _, ring := range []string{"GB1002", "GB9999"} {
45 for _, ring := range []string{"GB1002", "GB9999"} {
51 gull, ok, err := queries.FindGull(ctx, ring)
46 gull, ok, err := queries.FindGull(ctx, ring)
52 if err != nil {
47 if err != nil {
53 return fmt.Errorf("finding %s: %w", ring, err)
48 return fmt.Errorf("finding %s: %w", ring, err)
54 }
49 }
55 if !ok {
50 if !ok {
56 fmt.Fprintf(output, "no gull wears ring %s\n", ring)
51 fmt.Fprintf(output, "no gull wears ring %s\n", ring)
57 continue
52 continue
58 }
53 }
59 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)
60 }
55 }
61 return nil
56 return nil
62}
57}