DEV Community

Cover image for It's Prisma Time - Pagination
Luca Del Puppo for This is Learning

Posted on

It's Prisma Time - Pagination

Hi Guys πŸ‘‹
Today we are going to see how to paginate our results.
So don't waste time and let's go!

With Prisma there are two ways to paginate results: Offset pagination or Cursor-based pagination.
What are the differences though?
The first one is used when we need to create a sequence of pages and get their datas according to the page. The second one, instead, is used when we want the items after a specific record, to continue the list after that element.

But let's see an example of both, starting with the first one skip and take

{
  const pageOnePosts = await prisma.post.findMany({
    take: 3,
    orderBy: {
      id: "asc",
    },
  });
  console.log(`Page 1: `, JSON.stringify(pageOnePosts, undefined, 2));

  const pageTwoPosts = await prisma.post.findMany({
    skip: 3,
    take: 3,
    orderBy: {
      id: "asc",
    },
  });
  console.log(`Page 2: `, JSON.stringify(pageTwoPosts, undefined, 2));
}
Enter fullscreen mode Exit fullscreen mode

The result of this example is this

[
  {
    "id": 1,
    "title": "Post title 1",
    "content": "Post content 1",
    "published": false,
    "createAt": "2022-01-03T10:14:51.274Z",
    "updatedAt": "2022-01-03T10:14:51.274Z"
  },
  {
    "id": 2,
    "title": "Post title 8",
    "content": "Post content 8",
    "published": true,
    "createAt": "2022-01-03T10:14:51.274Z",
    "updatedAt": "2022-01-03T10:14:51.274Z"
  },
  {
    "id": 3,
    "title": "Post title 4",
    "content": "Post content 4",
    "published": true,
    "createAt": "2022-01-03T10:14:51.274Z",
    "updatedAt": "2022-01-03T10:14:51.274Z"
  }
]

[
  {
    "id": 4,
    "title": "Post title 10",
    "content": "Post content 10",
    "published": true,
    "createAt": "2022-01-03T10:14:51.274Z",
    "updatedAt": "2022-01-03T10:14:51.274Z"
  },
  {
    "id": 5,
    "title": "Post title 9",
    "content": "Post content 9",
    "published": false,
    "createAt": "2022-01-03T10:14:51.274Z",
    "updatedAt": "2022-01-03T10:14:51.274Z"
  },
  {
    "id": 6,
    "title": "Post title 6",
    "content": "Post content 6",
    "published": true,
    "createAt": "2022-01-03T10:14:51.274Z",
    "updatedAt": "2022-01-03T10:14:51.274Z"
  }
]
Enter fullscreen mode Exit fullscreen mode

As you can see, the first findMany has the orderBy and the take options. The orderBy is used to give a sort to our results and the take option is used to get the first 3 elements of the results. In this case the skip option isn't indicated so its value is 0 (default value). Instead, in the second findMany method there is also the skip option, and it's indicated with the value 3. In this case, the findMany method returns 3 elements (take: 3) after the third element (skip: 3), so we can see the results of the second page.
I think it's not so difficult to understand it, so let's go on to the cursor implementation.
Let's start from the code

const pageOnePosts = await prisma.post.findMany({
  take: 3,
  orderBy: {
    id: "asc",
  },
});
console.log(`Page 1: `, JSON.stringify(pageOnePosts, undefined, 2));

const pageTwoPosts = await prisma.post.findMany({
  skip: 1,
  take: 3,
  cursor: {
    id: pageOnePosts[pageOnePosts.length - 1].id,
  },
  orderBy: {
    id: "asc",
  },
});
console.log(`Page 2: `, JSON.stringify(pageTwoPosts, undefined, 2));
Enter fullscreen mode Exit fullscreen mode

The results

[
  {
    "id": 1,
    "title": "Post title 1",
    "content": "Post content 1",
    "published": false,
    "createAt": "2022-01-03T10:14:51.274Z",
    "updatedAt": "2022-01-03T10:14:51.274Z"
  },
  {
    "id": 2,
    "title": "Post title 8",
    "content": "Post content 8",
    "published": true,
    "createAt": "2022-01-03T10:14:51.274Z",
    "updatedAt": "2022-01-03T10:14:51.274Z"
  },
  {
    "id": 3,
    "title": "Post title 4",
    "content": "Post content 4",
    "published": true,
    "createAt": "2022-01-03T10:14:51.274Z",
    "updatedAt": "2022-01-03T10:14:51.274Z"
  }
]

[
  {
    "id": 4,
    "title": "Post title 10",
    "content": "Post content 10",
    "published": true,
    "createAt": "2022-01-03T10:14:51.274Z",
    "updatedAt": "2022-01-03T10:14:51.274Z"
  },
  {
    "id": 5,
    "title": "Post title 9",
    "content": "Post content 9",
    "published": false,
    "createAt": "2022-01-03T10:14:51.274Z",
    "updatedAt": "2022-01-03T10:14:51.274Z"
  },
  {
    "id": 6,
    "title": "Post title 6",
    "content": "Post content 6",
    "published": true,
    "createAt": "2022-01-03T10:14:51.274Z",
    "updatedAt": "2022-01-03T10:14:51.274Z"
  }
]
Enter fullscreen mode Exit fullscreen mode

As you may notice, the results are the same of the previous example but the implementation is a little bit different in the second part.
In this case, the second findMany has the same orderBy and take options as in the first example, but it has a different skip and uses the cursor option. First let's focus on the cursor option. This option allows us to indicate the last item returns by the previous search, so we can restart our pagination from this element and combining it with the skip: 1, we'll get the results after this element.
It's important to indicate the skip option with the one value, otherwise the element indicates in the cursor is part of the results.

As you can tell, both implementations are correct but each of them resolve a different type of pagination. Now it's your job to take the right one.

Ok guys that's all!
I hope now you have an idea of how you can paginate your datas using prisma.
In the next article, we are going to see how to aggregate and group the datas.

See you soon!
Bye Bye πŸ‘‹

Here you can find the code of this article.

Discussion (8)

Collapse
neeshsamsi profile image
Neesh Samsi

I was wondering how I can set up cursor-based pagination reliably for any page. I kind of understand the implementation of it in your example but I can't wrap my head around making a dynamic function to grab the page I want.

Collapse
puppo profile image
Luca Del Puppo Author

The cursor pagination is perfect in a context where there is an infinite scroll, it’s not used to retrieve a specific page. This is the offset pagination goal.
At this moment I don't have any idea if there are specific cases where we can mix both solutions. Maybe they exist but I think we use one or the other in the common use cases.

Collapse
neeshsamsi profile image
Neesh Samsi

I am trying to have a traditional pagination with page numbers on the bottom. I thought offset would be the way to go but if it's not scalable I don't know what to use. What would be the way to go about it?

Thread Thread
puppo profile image
Luca Del Puppo Author • Edited on

If you are using the offset pagination, you have two ways:

  1. The client know the size and the take value and your API retrieves the data using the size and the take value passed by the client
  2. The client sends only the page, in this case, the take value is known only by the server and you need to calculate the skip value in this way β€œ(pageNumber - 1) * take”. At this moment you have the skip and the take value and you can perform your query. I think this can help you, if not, ask me more without any problem πŸ˜ƒ
Thread Thread
neeshsamsi profile image
Neesh Samsi

The second was what I was thinking of but apparently offset method is not that good for larger databases. I doubt I would hit those issues but incase I do will it just be slow or will it error out

Thread Thread
puppo profile image
Luca Del Puppo Author

Probably if you are using the offset method and you have more filters your where clause can generate problems. In these cases, you need to check the indexes of the tables used in your queries. In the common cases the skip and take handling in the database doesn't generate any problems of the slowness, but the where clause yes.

Thread Thread
neeshsamsi profile image
Neesh Samsi

Thanks, that helps a ton, I have no where and just 1 orderBy so Offset is going to be the way I go!

Collapse
puppo profile image
Luca Del Puppo Author

Otherwise, if you are using the offset pagination and you want to grab page X you need to calculate the skip in this way β€œ(pageNumber - 1) * take”. Thus you have got the skip value to retrieve the data of your page.