Implementing Reliable Pagination in Sequelize with limit and offset

The Problem: Managing Large Result Sets

Fetching thousands of records in a single API request leads to memory exhaustion on the server and slow response times for the client. To solve this, you must implement pagination—the process of dividing a large dataset into smaller, manageable chunks (pages).

The most direct way to achieve this in Sequelize is by using the limit and offset options. limit defines the maximum number of rows to return, while offset tells the database how many rows to skip before it starts returning data.

The Recommended Implementation: findAndCountAll

When building a paginated UI, the frontend needs to know not only the current page's data but also the total number of records available to calculate the total number of pages. Using Model.findAll() would require a second, separate query to get the count. Instead, use Model.findAndCountAll(), which returns both the records and the total count in one operation.

Worked Configuration Example

Below is a practical implementation of a pagination service. This assumes you are using Sequelize v6 or v7 with a supported SQL dialect (PostgreSQL, MySQL, or SQLite).

async function getPaginatedUsers(page = 1, pageSize = 10) {
  // Ensure page is at least 1 to avoid negative offsets
  const currentPage = Math.max(1, page);
  
  // Calculate offset: (Page 1 starts at 0, Page 2 starts at pageSize, etc.)
  const offset = (currentPage - 1) * pageSize;

  try {
    const { count, rows } = await User.findAndCountAll({
      where: { status: 'active' },
      limit: pageSize,
      offset: offset,
      // Crucial: Always order results to ensure deterministic pagination
      order: [['createdAt', 'DESC']]
    });

    return {
      totalItems: count,
      totalPages: Math.ceil(count / pageSize),
      currentPage,
      data: rows
    };
  } catch (error) {
    console.error('Pagination error:', error);
    throw error;
  }
}

Execution Details

• Where to run: This logic should reside in your service layer or controller.
• Permissions: The database user must have SELECT permissions on the target table.
• Placeholders: page and pageSize should be validated as integers before being passed to the function.
• Expected Result: If you have 50 records and request page 3 with a size of 10, the offset becomes 20, and the database returns records 21 through 30.

Critical Engineering Constraints

The Determinism Risk

Using limit and offset without an order clause is a common mistake. Relational databases do not guarantee a default order. Without an explicit order (e.g., by id or createdAt), a record might appear on Page 1 and then appear again on Page 2 if the database engine changes its internal retrieval path between requests.

The Performance Ceiling

Offset-based pagination maps directly to the SQL OFFSET clause. As the offset value increases, performance degrades. For example, OFFSET 100000 LIMIT 10 requires the database to scan through 100,000 rows, discard them, and then return the next 10. On very large tables (millions of rows), this causes significant latency.

Common Calculation Errors

Verification and Testing

To verify your implementation is working correctly, perform these three checks:

1. Boundary Test: Request page 1 with a limit of 10. Verify the offset is 0.
2. Overlap Test: Request page 1, then page 2. Ensure no record from page 1 appears on page 2.
3. SQL Log Audit: Enable logging in your Sequelize instance (logging: console.log) and verify that the generated SQL contains the expected LIMIT X OFFSET Y syntax for your specific database dialect.