How to Paginate Data in a Retool Table

Select your Table component. In the Inspector, go to the Interaction section and find 'Pagination type'. Set it to 'Client-side' (or 'Pagination' depending on your Retool version). Configure the page size — common values are 10, 25, or 50 rows per page. Client-side pagination loads all query results into the browser and divides them into pages. No changes to your SQL query are needed. This works well for tables with fewer than a few thousand rows.

For large datasets, switch to server-side pagination. In the Table Inspector, set 'Pagination type' to 'Server-side'. This tells Retool the table is paginated server-side — Retool will no longer try to paginate client-side data. With server-side pagination enabled, you must update your SQL query to use LIMIT and OFFSET based on the current page. The table will show the navigation controls but relies on your query to return the correct page of data.

With server-side pagination enabled, update your SQL query to use table1.page and table1.pageSize. The page property is 1-based (first page = 1), so the OFFSET formula is (page - 1) * pageSize. Enable 'Run when inputs change' in the query's Advanced settings so it re-runs when the user navigates to a different page.

1-- PostgreSQL example with server-side pagination:
2SELECT
3  id,
4  first_name,
5  last_name,
6  email,
7  status,
8  created_at
9FROM users
10WHERE
11  ({{ searchInput.value || '' }} = ''
12   OR first_name ILIKE {{ '%' + (searchInput.value || '') + '%' }}
13   OR last_name  ILIKE {{ '%' + (searchInput.value || '') + '%' }})
14ORDER BY created_at DESC
15LIMIT {{ table1.pageSize || 25 }}
16OFFSET {{ ((table1.page || 1) - 1) * (table1.pageSize || 25) }};
17
18-- MySQL / SQLite equivalent:
19-- LIMIT 25 OFFSET 0  (page 1)
20-- LIMIT 25 OFFSET 25 (page 2)
21-- LIMIT 25 OFFSET 50 (page 3)

Server-side pagination needs a total count to display 'Page 2 of 47' style navigation. Create a second SQL query named 'getUsersCount' that returns the total number of matching rows. Apply the same WHERE filters as your main query but use COUNT(*) instead of SELECT. In the Table Inspector, set the 'Total rows' property to {{ getUsersCount.data[0].count }} so the table knows how many pages to show in the navigation.

1-- SQL Query: getUsersCount
2-- Must use same WHERE filters as main data query
3SELECT COUNT(*) AS count
4FROM users
5WHERE
6  ({{ searchInput.value || '' }} = ''
7   OR first_name ILIKE {{ '%' + (searchInput.value || '') + '%' }}
8   OR last_name  ILIKE {{ '%' + (searchInput.value || '') + '%' }});
9
10-- In Table Inspector → Interaction → Total rows:
11-- {{ parseInt(getUsersCount.data[0].count) || 0 }}

Add a Select component to let users control how many rows appear per page. Name it 'pageSizeSelect' and configure static options: [{label: '10', value: 10}, {label: '25', value: 25}, {label: '50', value: 50}, {label: '100', value: 100}]. If your table supports dynamic page size, bind the Table Inspector's 'Page size' to {{ pageSizeSelect.value }} instead of a hardcoded number. The SQL query already uses {{ table1.pageSize }} so it will adapt automatically.

1// pageSizeSelect options (static):
2// [{ label: '10 per page', value: 10 },
3//  { label: '25 per page', value: 25 },
4//  { label: '50 per page', value: 50 },
5//  { label: '100 per page', value: 100 }]
6
7// Table Inspector → Interaction → Page size:
8// {{ pageSizeSelect.value || 25 }}
9
10// When page size changes, reset to page 1 to avoid empty pages:
11// pageSizeSelect event handler → Run JS Query:
12await table1.setCurrentPage(1);
13// Note: table1.setCurrentPage() is async

When a user applies a filter, the total number of rows changes and the current page may no longer be valid. Reset to page 1 whenever a filter changes. Add a JS Query that resets the page and re-runs the count query, triggered from filter component event handlers.

1// JS Query: resetPaginationOnFilterChange
2// Triggered by searchInput onChange and statusFilter onChange
3
4// Reset table to page 1:
5await table1.setCurrentPage(1);
6
7// Retool will automatically re-run queries with 'Run when inputs change'
8// since table1.page just changed to 1 (from whatever it was)
9
10// If count query doesn't auto-run, trigger it:
11await getUsersCount.trigger();
12
13// Event handler setup:
14// searchInput → Change → Run JS Query: resetPaginationOnFilterChange

Add a loading indicator so users see feedback during server-side page transitions. Use query1.isFetching in a component's 'Disabled' or 'Loading' property. You can also show a spinner using a Spinner component with its 'Visible' property bound to {{ query1.isFetching }}.

1// Spinner component visibility:
2{{ query1.isFetching }}
3
4// Disable 'Next Page' button while loading:
5{{ query1.isFetching }}
6
7// Show row count info:
8{{ !query1.isFetching
9   ? 'Showing ' + ((table1.page - 1) * table1.pageSize + 1) +
10     '-' + Math.min(table1.page * table1.pageSize,
11       parseInt(getUsersCount.data[0]?.count || 0)) +
12     ' of ' + (getUsersCount.data[0]?.count || '?')
13   : 'Loading...'
14}}