package database import ( "context" "embed" "fmt" "io/fs" "sort" "strconv" "strings" "github.com/jackc/pgx/v5" "github.com/jackc/pgx/v5/pgxpool" ) //go:embed migrations/*.sql var migrations embed.FS func Migrate(ctx context.Context, pool *pgxpool.Pool) error { tx, err := pool.BeginTx(ctx, pgx.TxOptions{}) if err != nil { return err } defer tx.Rollback(ctx) if _, err = tx.Exec(ctx, `SELECT pg_advisory_xact_lock(202602)`); err != nil { return err } if _, err = tx.Exec(ctx, `CREATE TABLE IF NOT EXISTS schema_migrations (version integer PRIMARY KEY, applied_at timestamptz NOT NULL DEFAULT clock_timestamp())`); err != nil { return err } entries, err := fs.ReadDir(migrations, "migrations") if err != nil { return err } sort.Slice(entries, func(i, j int) bool { return entries[i].Name() < entries[j].Name() }) for _, entry := range entries { if entry.IsDir() || !strings.HasSuffix(entry.Name(), ".sql") { continue } part := strings.SplitN(entry.Name(), "_", 2)[0] version, parseErr := strconv.Atoi(part) if parseErr != nil { return fmt.Errorf("invalid migration name %s", entry.Name()) } var applied bool if err = tx.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM schema_migrations WHERE version=$1)`, version).Scan(&applied); err != nil { return err } if applied { continue } sqlBytes, readErr := migrations.ReadFile("migrations/" + entry.Name()) if readErr != nil { return readErr } if _, err = tx.Exec(ctx, string(sqlBytes)); err != nil { return fmt.Errorf("migration %s: %w", entry.Name(), err) } if _, err = tx.Exec(ctx, `INSERT INTO schema_migrations(version) VALUES($1)`, version); err != nil { return err } } return tx.Commit(ctx) }