apps/docs/content/docs.v6/orm/prisma-client/queries/pagination.mdx
Prisma Client supports both offset pagination and cursor-based pagination.
Offset pagination uses skip and take to skip a certain number of results and select a limited range. The following query skips the first 3 Post records and returns records 4 - 7:
const results = await prisma.post.findMany({
skip: 3,
take: 4,
});
To implement pages of results, you would just skip the number of pages multiplied by the number of results you show per page.
skip 200 records and take 10, which simulates jumping straight to page 21 of the result set (the underlying SQL uses OFFSET). This is not possible with cursor-based pagination.User records sorted by first name. This is not possible with cursor-based pagination, which requires sorting by a unique, sequential column.Post records by author and paginate the results.The following query returns all records where the email field contains prisma.io. The query skips the first 40 records and returns records 41 - 50.
const results = await prisma.post.findMany({
skip: 40,
take: 10,
where: {
email: {
contains: "prisma.io",
},
},
});
The following query returns all records where the email field contains Prisma, and sorts the result by the title field. The query skips the first 200 records and returns records 201 - 220.
const results = await prisma.post.findMany({
skip: 200,
take: 20,
where: {
email: {
contains: "Prisma",
},
},
orderBy: {
title: "desc",
},
});
Cursor-based pagination uses cursor and take to return a limited set of results before or after a given cursor. A cursor bookmarks your location in a result set and must be a unique, sequential column - such as an ID or a timestamp.
The following example returns the first 4 Post records that contain the word "Prisma" and saves the ID of the last record as myCursor:
Note: Since this is the first query, there is no cursor to pass in.
const firstQueryResults = await prisma.post.findMany({
take: 4,
where: {
title: {
contains: "Prisma" /* Optional filter */,
},
},
orderBy: {
id: "asc",
},
});
// Bookmark your location in the result set - in this
// case, the ID of the last post in the list of 4.
const lastPostInResults = firstQueryResults[3]; // Remember: zero-based index! :) // [!code highlight]
const myCursor = lastPostInResults.id; // Example: 29 // [!code highlight]
The following diagram shows the IDs of the first 4 results - or page 1. The cursor for the next query is 29:
The second query returns the first 4 Post records that contain the word "Prisma" after the supplied cursor (in other words - IDs that are larger than 29):
const secondQueryResults = await prisma.post.findMany({
take: 4,
skip: 1, // Skip the cursor
cursor: {
// [!code highlight]
id: myCursor, // [!code highlight]
}, // [!code highlight]
where: {
title: {
contains: "Prisma" /* Optional filter */,
},
},
orderBy: {
id: "asc",
},
});
const lastPostInResults = secondQueryResults[3]; // Remember: zero-based index! :)
const myCursor = lastPostInResults.id; // Example: 52
The following diagram shows the first 4 Post records after the record with ID 29. In this example, the new cursor is 52:
If you do not skip: 1, your result set will include your previous cursor. The first query returns four results and the cursor is 29:
Without skip: 1, the second query returns 4 results after (and including) the cursor:
If you skip: 1, the cursor is not included:
You can choose to skip: 1 or not depending on the pagination behavior that you want.
If you guess the value of the next cursor, you will page to an unknown location in your result set. Although IDs are sequential, you cannot predict the rate of increment (2, 20, 32 is more likely than 1, 2, 3, particularly in a filtered result set).
No, cursor pagination does not use cursors in the underlying database (e.g. PostgreSQL).
Using a nonexistent cursor returns null. Prisma Client does not try to locate adjacent values.
OFFSET, but instead queries all Post records with an ID greater than the value of cursor.const secondQuery = await prisma.post.findMany({
take: 4,
skip: 1,
cursor: {
id: myCursor,
},
where: {
// [!code highlight]
title: {
// [!code highlight]
contains: "Prisma" /* Optional filter */, // [!code highlight]
}, // [!code highlight]
}, // [!code highlight]
orderBy: {
id: "asc",
},
});
Cursor-based pagination requires you to sort by a sequential, unique column such as an ID or a timestamp. This value - known as a cursor - bookmarks your place in the result set and allows you to request the next set.
To page backwards, set take to a negative value. The following query returns 4 Post records with an id of less than 200, excluding the cursor:
const myOldCursor = 200;
const firstQueryResults = await prisma.post.findMany({
take: -4,
skip: 1,
cursor: {
id: myOldCursor,
},
where: {
title: {
contains: "Prisma" /* Optional filter */,
},
},
orderBy: {
id: "asc",
},
});