package main import ( "bytes" "context" "database/sql" "encoding/json" "errors" "log/slog" "net/url" "os" "path/filepath" "strings" "sync" "testing" "time" "github.com/google/uuid" "github.com/jackc/pgx/v5" "github.com/jackc/pgx/v5/pgconn" "github.com/jackc/pgx/v5/stdlib" ) // These tests need an explicitly supplied disposable PostgreSQL database. // Each test creates and drops only its own randomly named schema. func migrationPostgresFixture(t *testing.T) (*sql.DB, context.Context) { t.Helper() connectionURL := os.Getenv("MIGRATE_TEST_DATABASE_URL") if connectionURL == "" { t.Skip("set MIGRATE_TEST_DATABASE_URL to run PostgreSQL integration tests") } cfg, err := pgx.ParseConfig(connectionURL) if err != nil { t.Fatal("invalid test database configuration") } cfg.ConnectTimeout = 5 * time.Second admin := stdlib.OpenDB(*cfg) t.Cleanup(func() { admin.Close() }) ctx, cancel := context.WithTimeout(context.Background(), time.Minute) t.Cleanup(cancel) schema := "migration_test_" + strings.ReplaceAll(uuid.NewString(), "-", "") quoted := pgx.Identifier{schema}.Sanitize() if _, err := admin.ExecContext(ctx, "CREATE SCHEMA "+quoted); err != nil { t.Fatal("cannot create isolated migration test schema") } t.Cleanup(func() { cleanupCtx, cancel := context.WithTimeout(context.Background(), 10*time.Second) defer cancel() if _, err := admin.ExecContext(cleanupCtx, "DROP SCHEMA "+quoted+" CASCADE"); err != nil { t.Error("cannot remove isolated migration test schema") } }) cfg.RuntimeParams["search_path"] = schema // ConnString retains the original parsed URL; changing RuntimeParams alone // would not pass search_path to the separate migrator connection. scopedURL, err := url.Parse(connectionURL) if err != nil || (scopedURL.Scheme != "postgres" && scopedURL.Scheme != "postgresql") { t.Fatal("MIGRATE_TEST_DATABASE_URL must be a PostgreSQL URL") } query := scopedURL.Query() query.Set("search_path", schema) scopedURL.RawQuery = query.Encode() path := filepath.Join(t.TempDir(), "database_url") if err := os.WriteFile(path, []byte(scopedURL.String()), 0600); err != nil { t.Fatal("cannot write test connection secret") } t.Setenv("DATABASE_URL_FILE", path) db := stdlib.OpenDB(*cfg) t.Cleanup(func() { db.Close() }) return db, ctx } func migrationLogRecords(t *testing.T, output *bytes.Buffer) []map[string]any { t.Helper() var records []map[string]any for _, line := range bytes.Split(bytes.TrimSpace(output.Bytes()), []byte("\n")) { if len(line) == 0 { continue } var record map[string]any if err := json.Unmarshal(line, &record); err != nil { t.Fatal("migrator emitted a non-JSON log record") } for key := range record { switch key { case "time", "level", "msg", "file", "version", "state", "applied", "duration_seconds": default: t.Fatalf("unexpected log field %q; SQL or connection details must not be logged", key) } } if strings.Contains(string(line), "CREATE TABLE") || strings.Contains(string(line), "postgres://") { t.Fatal("migration log contains SQL or a connection URL") } records = append(records, record) } return records } func completedMigrationCount(t *testing.T, output *bytes.Buffer) int { t.Helper() for _, record := range migrationLogRecords(t, output) { if record["msg"] == "migrations completed" { count, ok := record["applied"].(float64) if !ok { t.Fatal("migration completion log has no numeric applied count") } return int(count) } } t.Fatal("successful migration run has no completion log") return 0 } const lab5MigrationCount = 8 func TestPostgresMigrationsRepeatWithoutChangingDataOrLoggingSQL(t *testing.T) { db, ctx := migrationPostgresFixture(t) var output bytes.Buffer logger := slog.New(slog.NewJSONHandler(&output, nil)) if err := run(ctx, "up", time.Minute, logger); err != nil { t.Fatal("migration of empty schema failed") } if count := completedMigrationCount(t, &output); count != lab5MigrationCount { t.Fatalf("empty schema: applied %d migrations, want %d", count, lab5MigrationCount) } if _, err := db.ExecContext(ctx, "INSERT INTO users (username, display_name, password_hash) VALUES ($1, $2, $3)", "migration_fixture", "Migration fixture", "synthetic-test-only-hash"); err != nil { t.Fatal("cannot insert persistent migration fixture") } output.Reset() if err := run(ctx, "up", time.Minute, logger); err != nil { t.Fatal("repeat migration failed") } if count := completedMigrationCount(t, &output); count != 0 { t.Fatalf("repeat run: applied %d migrations, want 0", count) } var users, versions int if err := db.QueryRowContext(ctx, "SELECT count(*) FROM users WHERE username = $1", "migration_fixture").Scan(&users); err != nil || users != 1 { t.Fatal("repeat migration lost or duplicated existing data") } if err := db.QueryRowContext(ctx, "SELECT count(*) FROM goose_db_version WHERE version_id > 0 AND is_applied").Scan(&versions); err != nil || versions != lab5MigrationCount { t.Fatal("repeat migration changed version history") } output.Reset() if err := run(ctx, "status", time.Minute, logger); err != nil { t.Fatal("migration status failed") } records := migrationLogRecords(t, &output) if len(records) != lab5MigrationCount { t.Fatal("status did not report all seven migrations") } for _, record := range records { if record["state"] != "applied" { t.Fatal("status did not report applied migrations") } } } func TestPostgresConcurrentMigratorsApplyEachVersionOnce(t *testing.T) { db, ctx := migrationPostgresFixture(t) var outputs [2]bytes.Buffer var runErrors [2]error var workers sync.WaitGroup start := make(chan struct{}) for i := range outputs { workers.Add(1) go func(i int) { defer workers.Done() <-start runErrors[i] = run(ctx, "up", time.Minute, slog.New(slog.NewJSONHandler(&outputs[i], nil))) }(i) } close(start) workers.Wait() var applied int for i, err := range runErrors { if err != nil { t.Fatal("concurrent migration run failed") } applied += completedMigrationCount(t, &outputs[i]) } if applied != lab5MigrationCount { t.Fatalf("concurrent runners applied %d migrations in total, want %d", applied, lab5MigrationCount) } var versions, distinctVersions int if err := db.QueryRowContext(ctx, "SELECT count(*), count(DISTINCT version_id) FROM goose_db_version WHERE version_id > 0 AND is_applied").Scan(&versions, &distinctVersions); err != nil || versions != lab5MigrationCount || distinctVersions != lab5MigrationCount { t.Fatal("concurrent runners duplicated or lost version history") } } func TestPostgresMigrationFailureRollsBackAndPreservesCause(t *testing.T) { db, ctx := migrationPostgresFixture(t) // The first migration creates users, then conflicts with this chats table. if _, err := db.ExecContext(ctx, "CREATE TABLE chats (marker text)"); err != nil { t.Fatal("cannot create conflicting test table") } var output bytes.Buffer err := run(ctx, "up", time.Minute, slog.New(slog.NewJSONHandler(&output, nil))) var pgErr *pgconn.PgError if err == nil || err.Error() != "apply pending migrations" || !errors.As(err, &pgErr) || pgErr.Code != "42P07" { t.Fatal("migration failure did not retain a safely reportable duplicate-table error") } migrationLogRecords(t, &output) var usersAbsent bool if err := db.QueryRowContext(ctx, "SELECT to_regclass('users') IS NULL").Scan(&usersAbsent); err != nil || !usersAbsent { t.Fatal("failed migration left a partially applied users table") } var versions int if err := db.QueryRowContext(ctx, "SELECT count(*) FROM goose_db_version WHERE version_id > 0 AND is_applied").Scan(&versions); err != nil || versions != 0 { t.Fatal("failed migration was recorded as applied") } }