Pagination is essential for APIs returning large datasets. The wrong approach leads to slow queries and inconsistent results. Here's how to choose and implement the right pattern.
Pagination Approaches#
Offset Pagination:
- ?page=2&limit=20
- Simple to implement
- Can skip items on updates
- Slow for large offsets
Cursor Pagination:
- ?cursor=abc123&limit=20
- Stable during updates
- No page jumping
- Efficient for any position
Keyset Pagination:
- ?after_id=123&limit=20
- Uses indexed columns
- Highly efficient
- Limited sorting options
Offset Pagination#
1// Simple but problematic for large datasets
2app.get('/api/users', async (req, res) => {
3 const page = parseInt(req.query.page as string) || 1;
4 const limit = Math.min(parseInt(req.query.limit as string) || 20, 100);
5 const offset = (page - 1) * limit;
6
7 const [users, total] = await Promise.all([
8 prisma.user.findMany({
9 skip: offset,
10 take: limit,
11 orderBy: { createdAt: 'desc' },
12 }),
13 prisma.user.count(),
14 ]);
15
16 res.json({
17 data: users,
18 pagination: {
19 page,
20 limit,
21 total,
22 totalPages: Math.ceil(total / limit),
23 hasMore: page * limit < total,
24 },
25 });
26});
27
28// Problem: OFFSET scans and discards rows
29// SELECT * FROM users ORDER BY created_at DESC OFFSET 10000 LIMIT 20
30// Database must read 10,020 rows to return 20Cursor Pagination#
1// Encode cursor
2function encodeCursor(data: { id: string; createdAt: Date }): string {
3 return Buffer.from(JSON.stringify(data)).toString('base64url');
4}
5
6// Decode cursor
7function decodeCursor(cursor: string): { id: string; createdAt: Date } | null {
8 try {
9 const data = JSON.parse(Buffer.from(cursor, 'base64url').toString());
10 return {
11 id: data.id,
12 createdAt: new Date(data.createdAt),
13 };
14 } catch {
15 return null;
16 }
17}
18
19app.get('/api/users', async (req, res) => {
20 const limit = Math.min(parseInt(req.query.limit as string) || 20, 100);
21 const cursor = req.query.cursor as string | undefined;
22
23 let where = {};
24
25 if (cursor) {
26 const decoded = decodeCursor(cursor);
27 if (decoded) {
28 where = {
29 OR: [
30 { createdAt: { lt: decoded.createdAt } },
31 {
32 createdAt: decoded.createdAt,
33 id: { lt: decoded.id },
34 },
35 ],
36 };
37 }
38 }
39
40 const users = await prisma.user.findMany({
41 where,
42 take: limit + 1, // Fetch one extra to check hasMore
43 orderBy: [{ createdAt: 'desc' }, { id: 'desc' }],
44 });
45
46 const hasMore = users.length > limit;
47 const data = hasMore ? users.slice(0, limit) : users;
48
49 const nextCursor = hasMore
50 ? encodeCursor({
51 id: data[data.length - 1].id,
52 createdAt: data[data.length - 1].createdAt,
53 })
54 : null;
55
56 res.json({
57 data,
58 pagination: {
59 limit,
60 hasMore,
61 nextCursor,
62 },
63 });
64});Keyset Pagination#
1// Simpler cursor using just the ID
2app.get('/api/posts', async (req, res) => {
3 const limit = Math.min(parseInt(req.query.limit as string) || 20, 100);
4 const afterId = req.query.after as string | undefined;
5
6 const where = afterId ? { id: { gt: afterId } } : {};
7
8 const posts = await prisma.post.findMany({
9 where,
10 take: limit + 1,
11 orderBy: { id: 'asc' },
12 });
13
14 const hasMore = posts.length > limit;
15 const data = hasMore ? posts.slice(0, limit) : posts;
16
17 res.json({
18 data,
19 pagination: {
20 limit,
21 hasMore,
22 endCursor: data.length > 0 ? data[data.length - 1].id : null,
23 },
24 });
25});
26
27// SQL equivalent:
28// SELECT * FROM posts WHERE id > 'last-id' ORDER BY id ASC LIMIT 21
29// Uses index efficiently regardless of positionBidirectional Cursor#
1interface PaginationParams {
2 first?: number;
3 after?: string;
4 last?: number;
5 before?: string;
6}
7
8app.get('/api/messages', async (req, res) => {
9 const { first, after, last, before } = req.query as unknown as PaginationParams;
10
11 const limit = first || last || 20;
12 const cursor = after || before;
13 const direction = last ? 'backward' : 'forward';
14
15 let where = {};
16 let orderBy: any = { createdAt: 'desc' };
17
18 if (cursor) {
19 const decoded = decodeCursor(cursor);
20 if (direction === 'forward') {
21 where = { createdAt: { lt: decoded.createdAt } };
22 } else {
23 where = { createdAt: { gt: decoded.createdAt } };
24 orderBy = { createdAt: 'asc' };
25 }
26 }
27
28 let messages = await prisma.message.findMany({
29 where,
30 take: limit + 1,
31 orderBy,
32 });
33
34 const hasMore = messages.length > limit;
35 messages = hasMore ? messages.slice(0, limit) : messages;
36
37 // Reverse if going backward
38 if (direction === 'backward') {
39 messages.reverse();
40 }
41
42 const edges = messages.map((msg) => ({
43 node: msg,
44 cursor: encodeCursor({ id: msg.id, createdAt: msg.createdAt }),
45 }));
46
47 res.json({
48 edges,
49 pageInfo: {
50 hasNextPage: direction === 'forward' ? hasMore : !!before,
51 hasPreviousPage: direction === 'backward' ? hasMore : !!after,
52 startCursor: edges[0]?.cursor,
53 endCursor: edges[edges.length - 1]?.cursor,
54 },
55 });
56});GraphQL Relay-Style#
1// GraphQL schema
2const typeDefs = gql`
3 type Query {
4 users(first: Int, after: String, last: Int, before: String): UserConnection!
5 }
6
7 type UserConnection {
8 edges: [UserEdge!]!
9 pageInfo: PageInfo!
10 totalCount: Int!
11 }
12
13 type UserEdge {
14 node: User!
15 cursor: String!
16 }
17
18 type PageInfo {
19 hasNextPage: Boolean!
20 hasPreviousPage: Boolean!
21 startCursor: String
22 endCursor: String
23 }
24`;
25
26// Resolver
27const resolvers = {
28 Query: {
29 users: async (_, args) => {
30 const { first, after, last, before } = args;
31 const limit = first || last || 20;
32
33 // Build query based on cursor direction
34 const result = await getUsersWithCursor({ first, after, last, before });
35
36 return {
37 edges: result.users.map((user) => ({
38 node: user,
39 cursor: encodeCursor(user),
40 })),
41 pageInfo: {
42 hasNextPage: result.hasNextPage,
43 hasPreviousPage: result.hasPreviousPage,
44 startCursor: result.startCursor,
45 endCursor: result.endCursor,
46 },
47 totalCount: result.totalCount,
48 };
49 },
50 },
51};Comparison#
| Feature | Offset | Cursor | Keyset |
|-------------------|-------------|-------------|-------------|
| Random access | Yes | No | No |
| Stable results | No | Yes | Yes |
| Large datasets | Slow | Fast | Fast |
| Implementation | Simple | Medium | Simple |
| Sorting | Flexible | Flexible | Limited |
| Client complexity | Low | Medium | Low |
Best Practices#
Use Offset when:
- Small datasets (< 10,000 items)
- Users need page numbers
- Random page access required
Use Cursor when:
- Large or growing datasets
- Real-time data (new items added)
- Infinite scroll UI
- Consistency matters
Always:
✓ Set maximum page size
✓ Include total count when practical
✓ Document your pagination format
✓ Index sort columns
✓ Handle invalid cursors gracefully
Conclusion#
Cursor pagination is the best default for APIs. It's efficient at any position, handles live data updates, and works well with infinite scroll. Use offset only when users truly need page numbers and datasets are small.
The key is matching your pagination approach to your use case and data size.