How to Automatically Add Authenticated Users to your Supabase Database
Published: 2024-06-11 00:49:27

# 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 
![screenshot of side panel in Supabase](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8a1yki4ffuekvbhv9am2.png)

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

``` plpgsql
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.

```plpgsql
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

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

and your Python backend like this 
``` python
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](https://boardshape.com/engineering/how-to-pull-google-profile-through-on-account-creation-with-supabase) for introducing me to this concept.