Compare · 011

Optional filters: ClickHouse and SQLite

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

Compare ClickHouse withSQLite PostgreSQL CockroachDB TimescaleDB MySQL MariaDB DuckDB

Configuration

sgl.yamldiffers

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

Migrations

db/migrations/0001_trips.up.sqldiffers

ClickHouse
SQLite
1CREATE TABLE trips (
1CREATE TABLE trips (
2    id           UInt64,
2    id           INTEGER PRIMARY KEY,
3    name         String,
3    name         TEXT    NOT NULL,
4    destination  String,
4    destination  TEXT    NOT NULL,
5    price_pence  UInt32,
5    price_pence  INTEGER NOT NULL,
6    dogs_welcome Bool
6    dogs_welcome BOOLEAN NOT NULL
7) ENGINE = MergeTree() ORDER BY id;
7);

db/migrations/0001_trips.down.sqlidentical

ClickHouse
SQLite
1DROP TABLE trips;
1DROP TABLE trips;

Queries

AddTripdiffers

ClickHouse · db/queries/trips.sql
SQLite · db/queries/trips.sql
1-- sgl.query(AddTrip, exec)
1-- sgl.query(AddTrip, exec)
2INSERT INTO trips (id, name, destination, price_pence, dogs_welcome)
2INSERT INTO trips (name, destination, price_pence, dogs_welcome) VALUES (?, ?, ?, ?);
3VALUES ({id:UInt64}, {name:String}, {destination:String}, {price_pence:UInt32}, {dogs_welcome:Bool});

SearchTripsdiffers

ClickHouse · db/queries/trips.sql
SQLite · db/queries/trips.sql
1-- sgl.query(SearchTrips, many)
1-- sgl.query(SearchTrips, many)
2-- {destination:Nullable(String)} as sgl.param(destination, optional: true)
2-- ?1 as sgl.param(destination, optional: true)
3-- {max_price:Nullable(UInt32)} as sgl.param(max_price, optional: true)
3-- ?2 as sgl.param(max_price, optional: true)
4-- {dogs_welcome:Nullable(Bool)} 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 ({destination:Nullable(String)} IS NULL OR destination = {destination:Nullable(String)})
7WHERE (?1 IS NULL OR destination = ?1)
8  AND ({max_price:Nullable(UInt32)} IS NULL OR price_pence <= {max_price:Nullable(UInt32)})
8  AND (?2 IS NULL OR price_pence <= ?2)
9  AND ({dogs_welcome:Nullable(Bool)} IS NULL OR dogs_welcome = {dogs_welcome:Nullable(Bool)})
9  AND (?3 IS NULL OR dogs_welcome = ?3)
10ORDER BY price_pence, id;
10ORDER BY price_pence, id;

What sgl generates

AddTripdiffers

ClickHouse
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	ID          uint64
5	Name        string
4	Name        string
6	Destination string
5	Destination string
7	PricePence  uint32
6	PricePence  int64
8	DogsWelcome bool
7	DogsWelcome bool
9}
8}

SearchTripsdiffers

ClickHouse
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    *uint32
5	MaxPrice    *int64
6	DogsWelcome *bool
6	DogsWelcome *bool
7}
7}
8
8
9type SearchTripsRow struct {
9type SearchTripsRow struct {
10	ID          uint64 `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  uint32 `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

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

Go type differences

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

Query	Field	ClickHouse	SQLite
AddTrip	parameter `price_pence`	`uint32`	`int64`
SearchTrips	parameter `max_price`	`*uint32`	`*int64`
SearchTrips	column `id`	`uint64`	`int64`
SearchTrips	column `price_pence`	`uint32`	`int64`