Compare · 030

Functions in migrations: PostgreSQL and MariaDB

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

Compare PostgreSQL withTimescaleDB MySQL MariaDB ClickHouse

Configuration

sgl.yamldiffers

PostgreSQL
MariaDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: market
4  - name: market
5    dialect: postgres
5    dialect: mysql
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_catches.up.sqldiffers

PostgreSQL
MariaDB
1CREATE TABLE catches (
1CREATE TABLE catches (
2    id           BIGINT  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
2    id           BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
3    boat         TEXT    NOT NULL,
3    boat         VARCHAR(50) NOT NULL,
4    species      TEXT    NOT NULL,
4    species      VARCHAR(50) NOT NULL,
5    weight_grams INTEGER NOT NULL
5    weight_grams INT         NOT NULL
6);
6);
7
7
8CREATE TABLE prices (
8CREATE TABLE prices (
9    species        TEXT    PRIMARY KEY,
9    species        VARCHAR(50) NOT NULL PRIMARY KEY,
10    pence_per_kilo INTEGER NOT NULL
10    pence_per_kilo INT         NOT NULL
11);
11);

db/migrations/0001_catches.down.sqlidentical

PostgreSQL
MariaDB
1DROP TABLE prices;
1DROP TABLE prices;
2DROP TABLE catches;
2DROP TABLE catches;

db/migrations/0002_market_functions.up.sqldiffers

PostgreSQL
MariaDB
1CREATE FUNCTION price_pence(weight_grams INTEGER, pence_per_kilo INTEGER) RETURNS INTEGER
1CREATE FUNCTION price_pence(weight_grams INT, pence_per_kilo INT) RETURNS INT
2LANGUAGE sql IMMUTABLE STRICT
2DETERMINISTIC NO SQL
3AS $$ SELECT weight_grams * pence_per_kilo / 1000 $$;
3RETURN 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
4
10CREATE FUNCTION land_catch(boat_name TEXT, caught TEXT, grams INTEGER) RETURNS BIGINT
5CREATE FUNCTION land_catch(boat_name VARCHAR(50), caught VARCHAR(50), grams INT) RETURNS BIGINT
11LANGUAGE plpgsql VOLATILE
6DETERMINISTIC MODIFIES SQL DATA
12AS $$
13DECLARE
14    landed BIGINT;
15BEGIN
7BEGIN
16    INSERT INTO catches (boat, species, weight_grams) VALUES (boat_name, caught, grams)
8    INSERT INTO catches (boat, species, weight_grams) VALUES (boat_name, caught, grams);
17    RETURNING id INTO landed;
9    RETURN LAST_INSERT_ID();
18    RETURN landed;
19END;
10END;
20$$;
21
11
22-- sgl.migration(readonly: true)
12-- sgl.migration(readonly: true)
23CREATE FUNCTION species_price(wanted TEXT) RETURNS INTEGER
13CREATE FUNCTION species_price(wanted VARCHAR(50)) RETURNS INT
24LANGUAGE sql VOLATILE
14DETERMINISTIC MODIFIES SQL DATA
25AS $$ SELECT pence_per_kilo FROM prices WHERE species = wanted $$;
15RETURN (SELECT pence_per_kilo FROM prices WHERE species = wanted);

db/migrations/0002_market_functions.down.sqldiffers

PostgreSQL
MariaDB
1DROP FUNCTION species_price(TEXT);
1DROP FUNCTION species_price;
2DROP FUNCTION land_catch(TEXT, TEXT, INTEGER);
2DROP FUNCTION land_catch;
3DROP FUNCTION boat_haul(TEXT);
3DROP FUNCTION price_pence;
4DROP FUNCTION price_pence(INTEGER, INTEGER);

Queries

SetPricediffers

PostgreSQL · db/queries/market.sql
MariaDB · db/queries/market.sql
1-- sgl.query(SetPrice, exec)
1-- sgl.query(SetPrice, exec)
2INSERT INTO prices (species, pence_per_kilo) VALUES ($1, $2);
2INSERT INTO prices (species, pence_per_kilo) VALUES (?, ?);

LandCatchdiffers

PostgreSQL · db/queries/market.sql
MariaDB · db/queries/market.sql
1-- sgl.query(LandCatch, one)
1-- sgl.query(LandCatch, one)
2SELECT land_catch(:boat, :species, :grams) AS id;
2-- ?1 as sgl.param(boat)
3-- ?2 as sgl.param(species)
4-- ?3 as sgl.param(grams)
5SELECT land_catch(?1, ?2, ?3) AS id;

SpeciesPricediffers

PostgreSQL · db/queries/market.sql
MariaDB · db/queries/market.sql
1-- sgl.query(SpeciesPrice, one)
1-- sgl.query(SpeciesPrice, one)
2SELECT species_price(:species) AS pence_per_kilo;
2-- ?1 as sgl.param(species)
3SELECT species_price(?1) AS pence_per_kilo;

BoatHauldiffers

PostgreSQL · db/queries/market.sql
MariaDB · db/queries/market.sql
1-- sgl.query(BoatHaul, many)
1-- sgl.query(BoatHaul, many)
2SELECT haul.species, haul.weight_grams, price_pence(haul.weight_grams, prices.pence_per_kilo) AS value_pence
2SELECT catches.species, catches.weight_grams, price_pence(catches.weight_grams, prices.pence_per_kilo) AS value_pence
3FROM boat_haul(:boat) AS haul
3FROM catches
4JOIN prices ON prices.species = haul.species
4JOIN prices ON prices.species = catches.species
5ORDER BY haul.species;
5WHERE catches.boat = ?
6ORDER BY catches.species;

What sgl generates

SetPriceidentical

PostgreSQL
MariaDB
1func (queries *Queries) SetPrice(ctx context.Context, params SetPriceParams) error
1func (queries *Queries) SetPrice(ctx context.Context, params SetPriceParams) error
2
2
3type SetPriceParams struct {
3type SetPriceParams struct {
4	Species      string
4	Species      string
5	PencePerKilo int32
5	PencePerKilo int32
6}
6}

LandCatchidentical

PostgreSQL
MariaDB
1func (queries *Queries) LandCatch(ctx context.Context, params LandCatchParams) (LandCatchRow, error)
1func (queries *Queries) LandCatch(ctx context.Context, params LandCatchParams) (LandCatchRow, error)
2
2
3type LandCatchParams struct {
3type LandCatchParams struct {
4	Boat    string
4	Boat    string
5	Species string
5	Species string
6	Grams   int32
6	Grams   int32
7}
7}
8
8
9type LandCatchRow struct {
9type LandCatchRow struct {
10	ID *int64 `json:"id"`
10	ID *int64 `json:"id"`
11}
11}

SpeciesPriceidentical

PostgreSQL
MariaDB
1func (queries *Queries) SpeciesPrice(ctx context.Context, species string) (SpeciesPriceRow, error)
1func (queries *Queries) SpeciesPrice(ctx context.Context, species string) (SpeciesPriceRow, error)
2
2
3type SpeciesPriceRow struct {
3type SpeciesPriceRow struct {
4	PencePerKilo *int32 `json:"pence_per_kilo"`
4	PencePerKilo *int32 `json:"pence_per_kilo"`
5}
5}

BoatHauldiffers

PostgreSQL
MariaDB
1func (queries *Queries) BoatHaul(ctx context.Context, boat string) ([]BoatHaulRow, error)
1func (queries *Queries) BoatHaul(ctx context.Context, boat string) ([]BoatHaulRow, error)
2
2
3type BoatHaulRow struct {
3type BoatHaulRow struct {
4	Species     *string `json:"species"`
4	Species     string `json:"species"`
5	WeightGrams *int32  `json:"weight_grams"`
5	WeightGrams int32  `json:"weight_grams"`
6	ValuePence  *int32  `json:"value_pence"`
6	ValuePence  *int32 `json:"value_pence"`
7}
7}

The program

example.godiffers

PostgreSQL
MariaDB
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/030_functions_in_migrations/internal/store"
9	"seagull.sh/seagull/examples/mysql/030_functions_in_migrations/internal/store"
10)
10)
11
11
12// connection is a store.DBTX that notes its name before each statement, so the example
12// connection is a store.DBTX that notes its name before each statement, so the example
13// can say whether Seagull sent a query to the writer or the reader. Both connections wrap
13// can say whether Seagull sent a query to the writer or the reader. Both connections wrap
14// the same database here; concept 24 puts the reader on a replica.
14// the same database here; concept 24 puts the reader on a replica.
15type connection struct {
15type connection struct {
16	*sql.DB
16	*sql.DB
17
17
18	// last receives the name of the connection that ran the latest statement.
18	// last receives the name of the connection that ran the latest statement.
19	last *string
19	last *string
20
20
21	// name labels the connection.
21	// name labels the connection.
22	name string
22	name string
23}
23}
24
24
25// ExecContext notes the connection, then runs the statement on the database.
25// ExecContext notes the connection, then runs the statement on the database.
26//
26//
27// Takes query (string) which is the SQL to run.
27// Takes query (string) which is the SQL to run.
28// Takes arguments (...any) which are its bind values.
28// Takes arguments (...any) which are its bind values.
29//
29//
30// Returns sql.Result which is the database's result.
30// Returns sql.Result which is the database's result.
31// Returns error when the statement fails.
31// Returns error when the statement fails.
32func (c connection) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
32func (c connection) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
33	*c.last = c.name
33	*c.last = c.name
34	return c.DB.ExecContext(ctx, query, arguments...)
34	return c.DB.ExecContext(ctx, query, arguments...)
35}
35}
36
36
37// QueryContext notes the connection, then runs the query on the database.
37// QueryContext notes the connection, then runs the query on the database.
38//
38//
39// Takes query (string) which is the SQL to run.
39// Takes query (string) which is the SQL to run.
40// Takes arguments (...any) which are its bind values.
40// Takes arguments (...any) which are its bind values.
41//
41//
42// Returns *sql.Rows which holds the result rows.
42// Returns *sql.Rows which holds the result rows.
43// Returns error when the query fails.
43// Returns error when the query fails.
44func (c connection) QueryContext(ctx context.Context, query string, arguments ...any) (*sql.Rows, error) {
44func (c connection) QueryContext(ctx context.Context, query string, arguments ...any) (*sql.Rows, error) {
45	*c.last = c.name
45	*c.last = c.name
46	return c.DB.QueryContext(ctx, query, arguments...)
46	return c.DB.QueryContext(ctx, query, arguments...)
47}
47}
48
48
49// QueryRowContext notes the connection, then runs the query on the database.
49// QueryRowContext notes the connection, then runs the query on the database.
50//
50//
51// Takes query (string) which is the SQL to run.
51// Takes query (string) which is the SQL to run.
52// Takes arguments (...any) which are its bind values.
52// Takes arguments (...any) which are its bind values.
53//
53//
54// Returns *sql.Row which holds the result row or the error.
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 {
55func (c connection) QueryRowContext(ctx context.Context, query string, arguments ...any) *sql.Row {
56	*c.last = c.name
56	*c.last = c.name
57	return c.DB.QueryRowContext(ctx, query, arguments...)
57	return c.DB.QueryRowContext(ctx, query, arguments...)
58}
58}
59
59
60// run prices the fish, lands three catches and reads the market back, all through the SQL
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
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
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
63// what it says about data access, along with what its body does, decides whether those
64// picks the writer or the reader. Volatility does not decide it.
64// queries are read-only, which picks the writer or the reader.
65//
65//
66//   - land_catch inserts a row, so LandCatch goes to the writer;
66//   - land_catch says MODIFIES SQL DATA and its body inserts a row, so LandCatch goes to
67//   - price_pence and boat_haul only read data, so BoatHaul, which calls both, goes to
67//     the writer. Its body is a BEGIN ... END block, which a migration can hold without
68//     the reader. boat_haul RETURNS TABLE, so it is a relation with typed columns, and
68//     DELIMITER, as Seagull splits the file into statements itself;
69//     its $1 is typed from the function's TEXT argument;
69//   - price_pence says NO SQL, so BoatHaul, which calls it, goes to the reader;
70//   - species_price is declared VOLATILE, but its body only reads, so SpeciesPrice goes
70//   - species_price says MODIFIES SQL DATA, which Seagull takes to mean it writes, but
71//     to the reader. The sgl.migration(readonly: true) line above it in the migration
71//     its body only reads. MySQL never checks these characteristics against the body. The
72//     states that outright, as a function whose body Seagull cannot read needs.
72//     sgl.migration(readonly: true) line above it in the migration says so, and
73//     SpeciesPrice goes to the reader.
73//
74//
75// With binary logging on, as it is by default, MySQL refuses a function that is not
76// DETERMINISTIC, NO SQL or READS SQL DATA, so the two MODIFIES SQL DATA functions are
77// declared DETERMINISTIC too.
78//
74// A function's result can be NULL, so the columns are pointers: species_price finds no
79// 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
80// row for a fish with no price.
76// result; here its arguments come from boat_haul, which may return NULLs.
77//
81//
78// Takes database (*sql.DB) which is a migrated database.
82// Takes database (*sql.DB) which is a migrated database.
79// Takes output (io.Writer) which receives each call's connection and result.
83// Takes output (io.Writer) which receives each call's connection and result.
80//
84//
81// Returns error when a query fails.
85// Returns error when a query fails.
82func run(ctx context.Context, database *sql.DB, output io.Writer) error {
86func run(ctx context.Context, database *sql.DB, output io.Writer) error {
83	var last string
87	var last string
84	queries := store.NewWithReplica(
88	queries := store.NewWithReplica(
85		connection{DB: database, last: &last, name: "writer"},
89		connection{DB: database, last: &last, name: "writer"},
86		connection{DB: database, last: &last, name: "reader"},
90		connection{DB: database, last: &last, name: "reader"},
87	)
91	)
88
92
89	prices := []store.SetPriceParams{
93	prices := []store.SetPriceParams{
90		{Species: "crab", PencePerKilo: 1200},
94		{Species: "crab", PencePerKilo: 1200},
91		{Species: "mackerel", PencePerKilo: 450},
95		{Species: "mackerel", PencePerKilo: 450},
92		{Species: "pollock", PencePerKilo: 800},
96		{Species: "pollock", PencePerKilo: 800},
93	}
97	}
94	for _, price := range prices {
98	for _, price := range prices {
95		if err := queries.SetPrice(ctx, price); err != nil {
99		if err := queries.SetPrice(ctx, price); err != nil {
96			return fmt.Errorf("pricing %s: %w", price.Species, err)
100			return fmt.Errorf("pricing %s: %w", price.Species, err)
97		}
101		}
98	}
102	}
99
103
100	catches := []store.LandCatchParams{
104	catches := []store.LandCatchParams{
101		{Boat: "Kittiwake", Species: "mackerel", Grams: 12000},
105		{Boat: "Kittiwake", Species: "mackerel", Grams: 12000},
102		{Boat: "Kittiwake", Species: "crab", Grams: 3500},
106		{Boat: "Kittiwake", Species: "crab", Grams: 3500},
103		{Boat: "Puffin", Species: "pollock", Grams: 8000},
107		{Boat: "Puffin", Species: "pollock", Grams: 8000},
104	}
108	}
105	for _, catch := range catches {
109	for _, catch := range catches {
106		landed, err := queries.LandCatch(ctx, catch)
110		landed, err := queries.LandCatch(ctx, catch)
107		if err != nil {
111		if err != nil {
108			return fmt.Errorf("landing %s: %w", catch.Species, err)
112			return fmt.Errorf("landing %s: %w", catch.Species, err)
109		}
113		}
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)
114		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	}
115	}
112
116
113	for _, species := range []string{"crab", "halibut"} {
117	for _, species := range []string{"crab", "halibut"} {
114		price, err := queries.SpeciesPrice(ctx, species)
118		price, err := queries.SpeciesPrice(ctx, species)
115		if err != nil {
119		if err != nil {
116			return fmt.Errorf("pricing %s: %w", species, err)
120			return fmt.Errorf("pricing %s: %w", species, err)
117		}
121		}
118		if price.PencePerKilo == nil {
122		if price.PencePerKilo == nil {
119			fmt.Fprintf(output, "SpeciesPrice on the %s: %s has no price\n", last, species)
123			fmt.Fprintf(output, "SpeciesPrice on the %s: %s has no price\n", last, species)
120			continue
124			continue
121		}
125		}
122		fmt.Fprintf(output, "SpeciesPrice on the %s: %s fetches %dp a kilo\n", last, species, *price.PencePerKilo)
126		fmt.Fprintf(output, "SpeciesPrice on the %s: %s fetches %dp a kilo\n", last, species, *price.PencePerKilo)
123	}
127	}
124
128
125	haul, err := queries.BoatHaul(ctx, "Kittiwake")
129	haul, err := queries.BoatHaul(ctx, "Kittiwake")
126	if err != nil {
130	if err != nil {
127		return fmt.Errorf("reading the Kittiwake's haul: %w", err)
131		return fmt.Errorf("reading the Kittiwake's haul: %w", err)
128	}
132	}
129	fmt.Fprintf(output, "BoatHaul     on the %s: the Kittiwake's haul\n", last)
133	fmt.Fprintf(output, "BoatHaul     on the %s: the Kittiwake's haul\n", last)
130	for _, row := range haul {
134	for _, row := range haul {
131		fmt.Fprintf(output, "  %-9s %6sg %5sp\n", orNull(row.Species), orNull(row.WeightGrams), orNull(row.ValuePence))
135		fmt.Fprintf(output, "  %-9s %6dg %5sp\n", row.Species, row.WeightGrams, orNull(row.ValuePence))
132	}
136	}
133	return nil
137	return nil
134}
138}
135
139
136// orNull formats a nullable field, or returns NULL when the field is nil.
140// orNull formats a nullable field, or returns NULL when the field is nil.
137//
141//
138// Takes value (*T) which is a nullable field.
142// Takes value (*T) which is a nullable field.
139//
143//
140// Returns string which is the text to print.
144// Returns string which is the text to print.
141func orNull[T any](value *T) string {
145func orNull[T any](value *T) string {
142	if value == nil {
146	if value == nil {
143		return "NULL"
147		return "NULL"
144	}
148	}
145	return fmt.Sprint(*value)
149	return fmt.Sprint(*value)
146}
150}

Go type differences

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

Query	Field	PostgreSQL	MariaDB
BoatHaul	column `species`	`*string`	`string`
BoatHaul	column `weight_grams`	`*int32`	`int32`