Understanding Sequelize Pagination

Understanding the Problem

Imagine you're building a music database application that needs to display lists of musicians, bands, and instruments. When you have thousands of records, sending all the data at once can be overwhelming for both the server and the client. We need to implement pagination to break the data into manageable chunks.

The core challenges we need to solve are:

1. Accepting and validating page and size parameters from API requests
2. Converting these parameters into Sequelize's limit and offset values
3. Implementing pagination across multiple endpoints consistently

Devising a Plan

Let's break this down into manageable steps:

1. Step 1: Parse and validate query parameters
   • Extract page and size from query parameters
   • Set default values (page=1, size=5)
   • Handle special case where page=0 returns all records
2. Step 2: Convert to Sequelize parameters
   • Calculate offset using formula: offset = size * (page - 1)
   • Set limit equal to size parameter
   • Handle special case where size=0 means no limit
3. Step 3: Apply pagination to queries
   • Add limit and offset to Sequelize queries
   • Maintain proper ordering and associations
   • Return paginated results

Implementing the Solution

Basic Implementation

// GET /musicians endpoint
app.get('/musicians', async (req, res, next) => {
    // Parse query parameters with defaults
    let { page = 1, size = 5 } = req.query;
    
    // Convert to numbers since query params are strings
    page = Number(page);
    size = Number(size);
    
    // Calculate limit and offset
    const pagination = {};
    
    // Special case: page=0 means return all records
    if (page !== 0) {
        pagination.limit = size;
        pagination.offset = size * (page - 1);
    }
    
    // Query with pagination
    const musicians = await Musician.findAll({ 
        order: [['lastName'], ['firstName']], 
        attributes: ['id', 'firstName', 'lastName'],
        include: [{
            model: Band,
            attributes: ['id', 'name']
        }],
        ...pagination  // Spread the pagination options
    });

    res.json(musicians);
});
    

Advanced Implementation with Middleware

// Pagination middleware
const paginationMiddleware = (req, res, next) => {
    let { page = 1, size = 5 } = req.query;
    
    page = Number(page);
    size = Number(size);
    
    const pagination = {};
    
    if (page !== 0) {
        pagination.limit = size;
        pagination.offset = size * (page - 1);
    }
    
    // Attach pagination to request object
    req.pagination = pagination;
    next();
};

// Use middleware in routes
app.get('/musicians', paginationMiddleware, async (req, res, next) => {
    const musicians = await Musician.findAll({ 
        ...req.pagination,
        order: [['lastName'], ['firstName']], 
        attributes: ['id', 'firstName', 'lastName'],
        include: [{
            model: Band,
            attributes: ['id', 'name']
        }]
    });

    res.json(musicians);
});
    

Understanding the Code

Query Parameters

When making requests to your API, clients can include query parameters to control pagination:

• page: Which set of results to return (starts at 1)
• size: How many results per page
• Special case: page=0 returns all results

Offset Calculation

The formula offset = size * (page - 1) works like this:

• Page 1: offset = 5 * (1-1) = 0 (start at beginning)
• Page 2: offset = 5 * (2-1) = 5 (skip first 5 records)
• Page 3: offset = 5 * (3-1) = 10 (skip first 10 records)

Real World Examples

Think of pagination like:

• A book's table of contents dividing content into chapters
• A phone book splitting listings into manageable sections
• Search engine results showing 10 results per page
• Social media feeds loading more posts as you scroll

Common Pagination Patterns

There are several common ways to implement pagination in APIs:

Offset Pagination (What we implemented)

Uses page numbers and page sizes. Good for:

• Stable data that doesn't change often
• User interfaces that show page numbers
• Relatively small datasets

Cursor Pagination

Uses a pointer to the last seen record. Better for:

• Real-time data that changes frequently
• Infinite scroll interfaces
• Large datasets

Best Practices

When implementing pagination, remember to:

• Always validate and sanitize query parameters
• Set reasonable default and maximum page sizes
• Include metadata about total records and available pages
• Consider caching for frequently accessed pages
• Use proper HTTP status codes (e.g., 404 for pages beyond available data)

Performance Considerations

When working with pagination, keep in mind:

• Large offsets can be slow on big tables
• Consider using WHERE clauses with IDs for better performance
• Index columns used in ORDER BY clauses
• Monitor query execution time as offset grows