How to Insert Usernames into Profiles Table Using Supabase Triggers
#supabase, #triggers, #user management, #sql triggers
Aug 28, 20244 min read
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: '[email protected]',
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 theid
of the newly created user fromauth.users
and will be added to theuser_id
column.new.raw_user_meta_data->>'username'
: Gets theusername
from the data you provided during signup. Any properties you added inoptions.data
can be accessed like this. It will be added to theusername
column.new.raw_user_meta_data->>'firstname'
: Similarly, gets thefirstname
fromoptions.data
. It will be added to thefirstname
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.
Back to Blogs