Cursor pagination with Prisma: a practical NestJS guide

If your NestJS app shows a loading spinner that never goes away when users scroll through a thousand-record table, you've hit the cursor pagination with Prisma problem. Offset-based pagination breaks at scale, forcing your database to skip and discard thousands of rows just to fetch page 50. Cursor-based pagination fixes this by remembering where you left off and fetching only what comes next.

Why not skip/take — The offset trap

Let me start with what most developers do first. You write a simple query with skip and take:

const leads = await prisma.lead.findMany({
  skip: (page - 1) * pageSize,
  take: pageSize,
  orderBy: { createdAt: 'desc' },
});

This works fine for pages 1–5. But imagine a customer scrolling through a million-row lead database and reaching page 1,000. The database now has to:

1. Scan and sort 19,999,980 rows
2. Skip the first 19,999,980
3. Return rows 19,999,981–20,000,000

That's a query that takes 5–10 seconds, your CPU spikes, and your users think the app is broken. I've seen this kill production systems during peak hours. The offset grows, the query degrades, and no amount of indexing saves you once the offset hits six figures.

Cursor-based pagination avoids this entirely. Instead of saying "skip N rows," you say "give me 20 rows after the lead with ID 12345." The database jumps straight there, no scanning required.

Cursor-based pagination — How it works

A cursor is usually the primary key (or a unique, sortable column) of the last item you showed the user. The next request passes that cursor back, and Prisma fetches the next batch directly.

Here's the Prisma query:

const leads = await prisma.lead.findMany({
  take: 20,
  skip: cursor ? 1 : 0,
  cursor: cursor ? { id: cursor } : undefined,
  orderBy: { id: 'asc' },
});

Let's break this down:

• cursor: { id: cursor } — Start after this ID
• skip: 1 — Skip the cursor row itself (we already showed it)
• take: 20 — Fetch exactly 20 rows
• orderBy — Must be a unique or stable column

On the first request, cursor is undefined, so Prisma fetches the first 20 rows. The client receives 20 leads plus the ID of the last one. On the second request, the client sends that ID as the cursor, and Prisma fetches the next 20 rows starting after that ID.

Think of it like a bookmark. You finish page 342 of a book, save the page number, and the next time you open the book, you jump straight to page 343 without re-reading the first 342.

Return the next cursor — Closing the loop

Your API response must tell the client what to pass next. This is the critical part that makes infinite scroll work seamlessly.

import { Controller, Get, Query } from '@nestjs/common';
import { PrismaService } from './prisma.service';

@Controller('leads')
export class LeadsController {
  constructor(private prisma: PrismaService) {}

  @Get()
  async getLeads(@Query('cursor') cursor?: string) {
    const pageSize = 20;
    
    const leads = await this.prisma.lead.findMany({
      take: pageSize,
      skip: cursor ? 1 : 0,
      cursor: cursor ? { id: cursor } : undefined,
      orderBy: { id: 'asc' },
      select: {
        id: true,
        name: true,
        email: true,
        createdAt: true,
      },
    });

    const nextCursor = leads.length === pageSize
      ? leads[leads.length - 1].id
      : null;

    return {
      data: leads,
      nextCursor,
      hasMore: nextCursor !== null,
    };
  }
}

The response looks like:

{
  "data": [
    { "id": "uuid-001", "name": "Alice", "email": "alice@example.com", "createdAt": "2024-01-10T14:23:00Z" },
    { "id": "uuid-002", "name": "Bob", "email": "bob@example.com", "createdAt": "2024-01-11T09:15:00Z" },
    // ... 18 more rows
  ],
  "nextCursor": "uuid-020",
  "hasMore": true
}

The frontend stores nextCursor and passes it on the next request. When hasMore is false, the user has reached the end, and you stop fetching.

This approach scales to billions of rows because you're never skipping anything. Each query touches only the rows it needs.

Stable ordering — The non-negotiable requirement

Cursor pagination lives or dies by ordering consistency. If your order changes between requests, users see duplicate rows or rows disappear entirely.

Say you order by createdAt and two users were created in the same second. Your first query returns user A first, but the second query might return user B first (database order is undefined). The user sees B twice and misses A.

The safe solution: always include the primary key as a tie-breaker.

const leads = await this.prisma.lead.findMany({
  take: pageSize,
  skip: cursor ? 1 : 0,
  cursor: cursor ? { createdAt_id: cursor } : undefined,
  orderBy: [
    { createdAt: 'desc' },
    { id: 'asc' },
  ],
});

With a composite ordering, your cursor must also be composite. For this to work with Prisma's cursor syntax, you need a composite unique constraint in your schema:

model Lead {
  id        String    @id @default(cuid())
  name      String
  email     String
  createdAt DateTime  @default(now())

  @@unique([createdAt, id])
}

Now the cursor is an object: { createdAt: new Date(...), id: "uuid-020" }. Serialize it as JSON or a base64 string to pass through the URL.

Alternatively, if you can't enforce a composite unique constraint, order by id alone. It's always unique and works everywhere.

Common mistakes

• Forgetting skip: 1 — Without it, you return the cursor row again, and the client sees the last row from the previous batch duplicated.
• Mixing stable and unstable ordering — Ordering by a non-unique column without a tie-breaker causes rows to vanish or repeat.
• Passing cursor as a number — If your ID is a UUID or any non-integer, passing cursor: 1000 crashes. Always pass the actual ID value.
• Not serializing composite cursors — If your cursor is an object, base64 encode it or JSON-stringify it before sending to the client. The client must decode it the same way.
• Checking length > pageSize instead of length === pageSize — Prisma returns fewer rows than take only when there are no more rows. If you ask for 20 and get 19, you've reached the end.

Key takeaways

• Offset-based pagination with skip degrades as the offset grows; cursor-based pagination is O(1) regardless of dataset size.
• A cursor is the unique identifier of the last row shown; pass it back to fetch the next batch starting after that row.
• Always include skip: 1 with your cursor to avoid duplicates.
• Order by a unique column, or add the primary key as a secondary sort to guarantee consistency across requests.
• Return nextCursor and hasMore in your response so the frontend knows whether to fetch again.
• Cursor pagination powers infinite scroll, real-time feeds, and large data exports with constant memory and query time.