DEV Community

Isheanesu
Isheanesu

Posted on

How To SELECT, COUNT and JOIN Supabase Data

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:

const { data, error } = await supabase
  .from('products')
  .select()
Enter fullscreen mode Exit fullscreen mode

This is the equivalent of the following SQL query:

SELECT * FROM products;
Enter fullscreen mode Exit fullscreen mode

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

if (data) {
  // Do something with your data
}
Enter fullscreen mode Exit fullscreen mode

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

if (error) {
  // Tell your user that something went wrong
}
Enter fullscreen mode Exit fullscreen mode

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:

SELECT name, price, image_url FROM products;
Enter fullscreen mode Exit fullscreen mode

The Supabase equivalent is:

const { data, error } = await supabase
  .from('products')
  .select('name, price, image_url')
Enter fullscreen mode Exit fullscreen mode

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:

SELECT COUNT(*) FROM products;
Enter fullscreen mode Exit fullscreen mode

The Supabase equivalent is

const { count, error } = await supabase
  .from('products')
  .select('*', { count: 'exact', head: true })
Enter fullscreen mode Exit fullscreen mode

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:

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

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.

Top comments (0)