Implementing Cursor-Based Pagination in Prisma Client

Desired outcome

Retrieve a page of records from a Prisma model using cursor‑based pagination so that query cost stays stable as the dataset grows and users can page forward and backward reliably.

Prerequisites

• Node.js >=14 and a project with Prisma Client installed (e.g., npm i @prisma/client).
• A Prisma schema that defines a model with a unique, sequential field (commonly an auto‑increment id or cuid) that is indexed in the underlying database.
• Basic familiarity with TypeScript/JavaScript async/await.

Procedure

1. Define the page size. Choose a constant PAGE_SIZE (e.g., 10).
2. Fetch a page forward. Use take: PAGE_SIZE + 1 together with a cursor that points to the last record of the previous page. The cursor is omitted for the first page.
3. Exclude the cursor record. Add skip: 1 when a cursor is present so the record identified by the cursor is not returned.
4. Order by the same unique field. Apply orderBy: { id: 'asc' } (or desc for reverse pagination).
5. Detect a next page. If the query returns PAGE_SIZE + 1 rows, drop the extra row and use its id as the nextCursor. If fewer rows are returned, there is no next page.
6. Fetch a previous page (bidirectional). To go backward, use a negative take (e.g., take: -PAGE_SIZE) with the cursor set to the first record of the current page and orderBy: { id: 'asc' }. Again add skip: 1 to exclude the cursor record.
7. Return the page data and cursors. Provide the trimmed array of records, nextCursor (or null), and prevCursor (or null) to the caller.

Expected checks

• Verify that the generated SQL contains a WHERE clause on the cursor column (e.g., WHERE "id" > $1) and no large OFFSET. Enable query logging in Prisma Client (log: ['query']) and inspect the output.
• Confirm that requesting take: PAGE_SIZE + 1 on a non‑final page yields exactly PAGE_SIZE + 1 rows, and that the final page yields ≤ PAGE_SIZE rows.
• Check that no duplicate IDs appear across consecutive pages and that no IDs are missing when iterating through the full dataset.
• For backward paging, ensure that using a negative take with the first ID of the current page returns the previous page in the correct order.

Recovery options when pagination misbehaves

• If duplicate or missing rows appear, double‑check that the cursor field is unique and indexed. A non‑unique ordering field (e.g., createdAt) can cause ties; add a tiebreaker such as orderBy: [{ createdAt: 'asc' }, { id: 'asc' }] or paginate directly on the unique field.
• If query performance degrades, verify that you are not using skip values larger than 1. Remove any extra skip beyond the cursor‑exclusion step.
• If the generated SQL still shows an OFFSET, ensure you are combining cursor with take (and optional skip: 1) and not passing a standalone skip for large offsets.
• Confirm you are using a Prisma version where the cursor semantics are stable (e.g., >=2.0.0). Consult the release notes if you observe version‑specific behavior.

async function getPage({ afterId, beforeId, pageSize = 10 }) {
  let where = {};
  let orderBy = { id: 'asc' };
  let take = pageSize;
  let skip = undefined;

  // forward page
  if (afterId !== undefined) {
    where = { id: { gt: afterId } };
    skip = 1; // skip the cursor record itself
    take = pageSize + 1; // fetch extra to detect next page
  }
  // backward page
  if (beforeId !== undefined) {
    where = { id: { lt: beforeId } };
    orderBy = { id: 'desc' }; // we will reverse later
    take = - (pageSize + 1);
    skip = 1;
  }

  const rows = await prisma.user.findMany({
    where,
    orderBy,
    take,
    skip,
  });

  // normalize result
  let data = rows;
  let nextCursor = null;
  let prevCursor = null;

  if (afterId !== undefined) {
    if (rows.length > pageSize) {
      data = rows.slice(0, -1);
      nextCursor = rows[rows.length - 1].id;
    } else {
      data = rows;
    }
  }
  if (beforeId !== undefined) {
    // rows are in descending order because of negative take
    if (rows.length > pageSize) {
      data = rows.slice(0, -1).reverse();
      prevCursor = rows[rows.length - 1].id;
    } else {
      data = rows.reverse();
    }
  }

  return { data, nextCursor, prevCursor };
}