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.

Prerequisites

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

Ensure your 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 profiles table:

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 auth.users table's 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 profiles table.

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER
SECURITY DEFINER
AS $$
BEGIN
  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);
  RETURN NEW;
END;
$$ 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 profiles table.

Step 4: Test the Setup

After setting up the function and trigger, test the entire workflow:

1. 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 auth.users table.
2. Verify in the profiles Table: Check the profiles table 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.

Conclusion

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.