Supabase: Paginate results

Cover image by Pexels from Pixabay

A common use case with Supabase is showing your data in small chunks. This is done, for example, by showing everything on multiple pages. If you try to download millions of rows at once, you are in for a bad time.

If you are familiar with SQL we are basically going to implement the keywords LIMIT and OFFSET through Supabase.

In this blog, we will learn to use the keywords range, and count. Also, we are going to run an SQL query directly in Supabase and index a column.

Prerequisite knowledge

I assume you have the following knowledge:

• Set up a Supabase project (Article)

• You can use select, from, and order on a Supabase object (Article)

• (optional) paginate results in SQL (Article)

  This blog is part of caffeinecritics.com the code can be found on GitHub.

An example of caffeine critics having multiple pages of coffee

Range in Supabase

In Supabase you can use range. It will give all the rows from startIndex up until and including stopIndex. So range(10, 13) will return 4 rows (10, 11, 12, and 13).

In order to work with this we do need to know the total number of rows.

    supabase
        .from('drinks')
        .select('id, name, image_url, description')
        .order('name')
        .range(startIndex, stopIndex)
        .then((res) => {
            const data = res.data
            // Do something with the data
        })

Get total number of rows

We can select all data in Supabase and then count it, but this means sending a lot of data to the app. It’s better to count it in the database and only send the answer to the app. We can do this by selecting the function and the parameter count. By setting head to true we only get the count back. If you set it to false the response also will hold all the rows in data.

With the total number of rows and the amount of elements we want to show per page we can paginate our table in the app.

Please note that this counting is very costly, you go over all rows of your table. So make sure you only call it when it is really needed. Use caching to reduce the amount of times you need to call it.

    supabase
       .from('drinks')
       .select('*', { count: 'exact', head: true })
       .then((res) => {
           console.log(res.count)
       }
    )

Optimization

Using order on your database can be very costly, because if it is not indexed you need to go over all rows in order to order them. So the solution is to index the column we use to order. Keep in mind that indexing has a cost of its own, read more here.

To do this we need to execute an SQL query within Supabase. Go on the right to SQL editor. Here we can run queries on our database.

We will run

    CREATE INDEX index_name ON drinks (name);

This will index all the names within the table drinks for us.

A screenshot of the SQL editor within Supabase

Once we have done this we can see our result in the definition of our table. Go to the table editor and select the table. In the right corner you find a toggle with data and definition. Selection definition and you will see the read-only SQL query to create your table.

At the bottom you will see create index if not exists … with the index you just created

A screenshot of a definition of a table. Showing that the index is created.

Conclusion

To wrap up, when working with large datasets in Supabase, use features like range for pagination and count for row totals. Optimize your SQL queries by indexing columns. These practices ensure smoother user experiences.