Compare · 030

Functions in migrations: ClickHouse and TimescaleDB

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

Compare ClickHouse withPostgreSQLTimescaleDBMySQLMariaDB

Configuration

sgl.yamldiffers
ClickHouse
TimescaleDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: chippy
4 - name: market
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
12 migrate:
13 dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_menu.up.sqldiffers
ClickHouse
TimescaleDB · db/migrations/0001_catches.up.sql
1CREATE TABLE menu (
1CREATE TABLE catches (
2 id UInt64,
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 dish String,
3 boat TEXT NOT NULL,
4 price_pence UInt32
4 species TEXT NOT NULL,
5) ENGINE = MergeTree() ORDER BY id;
5 weight_grams INTEGER NOT NULL
6);
7
8CREATE TABLE prices (
9 species TEXT PRIMARY KEY,
10 pence_per_kilo INTEGER NOT NULL
11);
db/migrations/0001_menu.down.sqldiffers
ClickHouse
TimescaleDB · db/migrations/0001_catches.down.sql
1DROP TABLE menu;
1DROP TABLE prices;
2DROP TABLE catches;
db/migrations/0002_with_service.up.sqldiffers
ClickHouse
TimescaleDB · db/migrations/0002_market_functions.up.sql
1CREATE OR REPLACE FUNCTION with_service AS pence -> intDiv(pence * 110, 100);
1CREATE FUNCTION price_pence(weight_grams INTEGER, pence_per_kilo INTEGER) RETURNS INTEGER
2LANGUAGE sql IMMUTABLE STRICT
3AS $$ SELECT weight_grams * pence_per_kilo / 1000 $$;
4
5CREATE FUNCTION boat_haul(boat_name TEXT)
6RETURNS TABLE (species TEXT, weight_grams INTEGER)
7LANGUAGE sql STABLE
8AS $$ SELECT species, weight_grams FROM catches WHERE boat = boat_name $$;
9
10CREATE FUNCTION land_catch(boat_name TEXT, caught TEXT, grams INTEGER) RETURNS BIGINT
11LANGUAGE plpgsql VOLATILE
12AS $$
13DECLARE
14 landed BIGINT;
15BEGIN
16 INSERT INTO catches (boat, species, weight_grams) VALUES (boat_name, caught, grams)
17 RETURNING id INTO landed;
18 RETURN landed;
19END;
20$$;
21
22-- sgl.migration(readonly: true)
23CREATE FUNCTION species_price(wanted TEXT) RETURNS INTEGER
24LANGUAGE sql VOLATILE
25AS $$ SELECT pence_per_kilo FROM prices WHERE species = wanted $$;
db/migrations/0002_with_service.down.sqldiffers
ClickHouse
TimescaleDB · db/migrations/0002_market_functions.down.sql
1DROP FUNCTION IF EXISTS with_service;
1DROP FUNCTION species_price(TEXT);
2DROP FUNCTION land_catch(TEXT, TEXT, INTEGER);
3DROP FUNCTION boat_haul(TEXT);
4DROP FUNCTION price_pence(INTEGER, INTEGER);

Queries

AddDishonly ClickHouse
ClickHouse · db/queries/menu.sql
TimescaleDB · not in this version
1-- sgl.query(AddDish, exec)
2INSERT INTO menu (id, dish, price_pence) VALUES ({id:UInt64}, {dish:String}, {price_pence:UInt32});
MenuWithServiceonly ClickHouse
ClickHouse · db/queries/menu.sql
TimescaleDB · not in this version
1-- sgl.query(MenuWithService, many)
2SELECT dish, price_pence, with_service(price_pence) AS total_pence
3FROM menu
4ORDER BY id;
SetPriceonly TimescaleDB
ClickHouse · not in this version
TimescaleDB · db/queries/market.sql
1-- sgl.query(SetPrice, exec)
2INSERT INTO prices (species, pence_per_kilo) VALUES ($1, $2);
LandCatchonly TimescaleDB
ClickHouse · not in this version
TimescaleDB · db/queries/market.sql
1-- sgl.query(LandCatch, one)
2SELECT land_catch(:boat, :species, :grams) AS id;
SpeciesPriceonly TimescaleDB
ClickHouse · not in this version
TimescaleDB · db/queries/market.sql
1-- sgl.query(SpeciesPrice, one)
2SELECT species_price(:species) AS pence_per_kilo;
BoatHaulonly TimescaleDB
ClickHouse · not in this version
TimescaleDB · db/queries/market.sql
1-- sgl.query(BoatHaul, many)
2SELECT haul.species, haul.weight_grams, price_pence(haul.weight_grams, prices.pence_per_kilo) AS value_pence
3FROM boat_haul(:boat) AS haul
4JOIN prices ON prices.species = haul.species
5ORDER BY haul.species;

What sgl generates

AddDishonly ClickHouse
ClickHouse
TimescaleDB · not in this version
1func (queries *Queries) AddDish(ctx context.Context, params AddDishParams) error
2
3type AddDishParams struct {
4 ID uint64
5 Dish string
6 PricePence uint32
7}
MenuWithServiceonly ClickHouse
ClickHouse
TimescaleDB · not in this version
1func (queries *Queries) MenuWithService(ctx context.Context) ([]MenuWithServiceRow, error)
2
3type MenuWithServiceRow struct {
4 Dish string `json:"dish"`
5 PricePence uint32 `json:"price_pence"`
6 TotalPence uint64 `json:"total_pence"`
7}
SetPriceonly TimescaleDB
ClickHouse · not in this version
TimescaleDB
1func (queries *Queries) SetPrice(ctx context.Context, params SetPriceParams) error
2
3type SetPriceParams struct {
4 Species string
5 PencePerKilo int32
6}
LandCatchonly TimescaleDB
ClickHouse · not in this version
TimescaleDB
1func (queries *Queries) LandCatch(ctx context.Context, params LandCatchParams) (LandCatchRow, error)
2
3type LandCatchParams struct {
4 Boat string
5 Species string
6 Grams int32
7}
8
9type LandCatchRow struct {
10 ID *int64 `json:"id"`
11}
SpeciesPriceonly TimescaleDB
ClickHouse · not in this version
TimescaleDB
1func (queries *Queries) SpeciesPrice(ctx context.Context, species string) (SpeciesPriceRow, error)
2
3type SpeciesPriceRow struct {
4 PencePerKilo *int32 `json:"pence_per_kilo"`
5}
BoatHaulonly TimescaleDB
ClickHouse · not in this version
TimescaleDB
1func (queries *Queries) BoatHaul(ctx context.Context, boat string) ([]BoatHaulRow, error)
2
3type BoatHaulRow struct {
4 Species *string `json:"species"`
5 WeightGrams *int32 `json:"weight_grams"`
6 ValuePence *int32 `json:"value_pence"`
7}

The program

example.godiffers
ClickHouse
TimescaleDB
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/clickhouse/030_functions_in_migrations/internal/store"
9 "seagull.sh/seagull/examples/postgres/030_functions_in_migrations/internal/store"
10)
10)
11
11
12// run prices the chip shop's menu with a service charge worked out by a SQL function that
12// connection is a store.DBTX that notes its name before each statement, so the example
13// a migration defines. In ClickHouse a SQL function is a lambda, CREATE FUNCTION
13// can say whether Seagull sent a query to the writer or the reader. Both connections wrap
14// with_service AS pence -> intDiv(pence * 110, 100), and it belongs to the server rather
14// the same database here; concept 24 puts the reader on a replica.
15// than to one database, so the migration says CREATE OR REPLACE and the down migration
15type connection struct {
16// drops it. A lambda can only compute from its arguments; it cannot read or write a
16 *sql.DB
17// table, so a query that calls one stays read-only and there is no volatility to declare.
17
18 // last receives the name of the connection that ran the latest statement.
19 last *string
20
21 // name labels the connection.
22 name string
23}
24
25// ExecContext notes the connection, then runs the statement on the database.
18//
26//
19// Seagull reads the lambda from the migration and types each call from its body, with the
27// Takes query (string) which is the SQL to run.
20// argument in place of pence. price_pence is a UInt32; ClickHouse multiplies it by 110
28// Takes arguments (...any) which are its bind values.
21// into a UInt64, the next wider type, and intDiv keeps that, so TotalPence is a uint64,
22// as the server returns. The call reads no table, so the query stays a read, and a store
23// built with NewWithReplica sends it to the reader.
24//
29//
30// Returns sql.Result which is the database's result.
31// Returns error when the statement fails.
32func (c connection) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
33 *c.last = c.name
34 return c.DB.ExecContext(ctx, query, arguments...)
35}
36
37// QueryContext notes the connection, then runs the query on the database.
38//
39// Takes query (string) which is the SQL to run.
40// Takes arguments (...any) which are its bind values.
41//
42// Returns *sql.Rows which holds the result rows.
43// Returns error when the query fails.
44func (c connection) QueryContext(ctx context.Context, query string, arguments ...any) (*sql.Rows, error) {
45 *c.last = c.name
46 return c.DB.QueryContext(ctx, query, arguments...)
47}
48
49// QueryRowContext notes the connection, then runs the query on the database.
50//
51// Takes query (string) which is the SQL to run.
52// Takes arguments (...any) which are its bind values.
53//
54// Returns *sql.Row which holds the result row or the error.
55func (c connection) QueryRowContext(ctx context.Context, query string, arguments ...any) *sql.Row {
56 *c.last = c.name
57 return c.DB.QueryRowContext(ctx, query, arguments...)
58}
59
60// run prices the fish, lands three catches and reads the market back, all through the SQL
61// functions that migration 0002 creates. Seagull reads each CREATE FUNCTION when it
62// builds the schema: the argument and return types type the queries that call it, and
63// whether the function may change data decides whether those queries are read-only, which
64// picks the writer or the reader. Volatility does not decide it.
65//
66// - land_catch inserts a row, so LandCatch goes to the writer;
67// - price_pence and boat_haul only read data, so BoatHaul, which calls both, goes to
68// the reader. boat_haul RETURNS TABLE, so it is a relation with typed columns, and
69// its $1 is typed from the function's TEXT argument;
70// - species_price is declared VOLATILE, but its body only reads, so SpeciesPrice goes
71// to the reader. The sgl.migration(readonly: true) line above it in the migration
72// states that outright, as a function whose body Seagull cannot read needs.
73//
74// A function's result can be NULL, so the columns are pointers: species_price finds no
75// row for a fish with no price. price_pence is STRICT, so a NULL argument gives a NULL
76// result; here its arguments come from boat_haul, which may return NULLs.
77//
25// Takes database (*sql.DB) which is a migrated database.
78// Takes database (*sql.DB) which is a migrated database.
26// Takes output (io.Writer) which receives the priced menu.
79// Takes output (io.Writer) which receives each call's connection and result.
27//
80//
28// Returns error when a query fails.
81// Returns error when a query fails.
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
82func run(ctx context.Context, database *sql.DB, output io.Writer) error {
30 queries := store.New(database)
83 var last string
84 queries := store.NewWithReplica(
85 connection{DB: database, last: &last, name: "writer"},
86 connection{DB: database, last: &last, name: "reader"},
87 )
31
88
32 dishes := []store.AddDishParams{
89 prices := []store.SetPriceParams{
33 {ID: 1, Dish: "cod and chips", PricePence: 850},
90 {Species: "crab", PencePerKilo: 1200},
34 {ID: 2, Dish: "haddock and chips", PricePence: 920},
91 {Species: "mackerel", PencePerKilo: 450},
35 {ID: 3, Dish: "mushy peas", PricePence: 150},
92 {Species: "pollock", PencePerKilo: 800},
36 }
93 }
37 for _, dish := range dishes {
94 for _, price := range prices {
38 if err := queries.AddDish(ctx, dish); err != nil {
95 if err := queries.SetPrice(ctx, price); err != nil {
39 return fmt.Errorf("adding %s: %w", dish.Dish, err)
96 return fmt.Errorf("pricing %s: %w", price.Species, err)
40 }
97 }
41 }
98 }
42
99
43 menu, err := queries.MenuWithService(ctx)
100 catches := []store.LandCatchParams{
101 {Boat: "Kittiwake", Species: "mackerel", Grams: 12000},
102 {Boat: "Kittiwake", Species: "crab", Grams: 3500},
103 {Boat: "Puffin", Species: "pollock", Grams: 8000},
104 }
105 for _, catch := range catches {
106 landed, err := queries.LandCatch(ctx, catch)
107 if err != nil {
108 return fmt.Errorf("landing %s: %w", catch.Species, err)
109 }
110 fmt.Fprintf(output, "LandCatch on the %s: catch %s, %dg of %s from the %s\n", last, orNull(landed.ID), catch.Grams, catch.Species, catch.Boat)
111 }
112
113 for _, species := range []string{"crab", "halibut"} {
114 price, err := queries.SpeciesPrice(ctx, species)
115 if err != nil {
116 return fmt.Errorf("pricing %s: %w", species, err)
117 }
118 if price.PencePerKilo == nil {
119 fmt.Fprintf(output, "SpeciesPrice on the %s: %s has no price\n", last, species)
120 continue
121 }
122 fmt.Fprintf(output, "SpeciesPrice on the %s: %s fetches %dp a kilo\n", last, species, *price.PencePerKilo)
123 }
124
125 haul, err := queries.BoatHaul(ctx, "Kittiwake")
44 if err != nil {
126 if err != nil {
45 return fmt.Errorf("pricing the menu: %w", err)
127 return fmt.Errorf("reading the Kittiwake's haul: %w", err)
46 }
128 }
47 for _, dish := range menu {
129 fmt.Fprintf(output, "BoatHaul on the %s: the Kittiwake's haul\n", last)
48 fmt.Fprintf(output, "%-18s %4dp, with service %dp\n", dish.Dish, dish.PricePence, dish.TotalPence)
130 for _, row := range haul {
131 fmt.Fprintf(output, " %-9s %6sg %5sp\n", orNull(row.Species), orNull(row.WeightGrams), orNull(row.ValuePence))
49 }
132 }
50 return nil
133 return nil
51}
134}
135
136// orNull formats a nullable field, or returns NULL when the field is nil.
137//
138// Takes value (*T) which is a nullable field.
139//
140// Returns string which is the text to print.
141func orNull[T any](value *T) string {
142 if value == nil {
143 return "NULL"
144 }
145 return fmt.Sprint(*value)
146}