NestJS TypeORM Pagination – Step By Step Guide

When working on an API, sometimes, we have to paginate our data in order to provide a good user experience. In this tutorial, we will be doing a NestJS TypeORM Pagination example. The example will be a products API.

Setting up a NestJS Project

First, let’s set up our project. We will generate a new NestJS project using the Nest CLI.

$> nest new products-api

Adding TypeORM

If we’re doing pagination, we most probably have a lot of data. And when having a lot of data, we also most probably are storing that data inside a database. To interact with our database we will use TypeORM.

let’s install and setup the module:

$> npm install --save @nestjs/typeorm typeorm mysql

I am using MySQL for this example, you can use whichever database you want, you just have to install the correct driver.

Next, let’s import the module in our root module, app.module.ts:

import { Module } from '@nestjs/common'
import { AppController } from './app.controller'
import { AppService } from './app.service'
import { TypeOrmModule } from '@nestjs/typeorm'

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'password',
      database: 'products',
      entities: [],
      synchronize: true,
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

The module will connect to our database when the app starts.

Creating the Products Module

For this example, we will create a small products module. In order to make thing simple, a product will have on an id, name price, and createdAt field.

First, we will create the TypeORM entity:

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm";

@Entity()
export class Product {
  @PrimaryGeneratedColumn()
  id: number

  @Column()
  name: string

  @Column()
  price: number
}

Then, we will add it to the entities array in our TypeORM configuration to sync our database and create the corresponding table:

// ...imports
import { Product } from './products/products.entity'

@Module({
  imports: [
    // ...code
    TypeOrmModule.forRoot({
      // ...code,
      entities: [Product],
    }),
  ],
  // ...code
})
export class AppModule {}

In order to consume this entity, we will create a ProductsService, with a create and findAll methods:

import { Injectable } from "@nestjs/common";
import { Product } from "./product.entity";
import { InjectRepository } from "@nestjs/typeorm";
import { Repository } from "typeorm";
import { CreateProductDto } from "./dto/CreateProduct.dto";

@Injectable()
export class ProductsService {
  constructor(
    @InjectRepository(Product)
    private readonly productsRespository: Repository<Product>
  ) {}

  findAll(): Promise<Product[]> {
    return this.productsRespository.find()
  }

  create(productDto: CreateProductDto): Promise<Product> {
    return this.productsRespository.save(productDto)
  }
}

We have injected the ProductRespository and we’re finding or saving new products, pretty simple so far.

Furthermore, we’re using a DTO as the request body type, CreateProductDto. Let’s add it to the project:

export class CreateProductDto {
  name: string
  price: number
}

Next, we need to create a controller that will use this service to handle the requests:

import { Product } from "./product.entity";
import { ProductsService } from "./products.service";
import { Post, Get, Controller, Body } from "@nestjs/common";
import { CreateProductDto } from "./dto/CreateProduct.dto";

@Controller('products')
export class ProductsController {
  constructor(
    private readonly productsService: ProductsService
  ) {}

  @Get()
  findAll(): Promise<Product[]> {
    return this.productsService.findAll()
  }

  @Post()
  create(@Body() productDto: CreateProductDto): Promise<Product> {
    return this.productsService.create(productDto)
  }
}

Now we will bring everything together inside a products module:

import { Module } from "@nestjs/common";
import { ProductsService } from "./products.service";
import { ProductsController } from "./products.controller";
import { TypeOrmModule } from "@nestjs/typeorm";
import { Product } from "./product.entity";

@Module({
  imports: [TypeOrmModule.forFeature([Product])],
  controllers: [ProductsController],
  providers: [ProductsService],
})
export class ProductsModule {}

Finally, we will import the module in our root module:

// ...imports
import { ProductsModule } from './products/products.module'

@Module({
  imports: [
    // ...code
    ProductsModule,
  ],
  // ...code
})
export class AppModule {}

If we start the server using $> npm run start:dev, we should be able to create a new product and fetch all the products:

Create product request
Fetch all products

NestJS TypeORM Pagination

There are multiple ways to implement pagination, in this tutorial, we will implement pagination using SQL offset and limit.

The limit is the amount of items we will get back.

The offset is the amount of data we will skip, so for the first page we will skip 0, and for the second page, we will skip the limit, etc…

We will add query params to our GET request, the DTO will contain:

export class PaginationDto {
  page: number
  limit: number
}

The response we’ll send back will be:

import { Product } from "../product.entity";

export class PaginatedProductsResultDto {
  data: Product[]
  page: number
  limit: number
  totalCount: number
}

First of all, we add the PaginationDto as the type of our query params object. And pass the data to the findAll service methods. Our method return type should change to Promise<PaginatedProductsResultDto>

// ... imports
import { /* ...named imports */ Query } from "@nestjs/common";
import { PaginationDto } from "./dto/Pagination.dto";

@Controller('products')
export class ProductsController {
  constructor(
    private readonly productsService: ProductsService
  ) {}

  @Get()
  findAll(@Query() paginationDto: PaginationDto): Promise<PaginatedProductsResultDto> {
    paginationDto.page = Number(paginationDto.page)
    paginationDto.limit = Number(paginationDto.limit)

    return this.productsService.findAll({
      ...paginationDto,
      limit: paginationDto.limit > 10 ? 10 : paginationDto.limit
    })
  }
  // ... code
}

Even though our DTO specifies that the page and limit properties are numbers, the query params will be strings. Therefore we need to explicitly convert these values. In addition, we’re also handling negative numbers using Math.abs.

When passing the pagination data to the service method, we’re overriding the limit to force it to be bellow 10. This number depends on your use case.

Next, we will implement the pagination in the service layer using the TypeORM query builder:

// ...imports
import { PaginationDto } from "./dto/Pagination.dto";
import { PaginatedProductsResultDto } from "./dto/PaginatedProductsResult.dto";

@Injectable()
export class ProductsService {
  // ...code

  async findAll(paginationDto: PaginationDto): Promise<PaginatedProductsResultDto> {
    const skippedItems = (paginationDto.page - 1) * paginationDto.limit;

    const totalCount = await this.productsRespository.count()
    const products = await this.productsRespository.createQueryBuilder()
      .orderBy('createdAt', "DESC")
      .offset(skippedItems)
      .limit(paginationDto.limit)
      .getMany()

    return {
      totalCount,
      page: paginationDto.page,
      limit: paginationDto.limit,
      data: products,
    }
  }
  // ...code
}
  

We’re computing the number of skipped items which will be the page minus 1 times the limit, that’s due to the fact that the first page will always be 1. For example, let’s say that our limit equals to 7, as a result, the first page will skip (1 – 1) * 7 which will be 0. Likewise, the second page will skip (2 – 1) * 7 which will be 7.

We’re also sorting the results using the orderBy method with the createdAt field

Finally, we can test this in postman:

NestJS TypeORM Pagination page 1
First page
NestJS TypeORM Pagination page 2
Second Page

Conclusion

We did basic pagination, there are a few improvements that can be done. We need to validate the products data when creating new items, we can do that in the CreateProductDto using the class-validator. Likewise, we can also validate the pagination data in the PaginationDto and remove the custom validation from the controller method.