Compare · 007

Enums as generated Go types, and your own types with go_type: ClickHouse and DuckDB

Every file of the ClickHouse and DuckDB versions side by side, with the lines that differ marked.

Compare ClickHouse withPostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
ClickHouse
DuckDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: harbour
4 - name: menu
5 dialect: clickhouse
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
11 types:
12 overrides:
13 - sql: item_status
14 go: seagull.sh/seagull/examples/duckdb/007_enums/internal/menu.Status

Migrations

db/migrations/0001_boats.up.sqldiffers
ClickHouse
DuckDB · db/migrations/0001_menu.up.sql
1CREATE TABLE boats (
1CREATE TYPE section AS ENUM ('fish', 'sides', 'puddings');
2 id UInt64,
2
3 name String,
3CREATE TYPE item_status AS ENUM ('available', 'sold_out');
4 status Enum8('moored' = 1, 'fishing' = 2, 'in_dock' = 3)
4
5) ENGINE = MergeTree() ORDER BY id;
5CREATE SEQUENCE menu_item_ids;
6
7CREATE TABLE menu_items (
8 id BIGINT PRIMARY KEY DEFAULT nextval('menu_item_ids'),
9 name VARCHAR NOT NULL UNIQUE,
10 section section NOT NULL,
11 status item_status NOT NULL DEFAULT 'available'
12);
db/migrations/0001_boats.down.sqldiffers
ClickHouse
DuckDB · db/migrations/0001_menu.down.sql
1DROP TABLE boats;
1DROP TABLE menu_items;
2DROP SEQUENCE menu_item_ids;
3DROP TYPE item_status;
4DROP TYPE section;

Queries

AddBoatonly ClickHouse
ClickHouse · db/queries/boats.sql
DuckDB · not in this version
1-- sgl.query(AddBoat, exec)
2INSERT INTO boats (id, name, status) VALUES ({id:UInt64}, {name:String}, {status:String});
ListBoatsonly ClickHouse
ClickHouse · db/queries/boats.sql
DuckDB · not in this version
1-- sgl.query(ListBoats, many)
2SELECT id, name, status FROM boats ORDER BY id;
BoatsNotInonly ClickHouse
ClickHouse · db/queries/boats.sql
DuckDB · not in this version
1-- sgl.query(BoatsNotIn, many)
2-- sgl.column(status, go_type: "seagull.sh/seagull/examples/clickhouse/007_enums/internal/domain.Status")
3SELECT id, name, status FROM boats WHERE status != {status:String} ORDER BY id;
AddItemonly DuckDB
ClickHouse · not in this version
DuckDB · db/queries/menu.sql
1-- sgl.query(AddItem, exec)
2INSERT INTO menu_items (name, section) VALUES ($1, $2);
SetStatusonly DuckDB
ClickHouse · not in this version
DuckDB · db/queries/menu.sql
1-- sgl.query(SetStatus, execrows)
2UPDATE menu_items SET status = $1 WHERE name = $2;
ListMenuonly DuckDB
ClickHouse · not in this version
DuckDB · db/queries/menu.sql
1-- sgl.query(ListMenu, many)
2SELECT name, section, status FROM menu_items ORDER BY section, name;
ItemsInSectiononly DuckDB
ClickHouse · not in this version
DuckDB · db/queries/menu.sql
1-- sgl.query(ItemsInSection, many)
2-- sgl.column(section, go_type: "seagull.sh/seagull/examples/duckdb/007_enums/internal/menu.Section")
3SELECT name, section, status FROM menu_items WHERE section = $1 ORDER BY name;

What sgl generates

AddBoatonly ClickHouse
ClickHouse
DuckDB · not in this version
1func (queries *Queries) AddBoat(ctx context.Context, params AddBoatParams) error
2
3type AddBoatParams struct {
4 ID uint64
5 Name string
6 Status string
7}
ListBoatsonly ClickHouse
ClickHouse
DuckDB · not in this version
1func (queries *Queries) ListBoats(ctx context.Context) ([]ListBoatsRow, error)
2
3type ListBoatsRow struct {
4 ID uint64 `json:"id"`
5 Name string `json:"name"`
6 Status BoatsStatus `json:"status"`
7}
8
9type BoatsStatus string
BoatsNotInonly ClickHouse
ClickHouse
DuckDB · not in this version
1func (queries *Queries) BoatsNotIn(ctx context.Context, status string) ([]BoatsNotInRow, error)
2
3type BoatsNotInRow struct {
4 ID uint64 `json:"id"`
5 Name string `json:"name"`
6 Status domain.Status `json:"status"`
7}
AddItemonly DuckDB
ClickHouse · not in this version
DuckDB
1func (queries *Queries) AddItem(ctx context.Context, params AddItemParams) error
2
3type AddItemParams struct {
4 Name string
5 Section Section
6}
7
8type Section string
SetStatusonly DuckDB
ClickHouse · not in this version
DuckDB
1func (queries *Queries) SetStatus(ctx context.Context, params SetStatusParams) (int64, error)
2
3type SetStatusParams struct {
4 Status menu.Status
5 Name string
6}
ListMenuonly DuckDB
ClickHouse · not in this version
DuckDB
1func (queries *Queries) ListMenu(ctx context.Context) ([]ListMenuRow, error)
2
3type ListMenuRow struct {
4 Name string `json:"name"`
5 Section Section `json:"section"`
6 Status menu.Status `json:"status"`
7}
8
9type Section string
ItemsInSectiononly DuckDB
ClickHouse · not in this version
DuckDB
1func (queries *Queries) ItemsInSection(ctx context.Context, section Section) ([]ItemsInSectionRow, error)
2
3type Section string
4
5type ItemsInSectionRow struct {
6 Name string `json:"name"`
7 Section menu.Section `json:"section"`
8 Status menu.Status `json:"status"`
9}

The program

example.godiffers
ClickHouse
DuckDB
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "database/sql"
6 "errors"
6 "fmt"
7 "fmt"
7 "io"
8 "io"
8 "strings"
9
9
10 "seagull.sh/seagull/examples/clickhouse/007_enums/internal/domain"
10 "seagull.sh/seagull/examples/duckdb/007_enums/internal/menu"
11 "seagull.sh/seagull/examples/clickhouse/007_enums/internal/store"
11 "seagull.sh/seagull/examples/duckdb/007_enums/internal/store"
12)
12)
13
13
14// run berths four boats and lists them twice. boats.status is an Enum8, and Seagull
14// run fills a chip shop menu whose section and status columns are DuckDB enums, marks an
15// declares a named Go type for it in models.go, named after the table and column that
15// item sold out and reads the menu back. Each enum becomes a named Go type in the
16// hold it: store.BoatsStatus, with a constant for each label, such as
16// generated package unless an override names a type of your own:
17// store.BoatsStatusMoored, and a Valid method. ListBoats returns Status as a
18// store.BoatsStatus. The status parameters are written {status:String}, so they are plain
19// strings, and the server converts the label to the Enum8.
20//
17//
21// BoatsNotIn adds sgl.column(status, go_type: "…/internal/domain.Status"), so its rows
18// - section has no override, so models.go declares store.Section, with a constant for
22// carry the harbour's own domain.Status type, with its constants and methods. The
19// each value, such as store.SectionFish, and a Valid method; ListMenuRow.Section,
23// override changes only that query's result rows: parameters stay string, so the example
20// AddItemParams.Section and ItemsInSection's parameter are all a store.Section;
24// converts with string(domain.StatusInDock) on the way in. A types.overrides entry in
21// - sgl.yaml's types.overrides names the enum type item_status, so every column of that
25// sgl.yaml would change every query's rows the same way.
22// enum gives menu.Status, as in ListMenuRow.Status, and so does every parameter
23// compared with or assigned to one, as in SetStatusParams.Status; the generated
24// package then declares no type for item_status;
25// - sgl.column(section, go_type: ...) in ItemsInSection gives that one query's returned
26// Section the type menu.Section; the parameter it compares with stays a
27// store.Section.
26//
28//
27// ClickHouse checks every label against the Enum8, so a status that is not one of its
29// An enum sorts in the order its values are declared, not alphabetically, so ORDER BY
28// labels is refused by the server, not stored.
30// section lists fish, then sides, then puddings. Valid reports whether a value is one the
31// enum declares, so the shop can check a section before it binds it. DuckDB checks each
32// value against the enum as it is stored as well, so a section the enum does not list is
33// refused; DuckDB keeps an enum as a small integer, and its error names that type.
29//
34//
30// Takes database (*sql.DB) which is a migrated database.
35// Takes database (*sql.DB) which is a migrated database.
31// Takes output (io.Writer) which receives both listings and the refusal.
36// Takes output (io.Writer) which receives the menu, the fish section and the refusal.
32//
37//
33// Returns error when a query fails or a bad status is accepted.
38// Returns error when a query fails other than as the example expects.
34func run(ctx context.Context, database *sql.DB, output io.Writer) error {
39func run(ctx context.Context, database *sql.DB, output io.Writer) error {
35 queries := store.New(database)
40 queries := store.New(database)
36
41
37 boats := []store.AddBoatParams{
42 items := []store.AddItemParams{
38 {ID: 1, Name: "Kittiwake", Status: string(domain.StatusMoored)},
43 {Name: "cod", Section: store.SectionFish},
39 {ID: 2, Name: "Salty Chip", Status: string(domain.StatusFishing)},
44 {Name: "haddock", Section: store.SectionFish},
40 {ID: 3, Name: "Gull Wing", Status: string(domain.StatusInDock)},
45 {Name: "chips", Section: store.SectionSides},
41 {ID: 4, Name: "Driftwood", Status: string(domain.StatusFishing)},
46 {Name: "mushy peas", Section: store.SectionSides},
47 {Name: "sticky toffee pudding", Section: store.SectionPuddings},
42 }
48 }
43 for _, boat := range boats {
49 for _, item := range items {
44 if err := queries.AddBoat(ctx, boat); err != nil {
50 if err := queries.AddItem(ctx, item); err != nil {
45 return fmt.Errorf("adding %s: %w", boat.Name, err)
51 return fmt.Errorf("adding %s: %w", item.Name, err)
46 }
52 }
47 }
53 }
54 if _, err := queries.SetStatus(ctx, store.SetStatusParams{Status: menu.SoldOut, Name: "haddock"}); err != nil {
55 return fmt.Errorf("selling out of haddock: %w", err)
56 }
48
57
49 listed, err := queries.ListBoats(ctx)
58 listed, err := queries.ListMenu(ctx)
50 if err != nil {
59 if err != nil {
51 return fmt.Errorf("listing boats: %w", err)
60 return fmt.Errorf("listing the menu: %w", err)
52 }
61 }
53 fmt.Fprintln(output, "all boats, status as store.BoatsStatus:")
62 var menuRow store.ListMenuRow
54 for _, boat := range listed {
63 fmt.Fprintf(output, "ListMenu: section %T, status %T\n", menuRow.Section, menuRow.Status)
55 fmt.Fprintf(output, " %d %-11s %s\n", boat.ID, boat.Name, boat.Status)
64 for _, item := range listed {
65 fmt.Fprintf(output, " %-9s %s%s\n", item.Section, item.Name, soldOut(item.Status))
56 }
66 }
57
67
58 afloat, err := queries.BoatsNotIn(ctx, string(domain.StatusInDock))
68 fish, err := queries.ItemsInSection(ctx, store.SectionFish)
59 if err != nil {
69 if err != nil {
60 return fmt.Errorf("listing boats not in dock: %w", err)
70 return fmt.Errorf("listing the fish: %w", err)
61 }
71 }
62 fmt.Fprintln(output, "not in dock, status as domain.Status:")
72 var sectionRow store.ItemsInSectionRow
63 for _, boat := range afloat {
73 fmt.Fprintf(output, "ItemsInSection: section %T, status %T\n", sectionRow.Section, sectionRow.Status)
64 fmt.Fprintf(output, " %d %-11s %-8s at sea: %t\n", boat.ID, boat.Name, boat.Status, boat.Status.AtSea())
74 for _, item := range fish {
75 fmt.Fprintf(output, " %-9s %s%s\n", item.Section, item.Name, soldOut(item.Status))
65 }
76 }
66
77
67 sunk := store.AddBoatParams{ID: 5, Name: "Old Barnacle", Status: "sunk"}
78 drinks := store.Section("drinks")
68 err = queries.AddBoat(ctx, sunk)
79 fmt.Fprintf(output, "section drinks is valid: %t\n", drinks.Valid())
80 err = queries.AddItem(ctx, store.AddItemParams{Name: "lemonade", Section: drinks})
69 if err == nil {
81 if err == nil {
70 return fmt.Errorf("adding status %q: got no error, want the server's refusal", sunk.Status)
82 return errors.New("added an item to a section the enum does not list")
71 }
72 if !strings.Contains(err.Error(), "Unknown element 'sunk' for enum") {
73 return fmt.Errorf("adding status %q: got %w, want the server's refusal", sunk.Status, err)
74 }
83 }
75 fmt.Fprintf(output, "status %q refused: not one of the Enum8 labels\n", sunk.Status)
84 fmt.Fprintf(output, "refused section drinks: %v\n", err)
76 return nil
85 return nil
77}
86}
87
88// soldOut returns a note for an item that has sold out, comparing against the typed
89// constant rather than a string.
90//
91// Takes status (menu.Status) which is the item's status.
92//
93// Returns string which is the note, or empty when the item is available.
94func soldOut(status menu.Status) string {
95 if status == menu.SoldOut {
96 return " (sold out)"
97 }
98 return ""
99}