No video :(

Call Postgres functions from JavaScript with RPC

Подписаться 31 тыс.

Просмотров 15 тыс.

50% 1

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/cou...
---
Learn more about Supabase 👇
🕸 Website: supabase.com/
🏁 Get started: app.supabase.com/
📄 Docs: supabase.com/docs
🐙 Github: github.com/sup...
💬 Discord: discord.supaba...
🐦 Twitter: / supabase
Jon Meyers 👇
🕸 Website: jonmeyers.io/
🎥 RU-vid Channel: / jonmeyers
🐦 Twitter: / jonmeyers_io
---

Опубликовано:

3 янв 2022

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 45

@BenniK88 Год назад

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__ 2 года назад

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 2 года назад

When Jon talks, I listen! Very cool :)

@GabrielSestrem 2 года назад

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.

@JonMeyers 2 года назад

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!

@RickGladwin Год назад

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.

@pepa007 Год назад

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.

@GabrielSestrem Год назад

@@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.

@carter8679 Год назад

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.

@electrolyteorb Год назад

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

@abdullahislam 2 года назад

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

@JS-fd5oh 2 года назад

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;

@JonMeyers 2 года назад

Yeah, excellent suggestion! 💯

@gtanmoy 2 года назад

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

@raybelmo 2 года назад

I tried this some days ago and worked.

@JonMeyers 2 года назад

Great to hear!

@carter8679 Год назад

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.

@Innesb 2 месяца назад

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.

@NathanBudd 8 месяцев назад

This was a really well explained video. Good content!

@logemann74 2 года назад

Great new video series

@JonMeyers 2 года назад

Thanks Marc, glad you're enjoying it!

@midokratie Год назад

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

@trashAndNoStar 2 года назад

Is there a sample code available somewhere? Thanks.

@NathanBudd 8 месяцев назад

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.

@Solly-hx1ov 11 месяцев назад

We need similar videos with Flutter

@edgarasben 2 года назад

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

@Supabase 2 года назад

Definitely ! Our CLI supports Database Migrations: github.com/supabase/cli

@edgarasben 2 года назад

@@Supabase Great! Are there any docs or examples for that approach? Or not yet?:)

@eprohoda 2 года назад

what a awesome job!see you o my side,

@arlandmv4053 2 года назад

Awesome

@JonMeyers 2 года назад

Thanks Arland! Glad you enjoyed it!

@codesengel6642 2 года назад

@@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? :)

@eliasbest 11 месяцев назад

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

@andreylakotko119 2 года назад

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? :)

@codesengel6642 2 года назад

hello, i am facing the same issue :(

@alejandrominozzi5733 2 года назад

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

@RootsterAnon Год назад

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.

@RuneJeppesen 2 года назад

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?

@gtanmoy 2 года назад

Can I send user.id from frontend while calling the same function as a parameter?

@JonMeyers 2 года назад

The user's session automatically gets sent when using RPC. It can be accessed in the function via the auth.uid() function 👍