Offset and keyset pagination with the Drizzle ORM
As our database grows, maintaining good performance becomes more important. Returning large amounts of data at once through our API can negatively affect…
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. articles.service.ts import { Injectable } from '@nestjs/common'; import { DrizzleService } from '../database/drizzle.service'; import { databaseSchema } from '../database/database-schema'; @Injectable() export class ArticlesService { constructor(private readonly drizzleService: DrizzleService) {} getAll() { return this.drizzleService.db.select().from(databaseSchema.articles); } // ... } 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.this.drizzleService.db .select() .from(databaseSchema.articles) .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.this.drizzleService.db .select() .from(databaseSchema.articles) .orderBy(asc(databaseSchema.articles.id)) .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.this.drizzleService.db .select() .from(databaseSchema.articles) .orderBy(asc(databaseSchema.articles.id)) .limit(5) .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. articles.service.ts import { Injectable } from '@nestjs/common'; import { DrizzleService } from '../database/drizzle.service'; import { databaseSchema } from '../database/database-schema'; import { count } from 'drizzle-orm'; @Injectable() export class ArticlesService { constructor(private readonly drizzleService: DrizzleService) {} async getAll() { const articlesCountResponses = await this.drizzleService.db .select({ articlesCount: count() }) .from(databaseSchema.articles); const { articlesCount } = articlesCountResponses.pop(); // ... } // ... }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 this.drizzleService.db.transaction(async (transaction) => { const articlesCountResponses = await transaction .select({ articlesCount: count() }) .from(databaseSchema.articles); const { articlesCount } = articlesCountResponses.pop(); const data = await transaction .select() .from(databaseSchema.articles) .orderBy(asc(databaseSchema.articles.id)) .limit(5) .offset(5); return { data, count: articlesCount, }; }); 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. pagination-params.dto.ts import { IsNumber, Min, IsOptional } from 'class-validator'; import { Type } from 'class-transformer'; export class PaginationParamsDto { @IsOptional() @Type(() => Number) @IsNumber() @Min(0) offset: number = 0; @IsOptional() @Type(() => Number) @IsNumber() @Min(1) limit: number | null = null; } 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. articles.controller.ts import { Controller, Get, Query } from '@nestjs/common'; import { ArticlesService } from './articles.service'; import { PaginationParamsDto } from '../utilities/pagination-params.dto'; @Controller('articles') export class ArticlesController { constructor(private readonly articlesService: ArticlesService) {} @Get() getAll(@Query() paginationParams: PaginationParamsDto) { return this.articlesService.getAll(paginationParams); } // ... }The last step is to add offset and limit pagination to our service. articles.service.ts import { Injectable } from '@nestjs/common'; import { DrizzleService } from '../database/drizzle.service'; import { databaseSchema } from '../database/database-schema'; import { asc, count } from 'drizzle-orm'; import { PaginationParamsDto } from '../utilities/pagination-params.dto'; @Injectable() export class ArticlesService { constructor(private readonly drizzleService: DrizzleService) {} getAll({ offset, limit }: PaginationParamsDto) { return this.drizzleService.db.transaction(async (transaction) => { const articlesCountResponses = await transaction .select({ articlesCount: count() }) .from(databaseSchema.articles); const { articlesCount } = articlesCountResponses[0]; const dataQuery = transaction .select() .from(databaseSchema.articles) .orderBy(asc(databaseSchema.articles.id)) .offset(offset); if (limit) { const data = await dataQuery.limit(limit); return { data, count: articlesCount, }; } const data = await dataQuery; return { data, count: articlesCount, }; }); } // ... }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:this.drizzleService.db .select() .from(databaseSchema.articles) .orderBy(asc(databaseSchema.articles.id)) .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.this.drizzleService.db .select() .from(databaseSchema.articles) .orderBy(asc(databaseSchema.articles.id)) .limit(5) .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. pagination-params.dto.ts import { IsNumber, Min, IsOptional } from 'class-validator'; import { Type } from 'class-transformer'; export class PaginationParamsDto { @IsOptional() @Type(() => Number) @IsNumber() @Min(0) offset: number = 0; @IsOptional() @Type(() => Number) @IsNumber() @Min(1) limit: number | null = null; @IsOptional() @Type(() => Number) @IsNumber() @Min(0) idsToSkip: number = 0; }Now, we need to adjust our service and use the new parameter. articles.service.ts import { Injectable } from '@nestjs/common'; import { DrizzleService } from '../database/drizzle.service'; import { databaseSchema } from '../database/database-schema'; import { asc, count, gt } from 'drizzle-orm'; import { PaginationParamsDto } from '../utilities/pagination-params.dto'; @Injectable() export class ArticlesService { constructor(private readonly drizzleService: DrizzleService) {} getAll({ offset, limit, idsToSkip }: PaginationParamsDto) { return this.drizzleService.db.transaction(async (transaction) => { const articlesCountResponses = await transaction .select({ articlesCount: count() }) .from(databaseSchema.articles); const { articlesCount } = articlesCountResponses[0]; const dataQuery = transaction .select()