Dev Skills
Go·Project 2 — Expense tracker

Step 6 — Filtering, paging, and a monthly report

Let the database do the counting, and never return an unbounded list.

Topic 71 of 98 · 5 min to read · 50 min to build
After this topic you can
  • Build paginated, filtered list queries with a capped page size.
  • Add filters with placeholders instead of string concatenation.
  • Write a monthly report that groups and sums in SQL, avoiding N+1.

Every command for this step

terminal — run these in order
touch internal/httpx/params.go
go build ./... && go run ./cmd/api
# filtering and paging
curl -s -H "Authorization: Bearer $TOKEN" 'localhost:8080/v1/expenses?limit=5&offset=0' | jq
curl -s -H "Authorization: Bearer $TOKEN" 'localhost:8080/v1/expenses?category=food' | jq
curl -s -H "Authorization: Bearer $TOKEN" 'localhost:8080/v1/expenses?limit=100000' | jq 'length'
# the report
curl -s -H "Authorization: Bearer $TOKEN" 'localhost:8080/v1/reports/monthly?month=2026-09-01' | jq
# check the index is actually used
docker exec -it exp-pg psql -U postgres -d expenses -c \
  'EXPLAIN ANALYZE SELECT * FROM expenses WHERE user_id=1 ORDER BY spent_on DESC LIMIT 20;'

What pagination is and why it is not optional

Pagination means returning one page of results instead of everything. limit says how many, offset says how many to skip.

It is not a feature you add when the data grows. By then the endpoint is in production, clients expect the full list, and adding a limit changes what they get back. Until then, every call builds one response holding every row the user has.

internal/httpx/params.go — create this file
// IntParam reads a query parameter, falls back to def, and clamps the range.
func IntParam(r *http.Request, key string, def, min, max int) int {
	v, err := strconv.Atoi(r.URL.Query().Get(key))
	if err != nil {
		return def
	}
	return Clamp(v, min, max)
}
// in the handler:
limit := httpx.IntParam(r, "limit", 20, 1, 100)      // hard ceiling of 100
offset := httpx.IntParam(r, "offset", 0, 0, 1_000_000)
Clamping means a client asking for 5000 gets 100 rows, not a 400. The cost: it may not notice it got fewer than it asked for, so document the ceiling.

Filtering without opening a hole

Users will want expenses by category and by date range. The tempting way to build a query with optional filters is to paste the values into the SQL string, and that is how SQL injection happens.

Build the conditions as fixed strings and collect the values separately, so every value still travels as a placeholder.

internal/expense/repository.go
func (r *Repository) List(ctx context.Context, userID int64, f Filter) ([]Expense, error) {
	// $1 is always the user id. It is never optional.
	where := []string{"user_id = $1"}
	args := []any{userID}
	if f.Category != "" {
		args = append(args, f.Category)
		where = append(where, fmt.Sprintf("category = $%d", len(args)))
	}
	if !f.From.IsZero() {
		args = append(args, f.From)
		where = append(where, fmt.Sprintf("spent_on >= $%d", len(args)))
	}
	args = append(args, f.Limit, f.Offset)
	q := fmt.Sprintf(`
		SELECT id, amount_cents, category, note, spent_on, created_at
		  FROM expenses WHERE %s
		 ORDER BY spent_on DESC, id DESC
		 LIMIT $%d OFFSET $%d`,
		strings.Join(where, " AND "), len(args)-1, len(args))
	rows, err := r.db.QueryContext(ctx, q, args...)
	...
}
The Sprintf here only ever inserts placeholder numbers like $2 and $3, never a user value. Every actual value still goes through args.
Watch out: Read that code twice. fmt.Sprintf near SQL is normally the injection warning sign. It is safe here only because the pieces being formatted in are $2, $3 and fixed column names that you wrote. The moment a value from the request goes into that Sprintf, you have a vulnerability. If in doubt, do not do it.

The monthly report

A report is a question: how much did I spend per category last month? You could load every row and add them up in Go. Do not. The database can do it in one query, faster than it could send you the rows.

internal/expense/repository.go — add this
type CategoryTotal struct {
	Category string `json:"category"`
	Total    int64  `json:"total_cents"`
	Count    int64  `json:"count"`
}
func (r *Repository) MonthlyReport(ctx context.Context, userID int64, month time.Time) ([]CategoryTotal, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT category, SUM(amount_cents) AS total, COUNT(*) AS n
		  FROM expenses
		 WHERE user_id = $1
		   AND spent_on >= $2
		   AND spent_on <  ($2::date + INTERVAL '1 month')
		 GROUP BY category
		 ORDER BY total DESC`,
		userID, month)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []CategoryTotal
	for rows.Next() {
		var c CategoryTotal
		if err := rows.Scan(&c.Category, &c.Total, &c.Count); err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}
GROUP BY plus SUM is the database doing in one round trip what would otherwise be thousands of rows over the network and a loop in Go.

The mistake to avoid: N+1

Suppose you list twenty expenses and then, for each one, run a query to fetch something extra. That is 1 + 20 = 21 round trips. At two hundred expenses it is 201, and at a millisecond or two per round trip the endpoint gets slow for reasons you cannot see in the Go code. The loop looks innocent.

This is called the N+1 problem, and it is one of the most common performance bugs in database-backed applications. The fix is a JOIN, or one extra query that fetches everything at once with WHERE id = ANY($1).

The short version
  • Paginate from the first version, with a hard maximum, and clamp instead of rejecting.
  • Build optional filters as fixed condition strings with the values in a separate args slice.
  • Let the database aggregate. GROUP BY and SUM beat loading rows and looping.
  • A query inside a loop is N+1, one of the most common performance bugs there is.
Remember: Every list endpoint needs a ceiling, and every total belongs in the database. Both are much harder to add once people depend on your API.
Build this before moving on
Add filtering by category and date range, pagination on the list, and the monthly report. Check EXPLAIN ANALYZE on the list query and confirm it uses your (user_id, spent_on DESC) index.
Then try these in your own editor
  1. 1Insert 5,000 expenses and compare the list endpoint with and without the index.
  2. 2Write the report the wrong way (load all rows, sum in Go) and time both.
  3. 3Add a ?category= filter and check that a category containing a quote cannot break the query.
  4. 4Try ?limit=100000 and confirm your clamp caps it.
Official documentation
Check yourselfTake the Project 2 — Expense tracker test