Supabase: Paginate results
Tags: supabase, webdev, beginners, tutorial
Published: 2024-01-19 12:33:48

> Cover image by <a href="https://pixabay.com/users/pexels-2286921/?utm_source=link-attribution&utm_medium=referral&utm_campaign=image&utm_content=1283865">Pexels</a> from <a href="https://pixabay.com//?utm_source=link-attribution&utm_medium=referral&utm_campaign=image&utm_content=1283865">Pixabay</a>

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](https://dev.to/wagenrace/getting-started-with-supabase-8n4))

* You can use **select**, **from**, and **order** on a Supabase object ([Article](https://dev.to/wagenrace/getting-started-with-supabase-8n4))

* (optional) paginate results in SQL ([Article](https://dev.to/wagenrace/paginate-sql-results-402d))
> # This blog is part of [caffeinecritics.com](https://caffeinecritics.com/) the code can be found on[ GitHub](https://github.com/rate-my-drink/rate-my-drink.github.io).

![An example of [caffeine critics](https://caffeinecritics.com/) having multiple pages of coffee](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hqpzok9oof2so7fkvkba.gif)
> An example of [caffeine critics](https://caffeinecritics.com/) 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.
```js
    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.
```js
    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](https://dev.to/wagenrace/paginate-sql-results-402d).

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
```sql
    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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pedffsbkjx0pxme3d5e2.png)
> 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.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hmec1gc8t4s9z6wroyat.png)
> 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.