When building applications with large datasets, one of the most common challenges is implementing efficient pagination. In Prisma, you can use the findMany method along with take and skip to handle pagination easily. However, when paginating data, you often need the total record count to display the number of pages, and Prisma doesn’t provide this automatically in a single query. But don’t worry! In this guide, I’ll show you how to implement pagination in Prisma and fetch the total record count using a shared where clause within a transaction.
Thank me by sharing on Twitter 🙏
By the end of this post, you’ll know how to efficiently paginate data and ensure your queries are consistent and reliable.
Why Is Pagination Important?
Pagination allows you to manage large datasets by breaking them down into manageable pages. Instead of loading thousands of records at once, which would overwhelm both your server and users, you can load data in smaller chunks. But pagination alone is not enough—often, we also need to know the total number of records to calculate how many pages are needed or display a “showing X of Y results” message to users.
However, Prisma doesn’t give us the total record count directly when using take for pagination. To get around this, we need to run a second query, and in most cases, we want both the paginated data and the count to be consistent, which can be done using a transaction.
Step 1: Set Up Pagination with Prisma
First, let’s start by looking at how to use Prisma’s findMany method to paginate results. When paginating, we typically use the take argument to specify how many records we want per page and skip to determine which page we are on. Here’s an example:
HP 67 Black/Tri-color Ink Cartridges (2 Pack) | Works with HP DeskJet 1255, 2700, 4100 Series, HP ENVY 6000, 6400 Series | Eligible for Instant Ink | 3YP29AN
$36.89 (as of December 21, 2024 08:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
Amazon Basics Micro SDXC Memory Card with Full Size Adapter, A2, U3, Read Speed up to 100 MB/s, 128 GB, Black
$10.99 (as of December 21, 2024 08:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
The Nvidia Way: Jensen Huang and the Making of a Tech Giant
$26.16 (as of December 21, 2024 19:39 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)const page = 1;
const pageSize = 10;
const paginatedResults = await prisma.modelName.findMany({
skip: (page - 1) * pageSize,
take: pageSize,
orderBy: {
createdAt: 'desc',
},
});In this example, we fetch 10 records per page. The skip argument allows us to jump over the records of previous pages. If we are on page 1, we skip 0 records ((page - 1) * pageSize = 0). If we’re on page 2, we skip 10 records ((page - 1) * pageSize = 10), and so on.
This is a great start, but it’s incomplete. Users will want to know how many total pages there are, which means we need the total count of records.
Step 2: Get the Total Record Count
To get the total count of records, we can use Prisma’s count method:
const totalCount = await prisma.modelName.count({
where: {
status: 'ACTIVE',
},
});This gives us the total number of active records in our table. However, if you’re applying filters or conditions in the findMany query, you’ll need to ensure that the same conditions are applied to the count query as well. This can lead to duplication of the where clause, which we’ll address next.
Step 3: Use a Shared Where Clause
To avoid duplicating the filtering logic, we can define a shared where clause object. This way, both the findMany and count queries will reference the same filtering conditions, ensuring consistency between the paginated data and the total count.
Here’s how we can define the shared where clause:
const sharedWhereClause = {
status: 'ACTIVE',
createdAt: {
gte: new Date('2023-01-01'),
},
};Now, we can use this shared where clause in both our findMany and count queries:
const paginatedResults = await prisma.modelName.findMany({
where: sharedWhereClause,
skip: (page - 1) * pageSize,
take: pageSize,
orderBy: {
createdAt: 'desc',
},
});
const totalCount = await prisma.modelName.count({
where: sharedWhereClause,
});This approach ensures that the filters are applied consistently in both queries, so the paginated data and total count are accurate.
Step 4: Wrap It in a Transaction for Consistency
While querying paginated data and the total count separately works fine, there’s always a risk that the underlying data could change between the two queries, especially in a highly concurrent environment. To guarantee consistency, we can wrap these two operations in a transaction.
Prisma provides a prisma.$transaction method that allows you to execute multiple queries in a single transaction. Let’s wrap our findMany and count queries in a transaction:
const [paginatedResults, totalCount] = await prisma.$transaction([
prisma.modelName.findMany({
where: sharedWhereClause,
skip: (page - 1) * pageSize,
take: pageSize,
orderBy: {
createdAt: 'desc',
},
}),
prisma.modelName.count({
where: sharedWhereClause,
}),
]);With this approach, both queries will run inside a single transaction, ensuring they are consistent and refer to the same snapshot of the data. This is especially useful when your data is being frequently updated, as it eliminates the risk of discrepancies between the paginated data and the total count.
Step 5: Return Paginated Results with Total Count
Once you have both the paginated results and the total count, you can return them as part of your API response, like so:
return {
paginatedResults,
totalCount,
page,
pageSize,
};This response includes both the paginated data and the total number of records, allowing your frontend to calculate how many pages are available and to display relevant messages to users.
Conclusion
Implementing pagination with Prisma is straightforward using the findMany method with take and skip. However, getting the total record count to calculate the number of pages or display results in a meaningful way requires an additional step. By combining the findMany and count queries with a shared where clause and wrapping them in a transaction, we ensure that both operations are consistent and reliable.
The ability to run both queries inside a transaction eliminates the risk of data discrepancies and ensures a smooth user experience when navigating through large datasets.
With this pattern, you’ll have the tools you need to handle pagination in Prisma efficiently, while ensuring consistent and accurate data across your queries.
