Best practices for adding "username" to profiles table at signup?

[KevTale]

Hi!

I want to create a userflow where user fill the signup form that contains 3 inputs: username, email, password, then at submit it should insert a new row in the profiles table with his/her username.

I've followed the documentation on triggering a function that insert a row with the new id, which works well, but I don't know how to insert the username from the input as well.

I tried to do something like this:

function handleSignUp() {

  const { error, user } = await supabase.auth.signUp({ email, password });

  if (!error) {
    await supabase
        .from("profiles")
        .update({
          username,
        })
        .eq("id", user.id);
  }
}

In theory this should works well but I want to keep the profiles table secure so I added some policy to this table like (role() = 'authenticated':: text) for INSERT and UPDATE. But that does not make any sense because at that point the user is not logged in 🙀
What are the best practices for this functionnality which guarentee a secured profiles table? Should I add another policy instead of checking if user is authenticated?

Thanks!

[ichbinedgar]

Use Sign up with additional user meta data. method. However, there is not documentation about it.

const { user, session, error } = await supabase.auth.signUp(
  {
    email: '[email protected]',
    password: 'example-password',
  },
  {
    data: { 
      first_name: 'John', 
      age: 27,
    }
  }
)

But I found this way on discord , you need to modify the trigger function that handles the insert on your profile table (see documentation about how to create the trigger funnction).

similar to this:

begin
    insert into public.profiles (user_id, user_email, full_name, introduction, website_url, linkedin_url, twitter_handle, contact_method )
    values (new.id, new.email, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'introduction', new.raw_user_meta_data->>'website_url', new.raw_user_meta_data->>'linkedin_url', new.raw_user_meta_data->>'twitter_handle', new.raw_user_meta_data->>'contact_method');
    return new;
end;

[sctgraham]

I'm looking do to the same thing, did you find a working solution to this ?

[smilo7]

I didn't sadly sorry. I ended up settling for generating random usernames on signup and then encouraging the user to change it later. Let me know if you figure something out.

[adonis2611]

hey, so i just did this and it worked,

my id in the profile table is my foreign key.

this is my sign up

	let email = '[email protected]';
	let password = 'fsdwqewdasdf3';
	let name = 'testname';

	const signUp = async () => {
		try {
			const { error } = await supabase.auth.signUp(
				{
					email,
					password
				},
				{
					data: {
						name
					}
				}
			);

			if (error) throw error;
		} catch (e) {
			console.log(e.message);
		}
	};

this is my function

begin 
  insert into public.profiles(id, name) 
  values(new.id, new.raw_user_meta_data->>'name'); 

  return new; 
end;

[jasanpreetn9]

where do I add the function
begin insert into public.profiles(id, name) values(new.id, new.raw_user_meta_data->>'name'); return new; end;

[mephobic]

You go to database > Functions > Create New and add it there. Make sure you set the type to 'Trigger'

[chris23lngr]

Small addition to the solution

As @ichbinedgar mentioned, the best solution approach is probably to use functions and triggers. However, for me, and other people in the comments, an error 500 has occurred again and again. When trying to fix my code, I noticed two issues:

1. I want each username to be used only once. So I have set the column "username" in my profiles table to unique and not nullable. This caused me to throw an error every time a new user registered.

   So if you set the column to nullable and not unique, the whole thing works.

   However, the trade-off is that now you always have to query before registering a user whether the username is already taken or not to guarantee uniqueness.

2. I have had errors in my function code. Instead of new.raw_user_meta_data I just used raw_user_meta_data, which of course can't work.

After fixing these two issues, everything works fine for me. However, I think that the supabase documentation could be expanded a bit at this point, since I also had to spend a long time troubleshooting.

[devhandler]

@chris23lngr could you share how you fixed the 2 issues?

[chris23lngr]

@chris23lngr could you share how you fixed the 2 issues?

I haven't worked on the project in a while, but I'll take a look if I can still find the solution.

[devhandler]

thank you! the 2nd issue is a quick fix as you already shared. i'm not sure about the 1st one when the 'username' needs to be unique and not nullable (probably using 'check with' or some constraint to check its uniqueness i guess....) and the forward the error to the client if not met.

[nddr]

@devhandler, you would make the column 'Allow Nullable' in Supabase dashboard, or in your ORM.

[mephobic]

This is the solution that I found working in Feb, 2022 -

const { data, error } = await supabase.auth.signUp({
      email: userEmail.value,
      password: password,
      options: {
        data: {
          user_name: userName.value,
        },
      },
    });

Make sure you add 'options' before 'data' for the raw_user_meta_data field to be populated in auth.users

For anyone looking to check if the user has already signed in, this is a working code in React

      const { data, error } = await supabase.auth.signUp({
        email: formData.get('email'),
        password: formData.get('password'),
        options: {
          data: {
            first_name: formData.get('firstName'),
            last_name: formData.get('lastName'),
          }
        }
      })
      
      if(error) {
        setErrorMessage(error.message)
      } else {
        if(data?.user?.identities?.length === 0){
          setErrorMessage("User with email already exists")
        } else {
          window.location.href = 'dashboard'
        }
      }

[brendanfurtado]

For anyone that wants to add username and email, here is the process I used:
If early in the creation and want to restart the creation, drop the profiles table and handle_new_user cascade:

DROP table IF EXISTS profiles cascade;

DROP function IF EXISTS handle_new_user cascade;

Modifying the User Management Starter SQL that supabase provides you can include the email and username fields through this:

-- Create a table for public profiles

create table profiles (
  id uuid references auth.users on delete cascade not null primary key,
  updated_at timestamp with time zone,
  username text unique,
  email text unique,
  full_name text,
  avatar_url text,
  website text,

  constraint username_length check (char_length(username) >= 3)
);
-- Set up Row Level Security (RLS)
-- See https://supabase.com/docs/guides/auth/row-level-security for more details.
alter table profiles
  enable row level security;

create policy "Public profiles are viewable by everyone." on profiles
  for select using (true);

create policy "Users can insert their own profile." on profiles
  for insert with check (auth.uid() = id);

create policy "Users can update own profile." on profiles
  for update using (auth.uid() = id);

-- inserts a row into public users
create function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id, email, username, full_name, avatar_url)
  values (new.id, 
  new.raw_user_meta_data->>'email',
  new.raw_user_meta_data->>'username',
  new.raw_user_meta_data->>'full_name', 
  new.raw_user_meta_data->>'avatar_url');
  return new;
end;
$$ language plpgsql security definer;
-- trigger the function every time a user is cre∂ƒated
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

This allowed me to store username, and email into the DB with a simple signUp call in my form page like:

      const { data, error } = await signUp({
        email: emailRef.current?.value,
        password: passwordRef.current?.value,
        options: {
          data: {
            username: usernameRef.current?.value,
          },
        },
      });

[Meez-tech]

@brendanfurtado this works, thank you very much!

[pbell23]

I'm not sure I understand this strategy. Could you clarify certain points for me :

1. The on_auth_user_created trigger executes after any insertion in the auth.users table and will copy some metadata to the profiles table. But, if users are allowed to update their own profile, then the profiles table won't reflect what we have in the auth.users table. Shouldn't we prevent the profiles table to be updated and add an on_auth_user_updated trigger that will do the same as on_auth_user_created ? But if we do that, what's the point of the profiles table ? It could be replaced by a view selecting specific columns from auth.users.

2. In terms of security, how should we handle the raw_user_meta_data column from auth.users ? Can we add a constraint on auth.users.raw_user_meta_data and validate the json schema with pg_jsonschema or is it considered a bad practice to edit auth.users ? Should this be done on public.profiles table ?

[brendanfurtado]

@pbell23 , I'll answer best as I can but I'm not sure what your use-cases are...

1. The profiles table doesn't necessarily have to reflect what exists within the auth.users table. From my understanding, Auth from supabase (which includes the auth.users table), is a built-in service to help with Authorization and Authentication with our apps. The point of the profiles table, at least in what I'm building, is to add other user meta data. For example likes, address info, general profile info, etc (app related data that is relevant to each user/profile on the app). There is a connection between the two tables though. The ID field, a uuid, within the profiles table ideally should be the same as the auth.users.id within the auth.users table (anyone correct me if I'm wrong here).

2. That being said, edits within my app are done through the profiles table and auth.users just has some useful data that can be used throughout the app and in other tables (like the profiles table) such as roles, tokens, email, etc. There are probably other use cases, but I'm kind of a newbie to learning the ins and outs of supabase too

Let me know if any of this makes sense, cheers!

[Jalbers90]

@pbell23

is it considered a bad practice to edit auth.users?

I think the answer to this is yes, though it's not entirely clear to me. I don't feel the docs are in anyway complete on this https://supabase.com/docs/guides/auth/managing-user-data#accessing-user-metadata. Hence the ongoing 2 year old discussion, some best practices and example repos directly from the supabase would be really nice.

So the best practice seems to be what @brendanfurtado has written out. However I would not store any info in public.profiles that auth.users already contains, such as email.

[thecybergypsy]

Wonderful solution, but I want to create two types of user on two separate tables, for example public.profile and public.profile_2 with different data. Triggering a function isn't any use because this will only put the data onto a single databsase.
Any ideas?

[lsadehaan]

Do you mean table when you say database? You can just put the data into both tables in the trigger function...

[thecybergypsy]

I thought of using metadata, the two types of user coudl be signalled in metadata so that the function could choose between them, but metadata is limited to 16KB per user so I can't put much data there to insert into each table

[josephrmartinez]

While you can just add metadata during sign up as stated in the docs:

const { data, error } = await supabase.auth.signUp({
  email: '[email protected]',
  password: 'example-password',
  options: {
    data: {
      first_name: 'John',
      age: 27,
    },
  },
})

And then access this metadata later:

const {
  data: { user },
} = await supabase.auth.getUser()
let metadata = user.user_metadata

`Hello, ${metadata.first_name}!`

You may want to use the approach outlined by @brendanfurtado due to the ease around subsequently editing the profiles table. In this case, you would not need to edit the auth.users metadata.

[Polyterative]

I have migrated to the new version of api, the version 2, and there currently is no way to access the usernames of my users, is anyone able to help?
how do you get the usernames of your users?

previously I was getting it through the signing method,in the new version it is not possible to do so

[fraybabak]

thanks to @brendanfurtado for a clear solution.

I made a trigger before inserting into auth.users

create function public.validate_username_before_user_signup () returns trigger as $$
begin
IF EXISTS (
    SELECT 1
    FROM public.profiles
    WHERE username = new.raw_user_meta_data->>'username' 

) THEN
    RAISE EXCEPTION 'User with username % already exists!', new.raw_user_meta_data->>'username';
END IF;

  return new;
end;
$$ language plpgsql security definer;

and for triggering :

create trigger check_username_before_insert_to_auth_users
  before insert on auth.users
  for each row execute procedure public.validate_username_before_user_signup();

[L-Sypniewski]

One tip from me - I recommend using user_name metadata name instead of username - this way these triggers work out-of-the-box with Github auth (and presumably with the other 3rd party providers)

[amarnathtiwari07]

spra126