How to Insert Usernames into Profiles Table Using Supabase Triggers

If you’ve ever worked with user signups, you know that automating profile creation can save you a lot of hassle. Instead of manually handling usernames and profiles after every new signup, why not let Supabase do the heavy lifting? In this quick guide, I’ll show you how to set up a Supabase trigger that automatically inserts usernames into your profiles table—making the signup process smoother so you can focus your energy on other things.

I won’t dive into every detail, but I’ll focus on what really matters: the signup and trigger logic.

Let’s go. 🚀

Getting Started

I’m assuming your project is already set up and running, and your current challenge is implementing the signup logic along with the trigger. By now, your database should include a profiles table. This table should have fields like username , firstname user_id, with user_id being a foreign key linked to auth.users.id. If you don’t have a profiles table yet, the Supabase docs outlines how you can create one.

Adding User Metadata

You can assign metadata to users during the signup process.

// add this to your signup function

const { error, data } = await supabase.auth.signUp({
  email: 'sterlingarcher@isis.com',
  password: 'babyseamus',
  options: {
    data: {
      username: 'duchess'
      firstname: 'archer'
    },
  },
});

The email and password are captured from the user inputs. You can customize the logic to generate the username and any other metadata fields. Notice the options property—under data, you can list all the metadata you want to attach to the user during signup.

Accessing the metadata

User metadata is stored on the raw_user_meta_data column of the auth.users table. To view the metadata, run select * from auth.users; in the SQL editor in Supabase.

In your code you can retrieve the metadata with:

const { data: { user }} = await supabase.auth.getUser()
const metadata = user.user_metadata

console.log(metadata)

Adding the Trigger

To update your public.profiles table every time a user signs up, set up a trigger. If the trigger fails, it could block signups, so test your code thoroughly.

To create the trigger in Supabase, you'll need to use the SQL Editor panel. In your Supabase dashboard, find and open the SQL Editor panel from the sidebar. This is where you can execute SQL commands directly on your database.

Paste the code below into the SQL Editor. Customize it as needed—whether it's the table name, metadata fields, or other details—so it fits perfectly with your project’s structure.

create
or replace function public.handle_new_user () returns trigger language plpgsql security definer
set
  search_path = public as $$
begin
  insert into public.profiles (user_id,username,firstname)
  values (new.id,  new.raw_user_meta_data->>'username',  new.raw_user_meta_data->>'firstname');
  return new;
end;
$$;

create
or replace trigger on_auth_user_created
after insert on auth.users for each row
execute procedure public.handle_new_user ();

This snippet sets up an automated process in your database that creates a new profile entry whenever a new user is added to the auth.users table in Supabase.

The Function

The handle_new_user function, written in PL/pgSQL, triggers each time a new user is added to auth.users. It inserts the user’s user_id, username, and firstname into the profiles table using the user’s metadata.

• new.id: Refers to the id of the newly created user from auth.users and will be added to the user_id column.

• new.raw_user_meta_data->>'username': Gets the username from the data you provided during signup. Any properties you added in options.data can be accessed like this. It will be added to the username column.

• new.raw_user_meta_data->>'firstname': Similarly, gets the firstname from options.data. It will be added to the firstname column.

The Trigger

The on_auth_user_created trigger automatically runs the handle_new_user function after a new user is added to the auth.users table. This function then inserts the user’s data into the profiles table.

Removing Triggers

Existing triggers can sometimes interfere with the signup flow. If you've previously set up triggers and suspect they might conflict with your new trigger, you can remove them using the following command:

DROP TRIGGER IF EXISTS your_trigger_name ON auth.users;

Make sure to replace your_trigger_name with the actual name of the trigger you want to remove, and auth.users with the appropriate table name if different.

With these updates, you’re all set! 🚀 If you run into any issues, check out the Supabase docs for more info.