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?

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?

• javascript
• reactjs
• supabase

3 Answers 3

What I would remend 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 :)

You cannot make requests to the auth.users table using the javascript library. This is not allowed as the auth.users table contains sensitive data and you wouldn't want this exposed to your client side code. You should create a table in your public schema with the data you want access to, you can take a look at this documentation page which discusses this https://supabase./docs/guides/auth/managing-user-data

You can update the above mentioned trigger by @iv444 to also include the metadata. This trigger will copy the data added to auth.users to public.users

CREATE TABLE USERS (
id uuid REFERENCES auth.users NOT NULL PRIMARY KEY,
email text,
fullname text
);

-- Update the trigger function to handle fullname
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
DECLARE
full_name text;

BEGIN
-- Extract the fullname from the user_metadata
full_name := (new.raw_user_meta_data ->> 'full_name');

-- Insert the new user into the USERS table
INSERT INTO public.USERS (id, email, fullname)
VALUES (new.id, new.email, full_name);

RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create or recreate the trigger
DROP TRIGGER IF EXISTS on_new_user ON auth.users;

CREATE TRIGGER on_new_user
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE PROCEDURE public.handle_new_user();`