Compare · 011

Optional filters: DuckDB and MariaDB

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

Compare DuckDB withSQLitePostgreSQLCockroachDBTimescaleDBMySQLMariaDBClickHouse

Configuration

sgl.yamldiffers
DuckDB
MariaDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: trips
4 - name: trips
5 dialect: duckdb
5 dialect: mysql
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_trips.up.sqldiffers
DuckDB
MariaDB
1CREATE SEQUENCE trip_ids;
2
3CREATE TABLE trips (
1CREATE TABLE trips (
4 id BIGINT PRIMARY KEY DEFAULT nextval('trip_ids'),
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
5 name VARCHAR NOT NULL,
3 name VARCHAR(50) NOT NULL,
6 destination VARCHAR NOT NULL,
4 destination VARCHAR(50) NOT NULL,
7 price_pence INTEGER NOT NULL,
5 price_pence INT NOT NULL,
8 dogs_welcome BOOLEAN NOT NULL
6 dogs_welcome BOOLEAN NOT NULL
9);
7);
db/migrations/0001_trips.down.sqldiffers
DuckDB
MariaDB
1DROP TABLE trips;
1DROP TABLE trips;
2DROP SEQUENCE trip_ids;

Queries

AddTripdiffers
DuckDB · db/queries/trips.sql
MariaDB · 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
DuckDB · db/queries/trips.sql
MariaDB · 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 IS NULL OR destination = ?1)
8 AND ($2 IS NULL OR price_pence <= $2)
8 AND (?2 IS NULL OR price_pence <= ?2)
9 AND ($3 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
DuckDB
MariaDB
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 int32
6 PricePence int32
7 DogsWelcome bool
7 DogsWelcome bool
8}
8}
SearchTripsidentical
DuckDB
MariaDB
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 *int32
5 MaxPrice *int32
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 int32 `json:"price_pence"`
13 PricePence int32 `json:"price_pence"`
14 DogsWelcome bool `json:"dogs_welcome"`
14 DogsWelcome bool `json:"dogs_welcome"`
15}
15}

The program

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