Compare · 014

The runtime query builder: CockroachDB and PostgreSQL

CockroachDB and PostgreSQL run the same files for this example: the SQL and the Go are identical.

Compare CockroachDB withSQLitePostgreSQLTimescaleDBMySQLMariaDBDuckDBClickHouse

Configuration

sgl.yamlidentical
CockroachDB
PostgreSQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: pier
4 - name: pier
5 dialect: postgres
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
11 driver: database/sql

Migrations

db/migrations/0001_lost_property.up.sqlidentical
CockroachDB
PostgreSQL
1CREATE TABLE lost_property (
1CREATE TABLE lost_property (
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3 item TEXT NOT NULL,
3 item TEXT NOT NULL,
4 colour TEXT NOT NULL,
4 colour TEXT NOT NULL,
5 found_at TEXT NOT NULL,
5 found_at TEXT NOT NULL,
6 days_held BIGINT NOT NULL,
6 days_held BIGINT NOT NULL,
7 claimed BOOLEAN NOT NULL DEFAULT false,
7 claimed BOOLEAN NOT NULL DEFAULT false,
8 owner_phone TEXT
8 owner_phone TEXT
9);
9);
db/migrations/0001_lost_property.down.sqlidentical
CockroachDB
PostgreSQL
1DROP TABLE lost_property;
1DROP TABLE lost_property;

Queries

HandInidentical
CockroachDB · db/queries/lost_property.sql
PostgreSQL · db/queries/lost_property.sql
1-- sgl.query(HandIn, exec)
1-- sgl.query(HandIn, exec)
2INSERT INTO lost_property (item, colour, found_at, days_held) VALUES ($1, $2, $3, $4);
2INSERT INTO lost_property (item, colour, found_at, days_held) VALUES ($1, $2, $3, $4);
Claimidentical
CockroachDB · db/queries/lost_property.sql
PostgreSQL · db/queries/lost_property.sql
1-- sgl.query(Claim, execrows)
1-- sgl.query(Claim, execrows)
2UPDATE lost_property SET claimed = true, owner_phone = $1 WHERE id = $2;
2UPDATE lost_property SET claimed = true, owner_phone = $1 WHERE id = $2;
SearchUnclaimedidentical
CockroachDB · db/queries/lost_property.sql
PostgreSQL · db/queries/lost_property.sql
1-- sgl.query(SearchUnclaimed, many, dynamic: runtime)
1-- sgl.query(SearchUnclaimed, many, dynamic: runtime)
2SELECT id, item, colour, found_at, days_held
2SELECT id, item, colour, found_at, days_held
3FROM lost_property
3FROM lost_property
4WHERE NOT claimed
4WHERE NOT claimed

What sgl generates

HandInidentical
CockroachDB
PostgreSQL
1func (queries *Queries) HandIn(ctx context.Context, params HandInParams) error
1func (queries *Queries) HandIn(ctx context.Context, params HandInParams) error
2
2
3type HandInParams struct {
3type HandInParams struct {
4 Item string
4 Item string
5 Colour string
5 Colour string
6 FoundAt string
6 FoundAt string
7 DaysHeld int64
7 DaysHeld int64
8}
8}
Claimidentical
CockroachDB
PostgreSQL
1func (queries *Queries) Claim(ctx context.Context, params ClaimParams) (int64, error)
1func (queries *Queries) Claim(ctx context.Context, params ClaimParams) (int64, error)
2
2
3type ClaimParams struct {
3type ClaimParams struct {
4 OwnerPhone *string
4 OwnerPhone *string
5 ID int64
5 ID int64
6}
6}
SearchUnclaimedidentical
CockroachDB
PostgreSQL
1func (queries *Queries) SearchUnclaimed() *SearchUnclaimedBuilder
1func (queries *Queries) SearchUnclaimed() *SearchUnclaimedBuilder
2
2
3type SearchUnclaimedRow struct {
3type SearchUnclaimedRow struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 Item string `json:"item"`
5 Item string `json:"item"`
6 Colour string `json:"colour"`
6 Colour string `json:"colour"`
7 FoundAt string `json:"found_at"`
7 FoundAt string `json:"found_at"`
8 DaysHeld int64 `json:"days_held"`
8 DaysHeld int64 `json:"days_held"`
9}
9}

The program

example.goidentical
CockroachDB
PostgreSQL
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "database/sql"
6 "errors"
6 "errors"
7 "fmt"
7 "fmt"
8 "io"
8 "io"
9
9
10 "seagull.sh/seagull/examples/postgres/014_runtime_builder/internal/store"
10 "seagull.sh/seagull/examples/postgres/014_runtime_builder/internal/store"
11)
11)
12
12
13// run searches the pier's unclaimed lost property. dynamic: runtime turns SearchUnclaimed
13// run searches the pier's unclaimed lost property. dynamic: runtime turns SearchUnclaimed
14// into a builder: Where, OrderBy, Limit and Offset add to the SQL, and All, One or Count
14// into a builder: Where, OrderBy, Limit and Offset add to the SQL, and All, One or Count
15// run it. The base query already has WHERE NOT claimed, so each Where is joined with AND,
15// run it. The base query already has WHERE NOT claimed, so each Where is joined with AND,
16// which sgl vet points out as hint Q022. The builder inserts its filters, order and limit
16// which sgl vet points out as hint Q022. The builder inserts its filters, order and limit
17// at the places the query leaves for them, before any LIMIT or trailing semicolon, and
17// at the places the query leaves for them, before any LIMIT or trailing semicolon, and
18// Count replaces the SELECT list with COUNT(*), wrapping the whole query instead when it
18// Count replaces the SELECT list with COUNT(*), wrapping the whole query instead when it
19// groups or is DISTINCT (Q023).
19// groups or is DISTINCT (Q023).
20//
20//
21// The builder is safe with untrusted input. Where and OrderBy accept only the columns the
21// The builder is safe with untrusted input. Where and OrderBy accept only the columns the
22// query selects, so owner_phone, which the table has but the query leaves out, cannot be
22// query selects, so owner_phone, which the table has but the query leaves out, cannot be
23// searched. Operators and directions come from fixed allow-lists, and every value is
23// searched. Operators and directions come from fixed allow-lists, and every value is
24// bound as a numbered $N parameter after the base query's own, never written into the
24// bound as a numbered $N parameter after the base query's own, never written into the
25// SQL. A refused input does not panic: the terminal returns ErrSglUnknownColumn,
25// SQL. A refused input does not panic: the terminal returns ErrSglUnknownColumn,
26// ErrSglUnknownOperator or ErrSglUnknownDirection.
26// ErrSglUnknownOperator or ErrSglUnknownDirection.
27//
27//
28// Takes database (*sql.DB) which is a migrated database.
28// Takes database (*sql.DB) which is a migrated database.
29// Takes output (io.Writer) which receives the searches and the refusals.
29// Takes output (io.Writer) which receives the searches and the refusals.
30//
30//
31// Returns error when a query fails or an unsafe input is not refused.
31// Returns error when a query fails or an unsafe input is not refused.
32func run(ctx context.Context, database *sql.DB, output io.Writer) error {
32func run(ctx context.Context, database *sql.DB, output io.Writer) error {
33 queries := store.New(database)
33 queries := store.New(database)
34
34
35 items := []store.HandInParams{
35 items := []store.HandInParams{
36 {Item: "umbrella", Colour: "red", FoundAt: "the pier", DaysHeld: 40},
36 {Item: "umbrella", Colour: "red", FoundAt: "the pier", DaysHeld: 40},
37 {Item: "sunglasses", Colour: "black", FoundAt: "the beach", DaysHeld: 12},
37 {Item: "sunglasses", Colour: "black", FoundAt: "the beach", DaysHeld: 12},
38 {Item: "bucket and spade", Colour: "red", FoundAt: "the beach", DaysHeld: 3},
38 {Item: "bucket and spade", Colour: "red", FoundAt: "the beach", DaysHeld: 3},
39 {Item: "umbrella", Colour: "blue", FoundAt: "the cafe", DaysHeld: 33},
39 {Item: "umbrella", Colour: "blue", FoundAt: "the cafe", DaysHeld: 33},
40 {Item: "phone", Colour: "black", FoundAt: "the pier", DaysHeld: 1},
40 {Item: "phone", Colour: "black", FoundAt: "the pier", DaysHeld: 1},
41 }
41 }
42 for _, item := range items {
42 for _, item := range items {
43 if err := queries.HandIn(ctx, item); err != nil {
43 if err := queries.HandIn(ctx, item); err != nil {
44 return fmt.Errorf("handing in a %s: %w", item.Item, err)
44 return fmt.Errorf("handing in a %s: %w", item.Item, err)
45 }
45 }
46 }
46 }
47 if _, err := queries.Claim(ctx, store.ClaimParams{OwnerPhone: new("07700 900123"), ID: 5}); err != nil {
47 if _, err := queries.Claim(ctx, store.ClaimParams{OwnerPhone: new("07700 900123"), ID: 5}); err != nil {
48 return fmt.Errorf("claiming the phone: %w", err)
48 return fmt.Errorf("claiming the phone: %w", err)
49 }
49 }
50
50
51 red, err := queries.SearchUnclaimed().Where("colour", "=", "red").All(ctx)
51 red, err := queries.SearchUnclaimed().Where("colour", "=", "red").All(ctx)
52 if err != nil {
52 if err != nil {
53 return fmt.Errorf("finding red items: %w", err)
53 return fmt.Errorf("finding red items: %w", err)
54 }
54 }
55 fmt.Fprintln(output, "red:")
55 fmt.Fprintln(output, "red:")
56 printItems(output, red)
56 printItems(output, red)
57
57
58 longest, err := queries.SearchUnclaimed().
58 longest, err := queries.SearchUnclaimed().
59 Where("days_held", ">=", 30).
59 Where("days_held", ">=", 30).
60 OrderBy("days_held", "DESC").
60 OrderBy("days_held", "DESC").
61 Limit(2).
61 Limit(2).
62 All(ctx)
62 All(ctx)
63 if err != nil {
63 if err != nil {
64 return fmt.Errorf("finding long-held items: %w", err)
64 return fmt.Errorf("finding long-held items: %w", err)
65 }
65 }
66 fmt.Fprintln(output, "held 30 days or more, longest first:")
66 fmt.Fprintln(output, "held 30 days or more, longest first:")
67 printItems(output, longest)
67 printItems(output, longest)
68
68
69 umbrellas, err := queries.SearchUnclaimed().Where("item", "=", "umbrella").Count(ctx)
69 umbrellas, err := queries.SearchUnclaimed().Where("item", "=", "umbrella").Count(ctx)
70 if err != nil {
70 if err != nil {
71 return fmt.Errorf("counting umbrellas: %w", err)
71 return fmt.Errorf("counting umbrellas: %w", err)
72 }
72 }
73 fmt.Fprintf(output, "unclaimed umbrellas: %d\n", umbrellas)
73 fmt.Fprintf(output, "unclaimed umbrellas: %d\n", umbrellas)
74
74
75 tautology, err := queries.SearchUnclaimed().Where("item", "=", "' OR '1'='1").All(ctx)
75 tautology, err := queries.SearchUnclaimed().Where("item", "=", "' OR '1'='1").All(ctx)
76 if err != nil {
76 if err != nil {
77 return fmt.Errorf("searching for a quoted value: %w", err)
77 return fmt.Errorf("searching for a quoted value: %w", err)
78 }
78 }
79 fmt.Fprintf(output, "items named ' OR '1'='1: %d, as the value is bound, not run\n", len(tautology))
79 fmt.Fprintf(output, "items named ' OR '1'='1: %d, as the value is bound, not run\n", len(tautology))
80
80
81 attempts := []struct {
81 attempts := []struct {
82 label string
82 label string
83 search *store.SearchUnclaimedBuilder
83 search *store.SearchUnclaimedBuilder
84 }{
84 }{
85 {label: "a column the query does not select", search: queries.SearchUnclaimed().Where("owner_phone", "IS NOT NULL", nil)},
85 {label: "a column the query does not select", search: queries.SearchUnclaimed().Where("owner_phone", "IS NOT NULL", nil)},
86 {label: "SQL in a column name", search: queries.SearchUnclaimed().OrderBy("id; DROP TABLE lost_property", "ASC")},
86 {label: "SQL in a column name", search: queries.SearchUnclaimed().OrderBy("id; DROP TABLE lost_property", "ASC")},
87 {label: "an operator outside the allow-list", search: queries.SearchUnclaimed().Where("colour", "= 'red' OR 1=1 --", "red")},
87 {label: "an operator outside the allow-list", search: queries.SearchUnclaimed().Where("colour", "= 'red' OR 1=1 --", "red")},
88 {label: "a direction outside the allow-list", search: queries.SearchUnclaimed().OrderBy("days_held", "DESC; DROP TABLE lost_property")},
88 {label: "a direction outside the allow-list", search: queries.SearchUnclaimed().OrderBy("days_held", "DESC; DROP TABLE lost_property")},
89 }
89 }
90 for _, attempt := range attempts {
90 for _, attempt := range attempts {
91 _, err := attempt.search.All(ctx)
91 _, err := attempt.search.All(ctx)
92 switch {
92 switch {
93 case errors.Is(err, store.ErrSglUnknownColumn), errors.Is(err, store.ErrSglUnknownOperator), errors.Is(err, store.ErrSglUnknownDirection):
93 case errors.Is(err, store.ErrSglUnknownColumn), errors.Is(err, store.ErrSglUnknownOperator), errors.Is(err, store.ErrSglUnknownDirection):
94 fmt.Fprintf(output, "refused %s: %v\n", attempt.label, err)
94 fmt.Fprintf(output, "refused %s: %v\n", attempt.label, err)
95 case err == nil:
95 case err == nil:
96 return fmt.Errorf("%s: got no error, want a refusal", attempt.label)
96 return fmt.Errorf("%s: got no error, want a refusal", attempt.label)
97 default:
97 default:
98 return fmt.Errorf("%s: got %w, want a refusal", attempt.label, err)
98 return fmt.Errorf("%s: got %w, want a refusal", attempt.label, err)
99 }
99 }
100 }
100 }
101 return nil
101 return nil
102}
102}
103
103
104// printItems writes one line per item.
104// printItems writes one line per item.
105//
105//
106// Takes output (io.Writer) which receives the lines.
106// Takes output (io.Writer) which receives the lines.
107// Takes items ([]store.SearchUnclaimedRow) which are the items to print.
107// Takes items ([]store.SearchUnclaimedRow) which are the items to print.
108func printItems(output io.Writer, items []store.SearchUnclaimedRow) {
108func printItems(output io.Writer, items []store.SearchUnclaimedRow) {
109 for _, item := range items {
109 for _, item := range items {
110 fmt.Fprintf(output, " #%d %s %s, found at %s, held %d days\n", item.ID, item.Colour, item.Item, item.FoundAt, item.DaysHeld)
110 fmt.Fprintf(output, " #%d %s %s, found at %s, held %d days\n", item.ID, item.Colour, item.Item, item.FoundAt, item.DaysHeld)
111 }
111 }
112}
112}