Server side pagination examples

If you're building apps that use larger data sources, it's important to limit how much data your queries return. The more data to query, the more it can affect performance—especially if you're using complex queries that joins multiple tables. It's good practice to restrict queries so that they only retrieve the minimum amount of data needed, and to be mindful of how often queries run.

One method is to use server side pagination. When configured, a query can use an offset to retrieve only a certain number of records needed at any one time—not all of them. As the offset increases, the query reruns and it continues to fetch only the records needed.

Server side table pagination

In many cases you only want to display one page of data at a time, but still require the ability to access the entire range of data. Retool tables support server side pagination: you can enable and configure it in the Advanced Settings section of your table component.

Enable the 'Server side pagination' toggle and create two queries.Enable the 'Server side pagination' toggle and create two queries.

The query.data is used to fetch the data, while the queryCount.data.count is a different query that retrieves the number of total results.

In this case, here is what the query for fetching the data might look like:

select * from customer where first_name ilike {{ '%' + textinput1.value + '%' }}
order by customer_id
limit {{ table1.pageSize }}
offset {{ table1.paginationOffset }}

The important part of this query is the last two lines - the limit and offset statements use values from the table to fetch the correct range of rows.

The query to count the total results would look like this:

select count(*) from customer where first_name ilike {{ '%' + textinput1.value + '%' }}

The key here is that the filter condition in both queries are identical so that the counted number of rows is accurate. Your table should now automatically fetch the correct page of data whenever a user changes the selected page.

Server side pagination with dynamic column sorting

To get the table's column sorting working with a server side paginated setup, you will need to utilize the sortedColumn and sortedDesc properties of the Table. Here is an example of how you might do that:

select * from customer
order by
-- For each column you want to enable sorting by you must add the following two rows - including the ending comma.
case when {{ table1.sortedColumn == 'email' }} and {{ table1.sortedDesc }} then email end desc,
case when {{ table1.sortedColumn == 'email' }} and {{ !table1.sortedDesc }} then email end asc,
case when {{ table1.sortedColumn == 'first_name' }} and {{ table1.sortedDesc }} then first_name end desc,
case when {{ table1.sortedColumn == 'first_name' }} and {{ !table1.sortedDesc }} then first_name end asc
limit {{ table1.pageSize}}
offset {{ table1.paginationOffset }}

While verbose, this is necessary because SQL queries are converted into parameterized queries safe from SQL injection.

Limit - Offset based pagination

This is most suitable to be used with SQL databases and APIs that support providing a page number / limit to responses.

1. Create a query and display the data in the Table, and then enable server side pagination.

2. Use the .pageSize and .paginationOffset in the query that pulls the data into your table

See below for an example.

select * from customer
order by customer_id
limit {{ table1.pageSize}}
offset {{ table1.paginationOffset}}

3. Provide the number of results to the query separately

You can also set the table up to allow for jumping to arbitrary pages. To do this, you need to provide the Table the total number of results in the entire dataset being paginated through. Usually, you can do this in a separate query like below:

select count(*) from customer

Cursor based pagination

📘

What is cursor based pagination?

For a brief introduction on cursor based pagination, you can read this excellent blog post from Slack.
Here's a quick guide on how you can use cursor based pagination in Retool. In this example, we'll be connecting a Table to Stripe's REST API that implements pagination with cursors

1. Connect a Stripe REST API resource

First, let's create a Stripe API REST API resource so that we can make authenticated API calls to Stripe. Use the screenshot below as a template.

2. Show Stripe customers in a Table

After creating the resource, we can make an API call to Stripe to retrieve a list of our customers and render them in a Table.

🚧

Wait, what's going on?

If you're not sure how this works check out our Quickstart and then come back to this.

3. Setting up the Table's pagination settings

Stripe's API only returns a maximum of 100 records at a time, so if you'd like to be able to see the full list of data you'll need to use Stripe's Pagination API.

Scroll down in the Table's property inspector and in the Pagination section, enable Server-side pagination and choose Cursor based in the Pagination type dropdown.

Then in the Next page cursor field, we need to specify to the Table what the next page's cursor should be. In this case, this is just the id of the last customer in the Table.

4. Connecting the query to the table's pagination settings

Next, we'll need to modify the API query to make use of the Table's pagination properties. You can use the screenshot below as a template.

📘

What's up with {{ table1.afterCursor ? 'starting_after' : null }}?

Stripe's API doesn't allow you to specify a null argument to the starting_after parameter. To get around this, we use the expression, {{ table1.afterCursor ? 'starting_after' : null }}, to remove the starting_after parameter if the table's afterCursor property is null.
After that, save your query and your table should now paginate correctly.

How this works

When you click the next page button in the Table, the Table updates it's afterCursor property to be the value we specified in the Next page cursor property, which causes the API query to re-run. The new set of data is then appended to the Table's data. Since the Table stores the previously loaded results from the API, it's possible to go back to a previous page in the Table without re-running the API call.

GraphQL cursor based pagination

For more information on this, you can see https://facebook.github.io/relay/graphql/connections.htm

1. Create a GraphQL query with parameters that support pagination.

See below for an example how to do this with the Github GraphQL API. We've filled in the arguments with placeholder values so that the query can run without errors.

2. Display the data in a Table

3. Turn on server side pagination in the Table and choose "GraphQL Cursor based" in the pagination type

After choosing it, use a {{ }} expression to describe the cursor that would be used to pull the previous page, and another expression to describe the cursor that would be used to show the next page of data.

4. Replace the placeholder variables in the query with table properties

Note how we alternate between using first and last depending on whether or not the beforeCursor or afterCursor property is defined on the table.

📘

Note

Depending on the implementation of your GraphQL API, you may also want to leverage the table.pageSize property in order to fetch only the number of records that will fit within the height of your table component.

5. Testing it out and understanding how it works

There's two scenarios:

A) The user tries to move to the next page on the table

When this happens the table sets the afterCursor property to be whatever the current "Next Page Cursor" value is currently. At the same time, it sets the 'beforeCursor" property to null. Doing this solves for the ambiguity when paginating forwards vs paginating backwards.

B) The user tries to move to the previous page on the table

Similarly to the previous example, the table sets the beforeCursor property to the current Previous Page Cursor and then sets the afterCursor property to null.