Compare · 027

Migrations at start-up: DuckDB and ClickHouse

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

Compare DuckDB withSQLite PostgreSQL CockroachDB TimescaleDB MySQL MariaDB ClickHouse

Configuration

sgl.yamldiffers

DuckDB
ClickHouse
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: harbour
4  - name: harbour
5    dialect: duckdb
5    dialect: clickhouse
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

Migrations

db/migrations/0001_boats.up.sqldiffers

DuckDB
ClickHouse
1CREATE SEQUENCE boat_ids;
2
3CREATE TABLE boats (
1CREATE TABLE boats (
4    id   BIGINT  PRIMARY KEY DEFAULT nextval('boat_ids'),
2    id   UInt64,
5    name VARCHAR NOT NULL
3    name String
6);
4) ENGINE = MergeTree() ORDER BY id;

db/migrations/0001_boats.down.sqldiffers

DuckDB
ClickHouse
1DROP TABLE boats;
1DROP TABLE boats;
2DROP SEQUENCE boat_ids;

db/migrations/0002_boat_home_port.up.sqldiffers

DuckDB
ClickHouse
1ALTER TABLE boats ADD COLUMN home_port VARCHAR DEFAULT 'Padstow';
1ALTER TABLE boats ADD COLUMN home_port String DEFAULT 'Padstow';

db/migrations/0002_boat_home_port.down.sqlidentical

DuckDB
ClickHouse
1ALTER TABLE boats DROP COLUMN home_port;
1ALTER TABLE boats DROP COLUMN home_port;

Queries

AddBoatdiffers

DuckDB · db/queries/boats.sql
ClickHouse · db/queries/boats.sql
1-- sgl.query(AddBoat, exec)
1-- sgl.query(AddBoat, exec)
2INSERT INTO boats (name, home_port) VALUES ($1, $2);
2INSERT INTO boats (id, name, home_port) VALUES ({id:UInt64}, {name:String}, {home_port:String});

ListBoatsidentical

DuckDB · db/queries/boats.sql
ClickHouse · 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

DuckDB
ClickHouse
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
4	Name     string
5	Name     string
5	HomePort *string
6	HomePort string
6}
7}

ListBoatsdiffers

DuckDB
ClickHouse
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       int64   `json:"id"`
4	ID       uint64 `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

DuckDB
ClickHouse
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/duckdb/027_migrations_at_startup/internal/store"
10	"seagull.sh/seagull/examples/clickhouse/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_duckdb"
12	"seagull.sh/seagull/sdk/migrate/migrate_backend_clickhouse"
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	for _, boat := range []string{"Kittiwake", "Puffin"} {
24	boats := []store.AddBoatParams{
25		{ID: 1, Name: "Kittiwake", HomePort: "Padstow"},
26		{ID: 2, Name: "Puffin", HomePort: "Padstow"},
27	}
28	for _, boat := range boats {
25		if err := start(ctx, database, migrations, boat, output); err != nil {
29		if err := start(ctx, database, migrations, boat, output); err != nil {
26			return err
30			return err
27		}
31		}
28	}
32	}
29	return printHistory(ctx, database, migrations, output)
33	return printHistory(ctx, database, migrations, output)
30}
34}
31
35
32// start is what the program does each time it starts: migrate first, then work. Here the
36// start is what the program does each time it starts: migrate first, then work. Here the
33// work is adding a boat and listing the harbour.
37// work is adding a boat and listing the harbour.
34//
38//
35// Takes database (*sql.DB) which is the database.
39// Takes database (*sql.DB) which is the database.
36// Takes migrations (fs.FS) which holds the migration files.
40// Takes migrations (fs.FS) which holds the migration files.
37// Takes boat (string) which names the boat to add.
41// Takes boat (store.AddBoatParams) which is the boat to add.
38// Takes output (io.Writer) which receives what happened.
42// Takes output (io.Writer) which receives what happened.
39//
43//
40// Returns error when migrating or a query fails.
44// Returns error when migrating or a query fails.
41func start(ctx context.Context, database *sql.DB, migrations fs.FS, boat string, output io.Writer) error {
45func start(ctx context.Context, database *sql.DB, migrations fs.FS, boat store.AddBoatParams, output io.Writer) error {
42	// migrate_backend_duckdb.New() is the DuckDB backend: it knows how to create the history
46	// migrate_backend_clickhouse.New() is the ClickHouse backend. It runs each statement of
43	// table, binds its own queries with $N placeholders, and runs each migration in a
47	// a migration without a transaction and records the migration once every statement has
44	// transaction, as DuckDB DDL is transactional. The backend takes no database lock;
48	// succeeded; it takes no lock, so only one process should migrate at a time. NewMigrator
45	// callers must serialise migrations across migrator instances. NewMigrator reads the
49	// reads the numbered *.up.sql and *.down.sql files from the directory inside migrations.
46	// numbered *.up.sql and *.down.sql files from the directory inside migrations.
50	migrator, err := migrate.NewMigrator(database, migrate_backend_clickhouse.New(), migrations, "db/migrations")
47	migrator, err := migrate.NewMigrator(database, migrate_backend_duckdb.New(), migrations, "db/migrations")
48	if err != nil {
51	if err != nil {
49		return err
52		return err
50	}
53	}
51
54
52	// Up applies every migration not yet in the history, in version order, and returns how
55	// Up applies every migration not yet in the history, in version order, and returns how
53	// many it applied. On a database that is up to date it applies none.
56	// many it applied. On a database that is up to date it applies none.
54	applied, err := migrator.Up(ctx)
57	applied, err := migrator.Up(ctx)
55	if err != nil {
58	if err != nil {
56		return fmt.Errorf("migrating: %w", err)
59		return fmt.Errorf("migrating: %w", err)
57	}
60	}
58	fmt.Fprintf(output, "start-up: applied %d migrations\n", applied)
61	fmt.Fprintf(output, "start-up: applied %d migrations\n", applied)
59
62
60	// DuckDB cannot add a NOT NULL column to an existing table, so 0002 adds home_port with
61	// a default only, and the column, like its field, may be NULL.
62	queries := store.New(database)
63	queries := store.New(database)
63	if err := queries.AddBoat(ctx, store.AddBoatParams{Name: boat, HomePort: new("Padstow")}); err != nil {
64	if err := queries.AddBoat(ctx, boat); err != nil {
64		return fmt.Errorf("adding %s: %w", boat, err)
65		return fmt.Errorf("adding %s: %w", boat.Name, err)
65	}
66	}
66	boats, err := queries.ListBoats(ctx)
67	listed, err := queries.ListBoats(ctx)
67	if err != nil {
68	if err != nil {
68		return fmt.Errorf("listing boats: %w", err)
69		return fmt.Errorf("listing boats: %w", err)
69	}
70	}
70	for _, row := range boats {
71	for _, row := range listed {
71		homePort := "an unknown port"
72		fmt.Fprintf(output, "  %d %-9s of %s\n", row.ID, row.Name, row.HomePort)
72		if row.HomePort != nil {
73			homePort = *row.HomePort
74		}
75		fmt.Fprintf(output, "  %d %-9s of %s\n", row.ID, row.Name, homePort)
76	}
73	}
77	return nil
74	return nil
78}
75}
79
76
80// printHistory prints the migration history the migrator keeps in the database.
77// printHistory prints the migration history the migrator keeps in the database.
81//
78//
82// Takes database (*sql.DB) which is the migrated database.
79// Takes database (*sql.DB) which is the migrated database.
83// Takes migrations (fs.FS) which holds the migration files.
80// Takes migrations (fs.FS) which holds the migration files.
84// Takes output (io.Writer) which receives the history.
81// Takes output (io.Writer) which receives the history.
85//
82//
86// Returns error when the history cannot be read.
83// Returns error when the history cannot be read.
87func printHistory(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
84func printHistory(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
88	backend := migrate_backend_duckdb.New()
85	backend := migrate_backend_clickhouse.New()
89	migrator, err := migrate.NewMigrator(database, backend, migrations, "db/migrations")
86	migrator, err := migrate.NewMigrator(database, backend, migrations, "db/migrations")
90	if err != nil {
87	if err != nil {
91		return err
88		return err
92	}
89	}
93	statuses, err := migrator.Status(ctx)
90	statuses, err := migrator.Status(ctx)
94	if err != nil {
91	if err != nil {
95		return fmt.Errorf("reading migration status: %w", err)
92		return fmt.Errorf("reading migration status: %w", err)
96	}
93	}
97	fmt.Fprintf(output, "history in %s:\n", backend.Names().History)
94	fmt.Fprintf(output, "history in %s:\n", backend.Names().History)
98	for _, status := range statuses {
95	for _, status := range statuses {
99		fmt.Fprintf(output, "  %04d %-15s applied=%t\n", status.Version, status.Name, status.Applied)
96		fmt.Fprintf(output, "  %04d %-15s applied=%t\n", status.Version, status.Name, status.Applied)
100	}
97	}
101	return nil
98	return nil
102}
99}

Go type differences

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

Query	Field	DuckDB	ClickHouse
AddBoat	parameter `home_port`	`*string`	`string`
ListBoats	column `id`	`int64`	`uint64`
ListBoats	column `home_port`	`*string`	`string`