The Read Path: What the Client Can Ask For

One query parameter, parsed rather than assumed, an index that answers the sort, and what a list endpoint grows into: allowlists, paging that survives inserts, projections and validators.

- Nicola Asuni

Table of contents

The item list takes one query parameter. That is enough to show the whole mechanism, because the interesting part of a list endpoint is not how many parameters it has: it is that the query string is a public interface a client can put anything into, and every parameter is a promise about what the database will be asked to do.

Sequence diagram of the item list request, from parsing the limit through the service default and the indexed query to a 200 carrying a JSON array. Each step appears below.

Parsing, not assuming

// internal/httphandlerpub/item.go

// itemListParams reads the page selector from the query string.
//
// An absent limit selects the service default. A present one must be a
// non-negative integer: an unparsable value is rejected rather than silently
// replaced by the default, so the answer matches the documented contract.
func itemListParams(q url.Values) (item.ListParams, error) {
	if !q.Has("limit") {
		return item.ListParams{}, nil
	}

	v, err := strconv.ParseUint(q.Get("limit"), 10, 32)
	if err != nil {
		return item.ListParams{}, fmt.Errorf("%w: limit must be a non-negative integer", item.ErrValidation)
	}

	return item.ListParams{Limit: uint(v)}, nil
}

q.Has rather than a check for the empty string, because ?limit= and no limit at all are different requests. The first is a client that built a query string from an empty variable, and telling them so is more useful than guessing what they meant.

ParseUint(s, 10, 32) refuses a negative, a fraction, a word and anything above the width in one call, and it is the same narrowing page 15 applied to the quantity: a value that cannot reach the column does not get past the edge. Returning the error rather than the default is what keeps the answer truthful, since a client sending ?limit=twenty and receiving twenty items has been told nothing about the typo.

The service holds the policy:

// internal/item/service.go

if p.Limit > MaxPageSize {
	return nil, fmt.Errorf("%w: limit above %d", ErrValidation, MaxPageSize)
}

limit := p.Limit

if limit == 0 {
	limit = DefaultPageSize
}

Twenty by default, one hundred at most, and by the time the repository is called the limit is a number with no decisions left in it. Bounding the page size is the one control standing between a list endpoint and a client asking for every row in the table.

The query

// internal/item/repository.go

const sqlSelectPage = "SELECT id, name, quantity, created_at FROM item ORDER BY created_at DESC, id DESC LIMIT ?"

// List returns at most limit items, most recently created first.
func (r *Repository) List(ctx context.Context, limit uint) ([]Item, error) {
	rows, err := r.read.QueryContext(ctx, sqlSelectPage, limit)
	if err != nil {
		return nil, fmt.Errorf("failed querying items: %w", err)
	}

	defer func() { _ = rows.Close() }()

	items := []Item{}

	for rows.Next() {
		var it Item

		err = rows.Scan(&it.ID, &it.Name, &it.Quantity, &it.CreatedAt)
		if err != nil {
			return nil, fmt.Errorf("failed scanning item row: %w", err)
		}

		items = append(items, it)
	}

	err = rows.Err()
	if err != nil {
		return nil, fmt.Errorf("failed reading item rows: %w", err)
	}

	return items, nil
}

A constant rather than a string built at the call site, so the statement is one thing the reader can see whole and the SQL contains nothing assembled from input. Every value is a bind parameter. Nothing in this feature concatenates a query. No part of it can be injected into.

r.read sends this to the replica connection, and the write path sends its insert to the other one. Two named connections (page 12) make that a decision at each call site rather than a property of whichever handle was in scope.

Three details in that loop are the ones people leave out.

rows.Err() is checked after the loop, because rows.Next returns false both at the end of the result set and on a read error, and only rows.Err tells them apart. Without it a connection that dropped halfway through a page is a short page and a 200.

defer func() { _ = rows.Close() }() discards the error deliberately, in a closure, rather than defer rows.Close(), which the linter rejects for discarding it silently. Closing is what returns the connection to the pool, so skipping it on an early return leaks one per request until the pool is empty.

items := []Item{} and not var items []Item. A nil slice marshals to null and an empty one to [], so the empty result is [] on the wire. A client that iterates the response gets zero iterations instead of a null-pointer failure, and the specification’s type: array stays true for every response.

The ordering is created_at DESC, id DESC, and the identifier is there as a tie-break. Two items recorded in the same microsecond would otherwise come back in whatever order the storage engine chose, which can differ between two runs of the same query. idx_item_created_at (created_at, id) from page 14 covers both columns, so the sort is read off the index rather than performed.

The handler writes the slice as it is:

h.httpres.SendJSON(ctx, w, http.StatusOK, items)

A bare JSON array, no envelope, no page metadata. That is a decision with a short life, and the rest of this page is what replaces it.

What the example leaves out

Everything below is beyond the four endpoints. It is the order in which a list endpoint usually grows, and each step has a failure that motivates it.

The query string is a public interface

Adding a second parameter is where the threat model starts. Four things a query string does to a service that accepts it as given:

Injection. A sort parameter concatenated into an ORDER BY, or a filter into a WHERE, and the client controls SQL. Bind parameters cover values and not identifiers, so a sort parameter is the one a placeholder cannot fix.

Unbounded work. limit=100000000 asks the database to materialise every row, and OFFSET 5000000 to read five million and discard them. Both are one request and neither looks like an attack in a log.

An unindexed sort. Sorting on a column with no index turns a query that returns in milliseconds into a table scan and a filesort.

Enumeration. A filter on a field a caller should not be able to search by lets them find records by guessing.

One mechanism closes all four:

// filterable maps every query parameter a client may filter on to its column.
// A parameter absent from this map is refused by name: the map is the whole
// public surface of the query string, and nothing outside it reaches SQL.
var filterable = map[string]string{
	"name": "name",
}

// sortable maps every accepted sort key to its column. Sorting on a column with
// no index turns a cheap query into a table scan, so this list is deliberately
// shorter than the set of columns.
var sortable = map[string]string{
	"created_at": "created_at",
	"name":       "name",
}

The maps are the API. A parameter name is a key to look up, the value is a column name from this file, and no client text reaches a SQL identifier. The indirection also decouples the public name from the storage name, so renaming a column is not a breaking API change. sortable stays shorter than the column list because each entry is a commitment to keep an index on that column.

The parsed result is a struct whose fields are already bounded, so the repository builds its statement without re-deciding anything: column names come from the maps, every client value goes into the argument list, and 1 = 1 seeds the WHERE so there is no branch on whether to write the keyword at all.

sqlutil has QuoteID and BuildInClauseString for a fragment that genuinely has to be composed. They are the fallback: quoting has to be applied correctly every time, where a map lookup either finds a key or does not.

Refusing unknown parameters is the other half, and the item endpoint does not do it. Unrecognised names are ignored, which is the common choice and has a specific failure: a client sends ?nmae=bolt, gets a 200, and receives every item in the table believing they filtered. Page 15’s DisallowUnknownFields argument, on the other half of the request.

Paging that survives an insert

The item list has a limit and no offset, so it can return the newest twenty and nothing else. The first thing a client asks for is the next twenty.

paging computes the standard metadata:

// All three arguments are uint, so a negative page cannot be expressed.
p := paging.New(currentPage, pageSize, totalItems)
// p.Offset and p.PageSize feed the SQL LIMIT/OFFSET clause.
// p.PreviousPage and p.NextPage are safe to put in the response.

It clamps out-of-range inputs, returns TotalPages == 1 for an empty set, and saturates the offset rather than wrapping. For a bounded set with a page-number interface that is the right answer. Two problems appear as the table grows.

Deep offsets are linear. LIMIT 20 OFFSET 100000 does not skip to row 100,000: the database reads 100,020 rows in sort order and discards 100,000. Page one is fast, page five thousand is a table scan, and nobody meets the cliff until somebody writes a script that walks every page.

Concurrent inserts shift the window. A client reads page one, ten items are recorded that sort before it, and page two now starts ten rows earlier than page one ended, so ten items arrive twice. A delete does the reverse, and neither is detectable from the client’s side.

Keyset paging says “start after this one” instead of “skip 100,000 rows”:

SELECT id, name, quantity, created_at FROM item
 WHERE (created_at, id) < (?, ?)
 ORDER BY created_at DESC, id DESC
 LIMIT ?

That is a row comparison, true when created_at is smaller or equal with a smaller id, and it is why the ordering already carries the identifier. The pair is exactly the index the migration created, so the predicate and the sort are answered by one index read from the position the last page ended at.

The client gets the pair back as an opaque token, base64 of the two values, so its format can change without breaking a client tempted to construct one. Ask for one row more than the page holds to learn whether another page exists, which is cheaper than SELECT COUNT(*) over the same WHERE and gives up the total. What cursors cost is page numbers: a client walks forward, and jumping to page 47 is not available. Offsets for a bounded set with a page-number interface, cursors for anything a script walks end to end.

A wire type that is not the row type

Item is one struct for the row and the response, which page 14 called a shortcut. A list endpoint is where it stops being one, once the row grows a column a list reader does not want: a description that runs to a kilobyte, a blob, an internal note.

A projection is a second type naming fewer columns, and the SELECT names the same ones. It buys bytes off the wire, and it closes the leak where a column added for internal use appears in the API the moment somebody adds it to a shared struct, because nobody decided to publish it. Two types for a resource where they are identical is ceremony; the line worth drawing is that the single-resource GET returns the full type and any collection endpoint gets a projection.

Conditional requests and caching

A client polling GET /items/:id receives the same bytes every time. An ETag turns that into a header exchange: send a validator with the response, accept it back in If-None-Match, and answer 304 with no body when it still matches.

The tag has to be correct, and getting it wrong produces stale data that refreshing does not fix. Everything in the response has to be covered by it, anything that varies the response has to be in the tag or in Vary, and two clients receiving the same tag have to receive the same bytes. Hashing the serialised body is the safe version and throws away most of the saving, since you build the response before deciding not to send it. Deriving the tag from a version column is cheap and depends on that column being right, which is the optimistic concurrency material from page 15 paying for itself twice.

A 304 also needs a Cache-Control that overrides page 09’s no-store default: private, max-age=0, must-revalidate keeps the body out of shared caches and produces a revalidation rather than a blind cache hit.

For a read that is expensive and tolerates being slightly stale, sfcache collapses concurrent lookups and holds the result. Single-flight is the property that matters: a hundred concurrent requests for the same cold key produce one query and ninety-nine waiters, which is the difference between a cache miss and a stampede. Three details from its documentation change how it is used. Only successful lookups are cached, and an error is shared with the coalesced callers but never stored. Cached values are shared by reference, so a handler that mutates one has mutated it for everybody. The stale-if-error window serves the last good value while the upstream is down, which means deliberately serving data known to be old.

Telling the client where the next page is

w.Header().Add("Link", `<`+next.RequestURI()+`>; rel="next"`)

RFC 8288 link relations let a generic client walk pages without knowing this API’s parameter names, and the token belongs in the body as well for clients that do not read headers.

httputil.Link composes absolute links from a template, with one caveat its documentation is blunt about: with segments, the template is a fmt.Sprintf format string, so it has to be a compile-time constant. A client-supplied value there is a format-string injection, and segments are not URL-escaped either.

Deliberately not used

nurago has a filter package with a rule grammar for client-supplied queries, and none of the above reaches for it. Its grammar solves a harder problem: arbitrary boolean expressions over fields, from a client you do not trust. A limit, two filters and a sort do not need a query language, and adopting one turns the read path into a page about parsing.

Next: What Is Reachable From Where.