Sequelize Cursor Pagination

Sequelize model decorator which provides cursor-based pagination queries. Some motivation and background.

Install

With npm:

npm install sequelize-cursor-pagination

Or with Yarn:

yarn add sequelize-cursor-pagination

How to use?

Define a Sequelize model and decorate it with the withPagination decorator:

// ...

const withPagination = require('sequelize-cursor-pagination');

const Counter = sequelize.define('counter', {
  id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
  value: Sequelize.INTEGER,
});

const options = {
  methodName: 'paginate',
  primaryKeyField: 'id',
};

withPagination(options)(Counter);

The withPagination function has the following options:

• methodName: The name of the pagination method. The default value is paginate.
• primaryKeyField: The primary key field of the model. With composite primary key provide an array containing the keys, for example ['key1', 'key2']. The default value is 'id'.
• omitPrimaryKeyFromOrder: By default the primary key is automatically included into the order if it is missing. By setting this option to true will override this behavior. The default value is false.

Call the paginate (the default method name) method:

const result = await Counter.paginate({
  where: { value: { [Op.gt]: 2 } },
  limit: 10,
});

The paginate method returns a promise, which resolves an object with the following properties:

• edges: An array containing the results of the query. Each item in the array contains an object with the following properties:
  • node: The model instance
  • cursor: Cursor for the model instance
• totalCount: The total numbers rows matching the query
• pageInfo: An object containing the pagination related data with the following properties:
  • startCursor: The cursor for the first node in the result edges
  • endCursor: The cursor for the last node in the result edges
  • hasNextPage: A boolean that indicates whether there are edges after the endCursor (false indicates that there are no more edges after the endCursor)
  • hasPreviousPage: A boolean that indicates whether there are edges before the startCursor (false indicates that there are no more edges before the startCursor)

The paginate method has the following options:

• after: The cursor that indicates after which edge the next set of edges should be fetched
• before: The cursor that indicates before which edge next set of edges should be fetched
• limit: The maximum number of edges returned

Other options passed to the paginate method will be directly passed to the model's findAll method.

⚠️ NB: The order option format only supports the ['field'] and ['field', 'DESC'] variations (field name and the optional order direction). For example, ordering by an associated model's field won't work.

Examples

The examples use the Counter model defined above.

Fetch the first 20 edges ordered by the id field (the primaryKeyField field) in ascending order:

const result = await Counter.paginate({
  limit: 20,
});

First, fetch the first 10 edges ordered by the value field in a descending order. Second, fetch the first 10 edges after the endCursor. Third, fetch the last 10 edges before startCursor:

const firstResult = await Counter.paginate({
  order: [['value', 'DESC']],
  limit: 10,
});

const secondResult = await Counter.paginate({
  order: [['value', 'DESC']],
  limit: 10,
  after: firstResult.pageInfo.endCursor,
});

const thirdResult = await Counter.paginate({
  order: [['value', 'DESC']],
  limit: 10,
  before: secondResult.pageInfo.startCursor,
});

Ordering Columns With Null Values

The creation of cursor for pagination depends on primary key. But, when primary ordering is required on another column and if it happens to contain null values, the pagination query doesn't work as expected. This happens as lt, gt dont get applied to null values. We have added logic to take care of this scenario.

A sample of the pagination where clause that gets appended to the main query is provided below,

Note: Considered a boolean column isTracked on which primary ordering is expected.

Descending

Order: [null -> true -> false]

If cursor[0] is null, we need to make a union of all results that are greater than the primary key and all the non-null values.

where: {
  [Op.or]: [
    { isTracked: { [Op.ne]: null } },
    {
      isTracked: null,
      pk: { [Op.gt]: 'ed1a5338-7620-4386-9501-85ea08e04a37' }
    }
  ]
}

Ascending

Order: [false -> true -> null]

If cursor[0] is non-null, we need to make a union of all results that are less than the primary key and all the null values + greater than the column value filter (false in the below sample).

where: {
  [Op.or]: [
    {
      isTracked: {
          [Op.or]: [ { [Op.is]: null }, { [Op.gt]: false } ]
        },
      },
      { 
        isTracked: false,
        pk: { [Op.lt]: 'ed1a5338-7620-4386-9501-85ea08e04a37'
      }
    }
  ]
}

Running tests

npm run test