Using PostgreSQL triggers to automate processes with Supabase

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

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

50% 1

For the introductory video on PostgreSQL functions, check out 👉 • Create PostgreSQL Func...
Functions in Postgres allow us to encapsulate some logic in our database, but we don't want to have to call these functions manually, whenever data in our database changes. In order to automate this, we can use Postgres Triggers.
Triggers in PostgreSQL allow us to subscribe to particular events that occur in the database - insert, update or delete - and call a Postgres function whenever they occur. This allows us to automate some of those manual processes - such as creating a row in our profiles table for each user who signs in.
In this video, Jon Meyers ( / jonmeyers_io ) explains how we can model our database schema by creating a profiles table to hold additional data about our user. We can then create a trigger that listens for insert events on the auth.users table and automatically creates a profile for them.
This is part of a series of videos about functions in PostgreSQL, check out the rest of the playlist to learn more: • PostgreSQL Functions
👮‍♀️ Learn about Row Level Security: • Implement Authorizatio...
💰 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
---

Наука

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

10 сен 2024

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

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

dude if you knew how much the community racked their brains over this authoring feature...

@yoapps137 Год назад

I appreciate the way you are also describing, the options on the path you arent taking... it helps because when a developer really sits down to make his scratch they come across the same options and wonder "...but what might this do?"... and you have already prepped us for that. thanks

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

0:47 -- Application introduction 1:32 -- Auth settings configuration 2:11 -- Github Repo 3:43 -- Overview of the profiles table to store additional user data 5:07 -- Adding a column to store click count 5:40 -- Adding a trigger 6:09 -- Adding a function which takes effect on the trigger 6:50 -- Description of the "new" value 7:25 -- Changing security roles and rationale(Change to security Definer) 8:55 -- Explanation of trigger types(Row vs Statement) 10:17 -- Supabase Twitter and Discord

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

Please keep this going!! We need this beautiful level of documentation. Im loving supabase! :) Really grateful for it!!

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

That awesome! Really glad to hear you're enjoying it!

@anthonyngooo 10 месяцев назад

8:05 This part needs to be updated. As of sometime in October 2023, the auth schema is available, but the users table is not visible and only public tables are shown

@dnserror89 7 месяцев назад

You can still access the users table using the SQL editor. They only removed access direectly from the UI.

@antgoesafk 7 месяцев назад

@@dnserror89that is correct, I made a video on it too aha

@deoarlo 5 месяцев назад

note that this is 2 years ago. now you must create a trigger with SQL editor if you listen to auth

@isaacfigueroa 5 месяцев назад

Thanks for the comment

@zb1921 10 месяцев назад

It's no longer possible to create the trigger through the UI, but I figured out how to create the trigger in the SQL editor. create trigger "create_profile_for_user_trigger" after insert on auth.users for each row execute function create_profile_for_user();

@Guido_EFOMM 10 месяцев назад

Nice! It worked! Thank you.

@clojuregang3618 10 месяцев назад

Thank you, I wasted hours messing with this....

@elan2199 9 месяцев назад

Thank you!

@samifouad 2 дня назад

tutorials needs to update to explain this UI change, thanks 🙏🏾

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

Every time I try this I get a "Database error saving new user" 500 error response. I've rewatched many times now and I'm doing exactly what you are. Any ideas?

@Broski_Rodragweez 6 месяцев назад

I almost scoffed at the idea of using Supabase because for one I don't care much for SQL (got burned out on it a few years ago), for two I don't care for putting business logic in my database. BUT, since it appears that I can build apps without all the API layer boilerplate I am starting to come around. Plus having the AI available to help write sql makes it less painful.

@loribryant4999 7 месяцев назад

He please upddate this video there is no auth autho option anymore in trigger

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

it's exactly what I'm looking for, thank you!

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

Glad to hear it!

@evanrosz Год назад

Thanks for the overview. I pressed the like button twice to send you a very kind message!

@chaoslordi 6 месяцев назад

This video could use an update that triggers cannot be created via supabase trigger interface but with an sql query instead. At least that was the case when I followed along today.

@ProfRa6574 5 месяцев назад

The table to fire the trigger (auth table) does not show up in my list.

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

same issue here. only the public schema tables are displayed!

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

people are saying that the trigger must be created using sql code instead

@fanizul1282 9 месяцев назад

Trigger from auth is not available yet, how to trigger when user created?

@ofeenee Год назад

Please make more videos like these on triggers and plpgsql! 🙏🏼🙏🏼🙏🏼

@sonuaryan5287 11 дней назад

Now only i am getting is can't create new user Data base error. after automating on user insert in auth.users trigger

@aahl_work 6 месяцев назад

The auth schema is managed by Supabase and is read-only through the dashboard. Edit: Use the SQL Editor

@MishaMoroshko Год назад

@JonMeyers @Supabase I'd like to create a profile row only after the user confirms their email. Do I understand correctly that the UPDATE event will call the trigger function on any update, and there is no way to trigger the function on update of a specific column? Ideally, I'd like to trigger the function when `email_confirmed_at` is changing from NULL to a non-NULL value in `auth.users`. Is this possible to achieve this?

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

but with this FK in place (in profiles which relates to ID of auth.users), we cant delete a user in the AUTH section anymore because of FK constraint error on delete right?

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

Correct, you would need to delete the profile first. You could also remove the constraint and add one with cascading delete with raw SQL 👍

@Troy-ol5fk 2 года назад

How to do scheduled publish in Supabase ?

@leandroosterne Год назад

How would the automatic creation of records in a monthly table be, so that every month records were automatically generated in a table?

@rahulagarwal968 Год назад

When we are using email authentication then we can send raw user meta data and fetch inside functions. But How can we get the username and profile image value from functions if we are authenticating with Google ?

@JustADeveloper-l7x Год назад

7:45 damn i wish this was mentioned in the docs. Took me an hour to figure out why the function did not have permissions to run.

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

I am stuck with a superbase trigger. I can't set trigger on auth.users. In Add a new Trigger > Conditions to fire trigger > Table there is only one 'profiles' table. What am i doing wrong?

@zb1921 10 месяцев назад

You'll find the solution in my latest comment.

@maybe_omuamua 9 месяцев назад

Failed to create function: failed to create pg.functions: syntax error at or near "return"

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

This is incredibly useful

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

As many have noted, Supabase no longer allows us to create triggers based on the auth schema. I found a video with the workaround: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-mcrqn77lUmM.html

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

is it a good practice for a production application for Inventory management to use Triggers? Hope you can make a video to demonstrate inventory management using Triggers. Thanks!

@amoore2922 Год назад

Great video! Saved me a bunch of trial and error. Would like to point out that it seems that Orientation > Row is required if you want access to the "new" object in the function called by the trigger. Perhaps there is another object available in the function if Orientation > Statement is used?

@ajaychandrasekaran9262 Год назад

did u find a solution to this by any chance

@amoore2922 Год назад

@@ajaychandrasekaran9262 Are you looking to use the Orientation > Statement? We're using row for our project anyway, so it was not an issue for us. The supabase support has been very helpful. I'll bet if you create a help ticket they will have an engineer provide an answer.

@ajaychandrasekaran9262 Год назад

@@amoore2922Yeah, we are using statement. I ended up finding a solution actually, it was using transition tables that were introduced in Postgres v10

@hermeschi Год назад

Thanks, it was helpful

@jkohlin Год назад

What if you want to create a trigger function on deletion of a row. How do I get a reference to the primary key from the deleted row, so that I can clean up rows from other tables based on that?

@Supabase Год назад

>so that I can clean up rows from other tables It sounds like you want "cascading deletes". When you set up a column in a table which references another table you can choose to use "cascade delete" which will delete any references for the trigger, you can access the ID using the "OLD" values inside the function - eg: OLD.id

@jkohlin Год назад

@@Supabase ahaa that is awesome. How convenient! Thank you very much!

@jkohlin Год назад

@@Supabase 🤔...or that would have been awesome if it was true... Sorry, but I cant find cascading deletes anywhere. When I delete a user from auth users I just get an error referring to foreign keys. But when I edit the foreign keys-column in those tables, there is no such option 😟 Or maybe there's no UI for it yet? I fixed it by first removing the relationship in the UI and then ADD CONSTRAINT via an SQL query. Thanks anyway for pointing me in the right direction

@Supabase Год назад

@@jkohlin For now, you will need to add the column using SQL to get the CASCADE DELETE. We haven't added it to the UI yet - it's coming!

@alfonguti1 Год назад

Great video due! Thx :)

@RootsterAnon Год назад

I really wish you finish this video with clicking and reading that value from newly created profile, but I guess it involves more frontend part to do so.

@shohzodzet Год назад

Thank you, it works)

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

Why the update event does not trigger/work only the insert works.?

@misomenze Год назад

Did you find a solution to your question?

@dipankarmaikap Год назад

@@misomenze Yes I found the answer, actually we have to create two diffrent function. inser and update action returns two diffrent type of data.

@misomenze Год назад

@@dipankarmaikap hi, thanks I'm not sure I understand. What I'm trying to do is update public. users table (my users table) when I update the user's email or phone on the auth.user table. using a trigger for update. when I set it to update supabase declines any request I make on the auth. users table (signing in or signing up a new user).

@dipankarmaikap Год назад

@@misomenze create two function first one will use insert and second one will use update. I'm trying to add a github gist link but its getting deleted. May be reach out to me in another way I'll share you a code sample.

@misomenze Год назад

@@dipankarmaikap hi, thanks I just sent you a dm on Facebook

@kamil_supabase_enjoyer Год назад

That was really good. Thank you. I would like to also see how to do it in SQL editor.

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

Great work keep going forward…. Is supabase support mongodb also

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

No, supabase is a database of its own.

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

@@didiercatz There no any type of no sql database in supabase

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

Supabase wraps around PostgreSQL atm, and is really just a convenience wrapper trying to expose all the cool things that Postgres give you out of the box! Therefore, I think it is unlikely Supabase would move away from Postgres. Anything is possible though!

@ProfRa6574 5 месяцев назад

supabase are we going to get an answer on this? like ever??

@lasserravn Год назад

I got it working, but only when my profiles table has the exact same table headers as the names of the variables that are passed in the function. This fx. doesnt' work, but if I change the 2nd line to use full_name & avatar_url (and also rename the columns in the profiles table) then it works: begin insert into public.profiles (id, email, fullName, avatarUrl) values (new.id, new.raw_user_meta_data->>'email', new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url'); return new; end; Is there any way to declare which value belongs to which column in my profiles table? We need more examples on this! It's an awesome feature.