Luca Del Puppo for This is Learning
Posted on
#javascript #typescript #database #orm
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));}
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" }]
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));
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" }]
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.
Top comments (8)
Subscribe
Neesh Samsi
Neesh Samsi
Young Beginner, Dabbling into Web Development and UI/UX Design. Looking to Live, Learn & Grow.
-
Email
avaneeshsamsi@gmail.com
-
Location
Mumbai, India
-
Education
Currently in High School
-
Work
See AlsoCRUD (Reference) | Prisma DocumentationFiltering and Sorting (Concepts) | Prisma DocumentationBeginner, Student at Learning
-
Joined
âĸ Jan 15 '22
- Copy link
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.
Luca Del Puppo
Luca Del Puppo
@Microsoft MVP, @Google Developer Expert, @GitKraken Ambassador, Senior Software Developer and JavaScript enthusiasticYoutube Channel https://youtube.com/@Puppo_92
-
Location
Povegliano, TV
-
Work
Senior Software Developer at @NearForm
-
Joined
âĸ Jan 15 '22
- Copy link
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.
Neesh Samsi
Neesh Samsi
Young Beginner, Dabbling into Web Development and UI/UX Design. Looking to Live, Learn & Grow.
-
Email
avaneeshsamsi@gmail.com
-
Location
Mumbai, India
-
Education
Currently in High School
-
Work
Beginner, Student at Learning
-
Joined
âĸ Jan 15 '22
- Copy link
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?
Luca Del Puppo
Luca Del Puppo
@Microsoft MVP, @Google Developer Expert, @GitKraken Ambassador, Senior Software Developer and JavaScript enthusiasticYoutube Channel https://youtube.com/@Puppo_92
-
Location
Povegliano, TV
-
Work
Senior Software Developer at @NearForm
-
Joined
âĸ Jan 15 '22 âĸ Edited on Jan 15 âĸ Edited
- Copy link
If you are using the offset pagination, you have two ways:
- 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
- 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 đ
Neesh Samsi
Neesh Samsi
Young Beginner, Dabbling into Web Development and UI/UX Design. Looking to Live, Learn & Grow.
-
Email
avaneeshsamsi@gmail.com
-
Location
Mumbai, India
-
Education
Currently in High School
-
Work
Beginner, Student at Learning
-
Joined
âĸ Jan 15 '22
- Copy link
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
Luca Del Puppo
Luca Del Puppo
@Microsoft MVP, @Google Developer Expert, @GitKraken Ambassador, Senior Software Developer and JavaScript enthusiasticYoutube Channel https://youtube.com/@Puppo_92
-
Location
Povegliano, TV
-
Work
Senior Software Developer at @NearForm
-
Joined
âĸ Jan 16 '22
- Copy link
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.
Neesh Samsi
Neesh Samsi
Young Beginner, Dabbling into Web Development and UI/UX Design. Looking to Live, Learn & Grow.
-
Email
avaneeshsamsi@gmail.com
-
Location
Mumbai, India
-
Education
Currently in High School
-
Work
Beginner, Student at Learning
-
Joined
âĸ Jan 16 '22
- Copy link
Thanks, that helps a ton, I have no where and just 1 orderBy so Offset is going to be the way I go!
Luca Del Puppo
Luca Del Puppo
@Microsoft MVP, @Google Developer Expert, @GitKraken Ambassador, Senior Software Developer and JavaScript enthusiasticYoutube Channel https://youtube.com/@Puppo_92
-
Location
Povegliano, TV
-
Work
Senior Software Developer at @NearForm
-
Joined
âĸ Jan 15 '22
- Copy link
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.
For further actions, you may consider blocking this person and/or reporting abuse
