When dealing with a lot of records it is essential to create a way in which clients can specify how many they wish to obtain exactly. Returning records in chunks as opposed to dumping it all at once saves a lot of bandwidth and processing power.

A technique that splits the records into chunks is called pagination. A client sends two parameters: page and pageSize. Records are split into page number of pages - each page containing pageSize number of records.

Note that I am going to be explaining how to implement this using the Sequelize library specifically.

Paginating records with Sequelize comes down to including specific properties in a query object that we then pass to the model’s .findAll() method. The object has two properties - offset and limit - which indicate respectively how many records to skip and how many to take.

And so to calculate the offset we have to multiply page by pageSize. limit is equal to pageSize. Here’s an example of how this works:

If we wanted to get say page number 7 and on each page have 25 records the calculations would look like this:

offset = page(7) * pageSize(25) = 175
limit = pageSize(25)

So on page 7 there would now be records from 175 to 200.

Now that we understand how this works, let’s move to the implementation.

Implementation

As I mentioned earlier, paginating records with Sequelize comes down to setting correct limit and offset properties inside a query object. Both page and pageSize are sent to our server as the query parameters or inside the body of the request.

const handler = ({ page, pageSize }) => {
  const offset = page * pageSize;
  const limit = pageSize;

  return model.findAll({
    limit,
    offset,
    where: {}, // conditions
  });
};

Everything is working as expected but the code is not DRY (don’t repeat yourself) as we have to repeat it everywhere we want to apply pagination.

Let’s create a reusable helper function that can take care of pagination for us. There are two ways we can create the helper - one only slightly different from the other.

Adding pagination directly into a query object

In this approach we take a query object and an object containing page and pageSize and send them both to the helper function. This way we don’t have to worry that we accidentally put the pagination properties (offset and limit) in say the nested object.

const paginate = (query, { page, pageSize }) => {
  const offset = page * pageSize;
  const limit = pageSize;

  return {
    ...query,
    offset,
    limit,
  };
};

That is how we use it:

model.findAll(
  paginate(
    {
      where: {}, // conditions
    },
    { page, pageSize },
  ),
);

We have to pass the query object through the paginate function which, aside from making the code more difficult to read, ensures every property is precisely where it should be.

Adding pagination by spreading the pagination object

In this approach, instead of passing the entire query object to the paginate function, we spread the pagination properties in the query object. This makes it more clear about what the function is actually doing and gives us more control over the query object as it is not possible to change it from inside the helper function. The downside, however, is that we are more likely to make a mistake and spread the pagination properties in some nested object.

const paginate = ({ page, pageSize }) => {
  const offset = page * pageSize;
  const limit = pageSize;

  return {
    offset,
    limit,
  };
};

Here’s how we go about using it:

model.findAll({
  where: {}, // conditions
  ...paginate({ page, pageSize }),
});

Alternatively, we can write it like this:

model.findAll(
  Object.assign(
    {
      where: {}, // conditions
    },
    paginate({ page, pageSize }),
  ),
);

Summary

Pagination is particularly useful when creating endpoints that - if were to return all the records at once - would likely crash the server. Implementing it with Sequelize is quite simple and allows us to save a lot of bandwidth and processing power.

Also available on Medium.