Building a Reusable Offset Pagination Pattern in NestJS with ValidationPipe and TypeORM

Problem: string query params break numeric validation

When you accept page and limit from the query string, NestJS receives them as strings. If you rely only on @IsInt() decorators, the validation fails because "2" is not an integer type in JavaScript. A common symptom is a 400 response with messages like "page must be an integer" even though the client sent a valid number. This breaks any attempt to build a reusable pagination input.

Thesis: combine @Type(() => Number) with a global ValidationPipe to get declarative, type‑safe pagination that works across controllers.

1. Define a reusable DTO

Create a PaginationQueryDto that transforms incoming strings to numbers and applies sensible constraints.

import { IsInt, Min, Max } from 'class-validator';
import { Type } from 'class-transformer';

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

  @Type(() => Number)
  @IsInt()
  @Min(1)
  @Max(100) // enforce a maximum page size
  public limit: number = 10;
}

The @Type(() => Number) decorator tells class‑transformer to convert the incoming string to a JavaScript number before validation runs. Without it, the decorators would see a string and fail.

2. Wire up a global ValidationPipe

In main.ts enable transformation so every controller can use the DTO without extra boilerplate.

import { NestFactory } from '@nestjs/core';
import { ValidationPipe } from '@nestjs/common';
import { AppModule } from './app.module';

async function bootstrap() {
  const app = await NestFactory.create(AppModule);
  app.useGlobalPipes(
    new ValidationPipe({
      whitelist: true,          // strip unknown properties
      forbidNonWhitelisted: true, // reject unexpected fields
      transform: true,          // enable @Type conversion
    }),
  );
  await app.listen(3000);
}
bootstrap();

3. Use the DTO in a controller

Now the handler receives a properly typed object.

import { Controller, Get, Query } from '@nestjs/common';
import { PaginationQueryDto } from './dto/pagination-query.dto';
import { UsersService } from './users.service';

@Controller('users')
export class UsersController {
  constructor(private readonly usersService: UsersService) {}

  @Get()
  async findAll(@Query() pagination: PaginationQueryDto) {
    return this.usersService.paginate(pagination);
  }
}

4. Service layer: TypeORM findAndCount

The service builds the skip and take values, adds a deterministic ordering, and returns a standardized envelope.

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './user.entity';
import { PaginationQueryDto } from './dto/pagination-query.dto';

@Injectable()
export class UsersService {
  constructor(
    @InjectRepository(User)
    private readonly repo: Repository,
  ) {}

  async paginate(dto: PaginationQueryDto) {
    const page = dto.page;
    const limit = dto.limit;
    const skip = (page - 1) * limit;

    const [data, total] = await this.repo.findAndCount({
      skip,
      take: limit,
      order: { createdAt: 'ASC', id: 'ASC' }, // stable tie‑breaker
    });

    const totalPages = Math.ceil(total / limit);
    return {
      data,
      meta: {
        page,
        limit,
        total,
        totalPages,
      },
    };
  }
}

Trade‑off: offset pagination limitations

While the pattern above is quick to adopt, it has well‑known drawbacks:

• Performance: Large OFFSET values force the database to scan and discard preceding rows, which grows linearly with the page number.
• Consistency under writes: If rows are inserted or deleted between two requests, the same item can appear on two pages (duplicate) or be skipped entirely.

You can verify the consistency issue locally:

1. Seed a table with 200 rows.
2. Request page 1 (?page=1&limit=50) and note the IDs returned.
3. Insert a new row with an ID that would sort before the current page 1 results.
4. Request page 2 (?page=2&limit=50) and observe whether any ID from page 1 appears again or is missing.

For deep pagination or high‑write workloads, consider cursor (keyset) pagination using an indexed column (e.g., createdAt) as the next step.

Practical verification steps

After implementing the DTO and pipe:

• Call the endpoint with non‑numeric values (?page=two) and confirm you receive a 400 validation error.
• Call with numeric strings (?page=2&limit=25) and check that the response includes meta.page: 2 and meta.limit: 25.
• Run EXPLAIN ANALYZE SELECT ... LIMIT 25 OFFSET 500 against your database to see the scan cost; compare with a query using a keyset condition.

These checks let you confirm that the transformation works, the envelope is correct, and you understand the performance implication of the chosen offset approach.

Closing

By centralizing validation and transformation in a reusable DTO and a global ValidationPipe, you eliminate a common source of bugs and gain a consistent response shape across your NestJS API. Remember to cap the limit, enforce a stable ordering, and treat offset pagination as a convenient shortcut that may need replacement with cursor‑based pagination as your data grows or your write traffic increases.