Implementing NestJS Cursor Pagination with TypeORM

Most applications start with offset-based pagination. You accept page and limit parameters, apply OFFSET and LIMIT in SQL, and return the data. It works fine on small tables, but as tables scale into millions of rows, OFFSET forces the database engine to scan and discard rows it never returns. For any serious nestjs cursor pagination implementation, ditching OFFSET in favor of keyset pagination is the most reliable way to maintain sub-millisecond database queries.

Beyond raw latency, offset pagination causes dirty reads in active datasets. If a user views page one and new rows are inserted before they request page two, items shift downward, creating duplicates on their screen. Paired with proper database tuning, such as the steps covered in my guide to PostgreSQL Ubuntu production configuration, cursor-based pagination guarantees consistent index traversal regardless of write volume.

Why Keyset Pagination Outperforms Offset

When you execute OFFSET 50000 LIMIT 20, PostgreSQL or MySQL must read 50,020 index entries or table blocks, throw away the first 50,000, and return only the trailing 20. The deeper your client pages, the slower your response times become. Keyset pagination solves this by turning pagination into an indexed range query: WHERE id < :cursor ORDER BY id DESC LIMIT :limit.

Under this pattern, the database uses a B-tree index jump to locate the exact record matching your cursor and reads strictly the requested row count. Query performance for page 10,000 becomes identical to page 1. The trade-off is losing the ability to jump directly to arbitrary page numbers, but for feeds, infinite scrolls, and high-throughput exports, keyset pagination is strictly superior.

Designing the Cursor Schema

A solid cursor requires determinism and uniqueness. If you paginate by a non-unique column like created_at, two records created during the same millisecond could result in skipped rows. To prevent this, build a compound cursor combining the sort value and the primary key.

To keep your API clean, serialize this compound value into an opaque string, typically base64-encoded JSON. The client treats the cursor as a black box and returns it in subsequent queries via a query parameter.

Building the Keyset Cursor Helper

Let us construct a generic TypeScript helper to serialize and deserialize cursors. This handles encoding our sort field and primary key into a URL-safe format.

interface CursorPayload {
  id: string;
  createdAt: string;
}

export function encodeCursor(payload: CursorPayload): string {
  return Buffer.from(JSON.stringify(payload)).toString('base64url');
}

export function decodeCursor(cursor: string): CursorPayload {
  try {
    const json = Buffer.from(cursor, 'base64url').toString('utf8');
    const parsed = JSON.parse(json);
    if (!parsed.id || !parsed.createdAt) {
      throw new Error('Malformed cursor payload');
    }
    return parsed;
  } catch {
    throw new Error('Invalid cursor token');
  }
}

Implementing NestJS Cursor Pagination in TypeORM

Now we can implement the repository query inside a NestJS service. We will use TypeORM's SelectQueryBuilder to dynamically attach the cursor conditions. When using TypeORM pagination, fetch limit + 1 items. If the extra item exists, pop it off and use its values to generate the nextCursor token.

Remember to guard input parameters rigorously using class-validator pipes, preventing common parameter pollution vulnerabilities as discussed in Node.js API injection security.

import { Injectable, BadRequestException } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { PostEntity } from './post.entity';
import { encodeCursor, decodeCursor } from '../common/pagination/cursor.utils';

export interface PaginatedResult<T> {
  data: T[];
  nextCursor: string | null;
  hasNextPage: boolean;
}

@Injectable()
export class PostsService {
  constructor(
    @InjectRepository(PostEntity)
    private readonly postRepo: Repository<PostEntity>,
  ) {}

  async getPosts(limit = 20, cursor?: string): Promise<PaginatedResult<PostEntity>> {
    const qb = this.postRepo
      .createQueryBuilder('post')
      .orderBy('post.createdAt', 'DESC')
      .addOrderBy('post.id', 'DESC')
      .take(limit + 1);

    if (cursor) {
      try {
        const decoded = decodeCursor(cursor);
        qb.where(
          '(post.createdAt < :createdAt OR (post.createdAt = :createdAt AND post.id < :id))',
          {
            createdAt: new Date(decoded.createdAt),
            id: decoded.id,
          },
        );
      } catch (err) {
        throw new BadRequestException('Provided cursor is invalid.');
      }
    }

    const items = await qb.getMany();
    const hasNextPage = items.length > limit;
    const data = hasNextPage ? items.slice(0, limit) : items;

    let nextCursor: string | null = null;
    if (hasNextPage && data.length > 0) {
      const lastItem = data[data.length - 1];
      nextCursor = encodeCursor({
        id: lastItem.id,
        createdAt: lastItem.createdAt.toISOString(),
      });
    }

    return {
      data,
      nextCursor,
      hasNextPage,
    };
  }
}

Index Design for High-Throughput APIs

A cursor query is only as fast as the database index supporting it. Without a composite index covering both ordering criteria, your database planner will default to a sequential scan or an expensive sort node in memory, defeating the whole purpose of switching away from offset pagination.

In PostgreSQL, add a composite index matching the exact columns and directions specified in your query builder. For our post feed in our nestjs typeorm api, define the index on created_at and id descending:

CREATE INDEX CONCURRENTLY idx_posts_created_at_id_desc 
ON posts (created_at DESC, id DESC);

Running EXPLAIN ANALYZE on this query will demonstrate an Index Scan Backward or Index Scan on idx_posts_created_at_id_desc with zero buffer waste. The database jumps straight to the cursor's tuple and walks forward twenty rows.

Handling Edge Cases and API Ergonomics

When structuring your API response, avoid returning arbitrary database internals. Return an opaque string or null for nextCursor. Your frontend client simply tracks whether hasNextPage is true and sends nextCursor as the cursor query param in the next fetch.

If you need bidirectional pagination (scrolling up and down), you can extend this mechanism by supporting a prevCursor with an inverted comparison (WHERE post.createdAt > :createdAt) and reversing the returned array before dispatching the response.

Shifting to cursor pagination requires slightly more initial plumbing than passing page and limit straight into a SQL OFFSET clause. However, for applications expecting sustained growth, this architectural choice preserves database health, keeps p99 latencies predictable, and eliminates duplicate-item glitches for your users.