How do you implement pagination, filtering, and sorting in a REST API?
Learn how to implement pagination, filtering, and sorting in a REST API with query parameters, cursors, whitelisting, and fast, scalable queries.
Expected Interview Answer
You implement pagination, filtering, and sorting through query parameters on a collection endpoint, letting clients request a slice of results, narrow them by field values, and order them without changing the URL path.
Pagination is done with either offset/limit (page & size) or cursor-based tokens for large, changing datasets. Filtering maps query params like status=active or price[gte]=100 onto WHERE clauses, and sorting uses a sort=field,-otherField convention translated into ORDER BY. The server validates and whitelists allowed fields, applies sane defaults and caps, and returns metadata (total count, next/prev links) so clients can navigate.
- Keeps responses small and fast
- Reduces database and network load
- Gives clients flexible querying without new endpoints
- Supports predictable, cacheable requests
- Scales to very large collections with cursors
AI Mentor Explanation
Think of a full match scorecard with thousands of ball-by-ball entries. Pagination is turning to over 15 instead of reading every over at once; filtering is asking only for the boundaries a single batter hit; sorting is arranging deliveries by runs conceded so the most expensive overs surface first. The scorer never rewrites the book — you just tell them which slice, which condition, and which order you want to read.
Step-by-Step Explanation
Step 1
Design query parameters
Decide conventions: page/size or cursor for pagination, filter fields, and sort=field,-field syntax.
Step 2
Validate and whitelist
Only allow filtering and sorting on approved, indexed columns; reject or ignore unknown fields.
Step 3
Apply pagination
Translate offset/limit or cursor into SQL LIMIT/OFFSET or a keyset WHERE clause with a stable sort.
Step 4
Build filters and ordering
Map operators (gte, lte, in, like) onto WHERE conditions and sort tokens onto ORDER BY.
Step 5
Return metadata
Include total count (when cheap), next/prev cursors or page links, and applied filters in the response.
What Interviewer Expects
- Difference between offset and cursor-based pagination
- Whitelisting sortable and filterable fields to prevent abuse
- Consistent, documented query-parameter conventions
- Awareness of indexing for performance
- Returning pagination metadata and links
Common Mistakes
- Using large OFFSET values that scan and skip huge row counts
- Allowing sorting or filtering on arbitrary, unindexed columns
- Forgetting a stable tiebreaker sort, causing rows to repeat or skip across pages
- Not capping page size, letting clients request everything
- Building filters by string-concatenating input, risking SQL injection
Best Answer (HR Friendly)
“Instead of sending an entire huge list at once, the API sends it in small pages and lets the app ask for only what it needs. Users can also narrow results to what matters and put them in the order they want, which keeps the app fast and easy to use.”
Code Example
app.get('/api/products', async (req, res) => {
const page = Math.max(1, parseInt(req.query.page) || 1)
const size = Math.min(100, parseInt(req.query.size) || 20)
const offset = (page - 1) * size
const SORTABLE = { price: 'price', name: 'name', created: 'created_at' }
const sort = (req.query.sort || 'created')
.split(',')
.map(t => {
const desc = t.startsWith('-')
const key = desc ? t.slice(1) : t
return SORTABLE[key] ? `${SORTABLE[key]} ${desc ? 'DESC' : 'ASC'}` : null
})
.filter(Boolean)
sort.push('id ASC') // stable tiebreaker
const where = []
const params = []
if (req.query.status) { params.push(req.query.status); where.push(`status = $${params.length}`) }
if (req.query['price_gte']) { params.push(+req.query['price_gte']); where.push(`price >= $${params.length}`) }
const clause = where.length ? `WHERE ${where.join(' AND ')}` : ''
params.push(size, offset)
const rows = await db.query(
`SELECT * FROM products ${clause} ORDER BY ${sort.join(', ')} LIMIT $${params.length - 1} OFFSET $${params.length}`,
params,
)
res.json({ data: rows, page, size })
})Follow-up Questions
- When would you choose cursor-based pagination over offset-based?
- How do you keep pagination stable when new rows are inserted?
- How do you prevent expensive total-count queries on huge tables?
- How would you secure filtering against SQL injection?
- How do you document these query parameters for API consumers?
MCQ Practice
1. Why is large OFFSET pagination inefficient on big tables?
OFFSET N must read past N rows before returning results, so deep pages get progressively slower; keyset/cursor pagination avoids this.
2. What is essential for stable pagination across pages?
Without a deterministic ordering (including a unique tiebreaker like id), rows can repeat or be skipped between pages.
3. Why whitelist sortable and filterable fields?
Whitelisting restricts operations to safe, indexed columns, protecting performance and preventing injection through field names.
Flash Cards
Offset vs cursor pagination? — Offset uses page/limit but slows on deep pages; cursor (keyset) uses a token from the last row for stable, fast paging on large datasets.
Common sort syntax? — sort=field,-otherField where a leading '-' means descending, mapped to ORDER BY on whitelisted columns.
Why cap page size? — To stop clients requesting the entire table in one call, protecting memory and response time.
Why a tiebreaker sort? — A unique secondary sort key (e.g. id) guarantees deterministic ordering so pages don't repeat or skip rows.
Continue Learning
Related Interview Questions
How do you handle errors and return meaningful error responses in a REST API?
medium
How do you choose between offset and cursor pagination in a GraphQL schema?
medium
What do the major HTTP status code categories (2xx, 3xx, 4xx, 5xx) mean?
easy
What is the difference between 401 Unauthorized and 403 Forbidden?
medium