Relay-style connections/pagination?

[jmcneese]

Sqlmancer looks great, but one missing functionality that would prevent me from adopting it is the lack of relay-compatible connection definition and pagination. Unless I am missing something in the documentation or code, I don't see any way to do this. Is this something that might be directly supported in the future, or made possible via a plugin?

[danielrearden]

Good question. The library does not currently support relay-style pagination and connections, although it might in the future. Currently, Sqlmancer supports simple pagination as well as returning "Page" types that include results, aggregate, hasMore fields. Implementing relay-style pagination is doable, but would take considerable effort and it's not at the top of the backlog at the moment. I appreciate you opening this issue though -- if there's additional demand for this feature, I'll definitely circle back to it sooner rather than later.

[zth]

I'm also interested in this feature and would like to help out if possible. @danielrearden what are your initial thoughts on the difficulties involved in implementing connections in Sqlmancer? What do you see as the big hurdles?

[danielrearden]

I think it would make sense to create a connection model method that would work roughly like the existing paginate method works now.

The biggest challenge is that we currently compose pretty much the entire JSON object necessary to resolve a root field inside the database itself -- with a few small exceptions, we don't modify what's returned by the database and we don't change any resolver logic. Ideally, we wouldn't deviate from that way of doing things just to implement this feature. But that also means constructing some pretty complex JSON structures via just SQL. There's also a number of questions to answer:

• Is it necessary to support both forward and backward pagination when we already include an orderBy field?
• How do the first/last arguments interact with the orderBy argument. Presumably using last would effectively invert the direction specified by orderBy.
• How do we handle generating opaque cursors? Can this be done inside the database? How do we generate not only the cursors for each node, but also the startCursor and endCursor inside PageInfo efficiently?

[danielrearden]

@zth I'd really like to see this feature added, but it will definitely take some planning. I think the first question we need to answer is "what's the least amount of changes we'd need to make to be Relay-compliant". For example, if we can drop backward pagination, that may simplify things a bit.

I also want to be conscientious of the fact that Sqlmancer's API may be undergoing some pretty drastic changes in the near future, so it might be more efficient to implement any major new features like this against the new API. I'll have a better idea of the direction the library will be moving in after this week.

[tsiege]

Seconding this!

[jmcneese]

• Is it necessary to support both forward and backward pagination when we already include an orderBy field?

yes, otherwise the implementation would not be Relay-compliant. also, the sql order doesn't necessarily have anything to do with whether you are pagination forward or backward... in fact the relay connection spec says that ordering should not change when changing pagination direction.

• How do the first/last arguments interact with the orderBy argument. Presumably using last would effectively invert the direction specified by orderBy.

they aren't necessarily tied to each other, depending on the db. with mysql, it's necessary to change the overall orderBy direction for backwards paginating connections (when last arg is given), since that's the only way to get a limited result set since there's not negative offsets with mysql (afaik). then, when constructing the connection.edges array, the rows from the mysql resultset need to be reversed, so as to match the spec.

consider this (not relay-specific, but generic cursor-pagination) diagram:

imagine this is a resultset that is order by createdDate DESC. forward pagination (using first and after args) is moving left to right (newest to oldest) along this stream. backward pagination (using last and before args) is moving right to left (oldest to newest). at no point would it make sense to the client querying this data set if the items ordering somehow changed. if we need to change actual db query ordering to deal with it as mentioned above, the client shouldn't be aware of that.

• How do we handle generating opaque cursors? Can this be done inside the database?

I guess it could be done in the db, but then there'd be boilerplate or schema changes the developer would have to do to get this all to work. it would be better that the library would autocreate any cursors, depending on the primary table being queried, and the primary column being sorted on (so that this cursor information can later be interpreted when using after or before args). I have typically generated cursors like so: base64(${tableName}:${sortKey}:${record[sortKey]}). later, when constructing the query, it's just a matter of saying where ${cursorTableName}.${cursorSortKey} > ${cursorRecordValue} when forward paginating, or where ${cursorTableName}.${cursorSortKey} < ${cursorRecordValue}

How do we generate not only the cursors for each node, but also the startCursor and endCursor inside PageInfo efficiently?

const generateCursor = (tableName, sortKey, record) => Buffer.from(`${tableName}:${sortKey}:${record[sortKey]}`).toString('base64');
const startCursor = generateCursor(tableName, sortKey, records[0]);
const endCursor = generateCursor(tableName, sortKey, records[records.length - 1]);