Call Postgres functions from JavaScript with RPC
Vložit
- čas přidán 3. 01. 2022
- For the introductory video on PostgreSQL functions, check out 👉 • Create PostgreSQL Func...
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.
In this video, Jon Meyers ( / jonmeyers_io ) demonstrates how we can create a function to increment a column's value by one, and call this when the user clicks a button using RPC.
This is part of a series of videos about functions in PostgreSQL, check out the rest of the playlist to learn more: • PostgreSQL Functions
💰 Get really good at Supabase and build a SaaS product (free course): egghead.io/courses/build-a-sa...
---
Learn more about Supabase 👇
🕸 Website: supabase.com/
🏁 Get started: app.supabase.com/
📄 Docs: supabase.com/docs
🐙 Github: github.com/supabase
💬 Discord: discord.supabase.com/
🐦 Twitter: / supabase
Jon Meyers 👇
🕸 Website: jonmeyers.io/
🎥 CZcams Channel: / jonmeyers
🐦 Twitter: / jonmeyers_io
--- - Věda a technologie
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.
When Jon talks, I listen! Very cool :)
You taught me more about react and useEffect than superbase lol. You're a great teacher. Please keep making more tuts
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
watched the whole video on the docs page... just came here to give a thumbs up 👍
This was a really well explained video. Good content!
Please make a full video on Supabase and Angular. Learnt a lot from your excellent videos. Thank you.
Great new video series
Thanks Marc, glad you're enjoying it!
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.
Totally valid! It's always good to know all the tools at your disposal so you can make the write call for the problem you're facing!
I’m considering adding custom PostgreSQL functions using migrations so that these functions are at least part of the main codebase, in order to address that exact concern. I’m not satisfied yet with the way this functionality is represented in the code using this solution, so I don’t know if that’s how the app will run in production.
He mentions the use case is when you have any logic that requires a number of roundtrips, so it may not be feasible to do it on your server (note that supabase is de facto another server besides your server).
Counters are a notoriously hard problem in computer science. Even simple ones like the one in the video generally require at least a transaction that bundles read (of current count) and write (of updated count) together. Supabase clients don't support transactions (afaik), so basically the only way to implement a counter is a postgres function. You could use edge function too, but that doesn't make much sense. Anyway, it's a good idea to keep counter logic as close to the database as possible, and postgres functions fit the bill.
@@pepa007 Thanks for the example. In fact after watching this video we ended creating a few functions specially for our dashboard and analytics. Much easier doing aggregations such as count/sum/avg/max/min in a single function and passing all the data transformed to the API. We decided to add the function and documenting properly to be easy to maintain in the future.
it would be nice if we could define these RPC functions in the frontend, and then be able to call them like he's doing here.
I tried this some days ago and worked.
Great to hear!
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;
Yeah, excellent suggestion! 💯
Muchas gracias si me pareció muy útil e interesante seguro lo voy a intentar 🎉
How do you get this to work with arguments? Would have been good to cover an example that includes that.
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?
Is there a sample code available somewhere? Thanks.
what a awesome job!see you o my side,
How do we call a postgres function if the arguments have no label (omitted)?
We need similar videos with Flutter
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.
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
Awesome
Thanks Arland! Glad you enjoyed it!
@@JonMeyers from @andrey :
It's not working for me, by some reason when i trigger this rpc by curl it's work but if i try to do it from my backend its return an error -
{"message":"Could not find the public.decrement_tournament_open_slots() function or the public.decrement_tournament_open_slots function with a single unnamed json or jsonb parameter in the schema cache","hint":"If a new function was created in the database with this name and parameters, try reloading the schema cache."}
let { data, error } = await supabaseClient.rpc("decrement_tournament_open_slots", {tournament_id});
i have a id sending with this rpc call, as i understand it's a supabase-js had some problem with my parameters, any ideas? :)
Can I send user.id from frontend while calling the same function as a parameter?
The user's session automatically gets sent when using RPC. It can be accessed in the function via the auth.uid() function 👍
Cool! Would it be possible to somehow store the function code in the main project codebase rather than typing directly to the Supabase platform?
Definitely ! Our CLI supports Database Migrations: github.com/supabase/cli
@@Supabase Great! Are there any docs or examples for that approach? Or not yet?:)
What does RPC stand for?
It's not working for me, by some reason when i trigger this rpc by curl it's work but if i try to do it from my backend its return an error -
{"message":"Could not find the public.decrement_tournament_open_slots() function or the public.decrement_tournament_open_slots function with a single unnamed json or jsonb parameter in the schema cache","hint":"If a new function was created in the database with this name and parameters, try reloading the schema cache."}
let { data, error } = await supabaseClient.rpc("decrement_tournament_open_slots", {tournament_id});
i have a id sending with this rpc call, as i understand it's a supabase-js had some problem with my parameters, any ideas? :)
hello, i am facing the same issue :(
Can you show an example with an argument
one year later, in @9:44 you need to click on show advance options and set type of security to Security Definer and your function will be able to call and update, if you don't do this, your rpc call will not return fail, but will not do operations, it will always return +1 number all the time, in my case it was clicks = 15 ,and 16 would be returned every time i click on it. I hope this helps anyone having same situation as me.
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.
RLS would be the way to go in a functional application, but this tutorial was to explain Postgresql functions. Adding RLS into the video would have at least doubled its length! It might have been appropriate for Jon to mention RLS in the video and direct viewers to the appropriate resources.
For anyone who is unsure, Row Level Security (RLS) allows for the implementation of security rules on a table. For example, only allow the currently authenticated user to select and update rows where the user_id column matches their id. In this example app, it means that the WHERE clause would not be required, because only the authenticated user’s record would be returned. Jon has created a few videos on RLS, which are definitely worth watching.
does this method not work anymore?
Why would we want to do this ? This is what sql is for querying databases not tables and the entire advertisement was this is just postgres