Using Prisma Client Cursor Pagination for Stable API Pages

The problem: unpredictable pages when data changes

When building a REST or GraphQL endpoint that returns a list of records, engineers often reach for skip and take to implement pagination. This works fine while the dataset is static, but as soon as new rows are inserted or existing rows are deleted between requests, the same logical page can return duplicate items or skip others entirely. The instability hurts user experience and makes caching difficult.

Thesis: cursor‑based pagination in Prisma Client gives you a deterministic, type‑safe way to page through data without scanning preceding rows

Prisma Client exposes the arguments cursor, skip, take, and orderBy on every model query. By anchoring the query to a unique cursor value (usually the primary key) and setting skip: 1 (or 0 for the first page), the database can start the scan exactly after the last returned row, guaranteeing stable results even under concurrent writes.

How cursor pagination works in Prisma

The core idea is simple:

1. Choose a column that is unique and immutable for the ordering (most often the model’s @id field).
2. When you fetch a page, remember the value of that column from the last record you returned.
3. For the next request, pass that value as cursor and set skip: 1 (skip the cursor itself) together with take for the page size and orderBy matching the chosen column.

Because the cursor points to a specific row, the database does not need to count or skip preceding rows; it can start the scan directly after that row, which is efficient even on large tables.

Worked example: paginating a Post model

Assume a Prisma schema with a simple blog post:

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  createdAt DateTime @default(now())
}

After running prisma generate, the generated client provides type‑safe query builders. The following TypeScript snippet shows how to fetch the first page and then the next page using a cursor.

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function getPostsPage(cursorId: number | undefined, pageSize: number) {
  // cursorId is undefined for the first page
  return prisma.post.findMany({
    take: pageSize,
    skip: cursorId === undefined ? 0 : 1, // skip the cursor row itself
    cursor: cursorId === undefined ? undefined : { id: cursorId },
    orderBy: { id: 'asc' },
    select: { id: true, title: true, createdAt: true },
  })
}

// Example usage:
const PAGE_SIZE = 10
let firstPage = await getPostsPage(undefined, PAGE_SIZE)
console.log('First page length:', firstPage.length)

const lastId = firstPage[firstPage.length - 1]?.id
let secondPage = await getPostsPage(lastId, PAGE_SIZE)
console.log('Second page length:', secondPage.length)

Where to run: This code can be executed in any Node.js environment where the Prisma client is installed (e.g., an API handler, a script, or a Jest test). No special permissions are required beyond the database user that Prisma uses for connections.

Expected checks: After each call, verify that the returned array length is ≤ pageSize and that the id values are strictly increasing across pages. You can also run a count query (prisma.post.count()) to ensure the sum of returned rows matches the total when you iterate to exhaustion.

Trade‑off: ordering stability and the N+1 risk

Cursor pagination relies on a stable, unique ordering. If you choose a column that can change (e.g., a status field) or is not unique, you may see duplicate or missing rows when updates occur. Always anchor the cursor to the primary key or another column with a unique index.

When you include relations in the select or include clause, Prisma may issue separate queries for each related record unless you configure relation loading strategy. To avoid the N+1 problem, use include with the load option or fetch relations in a separate query after collecting the needed foreign keys.

Actionable closing: adopt cursor pagination for list endpoints

If your API needs to return large, changing datasets, replace skip/take pagination with the cursor pattern described above. Start by adding a unique orderBy field (usually the model’s ID), implement the helper function shown, and write a small integration test that asserts non‑overlapping pages under concurrent inserts. The generated TypeScript types will give you autocomplete and compile‑time safety for the shape of each page, reducing runtime bugs and making the data‑access layer declarative and portable across Prisma’s supported databases.