Call Postgres functions from JavaScript with RPC

Sometimes we have a collection of complex steps we need to perform in a particular sequence from the front-end. Maybe we want to execute an SQL statement that the supabase-js library does not yet support. These are both excellent use cases for RPC.

RPC in supabase allows us to call a PostgreSQL function from JavaScript. This means we can abstract away our complex logic, or multi-step process into a Postgres function, and just make one request to call this function from the client.

---
Learn more about Supabase 👇

Jon Meyers 👇

---

[BenniK]

I really like the way you communicate calmly, friendly and very informative. I hope they keep you for releasing features and making great tutorials. Very valuable! Much appreciated.

[__joellee__]

0:23 -- Why use RPC
0:59 -- Demo app
1:24 -- Demo Table
1:44 -- Overview of code
2:18 -- How the supabase client is used
3:16 -- Selecting the clicks column
3:38 -- How to get a single value
5:00 -- How to stop flash
5:20 -- Add brief loading
5:55 -- How to increment number of clicks
6:47 -- Creating a new SQL function
7:14 -- Start writing the PLPGSQL query
7:53 -- Crafting the exact query
9:24 -- Highlight the importance of where.. auth=uid
10:06 -- How to use an RPC call with Client library
11:32 -- Demo that it works
11:40 -- Handling the case without a user
13:04 -- Recap of the video
13:36 -- Recap of the plpgsql query

[JamesQQuick]

When Jon talks, I listen! Very cool :)

[abdullahislam]

You taught me more about react and useEffect than superbase lol. You're a great teacher. Please keep making more tuts

[GabrielSestrem]

From a developer perspective, in this scenario using NextJS. I'd prefer to write an API to handle this logic rather than having a function in the database which hides the logic from the developer. But it was nice to see this approach using functions.

[gtanmoy]

Please make a full video on Supabase and Angular. Learnt a lot from your excellent videos. Thank you.

[NathanBudd]

This was a really well explained video. Good content!

[electrolyteorb]

watched the whole video on the docs page... just came here to give a thumbs up 👍

[raybelmo]

I tried this some days ago and worked.

[JS-fdoh]

I understand that query optimization is not the point here. But you can even use RETURNING as I just learned it myself :)
update profiles set clicks = clicks + 1 where id = auth.uid() returning clicks;

[eliasbest]

Muchas gracias si me pareció muy útil e interesante seguro lo voy a intentar 🎉

[midokratie]

How do you get this to work with arguments? Would have been good to cover an example that includes that.

[trashAndNoStar]

Is there a sample code available somewhere? Thanks.

[RuneJeppesen]

Awesome video - and functionality!

I can have a table with RLS giving noone access, the functions will still be able to access them, right?

Please let us have the example code :)

If you have RLS on the profiles table the "eq('id', user.id)" is not necessary, right?

[macromicro]

How do we call a postgres function if the arguments have no label (omitted)?

[alejandrominozzi]

Hi, excellent demonstration, but I have a doubt, in your example the postrgresql function does not receive input parameters... I did a test and observed that in the supabase API call payload the parameters can be displayed... How could this be solved... so that the data is not hacked so easily. My english is terrible, that's why I used goolge translator

[NathanBudd]

Is this the best (only?) way to associated data through a pivot table?

Eg, I upload a blog post, and create 3 new tags.. .I then want to associate the tags to the post as a many-to-many relationship.

[eprohoda]

what a awesome job!see you o my side,

[edgarasben]

Cool! Would it be possible to somehow store the function code in the main project codebase rather than typing directly to the Supabase platform?

[carter]

Question... I see that Row Level Security is not enabled on your public.profiles table. Also, in your RPC, you are specifying the logged-in users with your 'WHERE id = auth.uid()' statement. It seems like it would be much cleaner to enable RLS on public.profiles, and limit reads to users who are logged in, and move that logic into an RLS policy. This is the way that Supabase tutorials advise you to implement RLS.

Why not do it this way? It seems much cleaner... Do RPCs and RLS policies not play well together? That seems the logical conclusion here.