Pagination the right way with MongoDB + Mongoose + Node JS

Over the years I’ve seen a lot of code, some good, some bad. And one of the most common bits of bad code I see is to do with paginating large amounts of data.

I’ve seen plenty of junior devs write code that queries the database and gets ALL records back in one go. And then they write a method which sorts and filters the data, before selecting the top 10 results to send back to the front end. It works, but it won’t for long.

I’ve just seen this scenario in a Node JS API, which was getting the data from a Mongo DB database (and Mongoose) to send back to a React JS website. So if you are trying to write Mongoose pagination code the right way, this post os for you.

Slow endpoints if pagination is done the wrong way

The problem with this is that whilst it works fine during the development cycle, problems start to appear as the database grows. The code is promoted up to the Test environment (or even worse, the Prod environment) where the database contains thousands, or hundreds of thousands of records.

Suddenly that same code has to get a list of 2000 records back from the database rather than 200, and the website will get slower and slower. The API endpoints will end up taking 3 or 4 seconds to return the data. If this is the scenario you’re facing now, read on to find out how to paginate the data the right way using Node JS, Mongoose and a Mongo DB database.

How to paginate data in Mongoose

How you paginate the MongoDB data in your Node JS application using Mongoose, depends on the query you’re using. I’ll provide an example how to do this using the aggregate() method.

In this application we need to join data in two tables, and filter the data so that we return only data that matches certain criteria.

The front end will pass a couple of values through with each request – limit and skip, which would tell us the page size and the point in the data that we needed to return from.

And lastly we would also need to return the total record count with each response, so that the front end knows how many pages there are in total.

Mongoose pagination using aggregate()

This code example uses the aggregate() method. It uses $lookup to join data from two tables:

const query = Orders.aggregate({
  $lookup: {
      from: 'customer',
      localField: 'customer',
      foreignField: '_id',
      as: 'customers',
    },
  });

//execute the query
const queryResponse = await query.exec();

Now we’ll add a $match property to the aggregation pipeline so that we can return only the data we specifically want, in this case we search only for Orders whose completed status is equal to true :

const query = Orders.aggregate({
  $lookup: {
      from: 'customer',
      localField: 'customer',
      foreignField: '_id',
      as: 'customers',
    },
  },
  {
    $match: {
      $and: { completed: true }, //provide the match criteria here
    },
  });

//execute the query
const queryResponse = await query.exec();

If you run this code it will return the Orders whose Completed status is true, along with the joined data from the Customers table. However, we’re still missing the total record count.

Return query total record count in response

To return the total record count we need to use the $facet element of the pipeline. It’s in here that we set up the pagination values – limit, skip and totalCount.

Limit is the number of records to return in each request, in other words it is the page size.

Skip tells the query which record number to start on when it counts out it’s next 6 records.

const query = Orders.aggregate({
  $lookup: {
      from: 'customer',
      localField: 'customer',
      foreignField: '_id',
      as: 'customers',
    },
  },
  {
    $match: {
      $and: { completed: true }, //provide the match criteria here
    },
  },
  {    
  	$facet: {
      orders: [
        {
          $skip: +skip,
        },
        {
          $limit: +limit,
        },
      ],
      count: [
        {
          $group: {
            _id: null,
            count: {
              $sum: 1,
            },
          },
        },
      ],
    }
  }
);

//execute the query
const queryResponse = await query.exec();

MongoServerError: invalid argument ….

You’ll also notice in the example below that there’s a ‘+’ symbol before both the skip and limit values that we provide to the $facet part of the query. If you don’t do this you will likely see this error message, which is a bug in Mongoose:

MongoServerError: invalid argument to $skip stage: Expected a number in: $skip:

If we put all of this together, and move the match criteria, and the whole query structure into a property called pipeline, we can build the query up dynamically in our code.

The finished Mongoose aggregate() pagination code

The final code looks like this:

var matches = {};

//build up the match conditions
if (filter === 'completed') {
	matches = { completed: true };
}

if (filter === 'south') {
	matches = { area: 'south' };
}

//define the pipeline
const pipeline = [
  {
    $lookup: {
      from: 'customer',
      localField: 'customer',
      foreignField: '_id',
      as: 'customers',
    },
  },
  {
    $match: {
      $and: [matches], //provide the match criteria here
    },
  },
  {
    $facet: {
      orders: [
        {
        	$sort: {
        		[order_by]: order == 'desc' ? -1 : 1
        	}
        },
        {
          $skip: +skip,
        },
        {
          $limit: +limit,
        },
      ],
      count: [
        {
          $group: {
            _id: null,
            count: {
              $sum: 1,
            },
          },
        },
      ],
    },
  },
];

//create the query from the pipeline
const query = Orders.aggregate(pipeline);

//execute the query
const queryResponse = await query.exec();

//make sure the response is the right shape for the front end
res.send({
  data: {
    claims: queryResponse[0]?.orders,
    count: queryResponse[0]?.count[0]?.count,
  },
});

To summarise

If you are trying to do pagination with MongoDB data, and specifically using an aggregate pipeline, I’ve provided a full code example to do just that in a Node JS API. I’ve also explained how to use the skip and limit properties for pagination, along with returning a total count of records.