Hand-written SQL in repositories

Status: Accepted (2026-09-14) · Amends: ADR-0005, ADR-0022

Context#

ADR-0005 chose sqlc: SQL in db/queries/*.sql, Go generated into internal/db, and repositories mapping generated types to domain types. The maintainer's previous production API used hand-written SQL instead, one repository file per operation, and the team found it easy to read, review and change. A generated package adds a tool to install, a DO NOT EDIT directory, a second place to look for each query, and mapping code between generated and domain types.

Options#

1. sqlc (ADR-0005 as written).
2. Hand-written SQL with pgx, one file per repository operation.
3. A query builder or ORM.

Decision#

Option 2. The generated app has no sqlc.yaml, no db/queries and no internal/db.

Repository layout (per module)#

internal/modules/users/repository/
├── store.go          UserStore{db postgres.DBTX}; NewUserStore(db)
├── errors.go         constraint names → domain errors
├── scan.go           unexported row structs and mapping to domain types (when shared)
├── insert_user.go    const insertUserSQL + (*UserStore).InsertUser
├── update_user.go    const updateUserSQL + (*UserStore).UpdateUser
├── delete_user.go    const deleteUserSQL + (*UserStore).DeleteUser
├── select_user.go    const selectUserByIDSQL … + SelectUserByID, SelectUsers
└── *_test.go         every query runs against Docker PostgreSQL through pgtest

// Shape only: repository/insert_user.go
const insertUserSQL = `
	INSERT INTO users (id, name, email, password_hash, role, status)
	VALUES ($1, $2, $3, $4, $5, $6)
	RETURNING created_at, updated_at`

// InsertUser inserts a new user row.
func (s *UserStore) InsertUser(ctx context.Context, u *domain.User) error {
	err := s.db.QueryRow(ctx, insertUserSQL,
		u.ID, u.Name, u.Email, u.PasswordHash, u.Role, u.Status,
	).Scan(&u.CreatedAt, &u.UpdatedAt)
	if constraint, ok := postgres.UniqueViolation(err); ok && constraint == usersEmailKey {
		return domain.ErrDuplicateEmail
	}
	if err != nil {
		return fmt.Errorf("insert user: %v", err)
	}
	return nil
}

Rules#

Why#

• A query and the Go code that runs it are in one file: "go to definition" lands on the SQL.
• No code-generation tool, no generated package, no mapping layer between generated and domain types.
• Familiar to the maintainer's team and to most Go developers who know database/sql or pgx.

Trade-offs#

• SQL is not checked against the schema at build time; typos and column mismatches surface in repository tests instead of at generate time. Repository tests are therefore required, not optional.
• More hand-written scanning code for wide tables (reduced by RowToStructByName).

Consequences#

• ADR-0005's "pgx + sqlc" becomes "pgx with hand-written SQL"; goose migrations, table prefixes and immutable released migrations are unchanged.
• aps gen resource generates the repository files and their tests in this layout.
• modules/postgres provides DBTX, InTx, error classification helpers and pgtest.