cursorPagination.ts

import { QueryBuilder } from 'knex';

import zip from './zip';

export interface Page {
    items: any[];
    hasNext: boolean;
    hasPrevious: boolean;
}

interface NormalizedOrder {
    column: string;
    order: 'ASC' | 'DESC';
}

export type Order =
    | string
    | {
          column: string;
          order?: string;
      };

// export interface CursorPaginatorArgs {
//     queryBuilder: QueryBuilder;
//     ordering: Array<string | Order>;
//     delimiter?: string;
// }

export interface CursorArgs {
    // Forward paging arguments
    first?: number;
    after?: string;

    // Backward paging arguments
    last?: number;
    before?: string;
}

/*
    Given an orderBy array such as
    `[{column: 'created', order: 'DESC'}, {column: 'uuid', order: 'ASC'}]`
    reverse the ordering and return a new array like
    `[{column: 'created', order: 'ASC'}, {column: 'uuid', order: 'DESC'}]`
*/
function reverseOrdering(ordering: NormalizedOrder[]): NormalizedOrder[] {
    return ordering.map(o => {
        return {
            column: o.column,
            order: o.order === 'ASC' ? 'DESC' : 'ASC',
        };
    });
}

function normalizeOrdering(ordering: Order[]): NormalizedOrder[] {
    return ordering.map(o => {
        if (typeof o === 'string') {
            return {
                column: o,
                order: 'ASC',
            };
        }
        return {
            column: o.column,
            order: o.order && o.order.toUpperCase() === 'DESC' ? 'DESC' : 'ASC',
        };
    });
}

export class CursorPaginator {
    private delimiter: string;
    private queryBuilder: QueryBuilder;
    private ordering: NormalizedOrder[];

    constructor(queryBuilder: QueryBuilder, ordering: Order[], delimiter = '|') {
        this.ordering = normalizeOrdering(ordering);
        this.queryBuilder = queryBuilder.clearOrder();
        this.delimiter = delimiter;
    }

    public cursor(instance: any): string {
        return this.encodeCursor(this.ordering.map(o => instance[o.column]));
    }

    public async page(args: CursorArgs): Promise<Page> {
        const qb = this.queryBuilder;
        const {first, last, after, before} = args;
        const pageSize = first || last;

        if (!pageSize) {
            return {
                items: await qb,
                hasNext: false,
                hasPrevious: false,
            };
        }
        if (!first && !last) {
            throw new Error('Cursor-based pagination cannot be forwards AND backwards');
        }
        if ((first && before) || (last && after)) {
            throw new Error('Paging must use either `first`/`after` OR `last`/`before`');
        }
        if ((first && first < 0) || (last && last < 0)) {
            throw new Error('Page size must be positive');
        }

        if (first) {
            qb.orderBy(this.ordering);
        } else {
            qb.orderBy(reverseOrdering(this.ordering));
        }

        if (after) {
            qb.modify(this.applyCursor.bind(this), after);
        } else if (before) {
            qb.modify(this.applyCursor.bind(this), before, true);
        }

        // Try to fetch an extra item for the "hasMore" check
        qb.limit(pageSize + 1);

        const rows = await qb;
        const items = rows.slice(0, pageSize);
        const hasMore = rows.length > items.length;
        if (first) {
            return {
                items: items,
                hasNext: hasMore,
                hasPrevious: !!after,
            };
        }
        return {
            items: items.reverse(),
            hasNext: !!before,
            hasPrevious: hasMore,
        };
    }

    private decodeCursor(cursor: string): string[] {
        return Buffer.from(cursor, 'base64')
            .toString()
            .split(this.delimiter);
    }
    private encodeCursor(position: Array<string | number>): string {
        return Buffer.from(position.join(this.delimiter)).toString('base64');
    }

    /*
        Restrict query `qb` to results after the position indicated by `cursor`
        (or before `cursor` when `reverse` is true) by adding to the query's
        WHERE clause.
    */
    private applyCursor(qb: QueryBuilder, cursor: string, reverse = false) {
        /*
         * The reference implementation in Python originally used a tuple (row value)
         * comparison. This had the disadvantage of requiring ordering fields to all
         * have the same order.
         *
         * If we have 3-tuples a and b, the comparison a < b is equivalent to:
         * (a.0 < b.0)
         * || (a.0 == b.0 && a.1 < b.1)
         * || (a.0 == b.0 && a.1 == b.1 && a.2 < b.2)
         *
         * The expression above does not depend on short-circuit evaluation support,
         * which is usually unavailable in relational databases. Furthermore, since
         * each part of the comparison is individually constructed, the direction of
         * ordering fields can be different. This can be reflected in a database query
         * with a corresponding WHERE clause.
         *
         * For example, if
         * 1. `ordering` is equal to `[
         *         {column: 'field1', order: 'ASC'},
         *         {column: 'field2', order: 'DESC'},
         *         {column: 'field3', order: 'ASC'}
         *     ]`
         * 2. the decoded `cursor` is equal to `['value1', 'value2', 'value3']`, and
         * 3. `reverse` is false,
         *
         * then the WHERE clause needs to be:
         *
         * WHERE ((field1 < value1)
         *     OR (field1 = value1 AND field2 > value2)
         *     OR (field1 = value1 AND field2 = value2 AND field3 < value3))
         *
         * (Note the flipped comparison operator on line 2.)
         *
         * When generating an OR clause for the j-th position/ordering pair, `previous`
         * contains `[column, '=', value]` values for the 0th to (j-1)th pairs.
         */

        type Clause = [string, '=' | '<' | '>', string];

        const position = this.decodeCursor(cursor);
        const filter: Clause[][] = [];
        const previous: Clause[] = [];

        type Zipped = [NormalizedOrder, string][];
        for (let [o, value] of zip(this.ordering, position) as Zipped) {
            const isDesc = o.order === 'DESC';
            const operator = reverse === isDesc ? '>' : '<';
            const orClause = [...previous, [o.column, operator, value] as Clause];
            filter.push(orClause);

            previous.push([o.column, '=', value]);
        }

        qb.where(builder => {
            for (const orSet of filter) {
                builder.orWhere(builder2 => {
                    for (const andClause of orSet) {
                        builder2.andWhere(...andClause);
                    }
                });
            }
        });
    }
}

zip.ts

/**
 * Merge two or more arrays in a zipper-like fashion.
 */
export default function zip(...arrays: Array<Array<any>>) {
    return [...arrays[0]].map((_, index) => {
        return arrays.map(array => array[index]);
    });
}

Based on Django cursor pagination, but for JavaScript's Knex. Everything in the original readme applies here.