Introduction

SQL databases are a powerful tool that can do a lot more than just store and serve your data. If you're new to programming, you might not be aware that you can create functions and triggers for those functions that allow the database to handle some pretty important stuff on its own. In this case, SQL is going to detect a newly authenticated user and create a profile for them in the database.

I'm using Supabase, but Firebase, Render, and other cloud database options will offer similar capabilities. For example, Firebase utilizes Google Cloud Functions.

The Goal

We have a website that needs to authenticate users and give them a profile to display information about themselves, including a screen name. We decide to use Supabase because it offers flexible options for different types of authentication, such as email, magic link, Google, and more.

Background

The type of authentication used doesn't matter because our solution is going to occur directly in the database, not the application.

We're going to assume we've already created our table for the user's profile, including a string ID, string username, and string email. It's important for the ID to be a string because this ID is going to be the same as the ID in your user's Supabase auth, which uses string IDs.

You can actually access your auth data in Supabase in your Table Editor because the data there is also saved in a SQL table. Just go to schema > auth > users to have a look.

The Solution

1. Open the SQL Editor in the side panel of your project in Supabase

2. In the terminal, write the following function and click run.

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

  RETURN NEW;
END;
$$;

This uses PL/pgSQL, the programming language used to manage SQL databases to create a function in your database. This code doesn't run that function, rather it creates and defines the function or redefines it if it already exists. You could delete the session after running it, but your function would still exist until you write more code in the terminal to delete it.

PL/pgSQL looks a lot different than Python or Javascript, so here's a quick rundown. The function inserts a new row into the profiles table in the public schema and writes the id and email columns based upon the value returned to the function by the trigger, defined as NEW.

3. Now its time to tell the database when to trigger this function.

Write and run the following code in the terminal. You can write it under the function definition or in a new session.

create trigger create_profile_from_auth after
insert
    on
    auth.users for each row execute function create_user_profile();

This tells the database to trigger a function whenever a new row is inserted into the users table of the auth schema. The function to trigger is the one we defined before, called create_user_profile and the trigger is called create_profile_from_auth.

4. Access profiles using the Supabase auth user ID as your primary key.

Now you can access your profiles like any normal SQL table. Since our ID / primary key is the same as the user ID in the auth table, we use that to access the profiles.

You can retrieve the user ID in your JS front end like this

const user = await supabase.auth.getUser();
const uid = user.id

and your Python backend like this

data = supabase.auth.get_user()
uid = user.id

Conclusion

I wrote this blog as a quick solution for someone new to Supabase who was having trouble moving between the Auth and Database sides of Supabase. Special thanks to Tom Holder and this blog post for introducing me to this concept.