objection-keyset-pagination

Objection-keyset-pagination is a plugin to Objection.js ORM to implement keyset based pagination, also known as cursor pagination.

Keyset pagination requires having strict ordering of records. On the user interface side, keyset pagination goes well with infinite scroll elements. Keyset pagination provides stable results. Next batch of records always continues from the last record of previous batch even if there would be insertion or deletions between queries. Using dummy numbered pages such as Objection.js' own .page() or OFFSET in SQL, then insertion or deletion causes some records omitted or duplicated.

Installation

Install:

npm install objection-keyset-pagination

Register the plugin:

const Model = require('objection').Model;
const keysetPagination = require('objection-keyset-pagination')();
 
class Person extends keysetPagination(Model) {
  static get tableName() {
    return 'Person';
  }
}

Options

limit: The number of rows per request if not set. The limit can be also set per query with .limit(). Default value is 10.

countTotal: Query total number of rows. Default value is false. Second query will be performed to get the total count.

The options can be provided by optional second argument when registering the plugin:

const keysetPagination = require('objection-keyset-pagination')({
  limit: 20});

Usage

Query with .keysetPage() to fetch first batch of rows and the keyset. Then later fetch next batch of rows starting from the keyset. The keyset is a plain old JS object, which can be easily passed around in JSON serialization.

const result1 = await Person
  .query()
  .where('age', '>', 20)
  .limit(5)
  .orderBy('id')
  .keysetPage();

The models are returned in result.results just like with .page().

The keyset index columns are returned in result.keyset. Pass this back in the next query to fetch next batch of rows.

If the query doesn't have orderBy clause, Model's idColumn will be used in ascending order.

If countTotal was set to true, then the return value also has result.total which contains total count of rows.

The next batch of rows are fetched by passing result.keyset as parameter to .keysetPage().

const result2 = await Person
  .query()
  .where('age', '>', 20)
  .limit(5)
  .orderBy('id')
  .keysetPage(result1.keyset);

Fetching backwards can be achieved using .previousKeysetPage(result.keyset). The returned models here are in reverse order.

const result3 = await Person
  .query()
  .where('age', '>', 20)
  .limit(5)
  .orderBy('id')
  .previousKeysetPage(result2.keyset);
 
expect(result3.results).to.eql(result1.reverse());