Compare · 007

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

Every file of the ClickHouse and PostgreSQL versions side by side, with the lines that differ marked. 2 parameter or column types differ in Go.

Compare ClickHouse withPostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
ClickHouse
PostgreSQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: harbour
4 - name: harbour
5 dialect: clickhouse
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_boats.up.sqldiffers
ClickHouse
PostgreSQL
1CREATE TYPE boat_status AS ENUM ('moored', 'at_sea', 'dry_dock');
2
1CREATE TABLE boats (
3CREATE TABLE boats (
2 id UInt64,
4 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3 name String,
5 name TEXT NOT NULL,
4 status Enum8('moored' = 1, 'fishing' = 2, 'in_dock' = 3)
6 status boat_status NOT NULL DEFAULT 'moored'
5) ENGINE = MergeTree() ORDER BY id;
7);
db/migrations/0001_boats.down.sqldiffers
ClickHouse
PostgreSQL
1DROP TABLE boats;
1DROP TABLE boats;
2DROP TYPE boat_status;

Queries

AddBoatdiffers
ClickHouse · db/queries/boats.sql
PostgreSQL · db/queries/boats.sql
1-- sgl.query(AddBoat, exec)
1-- sgl.query(AddBoat, exec)
2INSERT INTO boats (id, name, status) VALUES ({id:UInt64}, {name:String}, {status:String});
2INSERT INTO boats (name, status) VALUES ($1, $2);
ListBoatsdiffers
ClickHouse · db/queries/boats.sql
PostgreSQL · db/queries/boats.sql
1-- sgl.query(ListBoats, many)
1-- sgl.query(ListBoats, many)
2SELECT id, name, status FROM boats ORDER BY id;
2SELECT name, status FROM boats ORDER BY id;
BoatsNotInonly ClickHouse
ClickHouse · db/queries/boats.sql
PostgreSQL · 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;
SetStatusonly PostgreSQL
ClickHouse · not in this version
PostgreSQL · db/queries/boats.sql
1-- sgl.query(SetStatus, execrows)
2UPDATE boats SET status = $1 WHERE name = $2;
BoatsWithStatusonly PostgreSQL
ClickHouse · not in this version
PostgreSQL · db/queries/boats.sql
1-- sgl.query(BoatsWithStatus, many)
2-- sgl.column(status, go_type: "seagull.sh/seagull/examples/postgres/007_enums/internal/harbour.Status")
3SELECT name, status FROM boats WHERE status = $1 ORDER BY id;

What sgl generates

AddBoatdiffers
ClickHouse
PostgreSQL
1func (queries *Queries) AddBoat(ctx context.Context, params AddBoatParams) error
1func (queries *Queries) AddBoat(ctx context.Context, params AddBoatParams) error
2
2
3type AddBoatParams struct {
3type AddBoatParams struct {
4 ID uint64
5 Name string
4 Name string
6 Status string
5 Status BoatStatus
7}
6}
7
8type BoatStatus string
ListBoatsdiffers
ClickHouse
PostgreSQL
1func (queries *Queries) ListBoats(ctx context.Context) ([]ListBoatsRow, error)
1func (queries *Queries) ListBoats(ctx context.Context) ([]ListBoatsRow, error)
2
2
3type ListBoatsRow struct {
3type ListBoatsRow struct {
4 ID uint64 `json:"id"`
4 Name string `json:"name"`
5 Name string `json:"name"`
5 Status BoatStatus `json:"status"`
6 Status BoatsStatus `json:"status"`
7}
6}
8
7
9type BoatsStatus string
8type BoatStatus string
BoatsNotInonly ClickHouse
ClickHouse
PostgreSQL · 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}
SetStatusonly PostgreSQL
ClickHouse · not in this version
PostgreSQL
1func (queries *Queries) SetStatus(ctx context.Context, params SetStatusParams) (int64, error)
2
3type SetStatusParams struct {
4 Status BoatStatus
5 Name string
6}
7
8type BoatStatus string
BoatsWithStatusonly PostgreSQL
ClickHouse · not in this version
PostgreSQL
1func (queries *Queries) BoatsWithStatus(ctx context.Context, status BoatStatus) ([]BoatsWithStatusRow, error)
2
3type BoatStatus string
4
5type BoatsWithStatusRow struct {
6 Name string `json:"name"`
7 Status harbour.Status `json:"status"`
8}

The program

example.godiffers
ClickHouse
PostgreSQL
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/postgres/007_enums/internal/store"
11 "seagull.sh/seagull/examples/clickhouse/007_enums/internal/store"
12)
11)
13
12
14// run berths four boats and lists them twice. boats.status is an Enum8, and Seagull
13// run gives the harbour's boats a status from the boat_status enum and lists them twice.
15// declares a named Go type for it in models.go, named after the table and column that
14// The enum becomes a named Go type in the generated package: models.go declares
16// hold it: store.BoatsStatus, with a constant for each label, such as
15// store.BoatStatus, named after the enum type, with a constant for each label, such as
17// store.BoatsStatusMoored, and a Valid method. ListBoats returns Status as a
16// store.BoatStatusMoored, and a Valid method. Every column and parameter of the enum
18// store.BoatsStatus. The status parameters are written {status:String}, so they are plain
17// takes it, in models.go, in Params structs and in Row structs, as ListBoats shows. Valid
19// strings, and the server converts the label to the Enum8.
18// reports whether a value is one of the labels, and the server still enforces them, so a
20//
19// status outside the enum is refused when the statement runs.
21// BoatsNotIn adds sgl.column(status, go_type: "…/internal/domain.Status"), so its rows
22// carry the harbour's own domain.Status type, with its constants and methods. The
23// override changes only that query's result rows: parameters stay string, so the example
24// converts with string(domain.StatusInDock) on the way in. A types.overrides entry in
25// sgl.yaml would change every query's rows the same way.
26//
20//
27// ClickHouse checks every label against the Enum8, so a status that is not one of its
21// For a type of your own, here harbour.Status with a method that describes each label,
28// labels is refused by the server, not stored.
22// name it with a go_type override. BoatsWithStatus does that for one query with
23// sgl.column(status, go_type: ...); a types.overrides entry in sgl.yaml, naming the enum
24// type boat_status or the column boats.status, would do it for every query, and for the
25// parameters bound to the column too. A query's sgl.column override changes only that
26// query's returned column, so AddBoat, SetStatus and BoatsWithStatus's own parameter
27// still take the generated store.BoatStatus.
29//
28//
30// Takes database (*sql.DB) which is a migrated database.
29// Takes database (*sql.DB) which is a migrated database.
31// Takes output (io.Writer) which receives both listings and the refusal.
30// Takes output (io.Writer) which receives both listings.
32//
31//
33// Returns error when a query fails or a bad status is accepted.
32// Returns error when a query fails or a status outside the enum is accepted.
34func run(ctx context.Context, database *sql.DB, output io.Writer) error {
33func run(ctx context.Context, database *sql.DB, output io.Writer) error {
35 queries := store.New(database)
34 queries := store.New(database)
36
35
37 boats := []store.AddBoatParams{
36 boats := []store.AddBoatParams{
38 {ID: 1, Name: "Kittiwake", Status: string(domain.StatusMoored)},
37 {Name: "Kittiwake", Status: store.BoatStatusMoored},
39 {ID: 2, Name: "Salty Chip", Status: string(domain.StatusFishing)},
38 {Name: "Salty Chip", Status: store.BoatStatusAtSea},
40 {ID: 3, Name: "Gull Wing", Status: string(domain.StatusInDock)},
39 {Name: "Gull Wing", Status: store.BoatStatusDryDock},
41 {ID: 4, Name: "Driftwood", Status: string(domain.StatusFishing)},
40 {Name: "Driftwood", Status: store.BoatStatusMoored},
42 }
41 }
43 for _, boat := range boats {
42 for _, boat := range boats {
44 if err := queries.AddBoat(ctx, boat); err != nil {
43 if err := queries.AddBoat(ctx, boat); err != nil {
45 return fmt.Errorf("adding %s: %w", boat.Name, err)
44 return fmt.Errorf("adding %s: %w", boat.Name, err)
46 }
45 }
47 }
46 }
48
47
48 sunk := store.BoatStatus("sunk")
49 if err := queries.AddBoat(ctx, store.AddBoatParams{Name: "Wreck", Status: sunk}); err == nil {
50 return errors.New("adding a boat with status sunk: got no error, want the enum to refuse it")
51 }
52 fmt.Fprintf(output, "status %q: Valid() is %t, and the server refuses it, as it is not a boat_status label\n", sunk, sunk.Valid())
53
54 if _, err := queries.SetStatus(ctx, store.SetStatusParams{Status: store.BoatStatusAtSea, Name: "Kittiwake"}); err != nil {
55 return fmt.Errorf("sending Kittiwake to sea: %w", err)
56 }
57
49 listed, err := queries.ListBoats(ctx)
58 listed, err := queries.ListBoats(ctx)
50 if err != nil {
59 if err != nil {
51 return fmt.Errorf("listing boats: %w", err)
60 return fmt.Errorf("listing boats: %w", err)
52 }
61 }
53 fmt.Fprintln(output, "all boats, status as store.BoatsStatus:")
62 fmt.Fprintln(output, "\nListBoats, where status is the generated store.BoatStatus:")
54 for _, boat := range listed {
63 for _, boat := range listed {
55 fmt.Fprintf(output, " %d %-11s %s\n", boat.ID, boat.Name, boat.Status)
64 fmt.Fprintf(output, " %-10s %-8s %T\n", boat.Name, boat.Status, boat.Status)
56 }
65 }
57
66
58 afloat, err := queries.BoatsNotIn(ctx, string(domain.StatusInDock))
67 atSea, err := queries.BoatsWithStatus(ctx, store.BoatStatusAtSea)
59 if err != nil {
68 if err != nil {
60 return fmt.Errorf("listing boats not in dock: %w", err)
69 return fmt.Errorf("listing boats at sea: %w", err)
61 }
62 fmt.Fprintln(output, "not in dock, status as domain.Status:")
63 for _, boat := range afloat {
64 fmt.Fprintf(output, " %d %-11s %-8s at sea: %t\n", boat.ID, boat.Name, boat.Status, boat.Status.AtSea())
65 }
66
67 sunk := store.AddBoatParams{ID: 5, Name: "Old Barnacle", Status: "sunk"}
68 err = queries.AddBoat(ctx, sunk)
69 if err == nil {
70 return fmt.Errorf("adding status %q: got no error, want the server's refusal", sunk.Status)
71 }
70 }
72 if !strings.Contains(err.Error(), "Unknown element 'sunk' for enum") {
71 fmt.Fprintln(output, "\nBoatsWithStatus, where a go_type override makes status a harbour.Status:")
73 return fmt.Errorf("adding status %q: got %w, want the server's refusal", sunk.Status, err)
72 for _, boat := range atSea {
73 fmt.Fprintf(output, " %-10s %-14s %T\n", boat.Name, boat.Status.Describe(), boat.Status)
74 }
74 }
75 fmt.Fprintf(output, "status %q refused: not one of the Enum8 labels\n", sunk.Status)
76 return nil
75 return nil
77}
76}

Go type differences

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

QueryFieldClickHousePostgreSQL
AddBoatparameter statusstringBoatStatus
ListBoatscolumn statusBoatsStatusBoatStatus