relay-style cursor-based pagination capable of filtering/sorting for SQL

cursor-helper.js

const { Base64 } = require('js-base64')
const { Op } = require('sequelize');

// https://github.com/graphql/graphql-relay-js/issues/94#issuecomment-232410564

const effectiveOrder = ({ last }, orderBy) => {
    /* adds `id ASC` to end of `ORDER BY` if `id` is not already in the `ORDER BY` clause
    flips `ASC` to `DESC` (and vice-versa) if pagination arg `last` is defined
    */
    const order = orderBy
        .map(([column, _]) => column)
        .includes('id')
        ? orderBy
        : [...orderBy, ['id', 'ASC']]

    if (last) {
        return { order: flipOrder(order), flip: true }
    }
    return { order, flip: false}
}

/*
cursors are a list of values corresponding to that nodes data values
the list is ordered to correspond with the SQL sort order

e.g.

record = {id: 7, a: 1, b: 2, c: 'three'}
order = [['c', 'ASC'], ['b', 'DESC'], ['id', 'ASC']]

=>

decodedCursor = ['three', 2, 7]
cursor = encodeCursor(decodedCursor)

useful because we can decode the cursor to get conditions for our WHERE clause! (ie. in `orderByCursor` below)
*/

const encodeCursor = cursor => {
    return Base64.encode(JSON.stringify(cursor))
}

const decodeCursor = cursor => {
    return JSON.parse(Base64.decode(cursor))
}

const comparisonOperator = sortDirection => sortDirection == 'ASC' ? Op.gt : Op.lt

const orderByCursor = (order, cursor) => {
    /*
    e.g.

    decodedCursor = ['three', 2, 7]
    order = [['c', 'ASC'], ['b', 'DESC'], ['id', 'ASC']]

    =>

    WHERE c > 'three' OR (c = 'three' AND b < 2) OR (c = 'three' AND b = 2 AND id > 7)
    */
    const decodedCursor = decodeCursor(cursor)

    const validOrderings = order.map(([column, sortDirection], i) => {
        const allOthersEqual = order
            .slice(0, i)
            .map((other, j) => (
                { [other[0]]: decodedCursor[j] }
            ))

        return Object.assign(
            {
                [column]: { [comparisonOperator(sortDirection)]: decodedCursor[i] },
            },
            ...allOthersEqual
        )
    })

    return {[Op.or]: validOrderings}
}

const flipSortDirection = sortDirection => sortDirection == 'ASC' ? 'DESC' : 'ASC'
const flipOrder = order => order.map(([column, sortDirection]) => (
    [column, flipSortDirection(sortDirection)]
))

const valuesForColumns = (columns, row) => {
    if(row) {
        return columns.map(column => row.dataValues[column])
    } else {
        return [];
    }
}

const pageInfo = ({ order, results, hasMoreResults, flip, cursor }) => {

    const columns = order.map(([column, _]) => column)
    const startCursor = encodeCursor(valuesForColumns(columns, results[0]))
    const endCursor = encodeCursor(valuesForColumns(columns, results.slice(-1)[0]))

    return {
        hasPreviousPage: flip ? hasMoreResults : !!cursor,
        hasNextPage: flip ? !!cursor : hasMoreResults,
        startCursor,
        endCursor,
    }
}

const edge = ({result, order}) => {
    const columns = order.map(([column, _]) => column)
    const cursor = encodeCursor(valuesForColumns(columns, result))
    return {
        cursor,
        node: result.dataValues,
    }
}

const paginate = (model, { first, last, after, before, filters=[], orderBy=[]}) => {
    const { order, flip } = effectiveOrder({ first, last, after, before }, orderBy)

    const limit = (flip ? last : first) || 10

    const cursor = flip ? before : after
    const where = cursor ? orderByCursor(order, cursor) : {}

    return model
        .findAll({
            where,
            order,
            limit: limit + 1, // +1 to peek to see if there is more data
        })
        .then(queryResults => {
            const hasMoreResults = queryResults.length == limit + 1;
            let results = hasMoreResults ? queryResults.slice(0, -1) : queryResults
            results = flip ? results.reverse() : results
            return {
                pageInfo: pageInfo({results, hasMoreResults, order, flip, cursor}),
                edges: results.map(result => edge({result, order})),
            }
        })
}

module.exports = {
    paginate,
}

posts.js

const { toGlobalId, fromGlobalId } = require('graphql-base64');
const { paginate } = require("../../db/lib/cursor-helper");
const { Post } = require("../../db/models");

// generated from 
/*const {
    connectionArgs,
    connectionDefinitions,
} = require('graphql-relay')*/

const typeDefs = /* GraphQL */`
type Query {
    posts(
        """Returns the items in the list that come after the specified cursor."""
        after: String

        """Returns the first n items from the list."""
        first: Int

        """Returns the items in the list that come before the specified cursor."""
        before: String

        """Returns the last n items from the list."""
        last: Int

        """Order By"""
        orderBy: String

    ): PostConnection

    node(id:String!) : Node
}

"""A connection to a list of items."""
type PostConnection {
    """Information to aid in pagination."""
    pageInfo: PageInfo!

    """A list of edges."""
    edges: [PostEdge]
}

"""Information about pagination in a connection."""
type PageInfo {
    """When paginating forwards, are there more items?"""
    hasNextPage: Boolean!

    """When paginating backwards, are there more items?"""
    hasPreviousPage: Boolean!

    """When paginating backwards, the cursor to continue."""
    startCursor: String

    """When paginating forwards, the cursor to continue."""
    endCursor: String
}

"""An edge in a connection."""
type PostEdge {
    """The item at the end of the edge"""
    node: Post

    """A cursor for use in pagination"""
    cursor: String!
}

type Post implements Node {
    id: ID!
    authorId: Int
    entityId: Int
    body: String
    EntityTypeId: Int
    createdAt: String!
    updatedAt: String
}

interface Node {
    id: ID!
}

`;

const resolvers = {
    Post: {
      id({id}) {
          return toGlobalId('Post', id);
      }
    },
    Node: {
        __resolveType() {
            return 'Post';
        }
    },
    Query: {
        posts(_, args) {
            const orderBy = args?.orderBy || 'id';
            return paginate(Post, {...args, orderBy: [[orderBy, 'ASC']]})
        },
        async node(_, args) {
            const {id} = fromGlobalId(args.id);
            const node = await Post.findByPk(id);

            return node.dataValues;
        }
    }
};

module.exports = {
  typeDefs,
  resolvers,
}

Fixed issues with original gist (not working next/previous, broken additional sort). Added example GQL schema that isn't generated from graphql-relay types. Thanks to @pcattori for the original gist