Pagination and Filtering

Last time in this series, I covered API Versioning, because once external clients depend on your API, you need a contract you can evolve without breaking them. Pagination and filtering sit right next to that concern: they're the other place where a list endpoint's contract gets locked in early and becomes expensive to change once mobile apps, dashboards, and third-party integrations are all calling it.

This is part of the NestJS Production Guide, and it covers a problem every list endpoint in a real SaaS eventually has to solve. It looks simple, "just add a limit and offset", and it is simple, right up until the table has a few million rows, someone asks for sort-by-anything, and the product team wants an infinite-scroll feed that doesn't skip or duplicate rows when new data arrives mid-scroll.

I'll cover both major pagination strategies, when to reach for each, a filtering pattern that stays safe against injection while remaining flexible, and the pitfalls that show up specifically once an endpoint is in production and under real traffic.

Why pagination is not optional

Any endpoint that returns a list from a table that grows over time needs pagination from day one, not as a later optimization. Returning all rows works fine in a demo with twenty seed records and falls over the moment a customer has fifty thousand orders. The failure mode is rarely an obvious crash. More often it's a slow, memory-heavy response that degrades gradually as data grows, which makes it tempting to postpone and easy to forget until a support ticket comes in.

There are two pagination strategies worth knowing, and they solve different problems:

• Offset-based (limit/offset): the client asks for page N of size M. Simple to implement, simple for the client to reason about, and it supports jumping to an arbitrary page ("go to page 12").
• Cursor-based (keyset): the client asks for "the next M rows after this specific row". No arbitrary page jumps, but it stays stable and fast even as the underlying data changes between requests.

Neither is universally correct. Offset pagination is the right default for admin tables, reports, and anything with a page-number UI. Cursor pagination is the right choice for feeds, activity logs, and any endpoint where new rows are inserted at the head while a user is actively paging through it.

Offset-based pagination, done properly

The naive version of offset pagination is a page and pageSize query param mapped straight onto SKIP and TAKE. That much is easy. The part that's easy to get wrong is validation, defaults, and the total count.

// src/common/dto/pagination-query.dto.ts
import { Type } from 'class-transformer';
import { IsInt, IsOptional, Max, Min } from 'class-validator';

export class PaginationQueryDto {
  @IsOptional()
  @Type(() => Number)
  @IsInt()
  @Min(1)
  page: number = 1;

  @IsOptional()
  @Type(() => Number)
  @IsInt()
  @Min(1)
  @Max(100)
  pageSize: number = 20;

  get skip(): number {
    return (this.page - 1) * this.pageSize;
  }
}

The @Max(100) matters more than it looks. Without an upper bound, a client (or a misbehaving script) can request pageSize=1000000 and turn a cheap query into a full table scan that ties up a connection pool slot for seconds. Cap it, and document the cap in your API reference so client teams don't have to discover it by trial and error.

TypeORM's findAndCount is the natural fit here, since it returns both the page of rows and the total count in one call:

// src/orders/orders.service.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { Order } from './order.entity';
import { PaginationQueryDto } from '../common/dto/pagination-query.dto';

export interface PaginatedResult<T> {
  data: T[];
  meta: {
    page: number;
    pageSize: number;
    total: number;
    totalPages: number;
  };
}

@Injectable()
export class OrdersService {
  constructor(
    @InjectRepository(Order)
    private readonly ordersRepo: Repository<Order>,
  ) {}

  async findAll(
    orgId: string,
    query: PaginationQueryDto,
  ): Promise<PaginatedResult<Order>> {
    const [data, total] = await this.ordersRepo.findAndCount({
      where: { orgId },
      order: { createdAt: 'DESC' },
      skip: query.skip,
      take: query.pageSize,
    });

    return {
      data,
      meta: {
        page: query.page,
        pageSize: query.pageSize,
        total,
        totalPages: Math.ceil(total / query.pageSize),
      },
    };
  }
}

The tradeoff hiding in findAndCount is that it runs two queries: one for the page of rows and one COUNT(*) for the total. On a well-indexed, filtered query that count is usually cheap. On an unfiltered scan of a huge table, the count becomes the slow part of the request, sometimes slower than fetching the actual rows. If the total isn't something the UI strictly needs (many infinite-scroll UIs skip "page 4 of 90"), drop it. If the UI does need it and the table is large, cache the count for a few seconds instead of recomputing it every request, or use an approximate estimate from pg_class.reltuples.

Cursor-based pagination for feeds and logs

Offset pagination has a correctness problem that only shows up under concurrent writes: if a row is inserted or deleted between page 1 and page 2, the client either sees a duplicate or skips a row, because "page 2" is defined by position, not identity. For an admin table where the user reloads the whole list anyway, that's a non-issue. For an activity feed polled every few seconds while new events land, it's a real bug users notice.

Cursor pagination fixes this by anchoring to a value instead of a position. The client sends the last item it saw, and the query asks for rows strictly after that item, ordered consistently:

// src/common/dto/cursor-query.dto.ts
import { IsIn, IsOptional, IsInt, Min, Max } from 'class-validator';
import { Type } from 'class-transformer';

export class CursorQueryDto {
  @IsOptional()
  cursor?: string; // base64-encoded { createdAt, id }

  @IsOptional()
  @Type(() => Number)
  @IsInt()
  @Min(1)
  @Max(100)
  limit: number = 20;

  @IsOptional()
  @IsIn(['ASC', 'DESC'])
  order: 'ASC' | 'DESC' = 'DESC';
}

// src/events/events.service.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { Event } from './event.entity';
import { CursorQueryDto } from '../common/dto/cursor-query.dto';

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

function decodeCursor(cursor?: string): Cursor | null {
  if (!cursor) return null;
  return JSON.parse(Buffer.from(cursor, 'base64').toString('utf8'));
}

function encodeCursor(row: Cursor): string {
  return Buffer.from(JSON.stringify(row)).toString('base64');
}

@Injectable()
export class EventsService {
  constructor(
    @InjectRepository(Event)
    private readonly eventsRepo: Repository<Event>,
  ) {}

  async findPage(orgId: string, query: CursorQueryDto) {
    const decoded = decodeCursor(query.cursor);
    const qb = this.eventsRepo
      .createQueryBuilder('event')
      .where('event.orgId = :orgId', { orgId })
      .orderBy('event.createdAt', query.order)
      .addOrderBy('event.id', query.order)
      .take(query.limit + 1); // fetch one extra to know if there's a next page

    if (decoded) {
      const op = query.order === 'DESC' ? '<' : '>';
      qb.andWhere(
        `(event.createdAt, event.id) ${op} (:createdAt, :id)`,
        decoded,
      );
    }

    const rows = await qb.getMany();
    const hasNext = rows.length > query.limit;
    const page = hasNext ? rows.slice(0, query.limit) : rows;
    const last = page[page.length - 1];

    return {
      data: page,
      nextCursor: hasNext && last
        ? encodeCursor({ createdAt: last.createdAt.toISOString(), id: last.id })
        : null,
    };
  }
}

The tuple comparison (createdAt, id) < (createdAt, id) is the key trick: it gives a stable, unambiguous ordering even when multiple rows share the same createdAt down to the millisecond, which happens more often than you'd expect on bulk inserts. Using only createdAt as the cursor without a tiebreaker column is a common bug: it works in testing and silently drops or duplicates rows in production once two events land in the same millisecond.

The real tradeoff with cursor pagination is that you give up random access. There's no "jump to page 7" because a cursor only knows how to move forward from where it is. That's a fair trade for a feed; it's the wrong trade for an admin table where a support agent wants to jump straight to a specific page.

Filtering without opening an injection hole

Filtering wants to be flexible, "let the client filter by status, date range, assigned user, whatever", but careless flexibility is exactly how SQL injection gets into an application: someone builds the WHERE clause by concatenating a field name and operator straight from the query string. The fix is to whitelist rather than trust.

// src/orders/dto/order-filter.dto.ts
import { IsOptional, IsEnum, IsDateString, IsUUID } from 'class-validator';

export enum OrderStatus {
  PENDING = 'pending',
  PAID = 'paid',
  CANCELLED = 'cancelled',
}

export class OrderFilterDto {
  @IsOptional()
  @IsEnum(OrderStatus)
  status?: OrderStatus;

  @IsOptional()
  @IsUUID()
  assignedTo?: string;

  @IsOptional()
  @IsDateString()
  createdAfter?: string;

  @IsOptional()
  @IsDateString()
  createdBefore?: string;
}

// src/orders/orders.service.ts (excerpt)
async findFiltered(orgId: string, filter: OrderFilterDto, pagination: PaginationQueryDto) {
  const qb = this.ordersRepo
    .createQueryBuilder('order')
    .where('order.orgId = :orgId', { orgId });

  if (filter.status) {
    qb.andWhere('order.status = :status', { status: filter.status });
  }
  if (filter.assignedTo) {
    qb.andWhere('order.assignedTo = :assignedTo', { assignedTo: filter.assignedTo });
  }
  if (filter.createdAfter) {
    qb.andWhere('order.createdAt >= :createdAfter', { createdAfter: filter.createdAfter });
  }
  if (filter.createdBefore) {
    qb.andWhere('order.createdAt <= :createdBefore', { createdBefore: filter.createdBefore });
  }

  qb.orderBy('order.createdAt', 'DESC')
    .skip(pagination.skip)
    .take(pagination.pageSize);

  const [data, total] = await qb.getManyAndCount();
  return { data, total };
}

Every filterable field is an explicit DTO property with its own validator, and every value reaches the query through a parameterized binding (:status, :assignedTo), never through string interpolation. This also gives you, for free, a place to enforce authorization: filtering by orgId comes from the authenticated request context, not from the client, so a user from one tenant can never construct a query that reaches into another tenant's rows.

Generic "field=value&op=gt" style query languages exist and are useful for internal admin tools where the audience is trusted. For a public or semi-public API, I'd rather ship five well-defined filter fields than one generic filter DSL, because the DSL is both a bigger attack surface and a support burden once client teams start relying on undocumented operator combinations.

Production pitfalls worth knowing before they bite

A few issues only show up once an endpoint is live and under real traffic, not in local testing with a handful of rows.

Missing indexes on filter and sort columns. A WHERE org_id = ? AND status = ? ORDER BY created_at DESC query needs a composite index that matches, typically (org_id, status, created_at). Without it, Postgres falls back to a sequential scan or an inefficient sort, and the query slows down in proportion to table growth rather than result size.

COUNT(*) on large, loosely filtered tables. Covered above, but worth repeating: it's the most common pagination-related slow query I've seen in code review. Skip it when the UI doesn't strictly need it.

Deep offset pagination. OFFSET 500000 still requires the database to scan and discard half a million rows before returning anything. If a legitimate use case needs to page deep into a large table, that's a signal to switch to cursor pagination, rather than add more caching on top of offset pagination.

Unbounded pageSize. Already mentioned, but it's the easiest one to forget: cap it in the DTO, not just in documentation.

Inconsistent sort tiebreakers. Sorting by a single non-unique column, updatedAt is a frequent offender, means rows with equal values can appear in a different relative order between requests, corrupting both offset and cursor pagination. Always add the primary key as a secondary sort key.