Implementing Pagination
1. Using Skip Offset
const page = 3, size = 20;
await prisma.post.findMany({ skip: (page - 1) * size, take: size });| Aspect | Detail |
|---|---|
| Type | Offset-based |
| Cost | Slow for large skips |
2. Limiting Results
| Option | Use |
|---|---|
take: N | Limit to N rows |
take: -N | Last N rows (must combine with cursor) |
3. Implementing Cursor Pagination
await prisma.post.findMany({
take: 20,
skip: 1, // skip the cursor row itself
cursor: { id: lastSeenId },
orderBy: { id: "asc" }
});| Pro | Detail |
|---|---|
| Scales | O(log n) via index seek |
| Stable | Resilient to inserts/deletes |
| Constraint | orderBy must be unique/stable |
4. Combining Skip and Take
| Use | Detail |
|---|---|
| UI page numbers | Skip = (page-1)*size |
| Caveat | Becomes slow past tens of thousands |
5. Using Distinct Pagination
| Pattern | Detail |
|---|---|
distinct + orderBy | Stable distinct rows |
| Combine with cursor | Use first-of-group via window functions in raw SQL for complex cases |
6. Creating Infinite Scroll Pattern
Example: Infinite scroll endpoint
async function feed(cursor?: number) {
const items = await prisma.post.findMany({
take: 21,
cursor: cursor ? { id: cursor } : undefined,
skip: cursor ? 1 : 0,
orderBy: { id: "desc" }
});
const nextCursor = items.length === 21 ? items.pop()!.id : null;
return { items, nextCursor };
}| Detail | Why |
|---|---|
| Take N+1 | Determine if more pages exist |
7. Calculating Total Pages
const total = await prisma.post.count({ where });
const pages = Math.ceil(total / size);| Tip | Detail |
|---|---|
| Approximate | Use approx counts on huge tables (PG pg_class.reltuples) |
8. Handling Pagination Edge Cases
| Case | Handle |
|---|---|
| Empty page | Return [] + nextCursor=null |
| Cursor deleted | Fallback to next ID gracefully |
| Backward nav | Use reversed orderBy + cursor |
9. Optimizing Large Dataset Pagination
| Strategy | Detail |
|---|---|
| Cursor pagination | Avoid OFFSET |
| Indexed orderBy | Composite index per sort key |
| Cache totals | Avoid expensive COUNT(*) per request |
10. Using Relay-Style Pagination
| Element | Detail |
|---|---|
| Edges | { node, cursor } |
| PageInfo | { hasNextPage, endCursor } |
| Cursor | Base64-encoded opaque ID |