Example

Search Endpoint

This is the endpoint shape most teams ship first: a list page with optional text search, a few facet filters, safe sort keys, and a count query beside the results query.

The request shape

type UserSearchRequest struct {
    TenantID int
    Query    string
    Status   *string
    Roles    []string
    Sort     string
    Page     int
    PerPage  int
}

The request keeps user intent separate from SQL concerns. Empty search text should disappear. Sort keys should be chosen from a lookup table. Pagination should never be hand-built by string concatenation.

Build the list query

func BuildUserSearch(qq *quarry.Quarry, req UserSearchRequest) quarry.SQLer {
    return qq.Select(
        "id",
        "email",
        "display_name",
        "status",
        "created_at",
    ).
        From("users").
        Where(
            quarry.Eq("tenant_id", req.TenantID),
            quarry.Or(
                quarry.OptionalILike("email", req.Query),
                quarry.OptionalILike("display_name", req.Query),
            ),
            quarry.OptionalEq("status", req.Status),
            quarry.OptionalIn("role", anySlice(req.Roles)...),
        ).
        OrderBySafeDefault(req.Sort, quarry.SortMap{
            "newest": "created_at DESC",
            "alpha":  "display_name ASC",
            "email":  "email ASC",
        }, "newest").
        Page(req.Page, req.PerPage)
}

Why this stays readable Each optional helper disappears when it should. The caller does not need to build a string list, manage commas, or special-case blank values.

Count and list together

func SearchUsers(ctx context.Context, db scan.Queryer, qq *quarry.Quarry, req UserSearchRequest) ([]UserRow, int64, error) {
    base := qq.Select("id", "email", "display_name", "status", "created_at").
        From("users").
        Where(
            quarry.Eq("tenant_id", req.TenantID),
            quarry.Or(
                quarry.OptionalILike("email", req.Query),
                quarry.OptionalILike("display_name", req.Query),
            ),
            quarry.OptionalEq("status", req.Status),
            quarry.OptionalIn("role", anySlice(req.Roles)...),
        )

    total, err := scan.One[int64](ctx, db,
        qq.Select(quarry.Raw("COUNT(*)")).
            From("users").
            Where(
                quarry.Eq("tenant_id", req.TenantID),
                quarry.Or(
                    quarry.OptionalILike("email", req.Query),
                    quarry.OptionalILike("display_name", req.Query),
                ),
                quarry.OptionalEq("status", req.Status),
                quarry.OptionalIn("role", anySlice(req.Roles)...),
            ),
    )
    if err != nil {
        return nil, 0, err
    }

    rows, err := scan.All[UserRow](ctx, db,
        base.OrderBySafeDefault(req.Sort, quarry.SortMap{
            "newest": "created_at DESC",
            "alpha":  "display_name ASC",
            "email":  "email ASC",
        }, "newest").Page(req.Page, req.PerPage),
    )
    if err != nil {
        return nil, 0, err
    }

    return rows, total, nil
}

The count query is intentionally duplicated. This is one of the places where explicit SQL is easier to review than trying to factor everything into a clever abstraction.

What the SQL looks like

SELECT id, email, display_name, status, created_at
FROM users
WHERE tenant_id = $1
  AND (LOWER(email) LIKE LOWER($2) OR LOWER(display_name) LIKE LOWER($3))
  AND status = $4
  AND role IN ($5, $6)
ORDER BY created_at DESC
LIMIT 25 OFFSET 0

The values here are not special. The shape is. This is the pattern you want when the filter list changes with each request but the endpoint should still stay boring.