getting auth.users table data in supabase

Question

I created a recipes table in supabase with a foreign key of user_id inside it that is getting from supabase build in users table auth.user.id now I'm trying to get all the recipes with the date of the user (user_id in recipe table):

export async function getRecipes() {
  const { data, error } = await supabase.from("recipes").select("*,users(*)");

  if (error) {
    console.error(error);
    throw new Error("Recipes could not be loaded");
  }

  return data;
}

but i am getting this error details : "unexpected \"u\" expecting \"sum\", \"avg\", \"count\", \"max\" or \"min\"" message : "\"failed to parse select parameter (*,auth.users(*))

why is that happening? how can i get the data of the user?

Answer 1

What I would recommend is to create a public.users table that 'represents' some data from auth.users. You can do this easily using trigger.

As an example, I want to only have email and id on my public.users table because other information is not relevant.

You can copy paste this code (new query) on SQL Editor on the Supabase dashboard:

CREATE TABLE USERS (
  id uuid references auth.users not null primary key,
  email text
);
create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.users (id, email)
  values (new.id, new.email);
  return new;
end;
$$ language plpgsql security definer;

create trigger on_new_user
after insert on auth.users for each row
execute procedure public.handle_new_user ();

Next, try to register a new user and you will automatically see this new user shows BOTH on auth.users and public.users. You can then request to the public.users table using the javascript library :)