How to Copy Display Name and Email from Supabase Auth into The App Database

What are we gonna do in this blog post

• Goal and use case

• How the user info looks like

• View user info by sql query

• Copy user info from auth to app database

• Conclusion

Goal and Use case

If you have used Supabase and its authentication feature then you already know whenever a new user signed up it stores all the user information in Users table. This Users table is found under authentication section in the Supabase dashboard. Now we are maintaining another users table in the postgre database which is offered by Supabase. At one point we decided that we will keep the user name aka user’s full name and email in the users table maintained by us. Sure we can do that for the new users. But we need to migrate all the existing user information from Supabase Auth into the users table. This is the main goal of this article.

Now you may ask why we are using a separate users table other than the one provided by Supabase and even when we are using the Supabase authentication. The reason is that we have multiple roles that can be assigned to a particular user and those users can be associated with a department. I know that we can maintain separate tables for this purpose, but since we are creating an MVP so we decided to keep things simple.

How the user info looks like

Now lets see where in the Supabase dashboard the users information is located. To view the user information you have to select a project under an organization in Supabase. From there you have to click the Authentication from the side menu. Under the authentication section the Users section will be selected by default. See the following 2 screenshots for reference.

Now if you click on the UID section of a particular user from the user list you can view all the information of that particular user. As you can see from the following screenshot we can see the UID of the selected user, when this user is created, when this user is last logged in etc. If you notice, there is a tab called Raw Json in this user info panel. This is where our main interest lies

If we open the Raw Json tab, we can see all the information of this user like email, role, this and that. If we scroll down a little bit then we can see a property called “raw_user_meta_data“. This is the property where email and user’s full name is located. We will leverage this property to copy all the necessary information of users. For our purpose we only want to copy the email and the full name. See the below two screenshot for reference.

View user info by sql query

Now our goal is to copy user information from Supabase auth to our managed Users table. There are two options to do that

• Manually copy and past each user information to our managed Users table

• Use SQL query to copy all the information at one go

Now obviously we don’t want to go down the manual path rather I prefer the SQL query approach. We have already seen how to view user information from Supabase auth using the UI interface. How can we do the same with SQL query ? Supabase ultimately uses a postgre table to store all the users information. So all we need to do is just write a query to read data from that table. That table is called auth.users. Lets run the following query using the SQL editor provided by Supabase.

select * from auth.users;

If we run this query we will get all the users row saved in the auth.users table where Supabase stores the user information. Here is what the result looks like

Now since we are only interested in user’s email and full name, lets run the following query to retrieve just those needed info.

SELECT
  id AS user_id,
  email,
  raw_user_meta_data ->> 'full_name' AS full_name
FROM auth.users;

If you remember from the previous section raw_user_meta_data is the property under Raw json where all the user information of our interest is located. By writing this raw_user_meta_data ->> 'full_name' AS full_name, we are basically retrieving the full_name from raw_user_meta_data . Here’s the result of this query.

Copy user info from auth to app database

Now that we can view user info from Supabase’s internal users table, it is easy peasy to copy and update all the existing user information from Supabase auth.users to our managed users table. Just run this following query

UPDATE users
SET
  email = auth.users.email,
  full_name = auth.users.raw_user_meta_data ->> 'full_name'
FROM auth.users
WHERE user_uuid = auth.users.id;

Here is the users information from our managed users table after running the above query

As we can see all the rows of existing users now have the email and full name associated with them.

Conclusion

This is merely very easy thing to do, the copy thing. I had written this article to future reference it and for my own sake so that I don’t have to ask chatgpt each time. That’s it for today. If you like this post subscribe to the newsletter. See you around in another piece of article.