How To SELECT, COUNT and JOIN Supabase Data

#softwaredevelopment #performance #webdev #discuss

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()

This is the equivalent of the following SQL query:

SELECT * FROM products;

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

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

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:

SELECT name, price, image_url FROM products;

The Supabase equivalent is:

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:

SELECT COUNT(*) FROM products;

The Supabase equivalent is

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:

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