Why Cursor Pagination Is a Practical Default for Prisma APIs

Your Prisma endpoint feels instant on page one. By page fifty, the same query takes seconds. The culprit is usually offset pagination: skip: 500, take: 10 tells the database to read 510 rows and discard the first 500. As the table grows, that scan cost dominates response time and burns connection pool capacity.

Cursor-based pagination is a practical default for Prisma-backed APIs. Instead of counting rows to skip, you remember where the last page ended and ask for the next batch directly. Prisma supports this natively with cursor, take, and orderBy. The rest of this post shows how the two approaches differ, a worked example, and the trade-offs you accept when you switch.

Offset pagination: the skip tax

Offset pagination uses skip and take. Prisma passes these straight to SQL OFFSET and LIMIT. The database engine must locate the starting row by walking past every skipped record, even though it returns only a handful. On a table with 100,000 rows, skip: 99990, take: 10 still touches nearly the entire dataset.

That cost is invisible in development with a seeded table of 50 rows. It becomes a production incident when a report or admin UI requests deep pages. The query plan shows a sequential scan or index scan with a large offset, and latency climbs linearly with page depth.

Cursor pagination: seek, don't scan

Cursor pagination replaces the numeric offset with a pointer to a specific row. In Prisma, you supply cursor: { id: value } alongside take: n and an orderBy clause. The database uses the cursor value to seek directly to the position after that row, then returns the next n records. No rows are read and thrown away.

Two rules keep cursors stable. First, the cursor field must be unique; the primary key is the usual choice. Second, the orderBy direction must stay identical between requests. If you sort by a non-unique column such as createdAt, append a tiebreaker like id so the ordering is deterministic.

Exposing raw database IDs can leak information, especially if they are sequential integers. A common pattern is to encode the cursor as an opaque string—for example, Base64 of the last row's ID—so clients treat it as a black box.

Worked example: a paginated post feed

The following function runs in your Node.js service layer. It fetches one page of posts, plus one extra row to detect whether another page exists. Placeholders: lastCursor is the opaque string returned by the previous call; PAGE_SIZE is your page length.

// Service layer (Node.js / TypeScript)
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

export async function getPosts(lastCursor?: string) {
  const PAGE_SIZE = 10;

  // Decode the opaque cursor back to the last seen ID
  const cursorId = lastCursor
    ? Buffer.from(lastCursor, 'base64').toString('utf-8')
    : undefined;

  const rows = await prisma.post.findMany({
    take: PAGE_SIZE + 1, // one extra row detects hasNextPage
    ...(cursorId && { cursor: { id: cursorId } }),
    orderBy: [{ createdAt: 'desc' }, { id: 'desc' }],
  });

  const hasNextPage = rows.length > PAGE_SIZE;
  const items = hasNextPage ? rows.slice(0, PAGE_SIZE) : rows;

  const nextCursor = hasNextPage
    ? Buffer.from(items[items.length - 1].id).toString('base64')
    : null;

  return { items, nextCursor };
}

Expected checks: the response contains an items array and a nextCursor string. On the final page, nextCursor is null. If you log the generated SQL, you will see a WHERE id > $1 (or equivalent) clause rather than OFFSET.

Risk: if the client changes the orderBy direction or adds a filter between requests, the cursor may point to a row outside the new result set. That produces gaps or duplicates. Treat the cursor as valid only for the exact query shape that produced it.

Trade-offs and when offset still wins

Cursor pagination is not a drop-in replacement for every UI. It gives up arbitrary page jumps. A "jump to page 25" control needs a numeric offset, because a cursor only knows how to move forward (or backward with reversed ordering) from a known row.

It also assumes a stable sort. If rows are inserted or deleted concurrently, offset pagination shifts records between pages. Cursor pagination anchored to a unique ID remains stable: a new row inserted before the cursor does not affect the current page, and a deleted row after the cursor simply disappears from future pages without shifting earlier results.

For internal admin tools with small tables and numbered page controls, skip/take remains simpler. For public APIs, infinite scroll, and feeds that grow over time, cursors are the safer default.

How to verify it in your project

First, inspect the generated Prisma Client types for your model's findMany method. Confirm that cursor, take, and orderBy are accepted arguments. If the client is stale, regenerate it from your project root and re-check.

Second, write an integration test that requests page one, inserts a new row that would appear on page one, then requests page two using the returned cursor. The second page should not skip or duplicate records. This directly validates stability under concurrent writes.

Third, if you have a large table, compare query plans. Run the offset version and the cursor version against the same dataset and inspect whether the database performs a sequential scan with a high offset or an index seek. The difference is usually visible in the execution plan before it shows up in application latency.

Bottom line

Offset pagination asks the database to do work it immediately discards. Cursor pagination asks for exactly the rows you need, in a stable order, using a pointer that survives concurrent writes. For Prisma APIs that serve growing datasets, make cursors your default and keep skip/take for the rare cases that truly need numbered pages.