Add support for cursor pagination strategy

[francisbrito]

No description provided.

[melroy89]

This is a must, when you have large datasets and want to keep things performant. You can't just use limits and offsets. Read: https://raw.org/article/optimized-pagination-using-mysql/

So I implemented this featured myself in my own project (which is using Fastify + TypeScript + ESM..).

I'm using X-Total-Count, X-Per-Page and X-Last-Page HTTP headers for additional information required for pagination buttons to function properly. Then I use the official Link header which contains information about the last_id and per_page results, that is enough information when a user click a next, previous button to know where to continue further and how maybe results to display on each page.

When using cursor pagination you basically create a combination of of WHERE SQL query together , with LIMIT + ORDER query. And using an index to go through your DB records fast and efficiently.

First some preprocessing:

// Sorry if this codes look a bit ugly, I'm now refactoring this and migrating it from Express to Fastify ^^
let isNext = true
let isLast = false
if (typeof req.query['_dir'] !== 'undefined') {
  if (req.query['_dir'] === 'prev' || req.query['_dir'] === 'last') {
    isNext = false
  }
  if (req.query['_dir'] === 'last') {
    isLast = true
  }
}
// Add this data to the Fastify request object
req.isNextPage = isNext
req.isLastPage = isLast

Example of the where query part (user_id is just an example below):

// This is a simplification, I use a where array to build my SQL queries.
// I also use format() to string replace my index (in this example I just hard-coded it to user_id
// I try to keep my example as easy as possible, you can extend it in our own project to your needs obviously. 

let where = 'WHERE '

if (req.isNextPage) {
  where += 'user_id > ?', [req.query['_last_id']])
} else {
  where += 'user_id < ?', [req.query['_last_id']])
}
req.where = where

Example of the ORDER query part:

let order = 'ORDER BY '

if (req.isNextPage) {
  order += 'user_id ASC'
} else {
  order += 'user_id DESC'
}
req.order = order

Finally, the LIMIT query part:

limit = 'LIMIT 

const pageQuery = req.query['_page']
const perPageQuery = req.query['_per_page']
const pageNumber = parseInt(pageQuery, 10)
const limitPerPageNumber = parseInt(perPageQuery, 10)
if (typeof pageQuery !== 'undefined' && typeof pageQuery === 'string') {
  req.pageNumber = pageNumber
}

if (typeof perPageQuery !== 'undefined' && typeof perPageQuery === 'string') {
  // Range limit on the per page query
  if (limitPerPageNumber > 0 && limitPerPageNumber <= SOME_DEFAULT_PAGINATION_LIMIT_PER_PAGE) {
    perPage = limitPerPageNumber ||SOME_DEFAULT_PAGINATION_RESULTS_PER_PAGE
  }
}

const lastPageRemaining = (res.totalCount) ? (res.totalCount % perPage): 0
if (req.isLastPage && lastPageRemaining !== 0 && (lastPageRemaining < perPage)) {
  // Only on the last page, when there is a remaining, limit the amount to the last remaining items.
  // Otherwise, just use the perPage as limit for pagination
  limit += lastPageRemaining
} else {
  // In all normal cases, just use the perPage value
  limit += perPage
}

req.perPage = perPage
req.limit = limit

Then in your actual DB query, you can combine all the data, something like (eg. when using the Fastify instance server object to retrieve mysql object):

req.server.mysql.query(`SELECT * FROM ... ${req.where} ${req.order} ${req.limit}`)

Again read the post above, and see my code example. Good luck!