mAi
1fcf6356f8
The destructive deltas in 0010 (drop parent_id, drop path) broke idempotent re-runs of 0001/0002 on every boot — those expect the legacy columns to exist. Stop relying on every migration being safe-to-rerun and track applied versions in projax.schema_migrations instead. ApplyMigrations now: - ensures projax.schema_migrations exists, - reads the set of applied filenames, - applies only the missing ones, in lexicographic order, - records each apply on success. Existing fleet was bootstrapped via MCP-as-supabase-admin (where each 0001..0010 was applied directly to the live DB before this commit existed). To match the new runner's expectations, the tracker was seeded with all ten names before this push (matching DB state). migrate_test.go: TestMigrationsAreIdempotent now asserts on parent_ids shape instead of the old parent_id column. This is the production-down fix — the previous deploy was crashlooping on 'apply 0001_init.sql: ERROR: column "path" does not exist'.
86 lines
2.4 KiB
Go
86 lines
2.4 KiB
Go
package db
|
|
|
|
import (
|
|
"context"
|
|
"embed"
|
|
"fmt"
|
|
"sort"
|
|
"strings"
|
|
|
|
"github.com/jackc/pgx/v5/pgxpool"
|
|
)
|
|
|
|
//go:embed migrations/*.sql
|
|
var migrations embed.FS
|
|
|
|
// EmbeddedMigrations exposes the raw embedded FS for tests.
|
|
var EmbeddedMigrations = migrations
|
|
|
|
// ApplyMigrations runs every embedded migration that has not yet been
|
|
// recorded in projax.schema_migrations. Migrations are applied in lexicographic
|
|
// filename order; each successful apply inserts a row in schema_migrations so
|
|
// subsequent boots short-circuit without re-running. This lets later
|
|
// migrations destructively drop columns earlier ones created — historical
|
|
// idempotency-on-re-run is no longer required.
|
|
func ApplyMigrations(ctx context.Context, pool *pgxpool.Pool) error {
|
|
// Bootstrap the tracker. Lives in the projax schema so it shares grants
|
|
// with the rest of the surface. CREATE SCHEMA IF NOT EXISTS would fail
|
|
// without database-level CREATE; the tracker only creates within a
|
|
// schema we already own.
|
|
if _, err := pool.Exec(ctx, `
|
|
create table if not exists projax.schema_migrations (
|
|
name text primary key,
|
|
applied_at timestamptz not null default now()
|
|
)`); err != nil {
|
|
return fmt.Errorf("ensure schema_migrations: %w", err)
|
|
}
|
|
|
|
applied := map[string]struct{}{}
|
|
rows, err := pool.Query(ctx, `select name from projax.schema_migrations`)
|
|
if err != nil {
|
|
return fmt.Errorf("read applied migrations: %w", err)
|
|
}
|
|
for rows.Next() {
|
|
var n string
|
|
if err := rows.Scan(&n); err != nil {
|
|
rows.Close()
|
|
return fmt.Errorf("scan applied: %w", err)
|
|
}
|
|
applied[n] = struct{}{}
|
|
}
|
|
rows.Close()
|
|
|
|
entries, err := migrations.ReadDir("migrations")
|
|
if err != nil {
|
|
return fmt.Errorf("read migrations dir: %w", err)
|
|
}
|
|
names := make([]string, 0, len(entries))
|
|
for _, e := range entries {
|
|
if e.IsDir() || !strings.HasSuffix(e.Name(), ".sql") {
|
|
continue
|
|
}
|
|
names = append(names, e.Name())
|
|
}
|
|
sort.Strings(names)
|
|
|
|
for _, name := range names {
|
|
if _, ok := applied[name]; ok {
|
|
continue
|
|
}
|
|
body, err := migrations.ReadFile("migrations/" + name)
|
|
if err != nil {
|
|
return fmt.Errorf("read %s: %w", name, err)
|
|
}
|
|
if _, err := pool.Exec(ctx, string(body)); err != nil {
|
|
return fmt.Errorf("apply %s: %w", name, err)
|
|
}
|
|
if _, err := pool.Exec(ctx,
|
|
`insert into projax.schema_migrations (name) values ($1) on conflict (name) do nothing`,
|
|
name,
|
|
); err != nil {
|
|
return fmt.Errorf("record applied %s: %w", name, err)
|
|
}
|
|
}
|
|
return nil
|
|
}
|