Compare · 009

Type overrides: TimescaleDB and PostgreSQL

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

Compare TimescaleDB withSQLitePostgreSQLCockroachDB

Configuration

sgl.yamlidentical
TimescaleDB
PostgreSQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: menu
4 - name: menu
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
12 types:
12 types:
13 decimal: string
13 decimal: string
14 overrides:
14 overrides:
15 - column: menu_items.status
15 - column: menu_items.status
16 go: seagull.sh/seagull/examples/postgres/009_overrides/internal/menu.Status
16 go: seagull.sh/seagull/examples/postgres/009_overrides/internal/menu.Status

Migrations

db/migrations/0001_menu.up.sqlidentical
TimescaleDB
PostgreSQL
1-- sgl.column(menu_items.price, go_type: "seagull.sh/seagull/examples/postgres/009_overrides/internal/menu.Price")
1-- sgl.column(menu_items.price, go_type: "seagull.sh/seagull/examples/postgres/009_overrides/internal/menu.Price")
2CREATE TABLE menu_items (
2CREATE TABLE menu_items (
3 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
4 name TEXT NOT NULL,
4 name TEXT NOT NULL,
5 section TEXT NOT NULL,
5 section TEXT NOT NULL,
6 status TEXT NOT NULL CHECK (status IN ('available', 'sold_out')),
6 status TEXT NOT NULL CHECK (status IN ('available', 'sold_out')),
7 price NUMERIC(5, 2) NOT NULL,
7 price NUMERIC(5, 2) NOT NULL,
8 nutrition JSONB NOT NULL
8 nutrition JSONB NOT NULL
9);
9);
db/migrations/0001_menu.down.sqlidentical
TimescaleDB
PostgreSQL
1DROP TABLE menu_items;
1DROP TABLE menu_items;

Queries

AddItemidentical
TimescaleDB · db/queries/menu.sql
PostgreSQL · db/queries/menu.sql
1-- sgl.query(AddItem, exec)
1-- sgl.query(AddItem, exec)
2INSERT INTO menu_items (name, section, status, price, nutrition) VALUES ($1, $2, $3, $4, $5);
2INSERT INTO menu_items (name, section, status, price, nutrition) VALUES ($1, $2, $3, $4, $5);
ListMenuidentical
TimescaleDB · db/queries/menu.sql
PostgreSQL · db/queries/menu.sql
1-- sgl.query(ListMenu, many)
1-- sgl.query(ListMenu, many)
2-- sgl.column(kcal, type: bigint, nullable: false)
2-- sgl.column(kcal, type: bigint, nullable: false)
3SELECT name, status, price, nutrition ->> 'kcal' AS kcal
3SELECT name, status, price, nutrition ->> 'kcal' AS kcal
4FROM menu_items
4FROM menu_items
5ORDER BY section, name;
5ORDER BY section, name;
CheapestPerSectionidentical
TimescaleDB · db/queries/menu.sql
PostgreSQL · db/queries/menu.sql
1-- sgl.query(CheapestPerSection, many)
1-- sgl.query(CheapestPerSection, many)
2-- sgl.column(cheapest, go_type: "seagull.sh/seagull/examples/postgres/009_overrides/internal/menu.Price", nullable: false)
2-- sgl.column(cheapest, go_type: "seagull.sh/seagull/examples/postgres/009_overrides/internal/menu.Price", nullable: false)
3SELECT section, MIN(price) AS cheapest
3SELECT section, MIN(price) AS cheapest
4FROM menu_items
4FROM menu_items
5GROUP BY section
5GROUP BY section
6ORDER BY section;
6ORDER BY section;

What sgl generates

AddItemidentical
TimescaleDB
PostgreSQL
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 string
5 Section string
6 Status menu.Status
6 Status menu.Status
7 Price menu.Price
7 Price menu.Price
8 Nutrition JSON
8 Nutrition JSON
9}
9}
10
10
11type JSON []byte
11type JSON []byte
ListMenuidentical
TimescaleDB
PostgreSQL
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 Status menu.Status `json:"status"`
5 Status menu.Status `json:"status"`
6 Price menu.Price `json:"price"`
6 Price menu.Price `json:"price"`
7 Kcal int64 `json:"kcal"`
7 Kcal int64 `json:"kcal"`
8}
8}
CheapestPerSectionidentical
TimescaleDB
PostgreSQL
1func (queries *Queries) CheapestPerSection(ctx context.Context) ([]CheapestPerSectionRow, error)
1func (queries *Queries) CheapestPerSection(ctx context.Context) ([]CheapestPerSectionRow, error)
2
2
3type CheapestPerSectionRow struct {
3type CheapestPerSectionRow struct {
4 Section string `json:"section"`
4 Section string `json:"section"`
5 Cheapest menu.Price `json:"cheapest"`
5 Cheapest menu.Price `json:"cheapest"`
6}
6}

The program

example.goidentical
TimescaleDB
PostgreSQL
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "database/sql"
6 "fmt"
6 "fmt"
7 "io"
7 "io"
8
8
9 "seagull.sh/seagull/examples/postgres/009_overrides/internal/menu"
9 "seagull.sh/seagull/examples/postgres/009_overrides/internal/menu"
10 "seagull.sh/seagull/examples/postgres/009_overrides/internal/store"
10 "seagull.sh/seagull/examples/postgres/009_overrides/internal/store"
11)
11)
12
12
13// run fills a chip shop menu and reads it back. Five overrides replace the Go types
13// run fills a chip shop menu and reads it back. Five overrides replace the Go types
14// Seagull would infer:
14// Seagull would infer:
15//
15//
16// - sgl.yaml's types.decimal slot maps every NUMERIC to string, the server's exact
16// - sgl.yaml's types.decimal slot maps every NUMERIC to string, the server's exact
17// text, in place of apd.Decimal, so the module needs no decimal package; a slot
17// text, in place of apd.Decimal, so the module needs no decimal package; a slot
18// changes models.go and parameters as well as returned columns;
18// changes models.go and parameters as well as returned columns;
19// - sgl.yaml's types.overrides gives menu_items.status the type menu.Status;
19// - sgl.yaml's types.overrides gives menu_items.status the type menu.Status;
20// - sgl.column(menu_items.price, go_type: ...) in the migration gives price the type
20// - sgl.column(menu_items.price, go_type: ...) in the migration gives price the type
21// menu.Price in every query that returns the column as it is;
21// menu.Price in every query that returns the column as it is;
22// - sgl.column(kcal, type: bigint, nullable: false) in ListMenu types nutrition ->>
22// - sgl.column(kcal, type: bigint, nullable: false) in ListMenu types nutrition ->>
23// 'kcal', which is nullable text, as the number every document holds; a cast,
23// 'kcal', which is nullable text, as the number every document holds; a cast,
24// (nutrition ->> 'kcal')::bigint, would type it too, and make the server check it;
24// (nutrition ->> 'kcal')::bigint, would type it too, and make the server check it;
25// - in CheapestPerSection, MIN(price) is a new value, so the migration's override no
25// - in CheapestPerSection, MIN(price) is a new value, so the migration's override no
26// longer applies and the aggregate could be NULL; sgl.column(cheapest, go_type: ...,
26// longer applies and the aggregate could be NULL; sgl.column(cheapest, go_type: ...,
27// nullable: false) makes it menu.Price again, as every group has a row.
27// nullable: false) makes it menu.Price again, as every group has a row.
28//
28//
29// Column overrides also reach models.go and the parameters bound to those columns, so
29// Column overrides also reach models.go and the parameters bound to those columns, so
30// AddItemParams takes a menu.Status and a menu.Price.
30// AddItemParams takes a menu.Status and a menu.Price.
31//
31//
32// sgl vet catches mistakes in overrides: Q036 for a query override naming a column the
32// sgl vet catches mistakes in overrides: Q036 for a query override naming a column the
33// query does not return, Q037 for an override naming a table column that does not exist,
33// query does not return, Q037 for an override naming a table column that does not exist,
34// Q038 for type: and go_type: together, and Q049 for a go_type the generator cannot use.
34// Q038 for type: and go_type: together, and Q049 for a go_type the generator cannot use.
35//
35//
36// Takes database (*sql.DB) which is a migrated database.
36// Takes database (*sql.DB) which is a migrated database.
37// Takes output (io.Writer) which receives the menu and the cheapest item per section.
37// Takes output (io.Writer) which receives the menu and the cheapest item per section.
38//
38//
39// Returns error when a query fails.
39// Returns error when a query fails.
40func run(ctx context.Context, database *sql.DB, output io.Writer) error {
40func run(ctx context.Context, database *sql.DB, output io.Writer) error {
41 queries := store.New(database)
41 queries := store.New(database)
42
42
43 items := []store.AddItemParams{
43 items := []store.AddItemParams{
44 {Name: "Cod and chips", Section: "fish", Status: menu.Available, Price: "9.50", Nutrition: store.JSON(`{"kcal": 1050}`)},
44 {Name: "Cod and chips", Section: "fish", Status: menu.Available, Price: "9.50", Nutrition: store.JSON(`{"kcal": 1050}`)},
45 {Name: "Haddock and chips", Section: "fish", Status: menu.SoldOut, Price: "10.50", Nutrition: store.JSON(`{"kcal": 980}`)},
45 {Name: "Haddock and chips", Section: "fish", Status: menu.SoldOut, Price: "10.50", Nutrition: store.JSON(`{"kcal": 980}`)},
46 {Name: "Chips", Section: "sides", Status: menu.Available, Price: "3.50", Nutrition: store.JSON(`{"kcal": 560}`)},
46 {Name: "Chips", Section: "sides", Status: menu.Available, Price: "3.50", Nutrition: store.JSON(`{"kcal": 560}`)},
47 {Name: "Mushy peas", Section: "sides", Status: menu.Available, Price: "1.75", Nutrition: store.JSON(`{"kcal": 120}`)},
47 {Name: "Mushy peas", Section: "sides", Status: menu.Available, Price: "1.75", Nutrition: store.JSON(`{"kcal": 120}`)},
48 }
48 }
49 for _, item := range items {
49 for _, item := range items {
50 if err := queries.AddItem(ctx, item); err != nil {
50 if err := queries.AddItem(ctx, item); err != nil {
51 return fmt.Errorf("adding %s: %w", item.Name, err)
51 return fmt.Errorf("adding %s: %w", item.Name, err)
52 }
52 }
53 }
53 }
54
54
55 listed, err := queries.ListMenu(ctx)
55 listed, err := queries.ListMenu(ctx)
56 if err != nil {
56 if err != nil {
57 return fmt.Errorf("listing the menu: %w", err)
57 return fmt.Errorf("listing the menu: %w", err)
58 }
58 }
59 for _, item := range listed {
59 for _, item := range listed {
60 if item.Status == menu.SoldOut {
60 if item.Status == menu.SoldOut {
61 fmt.Fprintf(output, "%-18s sold out\n", item.Name)
61 fmt.Fprintf(output, "%-18s sold out\n", item.Name)
62 continue
62 continue
63 }
63 }
64 fmt.Fprintf(output, "%-18s %6s %4d kcal\n", item.Name, item.Price, item.Kcal)
64 fmt.Fprintf(output, "%-18s %6s %4d kcal\n", item.Name, item.Price, item.Kcal)
65 }
65 }
66
66
67 cheapest, err := queries.CheapestPerSection(ctx)
67 cheapest, err := queries.CheapestPerSection(ctx)
68 if err != nil {
68 if err != nil {
69 return fmt.Errorf("finding the cheapest items: %w", err)
69 return fmt.Errorf("finding the cheapest items: %w", err)
70 }
70 }
71 for _, section := range cheapest {
71 for _, section := range cheapest {
72 fmt.Fprintf(output, "cheapest %-6s from %s\n", section.Section, section.Cheapest)
72 fmt.Fprintf(output, "cheapest %-6s from %s\n", section.Section, section.Cheapest)
73 }
73 }
74 return nil
74 return nil
75}
75}