GraphQL Pagination

There are two types of pagination.

Offset-based PaginationUse database offset query to request/response per page

Cursor-based PaginationGrab n number of rows after the last row which client requested previously

Offset-Based Pagination

We could just use the first way, Offset-based by using a query like SELECT id FROM products ORDER BY id DESC LIMIT 20 OFFSET 40. However, there are two problems.

Problem 1: Duplicated data load

Let’s say that there are currently 20 rows are loaded from the database. While clients are looking at the first 20 rows, some other clients publish and add new 3 items into the database. In this case, because new items are stack above the previous items in the table when clients load the new 20 rows with the offset, it will load the 3 duplicated data that were previously loaded.

Problem 2: RDBMS performance issue

Please read the Why order by with limit and offset is slow

Cursor-Based Pagination

Thus, the best way of doing pagination is using the cursor-based method. Instead of using offset, we can use as follows in the query.

SELECT id, title
  FROM `products`
  WHERE id > 5
  ORDER BY id DESC
  LIMIT 5

This is an example of how we get the next 5 rows after the last row that was loaded previously. We need to make sure that the field for ordering should be unique. Otherwise, we may skip some data to load.

However, clients may order by different column such as price, name, age, etc. To handle this case in general, we need to come up with a way to create a unique ‘cursor’ for ordering data.

FYI: this is how you could create a unique cursor from a database

SELECT concat(a, b) AS ab FROM foo;

In this way, we append a unique column after the column that is used for ordering. For example, SELECT concat(name, id) AS cursor FROM file ORDER BY name. You can still remain order by name and use unique cursor by concatenating id after the name column.

What is Cursor?

Cursors are pointers to the spot where we’ve left off in the data. More specifically, cursors are variables that can hold any value that the client can pass to the server to help it find the point at which it should start returning data. It doesn’t matter what value you use, and in fact, it should be opaque from the point of view of the client.

This can be done by custom cursor generation. The custom cursor can be generated by the combination of two different column values. You could use plain SQL such as OR, but this may cause performance issues. Integer Timestamp may be another good choice for the cursor.

In GraphQL Schema, we can add a new input variable that contains pagination required inputs.

type File {
  id: ID!
  name: String!
  extension: String!
  size: Int!
  location: String!
}

type PageInfo {
  endCursor: String
  hasNextPage: Boolean!
  startCursor: String
  hasPrevPage: Boolean!
}

type FileConnection {
  edges: [File]
  pageInfo: PageInfo!
  totalCount: Int!
}

And you can use this query to get the first 5 rows, where "00100302" may indicate createdTime in String.

query {
  files(page: {first: 5, after: "00100302"}) {
    edges {
      name
      extension
    }
    pageInfo {
      endcursor
      hasNextPage
    }
    totalCount
  }
}