Validated ORDER BY: DuckDB and SQLite
Every file of the DuckDB and SQLite versions side by side, with the lines that differ marked. 4 parameter or column types differ in Go.
Configuration
sgl.yamldiffers
DuckDB
SQLite
1
version: 11
version: 12
2
3
databases:3
databases:4
- name: regatta4
- name: regatta5
dialect: duckdb5
dialect: sqlite6
migrations: db/migrations6
migrations: db/migrations7
queries: db/queries7
queries: db/queries8
go:8
go:9
package: store9
package: store10
out: internal/store10
out: internal/storeMigrations
db/migrations/0001_entries.up.sqldiffers
DuckDB
SQLite
1
CREATE SEQUENCE entry_ids;2
3
CREATE TABLE entries (1
CREATE TABLE entries (4
id BIGINT PRIMARY KEY DEFAULT nextval('entry_ids'),2
id INTEGER PRIMARY KEY,5
boat VARCHAR NOT NULL,3
boat TEXT NOT NULL,6
crew INTEGER NOT NULL,4
crew INTEGER NOT NULL,7
finish_seconds INTEGER NOT NULL5
finish_seconds INTEGER NOT NULL8
);6
);db/migrations/0001_entries.down.sqldiffers
DuckDB
SQLite
1
DROP TABLE entries;1
DROP TABLE entries;2
DROP SEQUENCE entry_ids;Queries
EnterBoatdiffers
DuckDB · db/queries/entries.sql
SQLite · db/queries/entries.sql
1
-- sgl.query(EnterBoat, exec)1
-- sgl.query(EnterBoat, exec)2
INSERT INTO entries (boat, crew, finish_seconds) VALUES ($1, $2, $3);2
INSERT INTO entries (boat, crew, finish_seconds) VALUES (?, ?, ?);ListEntriesidentical
DuckDB · db/queries/entries.sql
SQLite · db/queries/entries.sql
1
-- sgl.query(ListEntries, many)1
-- sgl.query(ListEntries, many)2
-- sgl.sortable(order_by, [boat, crew, finish_seconds])2
-- sgl.sortable(order_by, [boat, crew, finish_seconds])3
SELECT id, boat, crew, finish_seconds FROM entries3
SELECT id, boat, crew, finish_seconds FROM entriesWhat sgl generates
EnterBoatdiffers
DuckDB
SQLite
1
func (queries *Queries) EnterBoat(ctx context.Context, params EnterBoatParams) error1
func (queries *Queries) EnterBoat(ctx context.Context, params EnterBoatParams) error2
2
3
type EnterBoatParams struct {3
type EnterBoatParams struct {4
Boat string4
Boat string5
Crew int325
Crew int646
FinishSeconds int326
FinishSeconds int647
}7
}ListEntriesdiffers
DuckDB
SQLite
1
func (queries *Queries) ListEntries(ctx context.Context, params ListEntriesParams) ([]ListEntriesRow, error)1
func (queries *Queries) ListEntries(ctx context.Context, params ListEntriesParams) ([]ListEntriesRow, error)2
2
3
type ListEntriesParams struct {3
type ListEntriesParams struct {4
OrderBy ListEntriesOrderBy4
OrderBy ListEntriesOrderBy5
OrderByDirection OrderDirection5
OrderByDirection OrderDirection6
}6
}7
7
8
type ListEntriesRow struct {8
type ListEntriesRow struct {9
ID int64 `json:"id"`9
ID int64 `json:"id"`10
Boat string `json:"boat"`10
Boat string `json:"boat"`11
Crew int32 `json:"crew"`11
Crew int64 `json:"crew"`12
FinishSeconds int32 `json:"finish_seconds"`12
FinishSeconds int64 `json:"finish_seconds"`13
}13
}14
14
15
type ListEntriesOrderBy string15
type ListEntriesOrderBy string16
16
17
type OrderDirection string17
type OrderDirection stringThe program
example.godiffers
DuckDB
SQLite
1
package main1
package main2
2
3
import (3
import (4
"context"4
"context"5
"database/sql"5
"database/sql"6
"fmt"6
"fmt"7
"io"7
"io"8
"time"8
"time"9
9
10
"seagull.sh/seagull/examples/duckdb/012_sortable/internal/store"10
"seagull.sh/seagull/examples/sqlite/012_sortable/internal/store"11
)11
)12
12
13
// run lists a regatta's entries in four orders. sgl.sortable(order_by, [boat, crew,13
// run lists a regatta's entries in four orders. sgl.sortable(order_by, [boat, crew,14
// finish_seconds]) has no placeholder: it gives ListEntriesParams an OrderBy field, typed14
// finish_seconds]) has no placeholder: it gives ListEntriesParams an OrderBy field, typed15
// with one constant per allowed column, and an OrderByDirection. The method appends ORDER15
// with one constant per allowed column, and an OrderByDirection. The method appends ORDER16
// BY only when OrderBy is one of the allowed columns, so a value that is not, such as raw16
// BY only when OrderBy is one of the allowed columns, so a value that is not, such as raw17
// text from a request, adds nothing to the SQL and the rows come back unsorted. A17
// text from a request, adds nothing to the SQL and the rows come back unsorted. A18
// sortable column the query does not select makes sgl vet report Q011.18
// sortable column the query does not select makes sgl vet report Q011.19
//19
//20
// Takes database (*sql.DB) which is a migrated database.20
// Takes database (*sql.DB) which is a migrated database.21
// Takes output (io.Writer) which receives each listing.21
// Takes output (io.Writer) which receives each listing.22
//22
//23
// Returns error when a query fails.23
// Returns error when a query fails.24
func run(ctx context.Context, database *sql.DB, output io.Writer) error {24
func run(ctx context.Context, database *sql.DB, output io.Writer) error {25
queries := store.New(database)25
queries := store.New(database)26
26
27
entries := []store.EnterBoatParams{27
entries := []store.EnterBoatParams{28
{Boat: "Kittiwake", Crew: 4, FinishSeconds: 3725},28
{Boat: "Kittiwake", Crew: 4, FinishSeconds: 3725},29
{Boat: "Salty Chip", Crew: 3, FinishSeconds: 3410},29
{Boat: "Salty Chip", Crew: 3, FinishSeconds: 3410},30
{Boat: "Gull Wing", Crew: 5, FinishSeconds: 3990},30
{Boat: "Gull Wing", Crew: 5, FinishSeconds: 3990},31
{Boat: "Driftwood", Crew: 2, FinishSeconds: 3588},31
{Boat: "Driftwood", Crew: 2, FinishSeconds: 3588},32
}32
}33
for _, entry := range entries {33
for _, entry := range entries {34
if err := queries.EnterBoat(ctx, entry); err != nil {34
if err := queries.EnterBoat(ctx, entry); err != nil {35
return fmt.Errorf("entering %s: %w", entry.Boat, err)35
return fmt.Errorf("entering %s: %w", entry.Boat, err)36
}36
}37
}37
}38
38
39
orders := []struct {39
orders := []struct {40
label string40
label string41
parameters store.ListEntriesParams41
parameters store.ListEntriesParams42
}{42
}{43
{label: "results", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByFinishSeconds, OrderByDirection: store.OrderAsc}},43
{label: "results", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByFinishSeconds, OrderByDirection: store.OrderAsc}},44
{label: "by boat", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByBoat, OrderByDirection: store.OrderAsc}},44
{label: "by boat", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByBoat, OrderByDirection: store.OrderAsc}},45
{label: "largest crew first", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByCrew, OrderByDirection: store.OrderDesc}},45
{label: "largest crew first", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByCrew, OrderByDirection: store.OrderDesc}},46
{label: "raw text, ignored", parameters: store.ListEntriesParams{OrderBy: "boat; DROP TABLE entries", OrderByDirection: store.OrderAsc}},46
{label: "raw text, ignored", parameters: store.ListEntriesParams{OrderBy: "boat; DROP TABLE entries", OrderByDirection: store.OrderAsc}},47
}47
}48
for _, order := range orders {48
for _, order := range orders {49
listed, err := queries.ListEntries(ctx, order.parameters)49
listed, err := queries.ListEntries(ctx, order.parameters)50
if err != nil {50
if err != nil {51
return fmt.Errorf("listing entries %s: %w", order.label, err)51
return fmt.Errorf("listing entries %s: %w", order.label, err)52
}52
}53
fmt.Fprintf(output, "%s:\n", order.label)53
fmt.Fprintf(output, "%s:\n", order.label)54
for _, entry := range listed {54
for _, entry := range listed {55
finish := time.Duration(entry.FinishSeconds) * time.Second55
finish := time.Duration(entry.FinishSeconds) * time.Second56
fmt.Fprintf(output, " %-10s crew %d %s\n", entry.Boat, entry.Crew, finish)56
fmt.Fprintf(output, " %-10s crew %d %s\n", entry.Boat, entry.Crew, finish)57
}57
}58
}58
}59
return nil59
return nil60
}60
}Go type differences
The same SQL types map to different Go types, because each database reports them differently.
| Query | Field | DuckDB | SQLite |
|---|---|---|---|
| EnterBoat | parameter crew | int32 | int64 |
| EnterBoat | parameter finish_seconds | int32 | int64 |
| ListEntries | column crew | int32 | int64 |
| ListEntries | column finish_seconds | int32 | int64 |