Compare · 007

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

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

Compare MariaDB withPostgreSQLCockroachDBTimescaleDBMySQLDuckDBClickHouse

Configuration

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

Migrations

db/migrations/0001_menu.up.sqldiffers
MariaDB
DuckDB
1CREATE TYPE section AS ENUM ('fish', 'sides', 'puddings');
2
3CREATE TYPE item_status AS ENUM ('available', 'sold_out');
4
5CREATE SEQUENCE menu_item_ids;
6
1CREATE TABLE menu_items (
7CREATE TABLE menu_items (
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
8 id BIGINT PRIMARY KEY DEFAULT nextval('menu_item_ids'),
3 name VARCHAR(50) NOT NULL UNIQUE,
9 name VARCHAR NOT NULL UNIQUE,
4 section ENUM('fish', 'sides', 'puddings') NOT NULL,
10 section section NOT NULL,
5 status ENUM('available', 'sold_out') NOT NULL DEFAULT 'available'
11 status item_status NOT NULL DEFAULT 'available'
6);
12);
db/migrations/0001_menu.down.sqldiffers
MariaDB
DuckDB
1DROP TABLE menu_items;
1DROP TABLE menu_items;
2DROP SEQUENCE menu_item_ids;
3DROP TYPE item_status;
4DROP TYPE section;

Queries

AddItemdiffers
MariaDB · db/queries/menu.sql
DuckDB · db/queries/menu.sql
1-- sgl.query(AddItem, exec)
1-- sgl.query(AddItem, exec)
2INSERT INTO menu_items (name, section) VALUES (?, ?);
2INSERT INTO menu_items (name, section) VALUES ($1, $2);
SetStatusdiffers
MariaDB · db/queries/menu.sql
DuckDB · db/queries/menu.sql
1-- sgl.query(SetStatus, execrows)
1-- sgl.query(SetStatus, execrows)
2UPDATE menu_items SET status = ? WHERE name = ?;
2UPDATE menu_items SET status = $1 WHERE name = $2;
ListMenuidentical
MariaDB · db/queries/menu.sql
DuckDB · db/queries/menu.sql
1-- sgl.query(ListMenu, many)
1-- sgl.query(ListMenu, many)
2SELECT name, section, status FROM menu_items ORDER BY section, name;
2SELECT name, section, status FROM menu_items ORDER BY section, name;
ItemsInSectiondiffers
MariaDB · db/queries/menu.sql
DuckDB · db/queries/menu.sql
1-- sgl.query(ItemsInSection, many)
1-- sgl.query(ItemsInSection, many)
2-- sgl.column(section, go_type: "seagull.sh/seagull/examples/mysql/007_enums/internal/menu.Section")
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 = ? ORDER BY name;
3SELECT name, section, status FROM menu_items WHERE section = $1 ORDER BY name;

What sgl generates

AddItemdiffers
MariaDB
DuckDB
1func (queries *Queries) AddItem(ctx context.Context, params AddItemParams) error
1func (queries *Queries) AddItem(ctx context.Context, params AddItemParams) error
2
2
3type AddItemParams struct {
3type AddItemParams struct {
4 Name string
4 Name string
5 Section MenuItemsSection
5 Section Section
6}
6}
7
7
8type MenuItemsSection string
8type Section string
SetStatusidentical
MariaDB
DuckDB
1func (queries *Queries) SetStatus(ctx context.Context, params SetStatusParams) (int64, error)
1func (queries *Queries) SetStatus(ctx context.Context, params SetStatusParams) (int64, error)
2
2
3type SetStatusParams struct {
3type SetStatusParams struct {
4 Status menu.Status
4 Status menu.Status
5 Name string
5 Name string
6}
6}
ListMenudiffers
MariaDB
DuckDB
1func (queries *Queries) ListMenu(ctx context.Context) ([]ListMenuRow, error)
1func (queries *Queries) ListMenu(ctx context.Context) ([]ListMenuRow, error)
2
2
3type ListMenuRow struct {
3type ListMenuRow struct {
4 Name string `json:"name"`
4 Name string `json:"name"`
5 Section MenuItemsSection `json:"section"`
5 Section Section `json:"section"`
6 Status menu.Status `json:"status"`
6 Status menu.Status `json:"status"`
7}
7}
8
8
9type MenuItemsSection string
9type Section string
ItemsInSectiondiffers
MariaDB
DuckDB
1func (queries *Queries) ItemsInSection(ctx context.Context, section MenuItemsSection) ([]ItemsInSectionRow, error)
1func (queries *Queries) ItemsInSection(ctx context.Context, section Section) ([]ItemsInSectionRow, error)
2
2
3type MenuItemsSection string
3type Section string
4
4
5type ItemsInSectionRow struct {
5type ItemsInSectionRow struct {
6 Name string `json:"name"`
6 Name string `json:"name"`
7 Section menu.Section `json:"section"`
7 Section menu.Section `json:"section"`
8 Status menu.Status `json:"status"`
8 Status menu.Status `json:"status"`
9}
9}

The program

example.godiffers
MariaDB
DuckDB
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/mysql/007_enums/internal/menu"
10 "seagull.sh/seagull/examples/duckdb/007_enums/internal/menu"
11 "seagull.sh/seagull/examples/mysql/007_enums/internal/store"
11 "seagull.sh/seagull/examples/duckdb/007_enums/internal/store"
12)
12)
13
13
14// run fills a chip shop menu whose section and status columns are MySQL ENUMs, marks an
14// run fills a chip shop menu whose section and status columns are DuckDB enums, marks an
15// item sold out and reads the menu back. Each ENUM becomes a named Go type in the
15// item sold out and reads the menu back. Each enum becomes a named Go type in the
16// generated package unless an override names a type of your own:
16// generated package unless an override names a type of your own:
17//
17//
18// - section has no override, so models.go declares store.MenuItemsSection, named after
18// - section has no override, so models.go declares store.Section, with a constant for
19// the table and column that hold the ENUM, with a constant for each value, such as
19// each value, such as store.SectionFish, and a Valid method; ListMenuRow.Section,
20// store.MenuItemsSectionFish, and a Valid method; ListMenuRow.Section,
20// AddItemParams.Section and ItemsInSection's parameter are all a store.Section;
21// AddItemParams.Section and ItemsInSection's parameter are all a
21// - sgl.yaml's types.overrides names the enum type item_status, so every column of that
22// store.MenuItemsSection;
22// enum gives menu.Status, as in ListMenuRow.Status, and so does every parameter
23// - sgl.yaml's types.overrides names the column menu_items.status, so every query that
23// compared with or assigned to one, as in SetStatusParams.Status; the generated
24// returns it as it is gives menu.Status, as in ListMenuRow.Status, and so does every
24// package then declares no type for item_status;
25// parameter compared with or assigned to it, as in SetStatusParams.Status; models.go
26// uses it too, and the generated package declares no type for that ENUM;
27// - sgl.column(section, go_type: ...) in ItemsInSection gives that one query's returned
25// - sgl.column(section, go_type: ...) in ItemsInSection gives that one query's returned
28// Section the type menu.Section; the parameter it compares with stays a
26// Section the type menu.Section; the parameter it compares with stays a
29// store.MenuItemsSection.
27// store.Section.
30//
28//
31// An ENUM sorts in the order its values are declared, not alphabetically, so ORDER BY
29// An enum sorts in the order its values are declared, not alphabetically, so ORDER BY
32// section lists fish, then sides, then puddings. Valid reports whether a value is one the
30// section lists fish, then sides, then puddings. Valid reports whether a value is one the
33// ENUM declares, so the shop can check a section before it binds it. In strict mode, the
31// enum declares, so the shop can check a section before it binds it. DuckDB checks each
34// default since MySQL 5.7, a value the ENUM does not list is refused as it is stored as
32// value against the enum as it is stored as well, so a section the enum does not list is
35// well; MySQL keeps an ENUM as the index of its value, and its error says the value was
33// refused; DuckDB keeps an enum as a small integer, and its error names that type.
36// truncated.
37//
34//
38// Takes database (*sql.DB) which is a migrated database.
35// Takes database (*sql.DB) which is a migrated database.
39// Takes output (io.Writer) which receives the menu, the fish section and the refusal.
36// Takes output (io.Writer) which receives the menu, the fish section and the refusal.
40//
37//
41// Returns error when a query fails other than as the example expects.
38// Returns error when a query fails other than as the example expects.
42func run(ctx context.Context, database *sql.DB, output io.Writer) error {
39func run(ctx context.Context, database *sql.DB, output io.Writer) error {
43 queries := store.New(database)
40 queries := store.New(database)
44
41
45 items := []store.AddItemParams{
42 items := []store.AddItemParams{
46 {Name: "cod", Section: store.MenuItemsSectionFish},
43 {Name: "cod", Section: store.SectionFish},
47 {Name: "haddock", Section: store.MenuItemsSectionFish},
44 {Name: "haddock", Section: store.SectionFish},
48 {Name: "chips", Section: store.MenuItemsSectionSides},
45 {Name: "chips", Section: store.SectionSides},
49 {Name: "mushy peas", Section: store.MenuItemsSectionSides},
46 {Name: "mushy peas", Section: store.SectionSides},
50 {Name: "sticky toffee pudding", Section: store.MenuItemsSectionPuddings},
47 {Name: "sticky toffee pudding", Section: store.SectionPuddings},
51 }
48 }
52 for _, item := range items {
49 for _, item := range items {
53 if err := queries.AddItem(ctx, item); err != nil {
50 if err := queries.AddItem(ctx, item); err != nil {
54 return fmt.Errorf("adding %s: %w", item.Name, err)
51 return fmt.Errorf("adding %s: %w", item.Name, err)
55 }
52 }
56 }
53 }
57 if _, err := queries.SetStatus(ctx, store.SetStatusParams{Status: menu.SoldOut, Name: "haddock"}); err != nil {
54 if _, err := queries.SetStatus(ctx, store.SetStatusParams{Status: menu.SoldOut, Name: "haddock"}); err != nil {
58 return fmt.Errorf("selling out of haddock: %w", err)
55 return fmt.Errorf("selling out of haddock: %w", err)
59 }
56 }
60
57
61 listed, err := queries.ListMenu(ctx)
58 listed, err := queries.ListMenu(ctx)
62 if err != nil {
59 if err != nil {
63 return fmt.Errorf("listing the menu: %w", err)
60 return fmt.Errorf("listing the menu: %w", err)
64 }
61 }
65 var menuRow store.ListMenuRow
62 var menuRow store.ListMenuRow
66 fmt.Fprintf(output, "ListMenu: section %T, status %T\n", menuRow.Section, menuRow.Status)
63 fmt.Fprintf(output, "ListMenu: section %T, status %T\n", menuRow.Section, menuRow.Status)
67 for _, item := range listed {
64 for _, item := range listed {
68 fmt.Fprintf(output, " %-9s %s%s\n", item.Section, item.Name, soldOut(item.Status))
65 fmt.Fprintf(output, " %-9s %s%s\n", item.Section, item.Name, soldOut(item.Status))
69 }
66 }
70
67
71 fish, err := queries.ItemsInSection(ctx, store.MenuItemsSectionFish)
68 fish, err := queries.ItemsInSection(ctx, store.SectionFish)
72 if err != nil {
69 if err != nil {
73 return fmt.Errorf("listing the fish: %w", err)
70 return fmt.Errorf("listing the fish: %w", err)
74 }
71 }
75 var sectionRow store.ItemsInSectionRow
72 var sectionRow store.ItemsInSectionRow
76 fmt.Fprintf(output, "ItemsInSection: section %T, status %T\n", sectionRow.Section, sectionRow.Status)
73 fmt.Fprintf(output, "ItemsInSection: section %T, status %T\n", sectionRow.Section, sectionRow.Status)
77 for _, item := range fish {
74 for _, item := range fish {
78 fmt.Fprintf(output, " %-9s %s%s\n", item.Section, item.Name, soldOut(item.Status))
75 fmt.Fprintf(output, " %-9s %s%s\n", item.Section, item.Name, soldOut(item.Status))
79 }
76 }
80
77
81 drinks := store.MenuItemsSection("drinks")
78 drinks := store.Section("drinks")
82 fmt.Fprintf(output, "section drinks is valid: %t\n", drinks.Valid())
79 fmt.Fprintf(output, "section drinks is valid: %t\n", drinks.Valid())
83 err = queries.AddItem(ctx, store.AddItemParams{Name: "lemonade", Section: drinks})
80 err = queries.AddItem(ctx, store.AddItemParams{Name: "lemonade", Section: drinks})
84 if err == nil {
81 if err == nil {
85 return errors.New("added an item to a section the enum does not list")
82 return errors.New("added an item to a section the enum does not list")
86 }
83 }
87 fmt.Fprintf(output, "refused section drinks: %v\n", err)
84 fmt.Fprintf(output, "refused section drinks: %v\n", err)
88 return nil
85 return nil
89}
86}
90
87
91// soldOut returns a note for an item that has sold out, comparing against the typed
88// soldOut returns a note for an item that has sold out, comparing against the typed
92// constant rather than a string.
89// constant rather than a string.
93//
90//
94// Takes status (menu.Status) which is the item's status.
91// Takes status (menu.Status) which is the item's status.
95//
92//
96// Returns string which is the note, or empty when the item is available.
93// Returns string which is the note, or empty when the item is available.
97func soldOut(status menu.Status) string {
94func soldOut(status menu.Status) string {
98 if status == menu.SoldOut {
95 if status == menu.SoldOut {
99 return " (sold out)"
96 return " (sold out)"
100 }
97 }
101 return ""
98 return ""
102}
99}

Go type differences

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

QueryFieldMariaDBDuckDB
AddItemparameter sectionMenuItemsSectionSection
ListMenucolumn sectionMenuItemsSectionSection
ItemsInSectionparameter sectionMenuItemsSectionSection