Compare · 011

Optional filters: SQLite and CockroachDB

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

Compare SQLite withPostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDBClickHouse

Configuration

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

Migrations

db/migrations/0001_trips.up.sqldiffers
SQLite
CockroachDB
1CREATE TABLE trips (
1CREATE TABLE trips (
2 id INTEGER PRIMARY KEY,
2 id BIGINT GENERATED ALWAYS AS IDENTITY 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 INTEGER NOT NULL,
5 price_pence BIGINT NOT NULL,
6 dogs_welcome BOOLEAN NOT NULL
6 dogs_welcome BOOLEAN NOT NULL
7);
7);
db/migrations/0001_trips.down.sqlidentical
SQLite
CockroachDB
1DROP TABLE trips;
1DROP TABLE trips;

Queries

AddTripdiffers
SQLite · db/queries/trips.sql
CockroachDB · db/queries/trips.sql
1-- sgl.query(AddTrip, exec)
1-- sgl.query(AddTrip, exec)
2INSERT INTO trips (name, destination, price_pence, dogs_welcome) VALUES (?, ?, ?, ?);
2INSERT INTO trips (name, destination, price_pence, dogs_welcome) VALUES ($1, $2, $3, $4);
SearchTripsdiffers
SQLite · db/queries/trips.sql
CockroachDB · 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 IS NULL OR destination = ?1)
7WHERE ($1::text IS NULL OR destination = $1)
8 AND (?2 IS NULL OR price_pence <= ?2)
8 AND ($2::bigint IS NULL OR price_pence <= $2)
9 AND (?3 IS NULL OR dogs_welcome = ?3)
9 AND ($3::boolean IS NULL OR dogs_welcome = $3)
10ORDER BY price_pence, id;
10ORDER BY price_pence, id;

What sgl generates

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