Using Stored Procedures (RPC) in Supabase to Increment a “Like” Counter

In my recent side project, Lila, I wanted a simple way to increment a “Like” counter for the quotes on the site. As I am using Supabase — The Open Source Firebase Alternative for my backend database, I thought perhaps there’s an increment function within the APIs.

I really don’t want to read the database, update the count in my app then write it back to the database again. Feels like a long-winded way to do such a simple function. Plus, depending on the lag situation, the counter might go out of sync. But alas, there is no such API available.

Luckily there’s something called Stored Procedures (RPC) where you can add a small piece of logic or function to the database and then call it from your app. The problem is the documentation in Supabase requires you to have some basic SQL knowledge or similar experience to know how things work.

I went a super long way with trial and error and also asking a lot of stupid questions on Twitter and the Supabase Github discussions to finally get this to work. I thought I’ll just document how to get it to finally work here.

In the Suapabase dashboard, select the SQL tab on the left menu.

Create a ‘New query’ and type in the code below.

create function vote (quote_id int, increment_num int)
return void as
$$
  update my_table_name
  set which_field = which_field + increment_num
  where field_id = quote_id
$$
language sql volatile;

1. Substitute ‘my_table_name’ to your own table’s name.
2. Substitute ‘which_field’ to the field you want the function to update.
3. Substitute ‘field_id’ to where your ID is stored in your table.

Then click “RUN”.

This will add the ‘vote’ function in your Stored Procedures API. You can then call the function using a Supabase Remote Procedure Call (RPC).

const { data, error } = await supabase.rpc( 'vote', {
  quote_id: 123, increment_num: 1
} )

In case you want to edit your function, please don’t just edit the query and running it again. If you do that, you’re actually pushing a function with a duplicate name into the database. It doesn’t return an error as apparently, it is a valid action. But it’s one mistake that caused a lot of pain for me as I took a really long time trying to figure out why the update didn’t work and the function seem to be running multiple times.

So anyways, to update the function, you’ll need to delete it first and recreate the query above to add it again. To delete the Stored Procedure, run the code below in a ‘New query’.

DROP FUNCTION public.vote

This whole flow confused me a little when I first tried using it because I have no experience in SQL or Stored Procedures.

But this is the way I try to understand it.

Every time you run a ‘New query’, you’re pushing a function (or procedure) to your database. Even though that query might still be saved in the dashboard, it is not the actual representation of the function that’s being stored in your database so editing that doesn’t change anything. And running it is not saving or updating it, but creating it again.

To be honest, I’m writing this as a reference for myself in the future but if you found it useful, do help me like and share this article! 🙏🏻

Oh yes, let me just add a shameless plug to my side project Lila.

Lila 🙍🏻‍♀️ is a philosopher, a poet, and a drunk. She’s also a GPT-3 powered bot who loves to ponder about life.