Supabase pagination via REST API

Table of contents

• Introduction
• Data Pagination
  • Offset-based pagination
  • Cursor-based pagination
• Supabase REST API
  • Using HTTP Headers for offset-based Pagination
  • Using Query Parameters for offset-based Pagination
  • Using Query Parameters for cursor-based Pagination

Introduction

Supabase is a powerful tool that offers everything developers need to build apps quickly, including managing databases, user sign-ins, and real-time updates. A key feature for those dealing with a lot of data is pagination, which helps break down data into smaller, easier-to-handle pieces. This article explains how to use Supabase’s REST API for pagination, making it simpler to work with large datasets.

Data Pagination

Pagination is crucial in app development, especially with big datasets. It splits the data into pages, so users only load a small amount at a time. This makes apps faster and easier to use, improving the overall experience.

Offset-based pagination

Offset-based pagination is a common method used in web and application development to manage and navigate through large sets of data by dividing the data into smaller, manageable chunks or pages. This technique operates on the principle of skipping a certain number of records (the offset) and then returning a defined number of records (the limit) from the dataset.

• Offset: Specifies the number of records to bypass before beginning to fetch data. For example, an offset of 10 skips the first 10 records.
• Limit: Determines the maximum number of records retrieved following the offset, e.g., a limit of 10 fetches up to the next 10 records.

Cursor-based pagination

Cursor-based pagination works by returning a pointer (the cursor) along with the data fetched for a page. This cursor points to the position in the dataset immediately after the last record of the current page. When the next set of data is requested, the query uses this cursor as a reference point to fetch records following it. Cursors can be based on record IDs, timestamps, or any other field that guarantees a stable sort order.

• Cursor - A reference to a specific point in the dataset.

Supabase REST API

The Supabase platform extends its robust backend services with a powerful REST API, facilitated by PostgREST. PostgREST is an open-source tool that turns a PostgreSQL database directly into a RESTful API (available on the /rest/v1/{table-name} route), allowing for seamless interaction with your database using simple HTTP requests. This integration offers developers a straightforward way to execute queries, including complex operations like filtering, sorting, and, crucially, pagination, directly against their Supabase databases.

Using HTTP Headers for offset-based Pagination

Using HTTP headers for pagination involves setting specific header fields to guide data segmentation:

• Range: Indicates the desired range of items, e.g., 10-19 to fetch the next 10 records after the first 10.
• Prefer: Communicates preferences to the server, such as Prefer: count=exact for obtaining the total record count.
• Content-Range: Informs about the range of the current data slice and total data size, e.g., Content-Range: items 10-19/100 shows 10 items starting from the 11th record of a total of 100.

Request
  GET /rest/v1/{table_name}
  apikey ...supabase access key...
  Range 10-19
  Prefer count=exact
Response
  Content-Range: 10-19/100

Using Query Parameters for offset-based Pagination

Traditional query parameter-based pagination is the simplest way to acquire paginated data via REST API. When making a request to a PostgREST endpoint, simply append the offset and limit parameters to the URL to paginate the results. Here is a basic example of how to structure such a request:

Request
  GET /rest/v1/{table-name}
    ?offset=10
    &limit=10
  apikey ...supabase access key...
  Prefer count=exact
Response
  Content-Range: 10-19/100

Using Query Parameters for cursor-based Pagination

While Supabase does not directly offer built-in support for cursor-based pagination, you can effectively achieve it by leveraging query parameters. This approach involves using a specific column in your table as a cursor, combined with PostgREST operators, to simulate cursor-based navigation through your data.

• gt - Stands for “greater than”. It is used to select records where the specified column’s value is greater than a given value.
• asc - Specifies that the results should be ordered in ascending order.
• limit - Specifies the maximum number of records to return, similar to its use in offset-based pagination.

Request
  GET /rest/v1/{table-name}
    ?id=gt.10
    &order=id.asc
    &limit=10
  apikey ...supabase access key...
  Prefer count=exact
Response
  Content-Range: 0-9/100

This example assumes that the id field is unique and auto-incremented by the database, making it a suitable candidate for cursor-based pagination. The same pagination logic can be applied when using a created_at timestamp or any other sequential and unique column.

Note: In this setup, the Content-Range header might not accurately reflect the current range of records fetched; however, the total count of records remains correctly indicated.