How to implement pagination in nestjs with typeorm?

Implementing pagination queries with TypeORM in NestJS typically follows these steps:

1. Create a Data Access Service: Define a method within the service to handle query logic.
2. Receive Pagination Parameters: Accept pagination parameters from the client, typically page and limit, where page denotes the current page number and limit specifies the number of items per page.
3. Calculate the Number of Items to Skip: Compute the skip value based on pagination parameters, which indicates the number of items to skip. This is calculated as (page - 1) * limit.
4. Execute the Query and Return Results: Utilize TypeORM's findAndCount or createQueryBuilder methods to execute the pagination query and compute the total count.

Below is a concrete example:

typescript
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { YourEntity } from './entities/your.entity';

@Injectable()
export class YourService {
  constructor(
    @InjectRepository(YourEntity)
    private readonly yourEntityRepository: Repository<YourEntity>,
  ) {}

  async paginate(page: number = 1, limit: number = 10) {
    // Calculate the number of items to skip
    const skip = (page - 1) * limit;

    // Execute query and return results with total count
    const [results, total] = await this.yourEntityRepository.findAndCount({
      take: limit,
      skip: skip,
      // Add other query conditions and sorting as needed
      // where: { ... },
      // order: { ... },
    });

    // Return pagination results and metadata
    return {
      data: results, // Current page data
      count: total, // Total number of items
      currentPage: page, // Current page number
      totalPages: Math.ceil(total / limit), // Total pages
    };
  }
}

In this example, the paginate method in the YourService service accepts two parameters, page and limit, corresponding to the client's request for the current page number and items per page. The method uses findAndCount to execute the query and compute the total count, then returns an object containing the current page data, total count, current page number, and total pages.

Practically, you should handle edge cases such as verifying that page and limit are positive integers and that page does not exceed the total pages. Additionally, incorporate sorting and filtering conditions as needed to meet specific business requirements.

There are two methods to achieve this:

createQueryBuilder and findAndCount

shell
const userRepository = dataSource.getRepository(User);
const _take = query.take || 10;
const _skip = query.skip || 0;

Using createQueryBuilder

shell
const qb = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .orderBy("user.id", "DESC")
    .take(_take)
    .skip(_skip);

    const users = await qb.getMany();
    const total = await qb.getCount();

Using findAndCount. With this method, you can obtain all results in a single call.

shell
const [users, total]  = await userRepository.findAndCount({
        order: {
            id: 'DESC'
        }
        skip: _skip,
        take: _take
});

In short, TypeORM provides an excellent method specifically designed for paginated queries: findAndCount.

typescript
async findAll(query): Promise<Paginate> {
    const take = query.take || 10
    const skip = query.skip || 0
    const keyword = query.keyword || ''

    const [result, total] = await this.userRepository.findAndCount(
        {
            where: { name: Like('%' + keyword + '%') }, order: { name: "DESC" },
            take: take,
            skip: skip
        }
    );

    return {
        data: result,
        count: total
    }
}