Stage 7 · Master
The Data Layer
The Repository Pattern with pgx
Why raw SQL still needs structure, how repositories prevent tenant-scoping drift, and how Meridian should use pgx without hiding PostgreSQL behind an ORM-shaped fog.
Why a Repository Layer Exists
A repository layer exists to protect the rest of the application from having to remember storage rules repeatedly. That is especially important in a shared-schema multi-tenant system. If handlers, services, background jobs, and ad hoc helpers all write their own SQL, tenant predicates, row mapping, pagination rules, and uniqueness translations will drift. The repository pattern is not about pretending the database does not exist. It is about concentrating database discipline where it can be reviewed.
The beginner-friendly mental model is this: domain code should ask meaningful questions such as 'list overdue invoices for this tenant and unit' or 'find the membership for this tenant and user.' Repository code should answer those questions with explicit SQL and explicit row mapping. What domain code should not do is decide column lists, join order, and SQLSTATE handling every time it needs data.
| Approach | Benefit | Failure mode |
|---|---|---|
| Pass *pgxpool.Pool everywhere | No abstraction to learn | Tenant filters, scans, and error handling get duplicated everywhere |
| Heavy ORM everywhere | Less handwritten SQL | Query shape, index fit, and tenant safety become harder to see in review |
| Small repositories over pgx | Chosen: explicit SQL plus concentrated invariants | Requires deliberate interface design, which is a cost worth paying |
What Meridian Should Abstract
A useful repository is shaped around business use cases, not around generic CRUD purity. identity-service does not need a GenericRepository[T]. It needs questions such as FindMembershipByTenantAndUser, CreateTenant, and ListTenantMembers. community-service needs questions such as ListUnitsInBuilding or CreateComplaint. billing-service needs questions such as CreateInvoice, ListResidentInvoices, and RecordPayment. These names are valuable because they force the interface to carry the same scope the domain already reasons about.
Keeping SQL Visible with pgx
pgx is a strong fit because it gives full PostgreSQL control without forcing magical abstractions. The code can keep SQL explicit, use PostgreSQL-specific types and behavior where needed, and still remain straightforward Go. That combination matters in Meridian because the important queries are not trivial CRUD: they are tenant-scoped, index-aware, and often joined across containment relationships such as tenant → building → unit or tenant → resident → invoice.
A tiny Queryer abstraction is worth keeping because the same repository should work against a connection pool or against a transaction. That is the kind of abstraction that genuinely reduces duplication. A giant generic data-access framework usually does the opposite: it hides control flow that matters and introduces interfaces nobody can explain under incident pressure.
Error Translation and Testing
Repositories also define the boundary between storage-flavored failure and domain-flavored failure. PostgreSQL may report SQLSTATE 23505 for a uniqueness violation. The rest of the application should usually hear 'membership already exists for this tenant and user' or 'invoice reference number already exists in this tenant.' That translation is one of the most practical benefits of a repository layer because it keeps HTTP handlers and services from learning database internals they should not own.
Testing follows the same principle. Unit tests for services should not need to mock pgx internals. They should substitute a repository interface that answers in domain terms. Integration tests can then verify the actual SQL, indexes, and transactions against PostgreSQL. This layered testing strategy is easier to maintain than pretending every test must boot the whole stack or that no test should ever touch a real database.
Current Repository Status
No service in meridian imports pgx today, and the internal/domain packages are still empty. This lesson specifies the repository shape the implementation phase should adopt; it does not describe code that already exists in apps/identity-service, apps/community-service, or apps/billing-service.
apps/identity-service/internal/domain/domain.gomodifyResponsibility: Defines repository interfaces for Tenant, User, and Membership use cases.
Why now: The domain package should describe the questions the service needs answered before storage-specific code is written.
apps/identity-service/internal/store/postgres/membership_repository.gocreateResponsibility: Implements MembershipRepository with pgx and explicit tenant predicates.
Why now: Storage code belongs in an implementation package that can translate pgx concerns without polluting handlers or domain services.
apps/billing-service/internal/store/postgres/invoice_repository.gocreateResponsibility: Implements invoice reads and writes around tenant-scoped SQL and payment-aware constraints.
Why now: Billing queries are performance- and correctness-sensitive enough to deserve explicit SQL instead of ad hoc handler access.
- Use repositories to concentrate tenant-scoping rules, row mapping, and error translation.
- Prefer pgx with explicit SQL over magical ORM behavior for multi-tenant workloads.
- Keep repository interfaces shaped around use cases, not generic CRUD slogans.
- Abstract only what obviously helps, such as pool-versus-transaction execution.
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.