Compare · 011

Optional filters: SQLite and DuckDB

Every file of the SQLite and DuckDB versions side by side, with the lines that differ marked. 3 parameter or column types differ in Go.

Compare SQLite withPostgreSQL CockroachDB TimescaleDB MySQL MariaDB DuckDB ClickHouse

Configuration

sgl.yamldiffers

SQLite
DuckDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: trips
4  - name: trips
5    dialect: sqlite
5    dialect: duckdb
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

SQLite
DuckDB
1CREATE SEQUENCE trip_ids;
2
1CREATE TABLE trips (
3CREATE TABLE trips (
2    id           INTEGER PRIMARY KEY,
4    id           BIGINT  PRIMARY KEY DEFAULT nextval('trip_ids'),
3    name         TEXT    NOT NULL,
5    name         VARCHAR NOT NULL,
4    destination  TEXT    NOT NULL,
6    destination  VARCHAR NOT NULL,
5    price_pence  INTEGER NOT NULL,
7    price_pence  INTEGER NOT NULL,
6    dogs_welcome BOOLEAN NOT NULL
8    dogs_welcome BOOLEAN NOT NULL
7);
9);

db/migrations/0001_trips.down.sqldiffers

SQLite
DuckDB
1DROP TABLE trips;
1DROP TABLE trips;
2DROP SEQUENCE trip_ids;

Queries

AddTripdiffers

SQLite · db/queries/trips.sql
DuckDB · 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
DuckDB · 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

AddTripdiffers

SQLite
DuckDB
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  int32
7	DogsWelcome bool
7	DogsWelcome bool
8}
8}

SearchTripsdiffers

SQLite
DuckDB
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    *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  int64  `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

SQLite
DuckDB
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/duckdb/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(int64(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}

Go type differences

The same SQL types map to different Go types, because each database reports them differently.

Query	Field	SQLite	DuckDB
AddTrip	parameter `price_pence`	`int64`	`int32`
SearchTrips	parameter `max_price`	`*int64`	`*int32`
SearchTrips	column `price_pence`	`int64`	`int32`