As our database grows, maintaining good performance becomes more important. Returning large amounts of data at once through our API can negatively affect efficiency. A common solution is to divide data into smaller chunks, presenting it to the user as infinite scrolling or multiple pages. In this article, we implement this approach using PostgreSQL and the Drizzle ORM. We also compare different pagination methods and their impact on performance.
Offset and limit
Let’s start by looking at a simple select query that returns all entries from a particular table.
1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4
5@Injectable()
6export class ArticlesService {
7 constructor(private readonly drizzleService: DrizzleService) {}
8
9 getAll() {
10 return this.drizzleService.db.select().from(databaseSchema.articles);
11 }
12
13 // ...
14}The important thing about the results above is that the order of the returned records is not guaranteed. However, when implementing pagination, we need the order to be predictable. Therefore, we should sort the results.
1this.drizzleService.db
2 .select()
3 .from(databaseSchema.articles)
4 .orderBy(asc(databaseSchema.articles.id));The initial step in implementing pagination is to restrict the number of rows in the result. We can achieve this using the limit() function.
1this.drizzleService.db
2 .select()
3 .from(databaseSchema.articles)
4 .orderBy(asc(databaseSchema.articles.id))
5 .limit(5);With this approach, we fetch only five elements instead of the entire articles table. This gives us the first page of the results.
To access the second page, we need to skip a specific number of rows. We can do this using the offset() function.
1this.drizzleService.db
2 .select()
3 .from(databaseSchema.articles)
4 .orderBy(asc(databaseSchema.articles.id))
5 .limit(5)
6 .offset(5);By combining the limit() and offset() functions, we skip the first five rows and retrieve the next five rows. In this case, it returns rows with IDs from 6 to 10. Maintaining a consistent order of rows when navigating through different pages of data is essential to avoid skipping some rows or displaying them more than once.
Counting the number of rows
A typical feature is to show the user the total number of data pages. For example, if there are one hundred rows and we display twenty per page, we end up with five pages of data.
To figure this out, we need to know the total number of rows in the table. To do this, we must use the count() function.
1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { count } from 'drizzle-orm';
5
6@Injectable()
7export class ArticlesService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async getAll() {
11 const articlesCountResponses = await this.drizzleService.db
12 .select({ articlesCount: count() })
13 .from(databaseSchema.articles);
14
15 const { articlesCount } = articlesCountResponses.pop();
16
17 // ...
18 }
19
20 // ...
21}Counting the rows in the database within the same transaction as the query that fetches the data is crucial. Thanks to that, we ensure that our results remain consistent.
If you want to know more about transactions with the Drizzle ORM, check out API with NestJS #153. SQL transactions with the Drizzle ORM
1this.drizzleService.db.transaction(async (transaction) => {
2 const articlesCountResponses = await transaction
3 .select({ articlesCount: count() })
4 .from(databaseSchema.articles);
5
6 const { articlesCount } = articlesCountResponses.pop();
7
8 const data = await transaction
9 .select()
10 .from(databaseSchema.articles)
11 .orderBy(asc(databaseSchema.articles.id))
12 .limit(5)
13 .offset(5);
14
15 return {
16 data,
17 count: articlesCount,
18 };
19});Offset pagination with NestJS
When setting up offset pagination in a REST API, users typically supply the offset and limit through query parameters. Let’s create a class to handle them.
1import { IsNumber, Min, IsOptional } from 'class-validator';
2import { Type } from 'class-transformer';
3
4export class PaginationParamsDto {
5 @IsOptional()
6 @Type(() => Number)
7 @IsNumber()
8 @Min(0)
9 offset: number = 0;
10
11 @IsOptional()
12 @Type(() => Number)
13 @IsNumber()
14 @Min(1)
15 limit: number | null = null;
16}We can set the default offset to be because it won’t affect the result of the query.
The class we created can now be used in our controller to validate the user-provided offset and limit parameters.
1import { Controller, Get, Query } from '@nestjs/common';
2import { ArticlesService } from './articles.service';
3import { PaginationParamsDto } from '../utilities/pagination-params.dto';
4
5@Controller('articles')
6export class ArticlesController {
7 constructor(private readonly articlesService: ArticlesService) {}
8
9 @Get()
10 getAll(@Query() paginationParams: PaginationParamsDto) {
11 return this.articlesService.getAll(paginationParams);
12 }
13
14 // ...
15}The last step is to add offset and limit pagination to our service.
1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { asc, count } from 'drizzle-orm';
5import { PaginationParamsDto } from '../utilities/pagination-params.dto';
6
7@Injectable()
8export class ArticlesService {
9 constructor(private readonly drizzleService: DrizzleService) {}
10
11 getAll({ offset, limit }: PaginationParamsDto) {
12 return this.drizzleService.db.transaction(async (transaction) => {
13 const articlesCountResponses = await transaction
14 .select({ articlesCount: count() })
15 .from(databaseSchema.articles);
16
17 const { articlesCount } = articlesCountResponses[0];
18
19 const dataQuery = transaction
20 .select()
21 .from(databaseSchema.articles)
22 .orderBy(asc(databaseSchema.articles.id))
23 .offset(offset);
24
25 if (limit) {
26 const data = await dataQuery.limit(limit);
27 return {
28 data,
29 count: articlesCount,
30 };
31 }
32
33 const data = await dataQuery;
34
35 return {
36 data,
37 count: articlesCount,
38 };
39 });
40 }
41
42 // ...
43}With this approach, we achieve fully functional offset-based pagination.
Advantages
Offset-based pagination is a widely used method because it is simple to implement. It allows users to easily skip multiple data pages and change the columns we sort by. As a result, it is a suitable solution for many situations.
Disadvantages
However, offset-based pagination has significant drawbacks. The primary issue is that the database needs to process all the rows skipped by the offset, which can impact performance:
- the database sorts all rows based on the specified order,
- then, it discards the number of rows defined by the offset.
Additionally, there can be consistency issues:
- user one fetches the first page of articles,
- user two creates a new article that appears on the first page,
- user one then fetches the second page.
In this scenario, user one misses the new article added to the first page and sees the last item from the first page again on the second page.
Keyset pagination
A different way to handle pagination is using the where() function to filter data instead of relying on offset(). To illustrate that, let’s start with the following query:
1this.drizzleService.db
2 .select()
3 .from(databaseSchema.articles)
4 .orderBy(asc(databaseSchema.articles.id))
5 .limit(5);In the results shown, the last row has an ID of 5. We can use this to fetch articles with IDs greater than 5.
1this.drizzleService.db
2 .select()
3 .from(databaseSchema.articles)
4 .orderBy(asc(databaseSchema.articles.id))
5 .limit(5)
6 .where(gt(databaseSchema.articles.id, 5));It’s important to use the same column for both sorting and filtering when using the where() function.
To fetch the next set of results, we need to notice that the ID of the last row is 10 and use this information when calling the where() function.
However, this reveals the biggest drawback of keyset pagination. To retrieve the following data page, we must know the ID of the last item on the previous page. This limitation prevents us from skipping multiple pages at once.
Keyset pagination with NestJS
To set up keyset pagination in NestJS, we need to begin by adding an extra query parameter.
1import { IsNumber, Min, IsOptional } from 'class-validator';
2import { Type } from 'class-transformer';
3
4export class PaginationParamsDto {
5 @IsOptional()
6 @Type(() => Number)
7 @IsNumber()
8 @Min(0)
9 offset: number = 0;
10
11 @IsOptional()
12 @Type(() => Number)
13 @IsNumber()
14 @Min(1)
15 limit: number | null = null;
16
17 @IsOptional()
18 @Type(() => Number)
19 @IsNumber()
20 @Min(0)
21 idsToSkip: number = 0;
22}Now, we need to adjust our service and use the new parameter.
1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { asc, count, gt } from 'drizzle-orm';
5import { PaginationParamsDto } from '../utilities/pagination-params.dto';
6
7@Injectable()
8export class ArticlesService {
9 constructor(private readonly drizzleService: DrizzleService) {}
10
11 getAll({ offset, limit, idsToSkip }: PaginationParamsDto) {
12 return this.drizzleService.db.transaction(async (transaction) => {
13 const articlesCountResponses = await transaction
14 .select({ articlesCount: count() })
15 .from(databaseSchema.articles);
16
17 const { articlesCount } = articlesCountResponses[0];
18
19 const dataQuery = transaction
20 .select()
21 .from(databaseSchema.articles)
22 .orderBy(asc(databaseSchema.articles.id))
23 .offset(offset)
24 .where(gt(databaseSchema.articles.id, idsToSkip));
25
26 if (limit) {
27 const data = await dataQuery.limit(limit);
28 return {
29 data,
30 count: articlesCount,
31 };
32 }
33
34 const data = await dataQuery;
35
36 return {
37 data,
38 count: articlesCount,
39 };
40 });
41 }
42
43 // ...
44}Advantages
Keyset pagination can provide a significant performance boost compared to offset-based pagination, especially with large datasets. It also addresses the data inconsistency issues that can occur with offset pagination. When users add or remove rows, keyset pagination prevents elements from being skipped or duplicated as pages are fetched.
Disadvantages
The biggest drawback of keyset pagination is that users need to know the row ID from which to start. Fortunately, we can address this issue by combining keyset pagination with the offset-based approach.
The column used for filtering should have an index for better performance. Thankfully, PostgreSQL automatically creates an index for every primary key, so keyset pagination works efficiently with IDs.
However, sorting results by text columns can be challenging when natural sorting is required. If you want to know more, check out this question on StackOverflow.
Summary
In this article, we explored two different pagination methods that can be used with the Drizzle ORM and PostgreSQL. By examining their pros and cons, it’s clear that each approach is suitable for different scenarios. Keyset pagination, though more restrictive, offers better performance. Fortunately, mixing keyset and offset pagination allows us to handle various cases, leveraging the benefits of both methods.