A book is made up of pages. Each page is a part of the overall information that the book contains. However, we can only read the book by going through the pages one-by-one. The same is true for any application data as well. Pagination helps us divide the data into consumable chunks of information. In this post, we will specifically look at NestJS Prisma Pagination for REST APIs. Along with the theoretical aspects, we will also be creating actual code examples that can help you implement similar features for your own application.

1 – Offset Pagination vs Cursor Pagination

There are two pagination approaches available to us – offset and cursor pagination.

Offset Pagination works by asking the consumers of the API to supply two parameters in their query. These two parameters are usually called offset (or skip) and limit (or take). Basically, offset is the number of records we want to skip before selecting records. Limit is the number of records we want to take after skipping is done.

See below illustration that show the concept of Offset Pagination:

nestjs prisma pagination offset

As you can see, Skip 2 and Take 4 means that we skip the first 2 records and select the next 4 records. With this approach, clients of an API can request for a specific page by calculating the offset. The offset value can be found by multiplying the page size with the page number.

On the other hand, Cursor Pagination works on the concept of a unique sequential identifier. This identifier acts as a pointer to the next record. You can also think of it as a bookmark to your location within the result set. Important point here is that the identifier must have a unique value and should be sequential in nature. Ideal candidates for this are numeric IDs and timestamps.

See below illustration that depicts cursor-pagination approach.

nestjs prisma pagination cursor

As you can see, this approach requires the cursor and take properties. The skip value is set to 1 to skip the record where the cursor is currently located.

Prisma supports both offset and cursor pagination. Depending on our use-case, we can use any of them. In the next section, we will start with creating real examples.

2 – NestJS Prisma Offset Pagination Example

To demonstrate the pagination techniques, we will extend the application from our NestJS Prisma REST API post.

As a first step, we will create a NestJS service to return a list of books using offset pagination.

See below example:

import { Injectable } from "@nestjs/common";
import { Book, Prisma } from "@prisma/client";
import { DBService } from "./db.service";

@Injectable()
export class BookListService {
    constructor(private dbService: DBService) {}

    async getOffsetPaginationList(params: {
        skip?: number;
        take?: number;
    }): Promise<Book[]> {
        const { skip, take } = params;

        if (isNaN(skip)) {
            return this.dbService.book.findMany({
                take
            });
        }else{
            return this.dbService.book.findMany({
                skip,
                take
            });
        }
    }
}

Our method receives two variables as input – skip and take. We pass the two parameters to the findMany() method of the Prisma Client. If we have a 100 records in the book table and we provide skip value as 10 and take value as 20, we get the records 11-30 as output. Basically, the first 10 records are skipped and the next 20 are fetched.

Here, we do a small check to ensure that skip value is a valid number. If not, we don’t skip any record and return all records depending on the take value. We can implement additional checks to make the logic more robust.

Below is the controller request handler that calls the offset pagination method.

@Get('books-offset')
async getBookListWithOffset(@Query('skip') skip: string, @Query('take') take: string): Promise<Book[]> {
    return this.bookListService.getOffsetPaginationList({skip: Number(skip), take: Number(take)});
}

We accept the skip and take as query parameters. While calling the getOffsetPaginationList() method, we convert both to numbers.

If you are new to NestJS, you can read more about concepts such as NestJS Controllers and NestJS Services.

3 – Sorting and Filtering with Offset Pagination

We can also perform sorting and filtering on the result set with Prisma offset pagination. See below example:

async getOffsetPaginationList(params: {
        skip?: number;
        take?: number;
    }): Promise<Book[]> {
        const { skip, take } = params;

        if (isNaN(skip)) {
            return this.dbService.book.findMany({
                take
            });
        }else{
            return this.dbService.book.findMany({
                skip,
                take,
                where: {
                    title: {
                        contains: 'Foundation'
                    }
                },
                orderBy: {
                    publishYear: 'desc'
                }
            });
        }
    }

Here, we specify a filtering condition where title should contain the string ‘Foundation‘. Also, we have a sorting condition using orderBy. This condition will sort the results in descending order of the publishYear.

In a real production application, these conditions will not be hard-coded. They will be dynamic according to the user input. However, for our example, this is sufficient to demonstrate the capability of pagination in NestJS and Prisma.

4 – Pros and Cons of Offset Pagination

Pros

  • The primary advantage of offset pagination is that we can jump to any page immediately. In other words, we can directly jump 50 records and take 5 records to reach page 11 (considering that each page has 5 records).
  • Also, we can paginate the same set of data in any sort order. For example, we can skip any number of pages for a list of books depending on their publishYear. On the other hand, cursor pagination needs a unique sequential id for sorting.

Cons

  • Offset pagination is not scalable. Even when we are specifying an offset (or skip), database engine still needs to traverse all the records. This gets slower as the number of records increase. Imagine the situation when there is a Facebook feed with thousands of possible items.
  • Offset pagination is not suitable for datasets that change frequently. The window of results may be inaccurate from time to time depending on the number of writes. This can lead to missing results or even duplicates.

Due to these shortcomings, offset pagination use-case is ideal for small result sets. Also, it helps if the writes are not very frequent. Ideal example would be a blogging platform where we can filter posts by author and also paginate those results for easy consumption.

5 – NestJS Prisma Cursor Pagination Example

Let’s now see an example of how to support Prisma cursor-based pagination with NestJS.

See below method from the Book List Service.

import { Book, Prisma } from "@prisma/client";

async getCursorPaginationList(params: {
        take?: number;
        cursor?: Prisma.BookWhereUniqueInput;
    }): Promise<Book[]> {
        const { take, cursor } = params;
    
        return this.dbService.book.findMany({
            take,
            skip: 1,
            cursor
        })
    }

As you can see, here we have take and cursor parameters. Since cursor is a unique id, we use the Prisma-generated type.

Important point to note is that we set the skip value to 1. As discussed in the first section of this post, we need to skip the first record because that points to the current position of the cursor. This record would already be part of the previous set of data and usually, we don’t want to duplicate the same while moving to the next set. In case we have a requirement to duplicate the record, we can simply remove the skip parameter.

See below the request handler for cursor pagination approach.

@Get('books-cursor')
async getBookListWithCursor(@Query('take') take: string, @Query('cursor') cursor: string): Promise<Book[]> {
   return this.bookListService.getCursorPaginationList({take: Number(take), cursor: {id: Number(cursor)}});
}

Basically, the cursor parameter is an object that contains the id. In case you are wondering what this id is, below is the Book model for our application.

model Book {
  id  Int @default(autoincrement()) @id
  title String
  author String
  publishYear Int
}

6 – Filtering and Sorting with Cursor Pagination

In Prisma, filtering and sorting with cursor pagination is quite similar to offset pagination.

See below example:

import { Book, Prisma } from "@prisma/client";

async getCursorPaginationList(params: {
        take?: number;
        cursor?: Prisma.BookWhereUniqueInput;
    }): Promise<Book[]> {
        const { take, cursor } = params;
    
        return this.dbService.book.findMany({
            take,
            cursor,
            where: {
                title: {
                  contains: 'Foundation',
                },
              },
              orderBy: {
                id: 'asc',
              },
        })
    }

The main difference is that we sort using our cursor. In this case, the cursor is nothing but our unique sequential id.

7 – Pros and Cons of Cursor Pagination

Pros

  • Cursor pagination is scalable. This is because the underlying query does not use the concept of offset. Instead, it simply queries all records greater than the id value of the cursor.
  • Cursor-based pagination is considered as an industry-standard for complex applications.

Cons

  • Sorting is done on the cursor. It uses the unique sequential id.
  • We cannot skip to specific pages in a cursor pagination approach. This is because we do not know the cursor id that represents the start of a particular page.

Ideal use-cases for cursor pagination approach is for infinite scrolling webpages such as social media feeds. Also, it is ideal for batch processing large number of records.

Conclusion

With this, we have successfully learnt how to perform NestJS Prisma Pagination using both Offset Pagination and Cursor Pagination.

You can find the code for this post on Github.

The important aspects to keep in mind are the key differences between offset and cursor pagination techniques. Both techniques have their advantages and disadvantages. Knowing them can help us make better decisions when designing our own application.

If you have any comments or queries about this post, please feel free to mention them in the comments section below.


0 Comments

Leave a Reply

Your email address will not be published.