Implementing Efficient Pagination in Prisma: Offset vs. Cursor Strategies

The Performance Gap in Data Retrieval

Fetching thousands of records in a single API call can lead to application timeouts and memory exhaustion. While pagination solves this by splitting data into chunks, the method you choose determines whether your database performance remains stable as your dataset grows. The primary challenge is the offset penalty: as a user navigates to deeper pages, the database must scan and discard all preceding rows before returning the requested set.

Comparing Pagination Strategies

Prisma provides two primary mechanisms for limiting result sets. Choosing between them depends on whether your UI requires random page access (e.g., "Jump to Page 10") or a continuous stream (e.g., "Load More").

Feature	Offset-based (Skip/Take)	Cursor-based (Cursor/Take)
UI Pattern	Numbered pagination	Infinite scroll / Next‑Prev
Performance	Degrades as offset increases	Constant regardless of depth
Data Consistency	Items may shift/duplicate on insert	Stable relative to the cursor
Requirement	None	Unique, indexed field (ID)

Implementing Offset Pagination

Offset pagination uses skip to bypass a specific number of records and take to limit the result set. This is suitable for small datasets where users need to jump to specific pages.

// Run this in your application logic (Node.js/TypeScript)
// Required permissions: Read access to the database
// Placeholders: page (current page number), pageSize (items per page)

const page = 2;
const pageSize = 10;

const results = await prisma.user.findMany({
  skip: (page - 1) * pageSize, // Skips the first 10 records
  take: pageSize,             // Returns the next 10 records
});

Risk: In a table with millions of rows, skip: 100000 forces the database to read 100,000 rows into memory only to discard them, causing significant latency.

Implementing Cursor-based Pagination

Cursor-based pagination uses a unique identifier (the cursor) to tell the database exactly where to start reading. It avoids scanning previous rows entirely by utilizing index seeks.

// Run this in your application logic (Node.js/TypeScript)
// Required permissions: Read access to the database
// Placeholders: cursorId (the ID of the last item from the previous page)

const cursorId = "user_123"; // ID of the last record retrieved
const pageSize = 10;

const results = await prisma.user.findMany({
  take: pageSize + 1, // Fetch one extra to determine if there is a next page
  cursor: { id: cursorId },
  skip: 1, // Skip the cursor record itself to avoid duplication
});

Verification: To confirm this is working efficiently, monitor your database query logs. An offset query will show a OFFSET clause in SQL, while a cursor query will use a WHERE id > 'value' clause, indicating an index seek.

Handling Edge Cases and State

• The First Request: For cursor-based pagination, the first request cannot have a cursor. You must implement a conditional check to omit the cursor object on the initial call.
• Mixed Modes: Do not combine skip and cursor in the same query unless you are specifically skipping the cursor record itself (as shown in the example above). Mixing them for general pagination often leads to unpredictable results.
• Sorting: Cursor pagination requires a deterministic sort order. If you sort by a non-unique field (like createdAt), you must add a secondary unique sort (like id) to ensure the cursor remains precise.

Verification and Testing

1. Offset Check: Request page 1, then page 2. Verify that the first item of page 2 is the item immediately following the last item of page 1.
2. Cursor Check: Pass the ID of the 10th record as the cursor. Verify that the result set begins with the 11th record.
3. Boundary Check: Request a take value larger than the remaining records. Verify the API returns a partial list without crashing.

Rollback and Recovery

Since these operations are read-only (findMany), they do not change the database state. If a pagination change causes application errors, revert the Prisma client query logic to the previous version. If you added a new index to support a cursor field, you can remove it via a Prisma migration: npx prisma migrate dev after removing the @unique or @@index attribute from the schema.