MongoDB pagination with skip, limit, and a next page trick

One of the things that always bothered me about MongoDB pagination is how to know if there is a next page of results.

Let's say I ask for a page of 20 results and get 20 back from the database. That's a full page of results! I can send that back, and those results can be displayed.

But is that the end of the results? Or is there another page? Is there a result number 21? Or did it end at 20?

Since this is paged data, I'm probably going to have a "Next Page" button somewhere so that the user can request the next page of data.

I don't really want to show my users a "Next Page" button just for them to get an empty page when they request the next 20 results.

I used to do an extra call to the database to get a count of the total number of results to solve this problem. And sometimes, I still need to get a total count. But that can be a wasted call if there's less than one page of results in the first place.

So now I always start with a simple (hack?) method to check if there's anything left to fetch.

Get a page of results with skip and limit

I tend to use .skip() and .limit() to get a page of results from MongoDB. For example, getting a second page of results would look like this:

db.results.find({}).sort({ date: -1 }).skip(20).limit(20);

The order of .skip() and .limit() isn't important but if you are using the aggregation stages $skip and $limit then order matters!

We sort by the date (or sort by something) - so that we have some consistent ordering. We don't want our results in some random order or different orders between pages because that's going to mess our results up and mean we could be skipping results we haven't yet seen.

Now our results are in the right order, we can skip the first 20 results (the first page), and get the next set of 20 (the second page).

Here's how that might look in NodeJS:

function getResults(page) {
 let resultsPerPage = 20; // how many results for each page
 const results = await db.collection("results")
   .find({}) // the query
   .sort({ date: -1 }) // sort results (descending date)
   .skip(page x resultsPerPage) // skip previous pages
   .limit(resultsPerPage) // limit to the current page of results
   .toArray(); // change cursor to array
 return { data: results };
};

How this works is limit says, just give me 20 (or whatever your resultsPerPage number is) results.

When skip is 0, you get the first 20 results. But for the next page, skip will be 20 (1 x resultsPerPage) - so you'll skip the first 20 results, and get the next 20 - results 21 - 40. And so on...

Page 1: Results 1 - 20
Page 2: Results 21 - 40
Page 3: Results 41 - 60
Page 4: Results 61 - 80
...etc

And if we get back 19 results, we know we are at the end of the results, but if we get back 20, we don't really know - is that the end, or are there more results on the next page?

You'd have to call the next page to find out. And that could mean an empty page, which is not a great experience when you're expecting another page of results!

So here's what I do instead...

Get a page of results plus 1 (for hasNextPage)

We're still going to start with a single call to the database, but instead of getting 20 (or whatever our resultsPerPage is), we are going to get resultsPerPage + 1 - which in this example is 21.

We will only return 20 results to the user - we only need 20 results for the page. The extra result is just for us - we will discard it if we get it.

But that extra result tells us if there is another page. If we get 19 results, there's no next page. If we get 20 results, there's no next page. But if we get 21 results - there's a next page!

So we get 21 results to return another bit of information instead - the hasNextPage variable!

function getResults(page) {
 let resultsPerPage = 20;
 const results = await db.collection("results")
   .find({})
   .sort({ date: -1 })
   .skip(page x resultsPerPage)
   .limit(resultsPerPage + 1) // fetch one extra result
   .toArray();
 let hasNextPage = false;
 if (results.length > resultsPerPage) { // if got an extra result
  hasNextPage = true; // has a next page of results
  results.pop(); // remove extra result
 }
 return { data: results, hasNextPage };
};

Each time we get a page of results, we ask for an extra result - and that tells us if there is a next page.

If we get 21 results back, we know there is at least one more result on the next page. If we get 20 or fewer results, we know there is no next page.

Now we can return a hasNextPage variable to the client. But before we do, we remove that extra result from our results since we have one too many in our array.

 let hasNextPage = false;
 if (results.length > resultsPerPage) { // if got an extra result
  hasNextPage = true; // has a next page of results
  results.pop(); // remove extra result
 }

When we return hasNextPage as true, the client can know there's at least one more result to get, so the user can click the next page button to fetch it. And again, the next page will return a hasNextPage variable that is true or false.

Once a page of results returns a false hasNextPage, we can disable that next page button - because there is no next page - we are at the end of the results.

<button onClick={getNextPage} disable={!hasNextPage}>Next</button>

Everything is working now on the back end to implement pagination on the client. You can fetch a page of results, and you know if there's a next page (because the server tells you) and if there's a previous page (because the page number is greater than one).

But sometimes you might also want to display the number of results (count) and know the number of pages too. There are several ways to approach this and I think it mostly depends on your data which the best approach is, so I'm planning on covering this in a separate blog post in the future 🔮.