API with NestJS #107. Offset and keyset pagination with Prisma

May 8, 2023

This entry is part 107 of 121 in the API with NestJS

The bigger our database, the more we need to care about the performance. Returning too much data at once through our API might not be the best approach when it comes to performance. A common solution is to divide our data into chunks and present it to the user as infinite scrolling or multiple pages. In this article, we implement it with PostgreSQL and Prisma. We also compare various ways of paginating and how it can affect performance.

You can find the code from this article in this repository.

Offset and Limit

First, let’s take a look at a fundamental SELECT query.

1	SELECT id, title FROM "Post"

The most important thing is that it returns all of the records from our table. Also, we need to acknowledge that the order of the rows in the result is not guaranteed. When implementing pagination, we need the order to be predictable. Because of that, we have to sort the results.

1 2	SELECT id, title FROM "Post" ORDER BY id ASC

To divide our data into chunks, we need to limit the number of rows in the result. To do that, we should use the LIMIT keyword.

SELECT id, title FROM "Post"

ORDER BY id ASC

LIMIT 10

By doing the above, we limit the result to ten items and get the first page of the results. To get the next chunk of the data, we need to skip a certain number of rows. To change the starting point of our query, we need the OFFSET keyword.

SELECT id, title FROM "Post"

ORDER BY id ASC

OFFSET 10

LIMIT 10

With the above approach, we omit the first ten posts and get rows with ids from 11 to 20.

Using offset and limit with Prisma

To implement pagination in our API, we need the users to be able to send the offset and limit through query parameters. To allow that, let’s create a class that transforms and validates the data.

paginationParams.dto.ts

import { IsNumber, Min, IsOptional } from 'class-validator';

import { Transform } from 'class-transformer';

export class PaginationParamsDto {

@IsOptional()

@Transform(({ value }) => Number(value))

@IsNumber()

@Min(0)

offset?: number;

@IsOptional()

@Transform(({ value }) => Number(value))

@IsNumber()

@Min(1)

limit?: number;

}

We can now use the PaginationParamsDto class with the @Query() decorator to handle the query parameters in our controller.

posts.controller.ts

import { Controller, Get, Query } from '@nestjs/common';

import { PostsService } from './posts.service';

import { AuthorIdQueryDto } from './dto/authorIdQuery.dto';

import { PaginationParamsDto } from './dto/paginationParams.dto';

@Controller('posts')

export default class PostsController {

constructor(private readonly postsService: PostsService) {}

@Get()

getPosts(

@Query() { authorId }: AuthorIdQueryDto,

@Query() { offset, limit }: PaginationParamsDto,

) {

if (authorId !== undefined) {

return this.postsService.getPostsByAuthor(authorId, offset, limit);

}

return this.postsService.getPosts(offset, limit);

}

// ...

}

The last step is to use the take and skip parameters to apply the offset and limit to our query.

posts.service.ts

import { Injectable } from '@nestjs/common';

import { PrismaService } from '../prisma/prisma.service';

@Injectable()

export class PostsService {

constructor(private readonly prismaService: PrismaService) {}

getPosts(offset?: number, limit?: number) {

return this.prismaService.post.findMany({

take: limit,

skip: offset,

});

}

// ...

}

Counting the number of rows

It’s very common to display the number of available pages. For example, a hundred rows with ten elements per page gives us ten pages of data.

To achieve the above with SQL, we need the COUNT keyword.

1	SELECT COUNT(*) FROM "Post"

Unfortunately, Prisma does not support counting the rows and fetching them in the same query. To deal with this problem, we can use a transaction.

posts.service.ts

import { Injectable } from '@nestjs/common';

import { PrismaService } from '../prisma/prisma.service';

@Injectable()

export class PostsService {

constructor(private readonly prismaService: PrismaService) {}

async getPosts(offset?: number, limit?: number) {

const [count, items] = await this.prismaService.$transaction([

this.prismaService.post.count(),

this.prismaService.post.findMany({

take: limit,

skip: offset,

}),

]);

return {

count,

items,

};

}

async getPostsByAuthor(authorId: number, offset?: number, limit?: number) {

const [count, items] = await this.prismaService.$transaction([

this.prismaService.post.count({

where: {

authorId,

}),

this.prismaService.post.findMany({

take: limit,

skip: offset,

where: {

authorId,

}),

]);

return {

count,

items,

};

}

// ...

}

If you want to know more about using transactions with Prisma, check out API with NestJS #104. Writing transactions with Prisma

Thanks to the above approach, we can display the number of available pages in our interface.

Disadvantages

The offset and limit approach is very common. But, unfortunately, it has some severe disadvantages.

The most crucial downside is that the database needs to compute all of the rows skipped with the OFFSET operator:

the database sorts all of the rows in the table as specified with the ORDER BY keyword,
PostgreSQL removes the number of rows specified in the OFFSET.

Unfortunately, the above can take a toll on the performance. Aside from that, we can run into a problem with the consistency of our data:

the first user gets the first page of data,
the second user creates a new entry that ends up on the first page,
the first user fetches the second page of data.

Unfortunately, the above causes the first user to see the last element of the first page again on the second page. Also, the user won’t see the new element added to the first page.

Advantages

The offset and limit approach is very common and easy to implement. Also, it’s very straightforward to change the column we use for sorting. It’s also easy to skip a certain number of elements and go from the first page to the fifth page, for example.

All of the above makes the offset an acceptable solution in some cases if the expected data is not too big and the possible inconsistencies are acceptable.

Keyset pagination

Another approach to pagination involves using the WHERE keyword instead of the OFFSET. Let’s take another look at a simple query first.

SELECT id, title FROM "Post"

ORDER BY id ASC

LIMIT 10

The thing we need to notice in the above results is that the last element has an id of 10. Let’s use this knowledge to request the next ten elements.

SELECT id, title FROM "Post"

WHERE id > 10

ORDER BY id ASC

LIMIT 10

To get the third page of results, we should notice that the last element above has the id of 20. We need to use it to modify our WHERE filter.

SELECT id, title FROM "Post"

WHERE id > 20

ORDER BY id ASC

LIMIT 10

The above approach is referred to as the keyset pagination or the cursor pagination.

Implementing keyset pagination with Prisma

To add the keyset pagination to our NestJS application, we need to accept an additional query parameter. To do that, let’s modify our PaginationParamsDto class.

paginationParams.dto.ts

import { IsNumber, Min, IsOptional } from 'class-validator';

import { Transform } from 'class-transformer';

export class PaginationParamsDto {

@IsOptional()

@Transform(({ value }) => Number(value))

@IsNumber()

@Min(0)

offset?: number;

@IsOptional()

@Transform(({ value }) => Number(value))

@IsNumber()

@Min(1)

limit?: number;

@IsOptional()

@Transform(({ value }) => Number(value))

@IsNumber()

@Min(1)

startingId?: number;

}

We can use the cursor parameter to implement the keyset pagination with Prisma.

posts.service.ts

import { Injectable } from '@nestjs/common';

import { PrismaService } from '../prisma/prisma.service';

import { PaginationParamsDto } from './dto/paginationParams.dto';

@Injectable()

export class PostsService {

constructor(private readonly prismaService: PrismaService) {}

async getPosts({ limit, offset, startingId }: PaginationParamsDto) {

const [count, items] = await this.prismaService.$transaction([

this.prismaService.post.count(),

this.prismaService.post.findMany({

take: limit,

skip: offset,

cursor: {

id: startingId ?? 1,

}),

]);

return {

count,

items,

};

}

// ...

}

When using the cursor parameter built into Prisma, we need to acknowledge that it uses the WHERE id >= instead of WHERE id >. Therefore, if we provide a particular id, we will see it in the results.

Disadvantages

The most crucial drawback of the keyset pagination is that we need to know the id we want to start with. However, we can overcome it by mixing the cursor-based approach with the offset pagination.

Another important thing is that the column we use with the keyset pagination should have an index to have an additional performance boost. Fortunately, the official documentation states that PostgreSQL creates indexes for each primary key out of the box. Thanks to that, the cursor-based approach should be fast when used with ids.

If you want to know more about indexes with Prisma, check out API with NestJS #106. Improving performance through indexes with Prisma

Also, ordering the pagination results by text columns might not be straightforward if we aim for natural sorting. If you want to know more, check out this thread on StackOverflow.

Advantages

The keyset pagination offers a significant performance over the offset approach. It also fixes the data inconsistency issue that can occur with offset-based pagination. Thanks to that, the keyset pagination can be a good solution for many applications.

Summary

In this article, we’ve implemented two different approaches to pagination. We did that both through raw SQL and Prisma. Both approaches have pros and cons and can be helpful in various situations. The keyset approach is more restrictive but offers a serious performance boost. Since it’s difficult to skip a few pages of data at once when using the keyset pagination, we can resolve to the offset pagination when necessary. Thanks to that, we can cover different use cases while still providing a good user experience.

Series Navigation<< API with NestJS #106. Improving performance through indexes with PrismaAPI with NestJS #108. Date and time with Prisma and PostgreSQL >>