Compare · 027

Migrations at start-up: MariaDB and DuckDB

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

Compare MariaDB withSQLitePostgreSQLCockroachDBTimescaleDBMySQLDuckDBClickHouse

Configuration

sgl.yamldiffers
MariaDB
DuckDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: harbour
4 - name: harbour
5 dialect: mysql
5 dialect: duckdb
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
MariaDB
DuckDB
1CREATE SEQUENCE boat_ids;
2
1CREATE TABLE boats (
3CREATE TABLE boats (
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
4 id BIGINT PRIMARY KEY DEFAULT nextval('boat_ids'),
3 name VARCHAR(50) NOT NULL
5 name VARCHAR NOT NULL
4);
6);
db/migrations/0001_boats.down.sqldiffers
MariaDB
DuckDB
1DROP TABLE boats;
1DROP TABLE boats;
2DROP SEQUENCE boat_ids;
db/migrations/0002_boat_home_port.up.sqldiffers
MariaDB
DuckDB
1ALTER TABLE boats ADD COLUMN home_port VARCHAR(50) NOT NULL DEFAULT 'Padstow';
1ALTER TABLE boats ADD COLUMN home_port VARCHAR DEFAULT 'Padstow';
db/migrations/0002_boat_home_port.down.sqlidentical
MariaDB
DuckDB
1ALTER TABLE boats DROP COLUMN home_port;
1ALTER TABLE boats DROP COLUMN home_port;

Queries

AddBoatdiffers
MariaDB · db/queries/boats.sql
DuckDB · db/queries/boats.sql
1-- sgl.query(AddBoat, exec)
1-- sgl.query(AddBoat, exec)
2INSERT INTO boats (name, home_port) VALUES (?, ?);
2INSERT INTO boats (name, home_port) VALUES ($1, $2);
ListBoatsidentical
MariaDB · db/queries/boats.sql
DuckDB · 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
MariaDB
DuckDB
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 Name string
4 Name string
5 HomePort string
5 HomePort *string
6}
6}
ListBoatsdiffers
MariaDB
DuckDB
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 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
MariaDB
DuckDB
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/mysql/027_migrations_at_startup/internal/store"
10 "seagull.sh/seagull/examples/duckdb/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_mysql"
12 "seagull.sh/seagull/sdk/migrate/migrate_backend_duckdb"
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 for _, boat := range []string{"Kittiwake", "Puffin"} {
25 if err := start(ctx, database, migrations, boat, output); err != nil {
25 if err := start(ctx, database, migrations, boat, output); err != nil {
26 return err
26 return err
27 }
27 }
28 }
28 }
29 return printHistory(ctx, database, migrations, output)
29 return printHistory(ctx, database, migrations, output)
30}
30}
31
31
32// 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
33// work is adding a boat and listing the harbour.
33// work is adding a boat and listing the harbour.
34//
34//
35// Takes database (*sql.DB) which is the database.
35// Takes database (*sql.DB) which is the database.
36// Takes migrations (fs.FS) which holds the migration files.
36// Takes migrations (fs.FS) which holds the migration files.
37// Takes boat (string) which names the boat to add.
37// Takes boat (string) which names the boat to add.
38// Takes output (io.Writer) which receives what happened.
38// Takes output (io.Writer) which receives what happened.
39//
39//
40// Returns error when migrating or a query fails.
40// 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 {
41func start(ctx context.Context, database *sql.DB, migrations fs.FS, boat string, output io.Writer) error {
42 // migrate_backend_mysql.New() is the MySQL backend: it knows how to create the history
42 // migrate_backend_duckdb.New() is the DuckDB backend: it knows how to create the history
43 // table and holds a GET_LOCK advisory lock, scoped to this schema, while it works, so
43 // table, binds its own queries with $N placeholders, and runs each migration in a
44 // when several copies of a service start at once, one migrates and the rest wait, then
44 // transaction, as DuckDB DDL is transactional. The backend takes no database lock;
45 // find nothing to do. MySQL commits each CREATE or ALTER as it runs, so a migration
45 // callers must serialise migrations across migrator instances. NewMigrator reads the
46 // cannot be rolled back as a whole: keep each one to a single statement of DDL.
46 // numbered *.up.sql and *.down.sql files from the directory inside migrations.
47 // NewMigrator reads the numbered *.up.sql and *.down.sql files from the directory inside
47 migrator, err := migrate.NewMigrator(database, migrate_backend_duckdb.New(), migrations, "db/migrations")
48 // migrations.
49 migrator, err := migrate.NewMigrator(database, migrate_backend_mysql.New(), migrations, "db/migrations")
50 if err != nil {
48 if err != nil {
51 return err
49 return err
52 }
50 }
53
51
54 // Up applies every migration not yet in the history, in version order, and returns how
52 // Up applies every migration not yet in the history, in version order, and returns how
55 // many it applied. On a database that is up to date it applies none.
53 // many it applied. On a database that is up to date it applies none.
56 applied, err := migrator.Up(ctx)
54 applied, err := migrator.Up(ctx)
57 if err != nil {
55 if err != nil {
58 return fmt.Errorf("migrating: %w", err)
56 return fmt.Errorf("migrating: %w", err)
59 }
57 }
60 fmt.Fprintf(output, "start-up: applied %d migrations\n", applied)
58 fmt.Fprintf(output, "start-up: applied %d migrations\n", applied)
61
59
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)
62 queries := store.New(database)
63 if err := queries.AddBoat(ctx, store.AddBoatParams{Name: boat, HomePort: "Padstow"}); err != nil {
63 if err := queries.AddBoat(ctx, store.AddBoatParams{Name: boat, HomePort: new("Padstow")}); err != nil {
64 return fmt.Errorf("adding %s: %w", boat, err)
64 return fmt.Errorf("adding %s: %w", boat, err)
65 }
65 }
66 boats, err := queries.ListBoats(ctx)
66 boats, err := queries.ListBoats(ctx)
67 if err != nil {
67 if err != nil {
68 return fmt.Errorf("listing boats: %w", err)
68 return fmt.Errorf("listing boats: %w", err)
69 }
69 }
70 for _, row := range boats {
70 for _, row := range boats {
71 fmt.Fprintf(output, " %d %-9s of %s\n", row.ID, row.Name, row.HomePort)
71 homePort := "an unknown port"
72 if row.HomePort != nil {
73 homePort = *row.HomePort
74 }
75 fmt.Fprintf(output, " %d %-9s of %s\n", row.ID, row.Name, homePort)
72 }
76 }
73 return nil
77 return nil
74}
78}
75
79
76// printHistory prints the migration history the migrator keeps in the database.
80// printHistory prints the migration history the migrator keeps in the database.
77//
81//
78// Takes database (*sql.DB) which is the migrated database.
82// Takes database (*sql.DB) which is the migrated database.
79// Takes migrations (fs.FS) which holds the migration files.
83// Takes migrations (fs.FS) which holds the migration files.
80// Takes output (io.Writer) which receives the history.
84// Takes output (io.Writer) which receives the history.
81//
85//
82// Returns error when the history cannot be read.
86// Returns error when the history cannot be read.
83func printHistory(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
87func printHistory(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
84 backend := migrate_backend_mysql.New()
88 backend := migrate_backend_duckdb.New()
85 migrator, err := migrate.NewMigrator(database, backend, migrations, "db/migrations")
89 migrator, err := migrate.NewMigrator(database, backend, migrations, "db/migrations")
86 if err != nil {
90 if err != nil {
87 return err
91 return err
88 }
92 }
89 statuses, err := migrator.Status(ctx)
93 statuses, err := migrator.Status(ctx)
90 if err != nil {
94 if err != nil {
91 return fmt.Errorf("reading migration status: %w", err)
95 return fmt.Errorf("reading migration status: %w", err)
92 }
96 }
93 fmt.Fprintf(output, "history in %s:\n", backend.Names().History)
97 fmt.Fprintf(output, "history in %s:\n", backend.Names().History)
94 for _, status := range statuses {
98 for _, status := range statuses {
95 fmt.Fprintf(output, " %04d %-15s applied=%t\n", status.Version, status.Name, status.Applied)
99 fmt.Fprintf(output, " %04d %-15s applied=%t\n", status.Version, status.Name, status.Applied)
96 }
100 }
97 return nil
101 return nil
98}
102}

Go type differences

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

QueryFieldMariaDBDuckDB
AddBoatparameter home_portstring*string
ListBoatscolumn home_portstring*string