Adopt golib/pg for migrations and pool construction #125

2026-09-02T00:19:18+10:00

unkin-agent commented

2026-09-02 00:19:18 +10:00

Why

The Postgres schema was a ~180-line DDL blob inlined in internal/database/postgres.go and re-executed on every start. It could only grow — each change appended another ALTER TABLE ... ADD COLUMN IF NOT EXISTS — and nothing recorded what had ever been applied. golib/pg already owns that mechanic for the estate (arrproxy#10), so artifactapi keeps the SQL and hands over the machinery.

How

Depend on git.unkin.net/unkin/golib v0.1.0.
Move the DDL verbatim into migrations/0001_init.sql, embedded by the new migrations package, and build the pool with pg.NewMigrated (LockName: "artifactapi-migrations"). The runner takes a cluster-wide advisory lock the old blob never took, so replicas starting together queue instead of racing each other through the DDL.
No-op on the live database. Production has the schema but no schema_migrations, so its first start on this build re-runs 0001. Every statement is IF NOT EXISTS-guarded, so that run changes nothing and only lands the tracking row. TestMigratingAnAlreadyPopulatedSchemaIsANoOp drops the row from a migrated database and asserts exactly that; TestMigrationsAreIdempotent keeps future migrations additive and idempotent.
Env var contract unchanged. config.DatabaseDSN stays the DSN source rather than pg.DSNFromEnv: the variable names match, but golib deliberately has no default user or database name, and artifactapi documents and ships DBUSER/DBNAME defaults of artifacts.
Drift guards: the embedded set is asserted equal to migrations/, and the derived advisory key is pinned so a rename cannot silently let two builds migrate at once.
Plumb GOPRIVATE=git.unkin.net for the first cross-repo Go dependency — exported by the Makefile, set in the Dockerfile and both woodpecker Go steps, documented in the README.

database.New's signature and every caller are untouched.

Transitive bumps come with golib's go.mod (testcontainers-go 0.42 → 0.44, otel, x/crypto, x/net); go build, go vet, golangci-lint, go test -race ./pkg/... ./internal/... and pre-commit run --all-files are clean.

## Why The Postgres schema was a ~180-line DDL blob inlined in `internal/database/postgres.go` and re-executed on every start. It could only grow — each change appended another `ALTER TABLE ... ADD COLUMN IF NOT EXISTS` — and nothing recorded what had ever been applied. `golib/pg` already owns that mechanic for the estate (arrproxy#10), so artifactapi keeps the SQL and hands over the machinery. ## How - Depend on `git.unkin.net/unkin/golib` v0.1.0. - Move the DDL **verbatim** into `migrations/0001_init.sql`, embedded by the new `migrations` package, and build the pool with `pg.NewMigrated` (`LockName: "artifactapi-migrations"`). The runner takes a cluster-wide advisory lock the old blob never took, so replicas starting together queue instead of racing each other through the DDL. - **No-op on the live database.** Production has the schema but no `schema_migrations`, so its first start on this build re-runs `0001`. Every statement is `IF NOT EXISTS`-guarded, so that run changes nothing and only lands the tracking row. `TestMigratingAnAlreadyPopulatedSchemaIsANoOp` drops the row from a migrated database and asserts exactly that; `TestMigrationsAreIdempotent` keeps future migrations additive and idempotent. - **Env var contract unchanged.** `config.DatabaseDSN` stays the DSN source rather than `pg.DSNFromEnv`: the variable names match, but golib deliberately has no default user or database name, and artifactapi documents and ships `DBUSER`/`DBNAME` defaults of `artifacts`. - Drift guards: the embedded set is asserted equal to `migrations/`, and the derived advisory key is pinned so a rename cannot silently let two builds migrate at once. - Plumb `GOPRIVATE=git.unkin.net` for the first cross-repo Go dependency — exported by the `Makefile`, set in the `Dockerfile` and both woodpecker Go steps, documented in the README. `database.New`'s signature and every caller are untouched. Transitive bumps come with golib's `go.mod` (testcontainers-go 0.42 → 0.44, otel, x/crypto, x/net); `go build`, `go vet`, `golangci-lint`, `go test -race ./pkg/... ./internal/...` and `pre-commit run --all-files` are clean.

unkin-agent added 1 commit 2026-09-02 00:19:18 +10:00

Adopt golib/pg for migrations and pool construction

ci/woodpecker/pr/pre-commit Pipeline was successful

Details

ci/woodpecker/pr/build Pipeline was successful

Details

ci/woodpecker/pr/test Pipeline was successful

Details

82bb5708c8

artifactapi's schema was a ~180-line inline DDL blob re-executed on every
start, growing an ALTER TABLE ... IF NOT EXISTS line per change with nothing
recording what had run. golib/pg already owns that mechanic for the estate, so
move the SQL into a versioned, embedded set and let the library apply it.

- Depend on git.unkin.net/unkin/golib v0.1.0.
- Move the DDL verbatim to migrations/0001_init.sql, embedded via the new
  migrations package, and build the pool with pg.NewMigrated (LockName
  "artifactapi-migrations"). The runner adds a cluster-wide advisory lock the
  old blob never took, so replicas starting together queue instead of racing
  each other through the DDL.
- The live database has the schema but no schema_migrations, so its first start
  on this build re-runs 0001. Every statement is IF NOT EXISTS-guarded, so that
  run is a no-op landing only the tracking row; a container-backed test drops
  the row from a migrated database and asserts exactly that, and a static guard
  keeps future migrations additive and idempotent.
- Keep config.DatabaseDSN as the DSN source rather than pg.DSNFromEnv: the
  variable names match, but golib has no default user or database name, and
  artifactapi documents and ships DBUSER/DBNAME defaults of "artifacts". The
  deployed env var contract is unchanged.
- Guard the embedded set against migrations/ and pin the derived advisory key,
  so neither can drift unnoticed.
- Plumb GOPRIVATE=git.unkin.net for the first cross-repo Go dependency:
  exported by the Makefile, set in the Dockerfile and the woodpecker Go steps,
  documented in the README.

benvin merged commit 404f947cba into master

2026-09-05 12:10:59 +10:00

benvin deleted branch benvin/adopt-golib-pg

2026-09-05 12:11:00 +10:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: unkin/artifactapi#125