raw-sql-querybuilder-testing-patterns

Raw database/sql + Query Builder + Mock-Generated Repositories

A common Go data-access shape that isn't an ORM (GORM), a codegen tool (sqlc), or a fluent third-party builder (squirrel): a small, in-house query builder wrapping database/sql directly, plus interfaces over *sql.DB/ *sql.Tx specifically so mockgen-generated mocks can stand in for the database in unit tests. Distinct from postgres-patterns (which assumes pgx/idiomatic Postgres tooling) and database-migrations (which assumes a specific migration framework) — this skill is about the repository/ data-access layer itself when the stack is exactly this shape.

Interfaces exist for testability, not abstraction for its own sake

type Q interface {
    QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
    QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
    ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
}

//go:generate mockgen -destination=mocks/mock_db.go -package=mocks . Q

The point of wrapping *sql.DB/*sql.Tx behind a narrow interface like this is exclusively to let a repository be unit-tested against a generated mock instead of a real database — it is not a general abstraction layer to extend for its own sake. Keep the interface as narrow as the repository actually needs (the specific *Context methods it calls), not the full *sql.DB surface.

Translate driver errors at the repository boundary

func (r *RoomRepository) FindByID(ctx context.Context, id int64) (*Room, error) {
    row := r.db.QueryRowContext(ctx, "SELECT ... FROM rooms WHERE id = ?", id)
    var room Room
    if err := row.Scan(&room.ID, &room.Name /* ... */); err != nil {
        return nil, translateDBError(ctx, err)
    }
    return &room, nil
}

Callers above the repository layer should see a consistent, translated error type — not a raw driver error (sql.ErrNoRows, a MySQL-specific error code, a Postgres-specific error code) leaking upward. Centralize the translation once, at the boundary, rather than letting each repository method interpret driver errors its own way.