Supabase PostgreSQL Database Function and Trigger
Supabase is an open-source platform that provides a suite of tools for building and deploying applications. It includes a PostgreSQL database, authentication, and real-time capabilities, among other features. In this tutorial, you'll learn how to create a PostgreSQL function and trigger in a Supabase project to automatically populate a
profiles table with user data when a new user is created in the
auth.users table. This approach ensures that user profiles are consistently and securely maintained, enhancing the overall user experience and data integrity.
To follow this tutorial, you'll need:
- A Supabase project with a PostgreSQL database
- Access to the Supabase SQL editor
- Basic knowledge of SQL and PostgreSQL
It's assumed that you have already set up a Supabase project and are familiar with its basic features and capabilities. You can sign up for a free account and create a new project on the Supabase website. You also probably have an app that uses Supabase Auth to register new users.
Step 1: Define the profiles Table
profiles table is structured to store user profiles, including their ID, avatar URL, and display name. Execute the following SQL command in the Supabase SQL editor to create or modify the
CREATE TABLE IF NOT EXISTS profiles (
id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
avatar_url text NULL,
display_name text NULL,
email text NULL
This statement creates the
profiles table with a foreign key constraint on the
id column that references the
id column, ensuring that profiles are deleted when the associated user is deleted.
Step 2: Create a PostgreSQL Function
Create a PostgreSQL function that will be executed every time a new user is created. This function will insert a new row into the
CREATE OR REPLACE FUNCTION public.handle_new_user()
INSERT INTO public.profiles (id, avatar_url, display_name, email)
VALUES (NEW.id, NEW.raw_user_meta_data->>'avatar_url', NEW.raw_user_meta_data->>'display_name', NEW.email);
$$ LANGUAGE plpgsql;
Let's break down the function in detail:
CREATE OR REPLACE FUNCTION: This command creates a new function or replaces an existing function with the same name within the public schema. The function is named handle_new_user.
RETURNS TRIGGER: Indicates that the function is a trigger function, meaning it's intended to be fired in response to an event on a table (like an INSERT, UPDATE, or DELETE). Trigger functions must return a type TRIGGER.
This function, defined with
SECURITY DEFINER, ensures that it runs with the privileges of the user who created the function, allowing it to perform the necessary insert operation regardless of the privileges of the user causing the trigger to fire.
AS && dollar-quoted string constants used to define a function body, allowing for inclusion of single or double quotes in the function without needing to escape them.
BEGIN: Marks the start of the procedural code block within the function.
INSERT INTO public.profiles (id, avatar_url, display_name, email): This command inserts a new row into the profiles table, which is located in the public schema. The columns specified are id, avatar_url, display_name, and email.
VALUES (NEW.id, NEW.raw_user_meta_data->>'avatar_url', NEW.raw_user_meta_data->>'display_name', NEW.email): This command specifies the values to be inserted into the profiles table. The NEW keyword refers to the new row being inserted into the table that caused the trigger to fire. In this case, it's the new user being created in the auth.users table. The raw_user_meta_data column contains the user's metadata, including the avatar URL and display name.
RETURN NEW;: In a trigger function for row-level triggers, RETURN NEW passes the new row back to the caller. For BEFORE triggers, this means the new row will be used for the insert or update operation. For AFTER triggers, the row is not used but must still be returned.
Step 3: Create a Trigger for New Users
Set up a trigger on the
auth.users table to execute your function whenever a new user is added.
CREATE TRIGGER trigger_after_user_creation
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.handle_new_user();
This trigger ensures that after a new user is inserted into the
auth.users table, the
handle_new_user() function is called to create a corresponding entry in the
Step 4: Test the Setup
After setting up the function and trigger, test the entire workflow:
- Register a New User: Use your app to register a new user. This action should trigger the automatic creation of a new user in the
- Verify in the
profilesTable: Check the
profilestable in your Supabase project to confirm that a new row was added corresponding to the newly registered user, including their ID, avatar URL, and display name.
Security and Maintenance
- Review and Audit: Regularly review and audit the function and trigger to ensure they operate securely and efficiently. Pay special attention to changes in the application logic or database schema that might affect their behavior.
- Privilege Management: Ensure that the user who owns the
handle_new_user()function has only the necessary privileges to perform the required actions, adhering to the principle of least privilege to minimize security risks.
This tutorial provides a secure and automated way to synchronize user creation in Supabase Auth with the population of a
profiles table, leveraging PostgreSQL's powerful features such as functions, triggers, and the
SECURITY DEFINER clause. By following these steps, you ensure that your application maintains a consistent and secure database schema, enhancing the overall user experience and data integrity.
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.