Compare · 011

Optional filters: MySQL and TimescaleDB

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

Compare MySQL withSQLite PostgreSQL CockroachDB TimescaleDB MariaDB DuckDB ClickHouse

Configuration

sgl.yamldiffers

MySQL
TimescaleDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: trips
4  - name: trips
5    dialect: mysql
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

MySQL
TimescaleDB
1CREATE TABLE trips (
1CREATE TABLE trips (
2    id           BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
2    id           BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3    name         VARCHAR(50) NOT NULL,
3    name         TEXT    NOT NULL,
4    destination  VARCHAR(50) NOT NULL,
4    destination  TEXT    NOT NULL,
5    price_pence  INT         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

MySQL
TimescaleDB
1DROP TABLE trips;
1DROP TABLE trips;

Queries

AddTripdiffers

MySQL · db/queries/trips.sql
TimescaleDB · 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

MySQL · db/queries/trips.sql
TimescaleDB · 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

AddTripdiffers

MySQL
TimescaleDB
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  int64
7	DogsWelcome bool
7	DogsWelcome bool
8}
8}

SearchTripsdiffers

MySQL
TimescaleDB
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    *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  int32  `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

MySQL
TimescaleDB
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/mysql/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(int32(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}

Go type differences

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

Query	Field	MySQL	TimescaleDB
AddTrip	parameter `price_pence`	`int32`	`int64`
SearchTrips	parameter `max_price`	`*int32`	`*int64`
SearchTrips	column `price_pence`	`int32`	`int64`