Compare · 011

Optional filters: CockroachDB and SQLite

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

Compare CockroachDB withSQLitePostgreSQLTimescaleDBMySQLMariaDBDuckDBClickHouse

Configuration

sgl.yamldiffers
CockroachDB
SQLite
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: trips
4 - name: trips
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

Migrations

db/migrations/0001_trips.up.sqldiffers
CockroachDB
SQLite
1CREATE TABLE trips (
1CREATE TABLE trips (
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
2 id INTEGER PRIMARY KEY,
3 name TEXT NOT NULL,
3 name TEXT NOT NULL,
4 destination TEXT NOT NULL,
4 destination TEXT NOT NULL,
5 price_pence BIGINT NOT NULL,
5 price_pence INTEGER NOT NULL,
6 dogs_welcome BOOLEAN NOT NULL
6 dogs_welcome BOOLEAN NOT NULL
7);
7);
db/migrations/0001_trips.down.sqlidentical
CockroachDB
SQLite
1DROP TABLE trips;
1DROP TABLE trips;

Queries

AddTripdiffers
CockroachDB · db/queries/trips.sql
SQLite · db/queries/trips.sql
1-- sgl.query(AddTrip, exec)
1-- sgl.query(AddTrip, exec)
2INSERT INTO trips (name, destination, price_pence, dogs_welcome) VALUES ($1, $2, $3, $4);
2INSERT INTO trips (name, destination, price_pence, dogs_welcome) VALUES (?, ?, ?, ?);
SearchTripsdiffers
CockroachDB · db/queries/trips.sql
SQLite · db/queries/trips.sql
1-- sgl.query(SearchTrips, many)
1-- sgl.query(SearchTrips, many)
2-- $1 as sgl.param(destination, optional: true)
2-- ?1 as sgl.param(destination, optional: true)
3-- $2 as sgl.param(max_price, optional: true)
3-- ?2 as sgl.param(max_price, optional: true)
4-- $3 as sgl.param(dogs_welcome, optional: true)
4-- ?3 as sgl.param(dogs_welcome, optional: true)
5SELECT id, name, destination, price_pence, dogs_welcome
5SELECT id, name, destination, price_pence, dogs_welcome
6FROM trips
6FROM trips
7WHERE ($1::text IS NULL OR destination = $1)
7WHERE (?1 IS NULL OR destination = ?1)
8 AND ($2::bigint IS NULL OR price_pence <= $2)
8 AND (?2 IS NULL OR price_pence <= ?2)
9 AND ($3::boolean IS NULL OR dogs_welcome = $3)
9 AND (?3 IS NULL OR dogs_welcome = ?3)
10ORDER BY price_pence, id;
10ORDER BY price_pence, id;

What sgl generates

AddTripidentical
CockroachDB
SQLite
1func (queries *Queries) AddTrip(ctx context.Context, params AddTripParams) error
1func (queries *Queries) AddTrip(ctx context.Context, params AddTripParams) error
2
2
3type AddTripParams struct {
3type AddTripParams struct {
4 Name string
4 Name string
5 Destination string
5 Destination string
6 PricePence int64
6 PricePence int64
7 DogsWelcome bool
7 DogsWelcome bool
8}
8}
SearchTripsidentical
CockroachDB
SQLite
1func (queries *Queries) SearchTrips(ctx context.Context, params SearchTripsParams) ([]SearchTripsRow, error)
1func (queries *Queries) SearchTrips(ctx context.Context, params SearchTripsParams) ([]SearchTripsRow, error)
2
2
3type SearchTripsParams struct {
3type SearchTripsParams struct {
4 Destination *string
4 Destination *string
5 MaxPrice *int64
5 MaxPrice *int64
6 DogsWelcome *bool
6 DogsWelcome *bool
7}
7}
8
8
9type SearchTripsRow struct {
9type SearchTripsRow struct {
10 ID int64 `json:"id"`
10 ID int64 `json:"id"`
11 Name string `json:"name"`
11 Name string `json:"name"`
12 Destination string `json:"destination"`
12 Destination string `json:"destination"`
13 PricePence int64 `json:"price_pence"`
13 PricePence int64 `json:"price_pence"`
14 DogsWelcome bool `json:"dogs_welcome"`
14 DogsWelcome bool `json:"dogs_welcome"`
15}
15}

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 "strings"
8 "strings"
9
9
10 "seagull.sh/seagull/examples/postgres/011_optional_filters/internal/store"
10 "seagull.sh/seagull/examples/sqlite/011_optional_filters/internal/store"
11)
11)
12
12
13// run searches boat trips four ways with one query. Each filter in SearchTrips is written
13// run searches boat trips four ways with one query. Each filter in SearchTrips is written
14// as ($1::text IS NULL OR destination = $1), and its binding says optional: true, which
14// as (?1 IS NULL OR destination = ?1), and its binding says optional: true, which makes
15// makes the Params field a pointer. A nil pointer is sent as NULL, so $1::text IS NULL is
15// the Params field a pointer. A nil pointer is sent as NULL, so ?1 IS NULL is true and
16// true and that filter matches every row; a set pointer applies the filter. The SQL never
16// that filter matches every row; a set pointer applies the filter. The SQL never changes,
17// changes, only the values bound to it.
17// only the values bound to it.
18//
19// PostgreSQL needs the cast: in $1 IS NULL alone the server cannot tell the parameter's
20// type and refuses to prepare the statement. The cast must match the column, so the
21// filters use ::text, ::bigint and ::boolean.
22//
18//
23// Takes database (*sql.DB) which is a migrated database.
19// Takes database (*sql.DB) which is a migrated database.
24// Takes output (io.Writer) which receives each search's trips.
20// Takes output (io.Writer) which receives each search's trips.
25//
21//
26// Returns error when a query fails.
22// Returns error when a query fails.
27func run(ctx context.Context, database *sql.DB, output io.Writer) error {
23func run(ctx context.Context, database *sql.DB, output io.Writer) error {
28 queries := store.New(database)
24 queries := store.New(database)
29
25
30 trips := []store.AddTripParams{
26 trips := []store.AddTripParams{
31 {Name: "Seal spotting", Destination: "Gull Rock", PricePence: 1500, DogsWelcome: true},
27 {Name: "Seal spotting", Destination: "Gull Rock", PricePence: 1500, DogsWelcome: true},
32 {Name: "Puffin cruise", Destination: "Puffin Island", PricePence: 2500, DogsWelcome: false},
28 {Name: "Puffin cruise", Destination: "Puffin Island", PricePence: 2500, DogsWelcome: false},
33 {Name: "Mackerel fishing", Destination: "Gull Rock", PricePence: 2000, DogsWelcome: false},
29 {Name: "Mackerel fishing", Destination: "Gull Rock", PricePence: 2000, DogsWelcome: false},
34 {Name: "Sunset sail", Destination: "the bay", PricePence: 1200, DogsWelcome: true},
30 {Name: "Sunset sail", Destination: "the bay", PricePence: 1200, DogsWelcome: true},
35 }
31 }
36 for _, trip := range trips {
32 for _, trip := range trips {
37 if err := queries.AddTrip(ctx, trip); err != nil {
33 if err := queries.AddTrip(ctx, trip); err != nil {
38 return fmt.Errorf("adding %s: %w", trip.Name, err)
34 return fmt.Errorf("adding %s: %w", trip.Name, err)
39 }
35 }
40 }
36 }
41
37
42 searches := []struct {
38 searches := []struct {
43 label string
39 label string
44 parameters store.SearchTripsParams
40 parameters store.SearchTripsParams
45 }{
41 }{
46 {label: "no filters", parameters: store.SearchTripsParams{}},
42 {label: "no filters", parameters: store.SearchTripsParams{}},
47 {label: "to Gull Rock", parameters: store.SearchTripsParams{Destination: new("Gull Rock")}},
43 {label: "to Gull Rock", parameters: store.SearchTripsParams{Destination: new("Gull Rock")}},
48 {label: "£15 or less", parameters: store.SearchTripsParams{MaxPrice: new(int64(1500))}},
44 {label: "£15 or less", parameters: store.SearchTripsParams{MaxPrice: new(int64(1500))}},
49 {label: "to Gull Rock with a dog", parameters: store.SearchTripsParams{Destination: new("Gull Rock"), DogsWelcome: new(true)}},
45 {label: "to Gull Rock with a dog", parameters: store.SearchTripsParams{Destination: new("Gull Rock"), DogsWelcome: new(true)}},
50 }
46 }
51 for _, search := range searches {
47 for _, search := range searches {
52 found, err := queries.SearchTrips(ctx, search.parameters)
48 found, err := queries.SearchTrips(ctx, search.parameters)
53 if err != nil {
49 if err != nil {
54 return fmt.Errorf("searching %s: %w", search.label, err)
50 return fmt.Errorf("searching %s: %w", search.label, err)
55 }
51 }
56 names := make([]string, 0, len(found))
52 names := make([]string, 0, len(found))
57 for _, trip := range found {
53 for _, trip := range found {
58 names = append(names, trip.Name)
54 names = append(names, trip.Name)
59 }
55 }
60 fmt.Fprintf(output, "%-24s %s\n", search.label+":", strings.Join(names, ", "))
56 fmt.Fprintf(output, "%-24s %s\n", search.label+":", strings.Join(names, ", "))
61 }
57 }
62 return nil
58 return nil
63}
59}