Sequelize Pagination with LIMIT and OFFSET: Stable Pages and Predictable Boundaries

The problem with naive paging

Pagination looks trivial until a user requests page 500 and the query stalls, or until two consecutive pages return overlapping rows. In Sequelize, the limit and offset options map directly to SQL LIMIT and OFFSET clauses. They are easy to use, but they require a deterministic sort order and a separate total-count strategy to behave predictably.

Desired outcome

You want a page endpoint that returns a fixed number of rows, knows the total record count for the UI, and avoids duplicate or missing records when the underlying data changes between requests. You also want the generated SQL to be inspectable so you can confirm the database is not scanning the entire table on every page load.

Prerequisites and version assumptions

Sequelize has supported limit and offset on findAll and related query methods since version 3.x, and they remain available in current stable releases. The examples below assume Sequelize v6 or later with a CommonJS or ES module Node.js project.

Your database must support LIMIT/OFFSET semantics. PostgreSQL, MySQL, MariaDB, and SQLite do. Microsoft SQL Server uses OFFSET ... FETCH syntax; Sequelize generates the dialect-appropriate SQL, but you should verify the generated statement if you target that platform.

You need an indexed column to sort by. The primary key is the usual choice because it is unique and typically indexed. Sorting by a non-indexed column forces the database to sort the entire table before applying the limit, which defeats pagination.

Focused procedure

1. Order by an indexed column

Always pair limit and offset with an order clause on an indexed column. Without a deterministic order, the database may return rows in any sequence, and offset-based paging becomes unreliable.

// Run inside your application or a script with Sequelize initialized
const page = 3;
const pageSize = 10;

const users = await User.findAll({
  order: [['id', 'ASC']],
  limit: pageSize,
  offset: (page - 1) * pageSize,
});

Here offset is zero-based, meaning counting starts at zero. Page 1 uses offset: 0; page 3 uses offset: 20 when pageSize is 10. The limit value is the maximum number of rows returned, not the total count.

2. Fetch the total count separately

If your UI needs to show "Page 3 of 250," do not derive that from the page result. Use findAndCountAll, which issues a COUNT(*) query alongside the paged SELECT. Alternatively, call User.count() with the same where conditions.

const { rows, count } = await User.findAndCountAll({
  where: { status: 'active' },
  order: [['id', 'ASC']],
  limit: pageSize,
  offset: (page - 1) * pageSize,
});

// rows: the current page of records
// count: total matching rows, independent of limit/offset

This separation matters because LIMIT/OFFSET only constrain the returned rows; they do not tell you how many rows exist in total.

3. Inspect the generated SQL

Enable Sequelize logging to confirm the values are applied correctly. In your Sequelize constructor, set logging: console.log or pass a custom function. Run the query and verify the output contains LIMIT and OFFSET with the expected numbers.

const sequelize = new Sequelize(database, username, password, {
  host,
  dialect: 'postgres',
  logging: (...msg) => console.log(msg),
});

For PostgreSQL you should see LIMIT 10 OFFSET 20. For SQL Server you should see the OFFSET ... FETCH NEXT ... ROWS ONLY form. If the numbers do not match your variables, the issue is usually a misplaced offset calculation rather than a Sequelize bug.

Expected checks

After implementing the query, run these verifications:

• First page: Set offset: 0 and limit: 10. Confirm exactly 10 rows are returned and that their IDs are sequential when sorted ascending.
• Deep page: Set offset: 1000 and limit: 10. Confirm the row count is still 10 and that the IDs continue the sequence without gaps, assuming no rows were deleted.
• Count accuracy: Compare the count from findAndCountAll against a manual SELECT COUNT(*) in your database client. They should match.
• SQL review: Check the logged statement to ensure ORDER BY precedes LIMIT/OFFSET and that no unexpected WHERE clauses were injected by model hooks.

Recovery options and known limitations

High offsets degrade performance

When offset grows large, the database must count and discard all preceding rows before returning your page. On a table with millions of records, offset: 1000000 can be slow even with an index. The standard recovery is key-set pagination (also called seek pagination): instead of skipping rows, remember the last ID from the previous page and query for rows after it.

// Key-set alternative: no OFFSET, constant-time deep pages
const nextPage = await User.findAll({
  where: { id: { [Op.gt]: lastSeenId } },
  order: [['id', 'ASC']],
  limit: pageSize,
});

This approach requires a stable, unique sort key and does not support arbitrary "jump to page N" navigation, but it scales far better for infinite-scroll or "load more" interfaces.

Concurrent modifications and phantom rows

If rows are inserted or deleted while a user pages through results, offset-based paging can produce phantom reads—a row appears on two pages or is skipped entirely. If your application requires strict snapshot consistency, wrap the read in a transaction with an isolation level such as REPEATABLE READ (PostgreSQL, MySQL) or SERIALIZABLE. Note that long-running transactions holding snapshot isolation can increase database load, so use them only when necessary.

Dialect-specific behavior

Sequelize translates limit and offset into dialect-appropriate SQL. However, older database versions or unusual ORDER BY expressions may behave unexpectedly. If you observe inconsistent page boundaries on an older MySQL-compatible server, test the raw SQL directly in your database client to isolate whether the issue originates in Sequelize or the database engine.

A practical way to check the result

The fastest end-to-end check is a small script that requests three consecutive pages and prints the first and last ID of each page. With order: [['id', 'ASC']], limit: 10, and no concurrent writes, the last ID of page 1 should be exactly one less than the first ID of page 2. If the sequence breaks, revisit your order clause and confirm the column is indexed.