Pagination
Cursor-Based (recommended for large datasets)
app.get('/api/products', async (req, res) => {
const limit = Math.min(parseInt(req.query.limit as string) || 20, 100);
const cursor = req.query.cursor as string | undefined;
const where: any = {};
if (cursor) {
where.id = { gt: cursor };
}
const items = await db.product.findMany({
where,
take: limit + 1, // Fetch one extra to check hasMore
orderBy: { id: 'asc' },
});
const hasMore = items.length > limit;
if (hasMore) items.pop();
res.json({
data: items,
pagination: {
hasMore,
nextCursor: hasMore ? items[items.length - 1].id : null,
},
});
});
Offset-Based (simple, good for small datasets)
app.get('/api/products', async (req, res) => {
const page = Math.max(parseInt(req.query.page as string) || 1, 1);
const limit = Math.min(parseInt(req.query.limit as string) || 20, 100);
const offset = (page - 1) * limit;
const [items, total] = await Promise.all([
db.product.findMany({ skip: offset, take: limit, orderBy: { createdAt: 'desc' } }),
db.product.count(),
]);
res.json({
data: items,
pagination: {
page, limit, total,
totalPages: Math.ceil(total / limit),
hasMore: offset + items.length < total,
},
});
});
Filtering and Sorting
app.get('/api/products', async (req, res) => {
const { sort = 'createdAt', order = 'desc', category, minPrice, maxPrice, search } = req.query;
const where: any = {};
if (category) where.category = category;
if (minPrice || maxPrice) {
where.price = {};
if (minPrice) where.price.gte = parseFloat(minPrice as string);
if (maxPrice) where.price.lte = parseFloat(maxPrice as string);
}
if (search) where.name = { contains: search, mode: 'insensitive' };
const items = await db.product.findMany({
where,
orderBy: { [sort as string]: order },
take: limit,
skip: offset,
});
res.json({ data: items, pagination: { /* ... */ } });
});
Spring Boot (Pageable)
@GetMapping("/products")
public Page<ProductDto> list(
@RequestParam(defaultValue = "0") int page,
@RequestParam(defaultValue = "20") int size,
@RequestParam(defaultValue = "createdAt,desc") String[] sort) {
Pageable pageable = PageRequest.of(page, Math.min(size, 100),
Sort.by(Sort.Direction.fromString(sort[1]), sort[0]));
return productRepo.findAll(pageable).map(mapper::toDto);
}
Comparison
| Strategy | Pros | Cons | Best For |
|---|
| Offset | Simple, jump to page | Slow on large tables, skip drift | Admin panels, small datasets |
| Cursor | Fast, stable with inserts | Can't jump to page N | Feeds, infinite scroll, large datasets |
| Keyset | Fast, no skip drift | Complex multi-column sort | Time-series, ordered data |
Anti-Patterns
| Anti-Pattern | Fix |
|---|
| No max page size | Cap limit (e.g., max 100) |
| COUNT(*) on huge tables | Use cursor pagination, skip total count |
| Offset on millions of rows | Use cursor or keyset pagination |
| Returning all fields | Select only needed fields, support fields param |
| No default sorting | Always define default sort for stable results |
Production Checklist
- Maximum page size enforced (e.g., 100)
- Default sort order defined
- Cursor pagination for large/growing datasets
- Input validation on page/limit/sort params
- Consistent response envelope (
data, pagination)
