Skip to main content

Pagination

Prisma Client supports both offset pagination and cursor-based pagination.

Offset 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.

✔ Pros of offset pagination​

  • You can jump to any page immediately. For example, you can 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.
  • You can paginate the same result set in any sort order. For example, you can jump to page 21 of a list of User records sorted by first name. This is not possible with cursor-based pagination, which requires sorting by a unique, sequential column.

✘ Cons of offset pagination​

  • Offset pagination does not scale at a database level. For example, if you skip 200,000 records and take the first 10, the database still has to traverse the first 200,000 records before returning the 10 that you asked for - this negatively affects performance.

Use cases for offset pagination​

  • Shallow pagination of a small result set. For example, a blog interface that allows you to filter Post records by author and paginate the results.

Example: Filtering and offset pagination​

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',
    },
  },
})

Example: Sorting and offset pagination​

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​

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! :)
const myCursor = lastPostInResults.id // Example: 29

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: {
    id: myCursor,
  },
  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:

FAQ​

Do I always have to skip: 1?​

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.

Can I guess the value of the cursor?​

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).

Does cursor-based pagination use the concept of a cursor in the underlying database?​

No, cursor pagination does not use cursors in the underlying database (e.g. PostgreSQL).

What happens if the cursor value does not exist?​

Using a nonexistent cursor returns null. Prisma Client does not try to locate adjacent values.

✔ Pros of cursor-based pagination​

  • Cursor-based pagination scales. The underlying SQL does not use OFFSET, but instead queries all Post records with an ID greater than the value of cursor.

✘ Cons of cursor-based pagination​

  • You must sort by your cursor, which has to be a unique, sequential column.
  • You cannot jump to a specific page using only a cursor. For example, you cannot accurately predict which cursor represents the start of page 400 (page size 20) without first requesting pages 1 - 399.

Use cases for cursor-based pagination​

  • Infinite scroll - for example, sort blog posts by date/time descending and request 10 blog posts at a time.
  • Paging through an entire result set in batches - for example, as part of a long-running data export.

Example: Filtering and cursor-based pagination​

const secondQuery = await prisma.post.findMany({
  take: 4,
  cursor: {
    id: myCursor,
  },
  where: {
    title: {
      contains: 'Prisma' /* Optional filter */,
    },
  },
  orderBy: {
    id: 'asc',
  },
})

Sorting and cursor-based pagination​

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.

Example: Paging backwards with cursor-based pagination​

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',
  },
})