Database Patterns

Field	Value
Type	Agent Reference
Source	~/.copilot/agents/_refs/go-backend-engineer/database-patterns.md
Description	Not specified

Source Content

Database Patterns

Default ORM is GORM with pgx driver. Reach for sqlc when queries get complex enough that GORM’s abstraction hurts more than helps. Migrations via Atlas (declarative HCL, preferred) or golang-migrate / goose (SQL files).

Repository pattern

Service depends on an interface defined alongside the service; the postgres package implements it. The service is unaware of GORM.

internal/repository/user.go

package repository

import (
    "context"
    "github.com/yourorg/project/internal/domain"
)

type UserRepository interface {
    Create(ctx context.Context, u *domain.User) error
    GetByID(ctx context.Context, id string) (*domain.User, error)
    GetByEmail(ctx context.Context, email, tenantID string) (*domain.User, error)
    Update(ctx context.Context, u *domain.User) error
    Delete(ctx context.Context, id string) error
    List(ctx context.Context, tenantID string, limit, offset int) ([]*domain.User, int64, error)
}

internal/repository/postgres/user.go

package postgres

import (
    "context"
    "errors"
    "gorm.io/gorm"
    "github.com/yourorg/project/internal/domain"
)

type userRepo struct{ db *gorm.DB }

func NewUserRepository(db *gorm.DB) *userRepo { return &userRepo{db: db} }

func (r *userRepo) GetByID(ctx context.Context, id string) (*domain.User, error) {
    var u domain.User
    err := r.db.WithContext(ctx).First(&u, "id = ?", id).Error
    if errors.Is(err, gorm.ErrRecordNotFound) {
        return nil, domain.ErrNotFound
    }
    return &u, err
}

Connection pool sizing

Setting	Value	Reason
MaxOpenConns	<= replicas * (workers + 4)	avoid exhausting Postgres max_connections
MaxIdleConns	MaxOpenConns / 2	reuse warm conns
ConnMaxLifetime	5m	rotate to pick up DNS/CNPG failover
ConnMaxIdleTime	1m	release before pgbouncer kills

When fronted by pgbouncer in transaction pooling mode (default for CloudNativePG poolers): disable prepared statements (PreferSimpleProtocol: true for pgx) or use the session pooler.

sqlc for complex reads

-- queries/users.sql
-- name: GetUser :one
SELECT * FROM users WHERE id = $1 LIMIT 1;

-- name: ListUsers :many
SELECT * FROM users
WHERE tenant_id = $1
ORDER BY created_at DESC
LIMIT $2 OFFSET $3;

sqlc generate produces type-safe Go. Mix freely with GORM in the same repo — keep r.queries (sqlc) alongside r.db (GORM) on the repository struct.

Migrations

Atlas (preferred — declarative)

# schema.hcl — desired schema, Atlas computes the diff
table "users" {
  schema = schema.public
  column "id"         { type = uuid, null = false }
  column "email"      { type = text, null = false }
  column "tenant_id"  { type = uuid, null = false }
  column "created_at" { type = timestamptz, default = sql("now()") }
  primary_key { columns = [column.id] }
  index "idx_users_tenant" { columns = [column.tenant_id] }
}

atlas migrate diff add_users --to file://schema.hcl --dev-url docker://postgres/15
atlas migrate apply --url $DB_URL

golang-migrate (SQL files)

migrate create -ext sql -dir ./migrations -seq create_users
migrate -path ./migrations -database "$DB_URL" up

Naming: NNNNNN_short_description.{up,down}.sql, monotonic sequence.

Transactions

Wrap business logic in the service layer, not the repository. The service receives a *gorm.DB factory or a Tx(func(ctx) error) helper:

func (s *userService) CreateWithProfile(ctx context.Context, req *Req) error {
    return s.tx.Run(ctx, func(ctx context.Context) error {
        if err := s.userRepo.Create(ctx, ...); err != nil { return err }
        return s.profileRepo.Create(ctx, ...)
    })
}

The Tx.Run helper attaches the transactional *gorm.DB to context; repos pull it back via db := r.fromCtx(ctx).

Domain errors

internal/domain/errors.go

package domain
import "errors"

var (
    ErrNotFound           = errors.New("not found")
    ErrEmailAlreadyExists = errors.New("email already exists")
    ErrConflict           = errors.New("conflict")
)

Repository translates GORM/pgx errors → domain errors. Handler translates domain errors → HTTP status (RFC 9457 Problem Details).