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
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
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
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.
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?
Thanks man how to depend on specific data add another profile table? ex: when the user creates an account, check if the employee will save in the employee table and otherwise will save the manager tabel?
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 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.
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();
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.
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!
@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?
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.
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?
As many have noted, Supabase no longer allows us to create triggers based on the auth schema. I found a video with the workaround: th-cam.com/video/mcrqn77lUmM/w-d-xo.html
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 ?
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?
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?
>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
@@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 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!
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.
@@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).
@@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.
dude if you knew how much the community racked their brains over this authoring feature...
note that this is 2 years ago. now you must create a trigger with SQL editor if you listen to auth
Thanks for the comment
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
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
Please keep this going!! We need this beautiful level of documentation.
Im loving supabase! :)
Really grateful for it!!
That awesome! Really glad to hear you're enjoying it!
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
You can still access the users table using the SQL editor. They only removed access direectly from the UI.
@@dnserror89that is correct, I made a video on it too aha
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.
Thank you so much for this!!! I literally cried last night😂😂😂😂
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?
it's exactly what I'm looking for, thank you!
Glad to hear it!
Thanks man how to depend on specific data add another profile table?
ex: when the user creates an account, check if the employee will save in the employee table and otherwise will save the manager tabel?
The table to fire the trigger (auth table) does not show up in my list.
same issue here. only the public schema tables are displayed!
people are saying that the trigger must be created using sql code instead
Please make more videos like these on triggers and plpgsql! 🙏🏼🙏🏼🙏🏼
The auth schema is managed by Supabase and is read-only through the dashboard.
Edit: Use the SQL Editor
He please upddate this video there is no auth autho option anymore in trigger
Thanks for the overview. I pressed the like button twice to send you a very kind message!
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?
did u find a solution to this by any chance
@@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.
@@amoore2922Yeah, we are using statement. I ended up finding a solution actually, it was using transition tables that were introduced in Postgres v10
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();
Nice! It worked! Thank you.
Thank you, I wasted hours messing with this....
Thank you!
tutorials needs to update to explain this UI change, thanks 🙏🏾
Trigger from auth is not available yet, how to trigger when user created?
dude i want to ask, how to make the trigger work for some conditional time
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.
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!
@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?
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.
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?
Correct, you would need to delete the profile first. You could also remove the constraint and add one with cascading delete with raw SQL 👍
How would the automatic creation of records in a monthly table be, so that every month records were automatically generated in a table?
As many have noted, Supabase no longer allows us to create triggers based on the auth schema. I found a video with the workaround: th-cam.com/video/mcrqn77lUmM/w-d-xo.html
How to do scheduled publish in Supabase ?
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 ?
That was really good. Thank you. I would like to also see how to do it in SQL editor.
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?
You'll find the solution in my latest comment.
Failed to create function: failed to create pg.functions: syntax error at or near "return"
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?
>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
@@Supabase ahaa that is awesome. How convenient! Thank you very much!
@@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
@@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!
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.
Now only i am getting is can't create new user Data base error. after automating on user insert in auth.users trigger
Great work keep going forward…. Is supabase support mongodb also
No, supabase is a database of its own.
@@didiercatz
There no any type of no sql database in supabase
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!
Thanks, it was helpful
This is incredibly useful
Great video due! Thx :)
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.
Why the update event does not trigger/work only the insert works.?
Did you find a solution to your question?
@@misomenze Yes I found the answer, actually we have to create two diffrent function. inser and update action returns two diffrent type of data.
@@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).
@@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.
@@dipankarmaikap hi, thanks
I just sent you a dm on Facebook
Thank you, it works)
supabase are we going to get an answer on this? like ever??