oscaroox/objection-cursor

Cursor based pagination plugin for Objection.js

objection-cursor

An Objection.js plugin for cursor based pagination.

Using offsets for pagination is a widely popular technique. Clients tell the number of results they want per page, and the page number they want to return results from. While easy to implement and use, offsets come with a drawback: when items are written to the database at a high frequency, offset based pagination becomes unreliable. For example, if we fetch a page with 10 rows, and then 10 rows are added, fetching the second page might contain the same rows as the first page.

Cursor based pagination works by returning a pointer to a row in the database. Fetching the next/previous page will then return items after/before the given pointer. While reliable, this technique comes with a few drawbacks itself:

• The cursor must be based on a unique column (or columns)
• The concept of pages is lost, and thus you cannot jump to a specific one

Cursor pagination is used by companies such as Twitter, Facebook and Slack, and goes well with infinite scroll elements in general.

Installation

$ npm install objection-cursor

Usage

Mixin

const Model = require('objection').Model;
const cursorMixin = require('objection-cursor');

// Set options
const cursor = cursorMixin({limit: 10});

class Movie extends cursor(Model) {
  ...
}

// Options are not required
class Car extends cursorMixin(Model) {
  ...
}

Quick Start

const query = Movie.query()
  // Strict ordering is required
  .orderBy('title')
  .orderBy('author')
  .limit(10);

  query.clone().cursorPage()
    .then(result => {
      // Rows 1-10
      console.log(result.results);
      return query.clone().cursorPage(result.pageInfo.next);
    })
    .then(result => {
      // Rows 11-20
      console.log(result.results);
      return query.clone().previousCursorPage(result.pageInfo.previous);
    })
    .then(result => {
      // Rows 1-10
      console.log(result.results);
    });

Passing a reference builder to orderBy is supported. Raw queries, however, are not.

const query = Movie.query()
  .joinEager('director')
  .orderBy(ref('director.name'))
  // Order by a JSON field of an eagerly joined relation
  .orderBy(ref('director.born:time').castText())
  .orderBy('id')
  ...

Cursors ordered by nullable columns won't work out-of-the-box. For this reason the mixin also introduces an orderByCoalesce method, which you can use to treat nulls as some other value for the sake of comparisons. Same as orderBy, orderByCoalesce supports reference builders, but not raw queries.

const query = Movie.query()
  .orderByCoalesce('alt_title', 'asc', '') // Coalesce null values into empty string
  .orderByCoalesce('alt_title', 'asc') // Same as above
  .orderByCoalesce('alt_title', 'asc', [null, 'hello']) // First non-null value will be used
  .orderByCoalesce(ref('details:completed').castText(), 'desc') // Works with refs
   // Reference builders and raw queries can be coalesced to
  .orderByCoalesce('even_more_alt_title', 'asc', [ref('alt_title'), raw('?', '')])
  .orderBy('id')
  ...

API

Plugin

cursor(options | Model)

You can setup the mixin with or without options.

Example (with options):

const Model = require('objection').Model;
const cursorMixin = require('objection-cursor');

const cursor = cursorMixin({
  limit: 10,
  pageInfo: {
    total: true,
    hasNext: true
  }
});

class Movie extends cursor(Model) {
  ...
}

Movie.query()
  .orderBy('id')
  .cursorPage()
  .then(res => {
    console.log(res.results.length) // 10
    console.log(res.pageInfo.total) // Some number
    console.log(res.pageInfo.hasNext) // true

    console.log(res.pageInfo.remaining) // undefined
    console.log(res.pageInfo.hasPrevious) // undefined
  });

Example (without options):

const Model = require('objection').Model;
const cursorMixin = require('objection-cursor');

class Movie extends cursorMixin(Model) {
  ...
}

CursorQueryBuilder

cursorPage([cursor, [before]])

• cursor - A URL-safe string used to determine after/before which element items should be returned.
• before - When true, return items before the one specified in the cursor. Use this to "go back".
  • Default: false.

Response format:

{
  results: // Resulted rows.
  pageInfo: {
    next: // Provide this in the next `cursorPage` call to fetch items after current results.
    previous: // Provide this in the next `previousCursorPage` call to fetch items before current results.

    hasMore: // If `options.pageInfo.hasMore` is true.
    hasNext: // If `options.pageInfo.hasNext` is true.
    hasPrevious: // If `options.pageInfo.hasPrevious` is true.
    remaining: // If `options.pageInfo.remaining` is true. Number of items remaining (after or before `results`).
    remainingBefore: // If `options.pageInfo.remainingBefore` is true. Number of items remaining before `results`.
    remainingAfter: // If `options.pageInfo.remainingAfter` is true. Number of items remaining after `results`.
    total: // If `options.pageInfo.total` is true. Total number of available rows (without limit).
  }
}

nextCursorPage([cursor])

Alias for cursorPage, with before: false.

previousCursorPage([cursor])

Alias for cursorPage, with before: true.

orderByCoalesce(column, [direction, [values]])

Use this if you want to sort by a nullable column.

• column - Column to sort by.
• direction - Sort direction.
  • Default: asc
• values - Values to coalesce to. If column has a null value, treat it as the first non-null value in values. Can be one or many of: string, ReferenceBuilder or RawQuery.
  • Default: ['']

Options

Values shown are defaults.

{
  limit: 50, // Default limit in all queries
  pageInfo: {
    // When true, these values will be added to `pageInfo` in query response
    total: false, // Total amount of rows
    remaining: false, // Remaining amount of rows in *this* direction
    remainingBefore: false, // Remaining amount of rows before current results
    remainingAfter: false, // Remaining amount of rows after current results
    hasMore: false, // Are there more rows in this direction?
    hasNext: false, // Are there rows after current results?
    hasPrevious: false, // Are there rows before current results?
  }
}

Notes

• pageInfo.total requires additional query (A)
• pageInfo.remaining requires additional query (B)
• pageInfo.remainingBefore requires additional queries (A, B)
• pageInfo.remainingAfter requires additional queries (A, B)
• pageInfo.hasMore requires additional query (B)
• pageInfo.hasNext requires additional queries (A, B)
• pageInfo.hasPrevious requires additional queries (A, B)

remaining vs remainingBefore and remainingAfter:

remaining only tells you the remaining results in current direction and is therefore less descriptive as remainingBefore and remainingAfter combined. However, in cases where it's enough to know if there are "more" results, using only the remaining information will use one less query than using any one of remainingBefore, remainingAfter. Similarly hasMore uses one less query than hasPrevious, and hasNext.

However, if total is used, then using remaining no longer gives you the benefit of using one less query.