Stage 7 · Master
Phase 2 — Organization Service
Repository Layer
Write the SQL that actually reads and writes organizations, behind an interface declared where it is consumed, translating storage-specific failures into two small, storage-agnostic sentinel errors.
Defining the Interface Where It's Consumed, Not Where It's Implemented
Idiomatic Go declares an interface in the package that uses it, not the package that implements it — "accept interfaces, return structs." The organization service's Repository interface therefore lives in internal/service, not internal/repository; the concrete Postgres implementation in internal/repository satisfies it structurally, with no import back to internal/service required, because Go interfaces are satisfied implicitly.
package service
import (
"context"
"github.com/google/uuid"
"github.com/hoa-platform/backend/services/organization/internal/model"
"github.com/hoa-platform/backend/services/organization/internal/repository"
)
// Repository is declared here, in the package that consumes it, so any
// concrete implementation (internal/repository/organization_postgres.go,
// or a hand-written fake in this package's own tests) only needs to
// satisfy this shape — no import cycle is possible, because this file
// imports internal/repository only for the ListFilter value type below,
// never the other way around.
type Repository interface {
Create(ctx context.Context, o *model.Organization) error
GetByID(ctx context.Context, id uuid.UUID) (*model.Organization, error)
GetBySlug(ctx context.Context, slug string) (*model.Organization, error)
List(ctx context.Context, filter repository.ListFilter) ([]model.Organization, int, error)
Update(ctx context.Context, o *model.Organization) error
}
ListFilter itself lives in internal/repository, not here, because it describes a query shape naturally paired with the SQL that consumes it. The interface still lives with its consumer — only the filter's type definition lives with the implementation, and the import runs one way: service depends on repository for this type and for the sentinel errors below, never the reverse.
Hand-Written SQL With pgx, No ORM in Between
// Package repository implements internal/service's Repository interface
// against real PostgreSQL. These two sentinel errors, plus ListFilter
// below, are the only things internal/service imports from this package —
// plain values, never pgx-specific types, so the service layer stays
// testable against a fake that never touches pgx at all.
package repository
import (
"errors"
"github.com/hoa-platform/backend/services/organization/internal/model"
)
var (
ErrNotFound = errors.New("repository: organization not found")
ErrDuplicateSlug = errors.New("repository: slug already exists")
)
// ListFilter narrows List by status; an empty Status means no filter.
type ListFilter struct {
Status model.Status
Limit int
Offset int
}
package repository
import (
"context"
"errors"
"fmt"
"github.com/google/uuid"
"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgconn"
"github.com/jackc/pgx/v5/pgxpool"
"github.com/hoa-platform/backend/services/organization/internal/model"
)
const uniqueViolationCode = "23505"
type OrganizationPostgres struct {
pool *pgxpool.Pool
}
func NewOrganizationPostgres(pool *pgxpool.Pool) *OrganizationPostgres {
return &OrganizationPostgres{pool: pool}
}
func (r *OrganizationPostgres) Create(ctx context.Context, o *model.Organization) error {
const q = `
INSERT INTO organizations
(name, slug, registration_number, contact_email, contact_phone,
address_line1, address_line2, city, state, postal_code, country, status)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
RETURNING id, created_at, updated_at`
row := r.pool.QueryRow(ctx, q,
o.Name, o.Slug, o.RegistrationNumber, o.ContactEmail, o.ContactPhone,
o.AddressLine1, o.AddressLine2, o.City, o.State, o.PostalCode, o.Country, o.Status,
)
if err := row.Scan(&o.ID, &o.CreatedAt, &o.UpdatedAt); err != nil {
var pgErr *pgconn.PgError
if errors.As(err, &pgErr) && pgErr.Code == uniqueViolationCode {
return ErrDuplicateSlug
}
return fmt.Errorf("repository: creating organization: %w", err)
}
return nil
}
func (r *OrganizationPostgres) GetByID(ctx context.Context, id uuid.UUID) (*model.Organization, error) {
const q = `
SELECT id, name, slug, registration_number, contact_email, contact_phone,
address_line1, address_line2, city, state, postal_code, country,
status, created_at, updated_at
FROM organizations WHERE id = $1`
return r.scanOne(r.pool.QueryRow(ctx, q, id))
}
func (r *OrganizationPostgres) GetBySlug(ctx context.Context, slug string) (*model.Organization, error) {
const q = `
SELECT id, name, slug, registration_number, contact_email, contact_phone,
address_line1, address_line2, city, state, postal_code, country,
status, created_at, updated_at
FROM organizations WHERE slug = $1`
return r.scanOne(r.pool.QueryRow(ctx, q, slug))
}
func (r *OrganizationPostgres) scanOne(row pgx.Row) (*model.Organization, error) {
var o model.Organization
err := row.Scan(
&o.ID, &o.Name, &o.Slug, &o.RegistrationNumber, &o.ContactEmail, &o.ContactPhone,
&o.AddressLine1, &o.AddressLine2, &o.City, &o.State, &o.PostalCode, &o.Country,
&o.Status, &o.CreatedAt, &o.UpdatedAt,
)
if err != nil {
if errors.Is(err, pgx.ErrNoRows) {
return nil, ErrNotFound
}
return nil, fmt.Errorf("repository: scanning organization: %w", err)
}
return &o, nil
}
func (r *OrganizationPostgres) List(ctx context.Context, filter ListFilter) ([]model.Organization, int, error) {
where := ""
args := []any{filter.Limit, filter.Offset}
if filter.Status != "" {
where = "WHERE status = $3"
args = append(args, filter.Status)
}
q := fmt.Sprintf(`
SELECT id, name, slug, registration_number, contact_email, contact_phone,
address_line1, address_line2, city, state, postal_code, country,
status, created_at, updated_at
FROM organizations %s
ORDER BY created_at DESC
LIMIT $1 OFFSET $2`, where)
rows, err := r.pool.Query(ctx, q, args...)
if err != nil {
return nil, 0, fmt.Errorf("repository: listing organizations: %w", err)
}
defer rows.Close()
var results []model.Organization
for rows.Next() {
var o model.Organization
if err := rows.Scan(
&o.ID, &o.Name, &o.Slug, &o.RegistrationNumber, &o.ContactEmail, &o.ContactPhone,
&o.AddressLine1, &o.AddressLine2, &o.City, &o.State, &o.PostalCode, &o.Country,
&o.Status, &o.CreatedAt, &o.UpdatedAt,
); err != nil {
return nil, 0, fmt.Errorf("repository: scanning organization row: %w", err)
}
results = append(results, o)
}
var total int
countQ := "SELECT count(*) FROM organizations"
countArgs := []any{}
if filter.Status != "" {
countQ = "SELECT count(*) FROM organizations WHERE status = $1"
countArgs = append(countArgs, filter.Status)
}
if err := r.pool.QueryRow(ctx, countQ, countArgs...).Scan(&total); err != nil {
return nil, 0, fmt.Errorf("repository: counting organizations: %w", err)
}
return results, total, nil
}
func (r *OrganizationPostgres) Update(ctx context.Context, o *model.Organization) error {
const q = `
UPDATE organizations
SET name = $2, contact_email = $3, contact_phone = $4,
address_line1 = $5, address_line2 = $6, status = $7, updated_at = now()
WHERE id = $1
RETURNING updated_at`
err := r.pool.QueryRow(ctx, q,
o.ID, o.Name, o.ContactEmail, o.ContactPhone, o.AddressLine1, o.AddressLine2, o.Status,
).Scan(&o.UpdatedAt)
if err != nil {
if errors.Is(err, pgx.ErrNoRows) {
return ErrNotFound
}
return fmt.Errorf("repository: updating organization: %w", err)
}
return nil
}
Every method returns either a sentinel (ErrNotFound, ErrDuplicateSlug) or a freshly wrapped error — never a raw pgx or pgconn type. This package imports internal/model but never internal/service, keeping the dependency direction strictly one-way.
Catching pgErr.Code == "23505" (Postgres's unique_violation code) is the only place in the entire service that needs to know Postgres-specific error codes. Translating it immediately into the storage-agnostic ErrDuplicateSlug means that if this service ever migrated to a different database, only this one file would need to change — the service layer's logic would be untouched.
Why Translating to apperr Happens One Layer Up, Not Here
This repository never imports pkg/apperr. Its job is to report what happened in storage terms (not found, duplicate slug, or an unexpected error) — not to decide what HTTP status or client-facing message that implies. The Service Layer lesson, next, is where ErrNotFound and ErrDuplicateSlug get translated into apperr.NotFound and apperr.Conflict, alongside the actual business rules that care about those outcomes.
Testing the Exact SQL Outside Go, Before Trusting the Wrapper
Before trusting that the Go code above is correct, it is faster to paste the raw List query directly into psql with concrete values and confirm it returns what you expect — isolating whether a bug, if one exists later, is in the SQL itself or in the Go scanning code around it.
Applied exercise
Reproduce the duplicate-slug translation without writing a single Go test yet
The Unit Testing lesson later writes a real fake-backed test suite. For now, confirm the unique constraint and its translation manually.
- Attempt to insert a second organization row with the same slug ('maple-grove') directly via psql and record Postgres's exact error, including its SQLSTATE code.
- Confirm the SQLSTATE matches the uniqueViolationCode constant (23505) used in organization_postgres.go.
- Delete both seeded rows via psql afterward so the table is empty again for the next lesson's tests.
Deliverable
A short transcript showing the psql unique-violation error and its SQLSTATE code, matching the constant in the Go source.
Completion checks
- The reported SQLSTATE is exactly 23505.
- The organizations table is empty again by the end of the exercise.
Why does OrganizationPostgres.Create check for pgconn.PgError with code 23505 instead of trusting the application to check for a duplicate slug before inserting?
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.