Cursor Pagination

findAfterCursor implements keyset-based pagination, which is more efficient than offset-based pagination for large datasets.

Basic usage

// First page
const page1 = await db().users.findAfterCursor(
  {}, // no cursor for the first page
  25, // limit
  ['id'] // orderBy columns
);
// Returns { rows: [...], nextCursor: { id: 'last-id-value' } | null }

// Next page
const page2 = await db().users.findAfterCursor(
  page1.nextCursor, // pass the cursor from the previous result
  25,
  ['id']
);

nextCursor is null on the last page, including a last page that happens to hold exactly limit rows. The query asks the database for one row past the page to tell those two cases apart and discards it, so you always get at most limit rows and can loop on the cursor directly without a trailing empty query.

Multi-column cursors

Paginate by multiple columns for deterministic ordering:

const page = await db().users.findAfterCursor({}, 25, ['last_name', 'id']);
// Cursor: { last_name: 'Smith', id: 'abc-123' }

The cursor object must contain a value for every column in orderBy.

Descending order

const page = await db().users.findAfterCursor({}, 25, ['created_at', 'id'], {
  descending: true,
});
// ORDER BY "created_at" DESC, "id" DESC

descending applies to every column in orderBy. That matters for multi-column cursors: the keyset comparison is a single row-constructor predicate (("created_at", "id") < ($1, $2)), which is only correct if every column sorts the same way.

Filtering

Apply filters alongside cursor pagination:

const page = await db().users.findAfterCursor({}, 25, ['id'], {
  filters: { is_active: true, role: 'admin' },
  columnWhitelist: ['id', 'email', 'first_name'],
});

columnWhitelist must cover orderBy

The next cursor is read off the last row returned, so every ordering column has to survive the projection. A whitelist that omits one throws SchemaDefinitionError rather than handing back a cursor with an undefined value in it.

Filters support nested $and / $or logic:

const page = await db().users.findAfterCursor({}, 25, ['id'], {
  filters: {
    $and: [{ is_active: true }, { role: { $in: ['admin', 'moderator'] } }],
  },
});

Removed in 2.0.0

The lowercase and / or filter keys were removed in 2.0.0 (after warning at runtime in 1.8.0). Use $and / $or. As a side effect of the removal, $or filters are now correctly parenthesized against the cursor predicate.

Soft delete awareness

When soft delete is enabled, deactivated rows are automatically excluded unless you pass includeDeactivated: true:

const page = await db().users.findAfterCursor({}, 25, ['id'], {
  includeDeactivated: true,
});

Iterating all pages

let cursor = {};
let allRows = [];

while (true) {
  const { rows, nextCursor } = await db().users.findAfterCursor(cursor, 100, [
    'id',
  ]);
  allRows.push(...rows);
  if (!nextCursor) break;
  cursor = nextCursor;
}