Automated Profile Creation in Supabase PostgreSQL
Creating a profile table in Supabase that automatically gets a new record each time a new
auth.user is created is a great way to streamline user management and enhance data integrity. This article will guide you through the process using a PostgreSQL function and trigger to ensure that each new user automatically has a corresponding profile in the
profiles table. We'll include the
id in the
profiles record creation.
- A Supabase account and a project.
- Basic knowledge of SQL.
Step 1: Create the Profiles Table
First, let's create the
profiles table. This table will include an
id column that matches the
id of the
auth.users table and an
CREATE TABLE public.profiles (
id uuid NOT NULL,
PRIMARY KEY (id)
-- Add additional columns as needed, such as name, avatar_url, etc.
FOREIGN KEY (id) REFERENCES auth.users(id) ON DELETE CASCADE
In this SQL statement, the FOREIGN KEY (id) REFERENCES auth.users(id) part establishes the foreign key relationship, indicating that the id column in the profiles table corresponds to the id column in the auth.users table. The ON DELETE CASCADE clause then specifies that if a record in auth.users (a user) is deleted, any related record in profiles should be automatically deleted as well.
Step 2: Create the Function
Next, we'll create the
handle_new_user function. This function is triggered after a new user is inserted into the
auth.users table and will insert a new record into the
profiles table with the
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
INSERT INTO public.profiles(id, email)
VALUES (NEW.id, NEW.email);
$$ LANGUAGE plpgsql SECURITY DEFINER;
SECURITY DEFINER option allows the function to execute with the privileges of the function's owner, typically allowing it to perform operations (like inserts) that the calling user might not be directly authorized to do.
Step 3: Create the Trigger
trigger_after_user_creation will activate after an insert operation on the
auth.users table, executing the
handle_new_user function for each newly inserted row.
CREATE TRIGGER trigger_after_user_creation
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.handle_new_user();
Step 4: Testing the Setup
To ensure everything is working as expected:
- Create a New User: Use the Supabase Auth API or the Supabase Dashboard to create a new user.
- Check the Profiles Table: After creating a new user, verify that a corresponding record has been automatically created in the
profilestable with matching
- Delete the User: If you delete a user from the
auth.userstable, the corresponding record in the
profilestable will also be deleted due to the
ON DELETE CASCADEconstraint.
By following these steps, you've automated the creation of user profiles in your Supabase project. This setup not only simplifies user account management but also ensures that each user has a corresponding profile for additional information, which can be expanded with more attributes as needed.
Thanks for reading. If you enjoyed this post, I invite you to explore more of my site. I write about web development, programming, and other fun stuff.