Understanding Pagination in Sequelize

Understanding Pagination

Think of pagination like a book's chapters or a photo album. Instead of showing all content at once, we break it into manageable pieces. Just as a book doesn't show all its pages simultaneously, we can present data in smaller, digestible chunks.

Real-World Examples

Social media feeds loading more posts as you scroll
E-commerce product listings showing items page by page
Search engine results displaying 10 results per page
News website articles loading in batches

Basic Pagination Implementation

Let's start with a simple e-commerce product listing:


// models/Product.js
const Product = sequelize.define('Product', {
    name: DataTypes.STRING,
    price: DataTypes.DECIMAL,
    description: DataTypes.TEXT
});

// Product listing with pagination
const getProducts = async (page = 1, limit = 10) => {
    const offset = (page - 1) * limit;
    
    const products = await Product.findAndCountAll({
        limit,
        offset,
        order: [['name', 'ASC']]
    });
    
    return {
        products: products.rows,
        totalItems: products.count,
        totalPages: Math.ceil(products.count / limit),
        currentPage: page
    };
};

// Express route implementation
app.get('/api/products', async (req, res) => {
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 10;
    
    try {
        const result = await getProducts(page, limit);
        res.json(result);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

Advanced Pagination with Filtering and Sorting

Let's build a more complex product search system:


const searchProducts = async ({
    page = 1,
    limit = 10,
    search = '',
    category = null,
    minPrice = null,
    maxPrice = null,
    sortBy = 'name',
    sortOrder = 'ASC'
}) => {
    const offset = (page - 1) * limit;
    
    // Build where clause
    const whereClause = {};
    if (search) {
        whereClause.name = {
            [Op.iLike]: `%${search}%`
        };
    }
    if (category) {
        whereClause.categoryId = category;
    }
    if (minPrice || maxPrice) {
        whereClause.price = {};
        if (minPrice) whereClause.price[Op.gte] = minPrice;
        if (maxPrice) whereClause.price[Op.lte] = maxPrice;
    }

    const { rows, count } = await Product.findAndCountAll({
        where: whereClause,
        limit,
        offset,
        order: [[sortBy, sortOrder]],
        include: [{
            model: Category,
            attributes: ['name']
        }]
    });

    return {
        products: rows,
        totalItems: count,
        totalPages: Math.ceil(count / limit),
        currentPage: page
    };
};

Building a Social Media Feed

Let's create a complete social media feed system with pagination:


// models/Post.js
const Post = sequelize.define('Post', {
    content: DataTypes.TEXT,
    likes: DataTypes.INTEGER
});

// models/Comment.js
const Comment = sequelize.define('Comment', {
    content: DataTypes.TEXT
});

// Set up associations
Post.hasMany(Comment);
Comment.belongsTo(Post);

// Feed service
const FeedService = {
    async getFeed(userId, page = 1, limit = 10) {
        const offset = (page - 1) * limit;
        
        const { rows, count } = await Post.findAndCountAll({
            include: [
                {
                    model: User,
                    attributes: ['username', 'avatar']
                },
                {
                    model: Comment,
                    limit: 2,  // Show only 2 recent comments per post
                    order: [['createdAt', 'DESC']],
                    include: [{
                        model: User,
                        attributes: ['username']
                    }]
                }
            ],
            order: [['createdAt', 'DESC']],
            limit,
            offset,
            distinct: true  // Important for correct count with associations
        });

        // Format response
        const posts = rows.map(post => ({
            id: post.id,
            content: post.content,
            likes: post.likes,
            author: post.User.username,
            authorAvatar: post.User.avatar,
            commentCount: post.Comments.length,
            recentComments: post.Comments.map(comment => ({
                content: comment.content,
                author: comment.User.username
            }))
        }));

        return {
            posts,
            totalPosts: count,
            totalPages: Math.ceil(count / limit),
            currentPage: page,
            hasMore: page * limit < count
        };
    },

    // Load more comments for a specific post
    async getPostComments(postId, page = 1, limit = 10) {
        const offset = (page - 1) * limit;

        const { rows, count } = await Comment.findAndCountAll({
            where: { postId },
            include: [{
                model: User,
                attributes: ['username', 'avatar']
            }],
            order: [['createdAt', 'DESC']],
            limit,
            offset
        });

        return {
            comments: rows,
            totalComments: count,
            totalPages: Math.ceil(count / limit),
            currentPage: page
        };
    }
};

Best Practices and Tips

Always validate and sanitize page and limit parameters
Use reasonable default and maximum values for limit
Include total count and pagination metadata in responses
Consider using cursor-based pagination for real-time data
Implement proper error handling for invalid page requests

Common Pitfalls to Avoid

Not handling the last page properly
Forgetting to count total items
Ignoring performance with large offsets
Not considering associations in count calculations

Advanced Pagination Techniques

Cursor-based pagination
Infinite scroll implementation
Virtual scrolling for large datasets
Caching strategies for paginated data