Pagination with Sequelize explained
When a table grows past a few thousand rows, returning every record in one response stops being practical. The client has to wait for data it may never show, and the server has to hold the whole set in memory. Pagination splits the result into smaller pages that the client can request as needed.
The two numbers
Sequelize exposes two query options that do the job: limit tells the database how many rows to return, and offset tells it how many rows to skip. A page is therefore a function of the page number and the page size.
const page = 3;
const pageSize = 20;
const users = await User.findAll({
limit: pageSize,
offset: (page - 1) * pageSize,
order: [['createdAt', 'DESC']],
});
With a page size of 20, page 1 starts at offset 0, page 2 starts at offset 20, and page 3 starts at offset 40. Always pass an explicit order. Without one the database is free to return rows in any order, so a row can appear on two pages or disappear between requests.
Counting the total
A list of rows is not enough for a pager. The client also needs to know how many records exist. findAndCountAll runs the same query and returns both the rows for the current page and the total count.
const result = await User.findAndCountAll({
limit: pageSize,
offset: (page - 1) * pageSize,
order: [['createdAt', 'DESC']],
});
const totalPages = Math.ceil(result.count / pageSize);
const payload = {
rows: result.rows,
count: result.count,
page,
totalPages,
};
The count value is not affected by limit or offset, so it tells you the size of the full result set. That is exactly what a pager needs to draw its page links.
A small helper
Once the pattern is clear it is worth wrapping in a helper so every endpoint behaves the same way. The helper can read the page from the query string, clamp it to a safe range, and return a consistent shape.
function paginate(query) {
const page = Math.max(1, Number.parseInt(query.page, 10) || 1);
const pageSize = Math.min(100, Number.parseInt(query.pageSize, 10) || 20);
return {
limit: pageSize,
offset: (page - 1) * pageSize,
page,
pageSize,
};
}
const options = paginate(request.query);
const result = await User.findAndCountAll({
...options,
order: [['createdAt', 'DESC']],
});
The clamp on pageSize matters. Without it a client can ask for ten thousand rows in one request and undo the benefit of paging.
Pitfalls to watch
- Deep offsets become slower as the skipped rows grow. For very large tables, keyset pagination based on the last seen id is a better fit.
- Filtering must be applied to both the rows and the count. Pass the same
whereclause tofindAndCountAll. - Stable ordering is required. Add a unique column such as
idas a tie breaker after the main sort column. - Do not count on the database to return rows in insertion order. State the order explicitly.
Summary
Pagination in Sequelize comes down to limit, offset and a reliable count. Keep the page size bounded, keep the ordering stable, and return the total alongside the rows so the client can build its pager without a second request. Those four habits are enough for most read endpoints.
Reference shelf
Official documentation and further reading on the ideas in this note. These links open other sites.