The Repository Pattern with pgx

Why Not Just Pass the Pool Around

There is a seductive kind of simplicity in handing a *pgxpool.Pool to every service and letting each handler write whatever SQL it needs. For a tiny codebase, that works. For Fieldwork, it would mean SQL shape, tenant scoping, and row mapping all get repeated across handlers, background jobs, and future consumers. The problem is not that raw SQL appears in the codebase; raw SQL is a feature here. The problem is letting every layer invent its own version of the same data access rules.

I rejected both extremes: no abstraction at all and heavy ORM abstraction. No abstraction leaks storage concerns into transport and orchestration code. A heavy ORM hides query cost, encourages magical preloading, and makes tenant-scoped joins harder to see at review time. The middle ground is a repository layer with small interfaces, explicit methods, and pgx underneath so the SQL remains visible and intentional.

The Repository Shape Fieldwork Uses

A Fieldwork repository is shaped around a concrete set of use cases, not around CRUD purity. TaskRepository is allowed to have methods like ListProjectTasks, CreateTask, MoveTask, and FindTaskByID because those map to real operations the service performs. I am not trying to build a generic repository library that can store anything. The interface exists to define what the service needs from persistence and to give tests a seam smaller than 'boot Postgres for every unit test.'

Notice that tenant and workspace scope are part of the method signatures. That is not busywork. In Fieldwork, persistence methods should make scoping requirements impossible to ignore. If the function can be called without tenant context, somebody eventually will call it that way. The repository API itself participates in the safety story.

Keeping SQL Explicit with pgx

pgx gives Fieldwork the level of control I actually want: straightforward queries, good PostgreSQL support, predictable scanning, and access to transactions without ceremony. The repository implementation keeps SQL close to the code that maps rows into domain structs. That makes indexes, joins, and tenant filters visible when reviewing changes. An ORM would compress the code, but it would also hide the exact predicates that matter most in a multi-tenant system.

The repository implementations depend on a small Queryer abstraction rather than directly on pgxpool.Pool, so the same code can run against a transaction in write-heavy flows. That is one of the few abstractions here that pays for itself immediately. It keeps transaction handling composable without inventing a fake data access framework.

What This Pattern Does Not Try to Do

Fieldwork's repository layer is not pretending databases do not exist. It does not erase SQL, guarantee portability to another datastore, or model every table as a perfect aggregate root. That kind of abstraction theater usually produces worse code than honest SQL. The repository layer exists for narrower reasons: centralize tenant-aware data access, keep handlers and services from hand-coding storage details, and make tests substitute behavior without mocking pgx internals directly.

It also does not mean one repository per table no matter what. Some operations naturally span tables. A membership check may join memberships, workspaces, and users. A task listing may join tasks with projects for projection purposes. That is acceptable as long as the repository still represents a coherent slice of behavior instead of becoming a general-purpose SQL bucket.

The Decision

• Keep SQL explicit with pgx rather than hiding the database behind an ORM.
• Put persistence behind small, interface-shaped repositories aligned to use cases.
• Require tenant scope in repository method signatures where relevant.
• Use a tiny Queryer abstraction to support both pools and transactions.

Fieldwork uses repositories with pgx because the system needs discipline, not magic. Passing the pool everywhere would make storage concerns leak upward. An ORM would make the important predicates less visible. Small repositories keep SQL honest, tests practical, and tenant-aware access patterns concentrated in one layer where they can be reviewed with the scrutiny they deserve.