Pull through a Google profile on account creation with Supabase using only SQL

Supabase auth is great, very simple to get going with and add third-party oAuth providers such as Google.

For our app BoardShape (lightweight board management software for startups) we have a profile table in the public schema. We use a UUID field and when a user is created in Supabase we create a corresponding profile record with the same ID. So any entry in profiles represents a user managed by Supabase with the exact same ID. Our table definition looks like this:

CREATE TABLE public.profiles (
 id uuid NOT NULL,
 updated_at timestamptz NULL,
 avatar_url text NULL,
 first_name varchar NULL,
 last_name varchar NULL,
 email varchar NULL,
 has_set_password bool NOT NULL DEFAULT false,
 created_at timestamptz NULL DEFAULT now(),
 title text NULL,
 CONSTRAINT profiles_pkey PRIMARY KEY (id)
);

Simple table with a few details and we want to get as much as this from the initial account creation handled by Supabase. So, how do we do this and also copy over the user’s profile pic.

We add a function to our public schema (the schema that holds our tables not the supabase auth tables):

CREATE OR REPLACE FUNCTION public.create_profile_for_user()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
  INSERT INTO public.profiles (id, email, first_name, last_name, avatar_url, has_set_password)
  SELECT
    new.id,
    new.email,
    COALESCE(split_part(jsonb_extract_path_text(new.raw_user_meta_data, 'full_name'), ' ', 1), NULL),
    COALESCE(split_part(jsonb_extract_path_text(new.raw_user_meta_data, 'full_name'), ' ', 2), NULL),
    COALESCE(jsonb_extract_path_text(new.raw_user_meta_data, 'picture'), jsonb_extract_path_text(new.raw_user_meta_data, 'avatar_url'), NULL),
    CASE WHEN new.encrypted_password IS DISTINCT FROM '' THEN true ELSE false END;

  RETURN new;
END;
$function$
;

This is fairly straight forward but the lines for first_name, last_name and avatar_url are a bit icky because they come back from google in the raw_user_meta_data field in a json object. If we don’t get the data, we just insert nulls.

The bit at the end about new.encrypted password is because we just keep a boolean flag in our profiles table to determine if the user has a password set or not.

Note. You can use this exact same process for other oAuth providers but the details are likely not in the same place, so this trigger would need to be more involved to determine the oauth provider which you can pull from raw_app_meta_data on the auth.users table but again, it’s json, so you’ll have to extract it carefully.

Once you have this function you just need a trigger setup on the auth.users table as follows:

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

We also have an function and update trigger for recording the password being set or not like this:

CREATE OR REPLACE FUNCTION public.handle_update_user_password_status()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $function$
begin
update public.profiles
set has_set_password = CASE WHEN new.encrypted_password IS DISTINCT FROM '' THEN true ELSE false END
where id = new.id;
return new;
end;
$function$
;

create trigger update_user_password_status after
update
    of encrypted_password on
    auth.users for each row execute function handle_update_user_password_status();

Remember, when working with Supabase and postgres in general, don’t be afraid of triggers and functions. It takes a bit of a mental shift for some putting logic like this in the database but it’s fast, reliable and can really help with RLS as well.

Please head over to boardshape.com and ]try our product](https://boardshape.com/dashboard#signup). We’ve had a lot of fun working with Supabase and Next 13. You can also follow us on Twitter at /boardshape.