Pagination, Filtering, and Query Optimization in NestJS

When working with APIs that return large datasets, it’s essential to provide mechanisms like pagination, filtering, and query optimization. These strategies not only enhance user experience but also help improve application performance and reduce server load.

In this module, you’ll learn how to implement efficient pagination, flexible filtering, and optimized queries in NestJS, particularly when using TypeORM.

Table of Contents

1. Why Pagination and Filtering Matter
2. Implementing Pagination
   • Offset-Based Pagination
   • Cursor-Based Pagination (Brief Intro)
3. Adding Filtering to Endpoints
   • Using DTOs for Filters
4. Query Optimization Techniques
5. Practical Example
6. Best Practices
7. Conclusion

Why Pagination and Filtering Matter

Returning an entire database collection in one request is inefficient and unsustainable for both frontend and backend systems. Pagination and filtering allow clients to fetch data incrementally and refine the results, which:

• Reduces bandwidth usage
• Improves database performance
• Enhances user experience with faster load times

Implementing Pagination

Offset-Based Pagination (Standard)

This is the most common method using skip and take with TypeORM.

DTO for Pagination

tsCopyEdit// dto/pagination-query.dto.ts
import { Type } from 'class-transformer';
import { IsOptional, IsPositive, Min } from 'class-validator';

export class PaginationQueryDto {
  @IsOptional()
  @Type(() => Number)
  @IsPositive()
  limit?: number;

  @IsOptional()
  @Type(() => Number)
  @Min(0)
  offset?: number;
}

Applying in Service

tsCopyEdit// user.service.ts
async findAll(paginationQuery: PaginationQueryDto) {
  const { limit, offset } = paginationQuery;
  return this.userRepository.find({
    skip: offset,
    take: limit,
    order: { createdAt: 'DESC' },
  });
}

Cursor-Based Pagination (Brief Intro)

While not covered deeply here, cursor-based pagination is recommended for real-time apps and large datasets where offset becomes inefficient. It involves using unique identifiers (like timestamps or IDs) to fetch the next set of data.

Adding Filtering to Endpoints

You can add flexible filtering using optional query parameters.

Using DTOs for Filters

tsCopyEdit// dto/filter-query.dto.ts
import { IsOptional, IsEmail } from 'class-validator';

export class FilterQueryDto {
  @IsOptional()
  name?: string;

  @IsOptional()
  @IsEmail()
  email?: string;
}

Apply Filters in Query

tsCopyEditasync findAll(query: PaginationQueryDto & FilterQueryDto) {
  const { limit, offset, name, email } = query;

  const qb = this.userRepository.createQueryBuilder('user');

  if (name) {
    qb.andWhere('user.name LIKE :name', { name: `%${name}%` });
  }

  if (email) {
    qb.andWhere('user.email = :email', { email });
  }

  return qb.skip(offset).take(limit).orderBy('user.createdAt', 'DESC').getMany();
}

Query Optimization Techniques

To improve database performance, apply the following:

1. Select Only Required Fields

tsCopyEditqb.select(['user.id', 'user.name']);

2. Use Joins Only When Necessary

tsCopyEditqb.leftJoinAndSelect('user.profile', 'profile'); // only if needed

3. Add Indexes to Frequently Queried Fields

In your entity:

tsCopyEdit@Index()
@Column()
email: string;

4. Use .getRawMany() if You Don’t Need Entity Transformations

tsCopyEditconst rawUsers = await qb.getRawMany();

Practical Example: Combined Usage

tsCopyEdit@Get()
async getUsers(
  @Query() paginationQuery: PaginationQueryDto,
  @Query() filterQuery: FilterQueryDto
) {
  return this.userService.findAll({ ...paginationQuery, ...filterQuery });
}

Best Practices

• Validate and sanitize query parameters using DTOs.
• Cap the limit (e.g., max 100) to prevent large data dumps.
• Paginate all list endpoints for scalability.
• Use indexes and optimize queries with profiling tools (e.g., EXPLAIN).
• For large datasets, prefer cursor-based pagination.

Conclusion

Pagination, filtering, and query optimization are crucial for building scalable APIs in NestJS. With a combination of DTO validation, flexible TypeORM queries, and best practices, you can ensure your endpoints are performant and reliable—regardless of the data volume.