Supabase authentication giving error while saving use data in profile table

Question

I am building a next js app with authentication and user data. I am trying supabase for the first time and used some online help to create some tables and triggers to save user information in profile table. Authentication is working fine without any trigger but when I write the trigger to save the info in profile table authentication fails. Is it necessary to match the field names in profile table and user table.

CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO profiles (id, full_name, avatar_url)
  VALUES (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');
  RETURN new;
END;
$$ language plpgsql security definer;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE PROCEDURE handle_new_user();

cheekyprogrammer · Accepted Answer · 2025-01-22 11:38:42Z

0

Can you show the definition for your profiles table? I also ran into issues when setting triggers up and it ended up being something small that just needed to be tweaked.

if you check the logs it will usually throw an error you can see what it's complaining about. If you want, you can adjust the function to log an error so you can see what the problem is.

CREATE OR REPLACE FUNCTION handle_new_user() RETURNS trigger AS $$
BEGIN
    INSERT INTO profiles (id, full_name, avatar_url)
    VALUES (
        new.id, 
        new.raw_user_meta_data->>'full_name', 
        new.raw_user_meta_data->>'avatar_url'
    )
    ON CONFLICT (id) DO NOTHING;  -- This will ignore the insert if the id already exists

    RETURN NEW;
EXCEPTION
    WHEN OTHERS THEN
        RAISE WARNING 'An error occurred while handling the new user: %', SQLERRM;
        RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE PROCEDURE handle_new_user();

There's no reason why the code above shouldn't work as long as you have a profiles table with an id, full_name and avatar_url field. If its still not working then definitely check the logs to see why.

Hope this helps!

answered Jan 22 at 11:38

cheekyprogrammer

264 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hasan Zubairi Jan 22 at 17:37

I have added the screenshot of the profiles table

Hasan Zubairi Jan 22 at 19:23

Thank for responding. Your trigger is working and it is creating the new user but it is not saving anything in profiles table

cheekyprogrammer Jan 22 at 22:57

Hello there. Okay, so its letting you create the user now and not getting stuck, but still not saving anything? What provider are you using? At this point it sounds like the provider isnt giving anything for raw_user_meta_data for whatever reason. Take a look at the logs now to see if its coming up with any error or warning.

cheekyprogrammer Jan 22 at 22:58

What you can do is make a quick page that displays the current user from Supabase. confirm what its giving back for the identity and i have a feeling you'll find the answer there.

Collectives™ on Stack Overflow

Supabase authentication giving error while saving use data in profile table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related