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 email value along with the id in the profiles record creation.

Prerequisites

• 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 email column.

CREATE TABLE public.profiles (
    id uuid NOT NULL,
    email text,
    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 id and email from the new user.

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles(id, email)
  VALUES (NEW.id, NEW.email);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

The 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

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:

1. Create a New User: Use the Supabase Auth API or the Supabase Dashboard to create a new user.
2. Check the Profiles Table: After creating a new user, verify that a corresponding record has been automatically created in the profiles table with matching id and email.
3. Delete the User: If you delete a user from the auth.users table, the corresponding record in the profiles table will also be deleted due to the ON DELETE CASCADE constraint.

Conclusion

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.