How to Implement Pagination in Sequelize.js

Introduction

Implementing pagination is a core requirement for applications that handle large datasets or have the potential for large volumes of data retrieval. In web development, limiting the number of records sent to the client not only reduces the amount of data traveling over the network but also improves response times and enhances the user experience. Sequelize.js, a popular ORM for Node.js, provides a straightforward and efficient way to handle pagination.

This tutorial will guide you through the process of adding pagination to your Sequelize-based applications. We will start with the basics and gradually move to more advanced scenarios, with plenty of code examples along the way.

Getting Started

Before you begin, ensure you have Sequelize set up in your project. You’ll need a model to work with, and in this tutorial, we will use ‘User‘ as the sample model.

const User = sequelize.define('user', { /* attributes */ });

Basic Pagination

To implement basic pagination, you can use the ‘limit‘ and ‘offset‘ options in your Sequelize query:

const getPaginatedUsers = async (page, pageSize) => {
    const limit = pageSize; // number of records per page
    const offset = (page - 1) * pageSize;

    return await User.findAndCountAll({
        limit: limit,
        offset: offset,
    });
};

This function takes two parameters: ‘page‘ which represents the current page number, and ‘pageSize‘ which is the number of records to display per page. The ‘findAndCountAll‘ method is used to retrieve records along with the total number of records, which is helpful for building pagination controls on the client side.

Dynamic Pagination

Now let’s enhance our pagination function to include dynamic sorting and searching. Sequelize accommodates this with additional query options:

const getDynamicPaginatedUsers = async (page, pageSize, searchQuery, order) => {
    const limit = pageSize;
    const offset = (page - 1) * pageSize;

    let where = {};
    if (searchQuery) {
        where = {
            [Sequelize.Op.or]: [
                { firstName: { [Sequelize.Op.like]: `%${searchQuery}%` } },
                { lastName: { [Sequelize.Op.like]: `%${searchQuery}%` } }
            ]
        };
    }

    return await User.findAndCountAll({
        where: where,
        limit: limit,
        offset: offset,
        order: order
    });
};

This updated function allows filtering records by a search term and ordering them according to a specific column. Notice how we built the ‘where‘ clause dynamically based on the search query. The ‘order‘ parameter should be an array of arrays indicating the column and direction (e.g., [['createdAt', 'DESC']]) for sorting.

Cursors and Continuous Pagination

Offset pagination is not always efficient, particularly with very large datasets because it can result in slow queries due to high offsets. A better alternative, especially for infinite scrolling scenarios, could be cursor-based pagination:

const getUsersWithCursor = async (cursor, pageSize) => {
    const where = cursor ? { id: { [Sequelize.Op.gt]: cursor } } : undefined;

    return await User.findAll({
        where: where,
        limit: pageSize + 1
    });
};

This function retrieves one more record than the page size to check if there’s another page. The client would send the last record’s ID as the cursor for the next query. Note that this method requires that the result set is ordered by the cursor field (in this case, id). If you reach the end of the data set, you’ll get fewer records than the page size, indicating that there are no more pages.

Advanced Scenarios

In more advanced scenarios, you might need to use complex filters or apply pagination over associations. Sequelize allows you to build intricate queries while still supporting pagination. Here’s an example using associations:

const getPaginatedOrdersWithDetails = async (page, pageSize) => {
    // ... assume `Order` and `OrderDetail` models are defined with associations
    const limit = pageSize;
    const offset = (page - 1) * pageSize;

    return await Order.findAndCountAll({
        include: [{ model: OrderDetail }],
        limit: limit,
        offset: offset
    });
};

And for filtering through associations:

const getFilteredPaginatedUsers = async (page, pageSize, filterOptions) => {
    const limit = pageSize;
    const offset = (page - 1) * pageSize;

    const where = buildWhereClause(filterOptions); // a hypothetical function to construct the 'where' clause based on filters

    return await User.findAndCountAll({
        where: where,
        include: [{
            model: Profile, // assuming a 'Profile' model is associated
            where: filterOptions.profileWhereClause
        }],
        limit: limit,
        offset: offset
    });
};

Conclusion

In this tutorial, we have explored various approaches to implement pagination in Sequelize.js, starting with basic pagination techniques and evolving towards more complex scenarios. We highlighted the importance of considering performance and user experience implications when choosing the right pagination method. Whether you are building a small application or dealing with large-scale data, Sequelize provides the flexibility you need to implement efficient pagination. Remember that pagination isn’t just about handling the volume of data; it’s also about presenting it in a user-friendly way and optimizing data access patterns. Keep exploring and fine-tuning your implementation to best fit your application’s needs.