Configure server-side pagination for the Table component

Learn how to implement server-side pagination of data with the Table component.

When building apps using large data sets, it's important to limit how much data your queries return. Returning large sets of data at once can affect performance in your apps, especially when joining multiple tables in complex queries. Server-side pagination is a best practice of optimize queries so they only return the results necessary to populate a given view.

Most databases and APIs support a form of server-side pagination. When new data is needed to populate the view, the query is triggered again and the server retrieves the next set of results.

The Table component supports Limit-Offset, Cursor, and GraphQL Relay server-side pagination.

Demo

The following demo app uses server-side pagination with the NASA Image Library API to display results in a table. Each page contains ten results and updates whenever the page or search term changes. You can expand each row to reveal the image caption.

Demo

Enable server-side pagination

Filters do not function if server-side pagination is enabled. Any filtering logic must be added to the resource query.

In the Add-on settings, click + and select Pagination. The Table component uses client-side pagination by default. Click Enable server-side pagination to display the relevant settings.

Whichever pagination method you use, you must specify the Page size—the number of rows you want each page to use—for the table. Once enabled, the table's pagination property contains information for you to use in paginated queries.

The Table component uses zero-based numbering for pagination values (e.g., pageNumber and offset).

Configure Limit Offset pagination

Limit offset based pagination uses a limit to define the number of records to return and an offset to specify the starting point.

Parameter	Value	Description
Limit	pagination.pageSize	The page size of the table.
Offset	pagination.offset	The offset from which to start. The table automatically calculates this based on the page size and currently selected page.

You can optionally configure the Total row count, if available, so that the table displays numbered pagination controls.

Write a query to paginate results

After you enable server-side pagination, write a query that paginates results. Limit Offset-based pagination is commonly used in SQL queries or with APIs that allow basic control over paginated data.

SQL

To retrieve table data with server-side pagination, you first configure a query to reference the table's pagination properties using the limit and offset SQL clauses.

Server-side pagination example

select
  id,
  first_name,
  last_name,
  email
from
  customer
where
  first_name ilike {{ '%' + textInput1.value + '%' }}
order by
  id
limit
  {{ table1.pagination.pageSize }} 
offset 
  {{ table1.pagination.offset }}

Update the table's pagination settings

The Table component automatically triggers the query whenever the page is changed. If you wrote a separate query to get a total count of records, you can reference this in the Total row count setting.

For example, you can get the total number of all records for a database table with a separate query that uses count(). You can then reference this query and set Total row count.

Get total row count

select
  count(*)
from
  customer

If set, users can jump to specific pages of data when using the table.

API

Many APIs include support for limit and offset pagination. For example, the NASA Image and Video Library API supports perPage to limit the number of results and page_size to use as the offset.

Server-side pagination example

https://images-api.nasa.gov/search?q=artemis&page_size={{ table.pagination.pageSize }}&page={{ table.pagination.currentPage + 1 }}

Parameter	Value
page_size	{{ table.pagination.pageSize }}
page	{{ table.pagination.currentPage + 1 }}

Configure the table's pagination settings

The Table component automatically triggers the query whenever the page is changed. The NASA Image and Video Library API also returns the total number of results, collection.metadata.total_hits to use for the total row count.

Configure Cursor-based pagination

Cursor based pagination uses a unique identifier, such as an ID, that represents a specific position in the data set. This cursor changes with each page change and is referenced by the query to move through the data set. How you configure pagination settings is dependent on the data source.

Setting	Description
Next cursor	The identifier to use for the next set of results.
Has next page	Whether there is another page of results. If false, the Next button is disabled.

Write a query to paginate results

After you enable server-side pagination, write a query that paginates results. For example, the Stripe API supports cursor-based pagination with the limit and starting_after parameters. To retrieve a list of customers with server-side pagination, you first configure a query to reference the table's pagination properties.

1. Add a resource query for the Stripe resource.
2. Select the GET /v1/customers operation.
3. Set the limit parameter to {{ table1.pagination.pageSize }}.
4. Set the starting_after parameter to {{ table1.pagination.afterCursor }}.

Update the table's pagination settings

Next, update the table configuration to reference the query's pagination data. Stripe uses an object ID as the cursor so you would reference the last ID in the query's results to use as the cursor.

1. Select the table and open the Pagination settings.
2. Set Pagination type to Cursor-based.
3. Set Next cursor to the ID for the last item of the query results. You can use the preloaded lodash library and _.last to reference the ID for the last item in the array, such as {{ _.last(query.data.data).id }}.
4. Stripe also returns the has_more property which indicates whether there are more pages of results. Set Has next page to {{ query.data.has_more }}.

Configure GraphQL Relay Cursor pagination

Retool supports cursor-based pagination using Relay's GraphQL server specification. Paginated GraphQL queries accept the first, last, after, and before parameters to identify the data to retrieve.

Write a query to paginate results

After you enable server-side pagination, write a query that paginates results. For example, the GitHub GraphQL API supports Relay cursor-based pagination. To retrieve a list of public repositories for the github prganization with server-side pagination, you first configure a query to reference the table's pagination properties.

Get public repositories

query GetOrganizationRepositories($first: Int, $after: String, $before: String) {
  organization(login: "github") {
    repositories(first: $first, after: $after, before: $before, privacy: PUBLIC) {
      edges {
        node {
          name
          description
          url
        }
        cursor
      }
      pageInfo {
        startCursor
        endCursor
        hasNextPage
        hasPreviousPage
      }
    }
  }
}

GraphQL queries use variables for referenced values.

Variable	Value	Description
first	{{ table.pagination.pageSize }}	The page size of the table.
after	{{ table.pagination.afterCursor }}	The cursor from which to start when navigating to the next page.
before	{{ table.pagination.beforeCursor }}	The previous cursor from which to start when navigating to the previous page.

Update the table's pagination settings

Next, update the table configuration to reference the query's pagination data. GitHub returns both next and previous cursors with which to paginate.

1. Select the table and open the Pagination settings.
2. Set Pagination type to GraphQL Relay cursor based.
3. Set Previous cursor the cursor for the first item in the query results. You can use the preloaded lodash library and _.first to reference the cursor for the last item in the array, such as {{ _.first(query.data.organization.repositories.edges).cursor }}.
4. Set Next cursor to the cursor for the last item in the query results. You can use _.last to reference the cursor for the last item in the array, such as {{ _.last(query.data.organization.repositories.edges).cursor }}.
5. GitHub also returns the hasNextPage property which indicates whether there are more pages of results. Set Has next page to {{ query.data.organization.repositories.pageInfo.hasNextPage }}.

Since the query uses a fixed page size and the GitHub GraphQL API returns both next and previous cursors, last is not required.