Compare · 027

Migrations at start-up: ClickHouse and PostgreSQL

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

Compare ClickHouse withSQLitePostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDB

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
12 migrate:
13 dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_boats.up.sqldiffers
ClickHouse
PostgreSQL
1CREATE TABLE boats (
1CREATE TABLE boats (
2 id UInt64,
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 name String
3 name TEXT NOT NULL
4) ENGINE = MergeTree() ORDER BY id;
4);
db/migrations/0001_boats.down.sqlidentical
ClickHouse
PostgreSQL
1DROP TABLE boats;
1DROP TABLE boats;
db/migrations/0002_boat_home_port.up.sqldiffers
ClickHouse
PostgreSQL
1ALTER TABLE boats ADD COLUMN home_port String DEFAULT 'Padstow';
1ALTER TABLE boats ADD COLUMN home_port TEXT NOT NULL DEFAULT 'Padstow';
db/migrations/0002_boat_home_port.down.sqlidentical
ClickHouse
PostgreSQL
1ALTER TABLE boats DROP COLUMN home_port;
1ALTER TABLE boats DROP COLUMN home_port;

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, home_port) VALUES ({id:UInt64}, {name:String}, {home_port:String});
2INSERT INTO boats (name, home_port) VALUES ($1, $2);
ListBoatsidentical
ClickHouse · db/queries/boats.sql
PostgreSQL · db/queries/boats.sql
1-- sgl.query(ListBoats, many)
1-- sgl.query(ListBoats, many)
2SELECT id, name, home_port FROM boats ORDER BY id;
2SELECT id, name, home_port FROM boats 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 HomePort string
5 HomePort string
7}
6}
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 ID int64 `json:"id"`
5 Name string `json:"name"`
5 Name string `json:"name"`
6 HomePort string `json:"home_port"`
6 HomePort string `json:"home_port"`
7}
7}

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 "fmt"
6 "fmt"
7 "io"
7 "io"
8 "io/fs"
8 "io/fs"
9
9
10 "seagull.sh/seagull/examples/clickhouse/027_migrations_at_startup/internal/store"
10 "seagull.sh/seagull/examples/postgres/027_migrations_at_startup/internal/store"
11 "seagull.sh/seagull/sdk/migrate"
11 "seagull.sh/seagull/sdk/migrate"
12 "seagull.sh/seagull/sdk/migrate/migrate_backend_clickhouse"
12 "seagull.sh/seagull/sdk/migrate/migrate_backend_postgres"
13)
13)
14
14
15// run starts the program twice against the same database. The first start applies both
15// run starts the program twice against the same database. The first start applies both
16// migrations before the program uses the schema; the second finds nothing to do.
16// migrations before the program uses the schema; the second finds nothing to do.
17//
17//
18// Takes database (*sql.DB) which is the database to migrate, empty or not.
18// Takes database (*sql.DB) which is the database to migrate, empty or not.
19// Takes migrations (fs.FS) which holds db/migrations, the embed.FS in main.go.
19// Takes migrations (fs.FS) which holds db/migrations, the embed.FS in main.go.
20// Takes output (io.Writer) which receives what each start did.
20// Takes output (io.Writer) which receives what each start did.
21//
21//
22// Returns error when migrating or a query fails.
22// Returns error when migrating or a query fails.
23func run(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
23func run(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
24 boats := []store.AddBoatParams{
24 for _, boat := range []string{"Kittiwake", "Puffin"} {
25 {ID: 1, Name: "Kittiwake", HomePort: "Padstow"},
26 {ID: 2, Name: "Puffin", HomePort: "Padstow"},
27 }
28 for _, boat := range boats {
29 if err := start(ctx, database, migrations, boat, output); err != nil {
25 if err := start(ctx, database, migrations, boat, output); err != nil {
30 return err
26 return err
31 }
27 }
32 }
28 }
33 return printHistory(ctx, database, migrations, output)
29 return printHistory(ctx, database, migrations, output)
34}
30}
35
31
36// start is what the program does each time it starts: migrate first, then work. Here the
32// start is what the program does each time it starts: migrate first, then work. Here the
37// work is adding a boat and listing the harbour.
33// work is adding a boat and listing the harbour.
38//
34//
39// Takes database (*sql.DB) which is the database.
35// Takes database (*sql.DB) which is the database.
40// Takes migrations (fs.FS) which holds the migration files.
36// Takes migrations (fs.FS) which holds the migration files.
41// Takes boat (store.AddBoatParams) which is the boat to add.
37// Takes boat (string) which names the boat to add.
42// Takes output (io.Writer) which receives what happened.
38// Takes output (io.Writer) which receives what happened.
43//
39//
44// Returns error when migrating or a query fails.
40// Returns error when migrating or a query fails.
45func start(ctx context.Context, database *sql.DB, migrations fs.FS, boat store.AddBoatParams, output io.Writer) error {
41func start(ctx context.Context, database *sql.DB, migrations fs.FS, boat string, output io.Writer) error {
46 // migrate_backend_clickhouse.New() is the ClickHouse backend. It runs each statement of
42 // migrate_backend_postgres.New() is the PostgreSQL backend: it knows how to create the
47 // a migration without a transaction and records the migration once every statement has
43 // history table and run each migration in a transaction, as PostgreSQL's DDL is
48 // succeeded; it takes no lock, so only one process should migrate at a time. NewMigrator
44 // transactional. It also holds an advisory lock while it works, so when several copies
49 // reads the numbered *.up.sql and *.down.sql files from the directory inside migrations.
45 // of a service start at once, one migrates and the rest wait, then find nothing to do.
50 migrator, err := migrate.NewMigrator(database, migrate_backend_clickhouse.New(), migrations, "db/migrations")
46 // NewMigrator reads the numbered *.up.sql and *.down.sql files from the directory inside
47 // migrations.
48 migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.New(), migrations, "db/migrations")
51 if err != nil {
49 if err != nil {
52 return err
50 return err
53 }
51 }
54
52
55 // Up applies every migration not yet in the history, in version order, and returns how
53 // Up applies every migration not yet in the history, in version order, and returns how
56 // many it applied. On a database that is up to date it applies none.
54 // many it applied. On a database that is up to date it applies none.
57 applied, err := migrator.Up(ctx)
55 applied, err := migrator.Up(ctx)
58 if err != nil {
56 if err != nil {
59 return fmt.Errorf("migrating: %w", err)
57 return fmt.Errorf("migrating: %w", err)
60 }
58 }
61 fmt.Fprintf(output, "start-up: applied %d migrations\n", applied)
59 fmt.Fprintf(output, "start-up: applied %d migrations\n", applied)
62
60
63 queries := store.New(database)
61 queries := store.New(database)
64 if err := queries.AddBoat(ctx, boat); err != nil {
62 if err := queries.AddBoat(ctx, store.AddBoatParams{Name: boat, HomePort: "Padstow"}); err != nil {
65 return fmt.Errorf("adding %s: %w", boat.Name, err)
63 return fmt.Errorf("adding %s: %w", boat, err)
66 }
64 }
67 listed, err := queries.ListBoats(ctx)
65 boats, err := queries.ListBoats(ctx)
68 if err != nil {
66 if err != nil {
69 return fmt.Errorf("listing boats: %w", err)
67 return fmt.Errorf("listing boats: %w", err)
70 }
68 }
71 for _, row := range listed {
69 for _, row := range boats {
72 fmt.Fprintf(output, " %d %-9s of %s\n", row.ID, row.Name, row.HomePort)
70 fmt.Fprintf(output, " %d %-9s of %s\n", row.ID, row.Name, row.HomePort)
73 }
71 }
74 return nil
72 return nil
75}
73}
76
74
77// printHistory prints the migration history the migrator keeps in the database.
75// printHistory prints the migration history the migrator keeps in the database.
78//
76//
79// Takes database (*sql.DB) which is the migrated database.
77// Takes database (*sql.DB) which is the migrated database.
80// Takes migrations (fs.FS) which holds the migration files.
78// Takes migrations (fs.FS) which holds the migration files.
81// Takes output (io.Writer) which receives the history.
79// Takes output (io.Writer) which receives the history.
82//
80//
83// Returns error when the history cannot be read.
81// Returns error when the history cannot be read.
84func printHistory(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
82func printHistory(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
85 backend := migrate_backend_clickhouse.New()
83 backend := migrate_backend_postgres.New()
86 migrator, err := migrate.NewMigrator(database, backend, migrations, "db/migrations")
84 migrator, err := migrate.NewMigrator(database, backend, migrations, "db/migrations")
87 if err != nil {
85 if err != nil {
88 return err
86 return err
89 }
87 }
90 statuses, err := migrator.Status(ctx)
88 statuses, err := migrator.Status(ctx)
91 if err != nil {
89 if err != nil {
92 return fmt.Errorf("reading migration status: %w", err)
90 return fmt.Errorf("reading migration status: %w", err)
93 }
91 }
94 fmt.Fprintf(output, "history in %s:\n", backend.Names().History)
92 fmt.Fprintf(output, "history in %s:\n", backend.Names().History)
95 for _, status := range statuses {
93 for _, status := range statuses {
96 fmt.Fprintf(output, " %04d %-15s applied=%t\n", status.Version, status.Name, status.Applied)
94 fmt.Fprintf(output, " %04d %-15s applied=%t\n", status.Version, status.Name, status.Applied)
97 }
95 }
98 return nil
96 return nil
99}
97}

Go type differences

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

QueryFieldClickHousePostgreSQL
ListBoatscolumn iduint64int64