Compare · 027

Migrations at start-up: DuckDB and CockroachDB

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

Compare DuckDB withSQLite PostgreSQL CockroachDB TimescaleDB MySQL MariaDB ClickHouse

Configuration

sgl.yamldiffers

DuckDB
CockroachDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: harbour
4  - name: harbour
5    dialect: duckdb
5    dialect: cockroachdb
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
CockroachDB
1CREATE SEQUENCE boat_ids;
2
3CREATE TABLE boats (
1CREATE TABLE boats (
4    id   BIGINT  PRIMARY KEY DEFAULT nextval('boat_ids'),
2    id   INT8   NOT NULL DEFAULT unique_rowid() PRIMARY KEY,
5    name VARCHAR NOT NULL
3    name STRING NOT NULL
6);
4);

db/migrations/0001_boats.down.sqldiffers

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

db/migrations/0002_boat_home_port.up.sqldiffers

DuckDB
CockroachDB
1ALTER TABLE boats ADD COLUMN home_port VARCHAR DEFAULT 'Padstow';
1ALTER TABLE boats ADD COLUMN home_port STRING NOT NULL DEFAULT 'Padstow';

db/migrations/0002_boat_home_port.down.sqlidentical

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

Queries

AddBoatidentical

DuckDB · db/queries/boats.sql
CockroachDB · 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 (name, home_port) VALUES ($1, $2);

ListBoatsdiffers

DuckDB · db/queries/boats.sql
CockroachDB · 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 name, home_port FROM boats ORDER BY name;

What sgl generates

AddBoatdiffers

DuckDB
CockroachDB
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

DuckDB
CockroachDB
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	Name     string `json:"name"`
5	Name     string  `json:"name"`
5	HomePort string `json:"home_port"`
6	HomePort *string `json:"home_port"`
7}
6}

The program

example.godiffers

DuckDB
CockroachDB
1package main
1package main
2
2
3import (
3import (
4	"context"
4	"context"
5	"database/sql"
5	"database/sql"
6	"errors"
6	"fmt"
7	"fmt"
7	"io"
8	"io"
8	"io/fs"
9	"io/fs"
10	"sync"
9
11
10	"seagull.sh/seagull/examples/duckdb/027_migrations_at_startup/internal/store"
12	"seagull.sh/seagull/examples/cockroachdb/027_migrations_at_startup/internal/store"
11	"seagull.sh/seagull/sdk/migrate"
13	"seagull.sh/seagull/sdk/migrate"
12	"seagull.sh/seagull/sdk/migrate/migrate_backend_duckdb"
14	"seagull.sh/seagull/sdk/migrate/migrate_backend_cockroachdb"
13)
15)
14
16
15// run starts the program twice against the same database. The first start applies both
17// run deploys the program against an empty database the way an orchestrator does,
16// migrations before the program uses the schema; the second finds nothing to do.
18// starting several replicas at the same moment, each of which migrates before it works.
19// Then one replica restarts and finds nothing to migrate.
17//
20//
18// Takes database (*sql.DB) which is the database to migrate, empty or not.
21// 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.
22// 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.
23// Takes output (io.Writer) which receives what each start did.
21//
24//
22// Returns error when migrating or a query fails.
25// Returns error when migrating or a query fails.
23func run(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
26func run(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
24	for _, boat := range []string{"Kittiwake", "Puffin"} {
27	replicas := []string{"Kittiwake", "Puffin", "Gannet"}
25		if err := start(ctx, database, migrations, boat, output); err != nil {
28	applied, err := deploy(ctx, database, migrations, replicas)
26			return err
29	if err != nil {
27		}
30		return err
28	}
31	}
32	fmt.Fprintf(output, "%d replicas started at once and applied %d migrations between them\n", len(replicas), applied)
33
34	restarted, err := start(ctx, database, migrations, "Cormorant")
35	if err != nil {
36		return err
37	}
38	fmt.Fprintf(output, "a replica restarted and applied %d migrations\n", restarted)
39
40	if err := printBoats(ctx, database, output); err != nil {
41		return err
42	}
43	if err := refuseAdvisoryLock(database, migrations, output); err != nil {
44		return err
45	}
29	return printHistory(ctx, database, migrations, output)
46	return printHistory(ctx, database, migrations, output)
30}
47}
31
48
49// deploy starts one replica per boat at the same moment and waits for them all.
50//
51// The replicas here share this process's connection pool, where real replicas are
52// separate processes with pools of their own. The lock lives in the database, so it
53// serialises them the same way.
54//
55// Takes database (*sql.DB) which is the database.
56// Takes migrations (fs.FS) which holds the migration files.
57// Takes boats ([]string) which names the boat each replica adds.
58//
59// Returns int which is how many migrations the replicas applied between them.
60// Returns error which joins every replica's failure.
61func deploy(ctx context.Context, database *sql.DB, migrations fs.FS, boats []string) (int, error) {
62	applied := make([]int, len(boats))
63	failures := make([]error, len(boats))
64	var replicas sync.WaitGroup
65	for index, boat := range boats {
66		replicas.Go(func() {
67			applied[index], failures[index] = start(ctx, database, migrations, boat)
68		})
69	}
70	replicas.Wait()
71
72	total := 0
73	for _, count := range applied {
74		total += count
75	}
76	return total, errors.Join(failures...)
77}
78
32// start is what the program does each time it starts: migrate first, then work. Here the
79// 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.
80// work is adding a boat.
34//
81//
35// Takes database (*sql.DB) which is the database.
82// Takes database (*sql.DB) which is the database.
36// Takes migrations (fs.FS) which holds the migration files.
83// Takes migrations (fs.FS) which holds the migration files.
37// Takes boat (string) which names the boat to add.
84// Takes boat (string) which names the boat to add.
38// Takes output (io.Writer) which receives what happened.
39//
85//
40// Returns error when migrating or a query fails.
86// Returns int which is how many migrations this start applied.
41func start(ctx context.Context, database *sql.DB, migrations fs.FS, boat string, output io.Writer) error {
87// Returns error when migrating or the query fails.
42	// migrate_backend_duckdb.New() is the DuckDB backend: it knows how to create the history
88func start(ctx context.Context, database *sql.DB, migrations fs.FS, boat string) (int, error) {
43	// table, binds its own queries with $N placeholders, and runs each migration in a
89	// migrate_backend_cockroachdb.New() is the PostgreSQL backend with a lock table in place
44	// transaction, as DuckDB DDL is transactional. The backend takes no database lock;
90	// of an advisory lock, which CockroachDB does not have. Up creates sgl_migration_lock if
45	// callers must serialise migrations across migrator instances. NewMigrator reads the
91	// it is missing and holds its one row with SELECT ... FOR UPDATE in an open transaction,
46	// numbered *.up.sql and *.down.sql files from the directory inside migrations.
92	// so a second replica waits on that row until the first commits.
47	migrator, err := migrate.NewMigrator(database, migrate_backend_duckdb.New(), migrations, "db/migrations")
93	//
94	// By default CockroachDB keeps a SERIALIZABLE transaction's FOR UPDATE locks in memory
95	// and drops them when a range splits or moves, which a schema change such as 0002's ADD
96	// COLUMN ... DEFAULT can cause. The backend turns on
97	// enable_durable_locking_for_serializable for its lock transaction, so the lock holds
98	// until the migrations are done; that needs CockroachDB 23.2 or later.
99	//
100	// The migrations run on a second connection, one statement at a time and not in a
101	// transaction: CockroachDB cannot roll a schema change back as part of a larger
102	// transaction, so the backend records each finished statement instead. A file that fails
103	// partway is marked dirty and resumes from the statement that failed once the cause is
104	// fixed. Keeping one schema change per file keeps each step its own record.
105	migrator, err := migrate.NewMigrator(database, migrate_backend_cockroachdb.New(), migrations, "db/migrations")
48	if err != nil {
106	if err != nil {
49		return err
107		return 0, err
50	}
108	}
51
109
52	// Up applies every migration not yet in the history, in version order, and returns how
110	// Up applies every migration not yet in the history and returns how many it applied. It
53	// many it applied. On a database that is up to date it applies none.
111	// takes the lock only when something is pending, and reads the history again once it
112	// holds the lock, so a replica that waited finds the work done.
54	applied, err := migrator.Up(ctx)
113	applied, err := migrator.Up(ctx)
55	if err != nil {
114	if err != nil {
56		return fmt.Errorf("migrating: %w", err)
115		return 0, fmt.Errorf("migrating: %w", err)
57	}
116	}
58	fmt.Fprintf(output, "start-up: applied %d migrations\n", applied)
59
117
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)
118	queries := store.New(database)
63	if err := queries.AddBoat(ctx, store.AddBoatParams{Name: boat, HomePort: new("Padstow")}); err != nil {
119	if err := queries.AddBoat(ctx, store.AddBoatParams{Name: boat, HomePort: "Padstow"}); err != nil {
64		return fmt.Errorf("adding %s: %w", boat, err)
120		return 0, fmt.Errorf("adding %s: %w", boat, err)
65	}
121	}
66	boats, err := queries.ListBoats(ctx)
122	return applied, nil
123}
124
125// printBoats lists the harbour, which every replica wrote to once the schema existed.
126//
127// Takes database (*sql.DB) which is the migrated database.
128// Takes output (io.Writer) which receives the list.
129//
130// Returns error when the query fails.
131func printBoats(ctx context.Context, database *sql.DB, output io.Writer) error {
132	boats, err := store.New(database).ListBoats(ctx)
67	if err != nil {
133	if err != nil {
68		return fmt.Errorf("listing boats: %w", err)
134		return fmt.Errorf("listing boats: %w", err)
69	}
135	}
70	for _, row := range boats {
136	for _, row := range boats {
71		homePort := "an unknown port"
137		fmt.Fprintf(output, "  %-9s of %s\n", 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	}
138	}
77	return nil
139	return nil
78}
140}
79
141
80// printHistory prints the migration history the migrator keeps in the database.
142// refuseAdvisoryLock shows that the CockroachDB backend rejects an advisory lock, as a
143// configuration copied from PostgreSQL might ask for, before it touches the database.
81//
144//
145// Takes database (*sql.DB) which is the database.
146// Takes migrations (fs.FS) which holds the migration files.
147// Takes output (io.Writer) which receives the refusal.
148//
149// Returns error when the backend accepts the advisory lock.
150func refuseAdvisoryLock(database *sql.DB, migrations fs.FS, output io.Writer) error {
151	backend := migrate_backend_cockroachdb.New(migrate.WithLock(migrate.LockAdvisory))
152	_, err := migrate.NewMigrator(database, backend, migrations, "db/migrations")
153	if err == nil {
154		return errors.New("an advisory lock on CockroachDB gave no error, want migrate.ErrUnsupportedLock")
155	}
156	if !errors.Is(err, migrate.ErrUnsupportedLock) {
157		return fmt.Errorf("an advisory lock on CockroachDB gave %w, want migrate.ErrUnsupportedLock", err)
158	}
159	fmt.Fprintf(output, "advisory lock refused: %v\n", err)
160	return nil
161}
162
163// printHistory prints the migration history the migrator keeps in the database, and the
164// table its lock uses.
165//
82// Takes database (*sql.DB) which is the migrated database.
166// Takes database (*sql.DB) which is the migrated database.
83// Takes migrations (fs.FS) which holds the migration files.
167// Takes migrations (fs.FS) which holds the migration files.
84// Takes output (io.Writer) which receives the history.
168// Takes output (io.Writer) which receives the history.
85//
169//
86// Returns error when the history cannot be read.
170// Returns error when the history cannot be read.
87func printHistory(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
171func printHistory(ctx context.Context, database *sql.DB, migrations fs.FS, output io.Writer) error {
88	backend := migrate_backend_duckdb.New()
172	backend := migrate_backend_cockroachdb.New()
89	migrator, err := migrate.NewMigrator(database, backend, migrations, "db/migrations")
173	migrator, err := migrate.NewMigrator(database, backend, migrations, "db/migrations")
90	if err != nil {
174	if err != nil {
91		return err
175		return err
92	}
176	}
93	statuses, err := migrator.Status(ctx)
177	statuses, err := migrator.Status(ctx)
94	if err != nil {
178	if err != nil {
95		return fmt.Errorf("reading migration status: %w", err)
179		return fmt.Errorf("reading migration status: %w", err)
96	}
180	}
97	fmt.Fprintf(output, "history in %s:\n", backend.Names().History)
181	names := backend.Names()
182	fmt.Fprintf(output, "history in %s, locked through %s:\n", names.History, names.MigrationLock)
98	for _, status := range statuses {
183	for _, status := range statuses {
99		fmt.Fprintf(output, "  %04d %-15s applied=%t\n", status.Version, status.Name, status.Applied)
184		fmt.Fprintf(output, "  %04d %-15s applied=%t\n", status.Version, status.Name, status.Applied)
100	}
185	}
101	return nil
186	return nil
102}
187}

Go type differences

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

Query	Field	DuckDB	CockroachDB
AddBoat	parameter `home_port`	`*string`	`string`
ListBoats	column `home_port`	`*string`	`string`