How To SELECT, COUNT and JOIN Supabase Data
Tags: supabase, javascript
Published: 2022-11-28 10:50:37

## SELECT 

If you've ever used the Supabase JavaScript library, you'll know that getting a list of products from your e-commerce website is as easy as:

```javascript
const { data, error } = await supabase
  .from('products')
  .select()
```
This is the equivalent of the following SQL query:

```sql
SELECT * FROM products;
```

You'll get data if something is returned and you can check for that like so:

```javascript
if (data) {
  // Do something with your data
}
```
When something goes wrong though (e.g. no data being in that table, no Internet access, etc.), you'll get an error which you can also check for

```javascript
if (error) {
  // Tell your user that something went wrong
}
```
Okay, sounds good and we can go home right?

No, we can't.

Real world apps are rarely that simple, the first problem we will run into is overfetching. All of your columns of data are being returned when you might only want the name, price and image URL. The SQL statement for only getting those things assuming that the columns are named `name`, `price`, and `image_url` respectively looks like this:

```sql
SELECT name, price, image_url FROM products;
```
The Supabase equivalent is:

```javascript
const { data, error } = await supabase
  .from('products')
  .select('name, price, image_url')
```

## COUNT

But, wait... How many products does our online vintage 90's R&B vinyl store sell? Let's answer that oddly specific question with some computer science.

First, the SQL of it all:

```sql
SELECT COUNT(*) FROM products;
```

The Supabase equivalent is

```javascript
const { count, error } = await supabase
  .from('products')
  .select('*', { count: 'exact', head: true })
```
Let's unpack that quickly, starting with the `head` parameter. This tells Supabase to NOT send rows back when it's set to true, in this case we just want to know how many products there are not which ones there are exactly.

The `count` parameter can be set to `exact`, `planned`, or `estimated` depending on what you want to achieve.

- `count` actually uses the `COUNT(*)` and will give you the exact number of products but that algorithm is slow.
- `planned` uses a faster algorithm that might not get you the exact number which might not matter.
- `estimated` will use `exact` for small numbers and `planned` for large numbers.

## JOIN

Now we know how to count, we have one more problem (for today) in the form of the mighty `JOIN`.

Let's say our `products` table looks like this:

| id | name                             | price | image_url                            | label_id |
|----|----------------------------------|-------|--------------------------------------|-----------------|
| 1  | Soulful Album                    | 20    | https://example.com/883738 | 1               |
| 2  | Really Soulful Album             | 25    | https://example.com/229374 | 2               |
| 3  | Mediocre But Still Soulful Album | 5     | https://example.com/447889 | 2               |

I've introduced two new columns from what we were working with before, namely `id` and `label_id`

- `id` is the only way for the database to distinguish between different rows (different products in this case) and that's what we call the `PRIMARY KEY`. While the names are unique here, it is very possible for two albums to exist with the same name.
- `label_id` is what we are using to identify which record label released the album.

So, our second table `labels` contains record labels: 

| id | name          | founder      | founded_date |
|----|---------------|--------------|--------------|
| 1  | Smooth Sounds | Teddy Smooth | 1989         |
| 2  | Love Records  | Robin Love   | 1990         |
| 3  | Butter Music  | Teddy Smooth | 1990         |

The `label_id` in the `products` table is linked to an `id` in the `label` table, that's what we call a `FOREIGN KEY`. Assuming you've linked the foreign key correctly (look up how to do that if you haven't), you can now do something like this:

```javascript
const { data, error } = await supabase
  .from('products')
  .select(`
    products,
    name, 
    price, 
    image_url,
    labels (
      name
    )
  `)
```

You now have access to the name of the record label in your JavaScript code.

Your turn: Try to figure out how to get all the albums released by a record label founded by Teddy Smooth.