Sequelize Pagination With findAndCountAll: Count Handling, Association Quirks, and When to Switch to Keyset

The pagination problem you're likely to hit

Imagine an API endpoint that returns a paginated list of products. The client expects two things: the slice of records for the current page, and the total number of records so the frontend can render page controls. Sequelize provides findAndCountAll for exactly this scenario, but the first time you use it with limit and offset, a few behaviors are easy to misinterpret.

How findAndCountAll resolves (and what it hides)

findAndCountAll executes a single query that returns both the rows respecting limit and offset, and a count field representing the total rows matching the query's where conditions, without a LIMIT. This means you get the total item count without running a separate SELECT COUNT(*) query, which is the primary advantage.

Limit and offset in practice

Passing limit: 10 and offset: 20 tells Sequelize to skip the first 20 rows and return at most 10. The generated SQL always includes a LIMIT clause after the OFFSET, and the count field reflects rows before the LIMIT was applied.

The count shape

The response takes the form { rows: Product[], count: number }. If no rows match the offset range, rows is an empty array but count still holds the total matching rows. This lets your code compute total pages even when the current page is empty.

Associations, count queries, and the include trap

When you add include: [Category] to fetch products with their category, Sequelize must also count rows across the joined table. By default, it issues a separate SELECT COUNT(*) query that mirrors the main query's structure, including the JOINs.

Using count: true in include options

You can consolidate the count by passing count: true inside the include definition: include: [{ model: Category, count: true }]. This tells Sequelize to embed the count logic for that association into the primary SELECT COUNT(*) instead of firing a second query. The trade-off: on large joins, the single consolidated query can be more expensive to optimize than two smaller, targeted queries.

When OFFSET stops being cheap and keyset pagination calls

OFFSET-based pagination relies on skipping N rows before returning the slice. If the ORDER BY column isn't indexed, the database must scan and discard rows, which gets slower as the page number grows. On a table with millions of rows, page 100 might take noticeably longer than page 1.

Keyset (or seek) pagination avoids OFFSET entirely by using a where condition on the last seen value: WHERE id > :lastId ORDER BY id LIMIT 10. This pattern is faster at scale but doesn't support random page jumps—clients request the next page sequentially.

For many APIs, a hybrid approach works: use findAndCountAll for small-to-moderate datasets, and switch to keyset pagination for high-throughput endpoints.

Verifying your pagination setup

To confirm your query behaves as expected, run a targeted call and inspect the result shape, then enable SQL logging to see the exact statements sent to the database.

1. Sequelize config: set logging: console.log or pipe to a file.
2. Execute:

   await Product.findAndCountAll({ limit: 5, offset: 20 })

3. Check the console output. You should see a SELECT statement with LIMIT 5 OFFSET 20 and a corresponding SELECT COUNT(*) query.
4. Verify the returned object: rows.length must be less than or equal to 5, and count must be a number (possibly 0 if no rows match the conditions).

If the generated SQL uses dialect-specific syntax (e.g., TOP in SQL Server or LIMIT in PostgreSQL), the logging will reveal it. Always cross-check the output against your database's own query planner if performance feels off.

Pagination is one of those details that feels trivial until it isn't. By understanding how findAndCountAll structures its return value, how include affects the count query, and when to shift to keyset patterns, you can write API endpoints that stay fast as your data grows. Start with findAndCountAll for its simplicity, monitor the generated SQL, and reach for keyset pagination once the dataset outgrows OFFSET penalties.