Sequelize pagination

Question

Using sequelize on my nodejs web app, I want to query posts using pagination (by date). Reading sequelize docs, they offer to use offset and limit.

Since I want to display the posts from new to old, I need to consider the date they were created. For example, if I limit the first query to 10 page, and before executing the second query a new post was created, the next query with offset of 10 will result a duplicate post from the last query.

How should I implement the pagination so it will support new entries?

Answer 1

The easiest way to do this is to use Sequelize's findAndCountAll

Post.findAndCountAll({
    where: {...},
    order: [...],
    limit: 5,
    offset: 0,
}).then(function (result) {
    res.render(...);
});

Here, result has both the result of your query and count as result.rows and result.count. You can then increment the offset and use this for pagination.

Sequelize documentation for findAndCountAll

Answer 2

If you want to have a stable pagination, don't paginate on row offset, since it's volatile, for the reason you mention.

You should aim for paginating on a value that is stable over time and use a where clause for filtering results. The best case would be if you have an auto-incrementing id, but the post date could also be reasonable.

Something like:

Post.findAll({ 
where: { createdDate: { $lt: previousDate }, 
limit: 10
})

You need to keep track of previousDate for this ofc. This approach also has some caveats, and you may need to combine it with client-side de-duplication.

Here is a blog post that probably has all the answers you need: Pagination: You're (Probably) Doing It Wrong

Answer 3

With findAndCountAll here count is useful for pagination, from this total count we can limit as we want and also with async and await

let resAccidents = await ModalName.findAndCountAll({ where: { createdByID: employeeID }, offset: 0, limit: 10 });

this will return a count of total records as per where condition and 1st 10 records of it, then increase the value of offset to fetch further records.

Answer 4

Try this:

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

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


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

In order to avoid boilerplate code