Is Inner join possible for a relation from public table to a custom schema table in the js client?

[sooqwaifr]

Let say I have a custom schema enums that contains a ctegory table:

create table enums.category (
    id smallint PRIMARY KEY UNIQUE,
    parent smallint references enums.category,
    name VARCHAR(100),
);

also we have a post with a fk relation to enums.category:

create table post (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    owner uuid references public.user not null,
    title VARCHAR (300) NOT NULL,
    category smallint references enums.category NOT NULL
);

when I tried::

let { data, error } = await supabase.from("post").select("*, category (*)");

response
{"message":"Could not find a relationship between 'post' and 'category' in the schema cache","hint":"Verify that 'post' and 'category' exist in the schema 'public' and that there is a foreign key relationship between them. If a new relationship was created, try reloading the schema cache."}

Is it possible to do the inner join and do select operation and filtering on relationships from a custom schema?

[steve-chavez]

It's not possible to do cross-schema joins directly for now - but you can create a view of enums.category into the public schema to make it work.

Basically:

CREATE VIEW public.category AS
SELECT * FROM enums.category

-- Extra step for making sure RLS applies to your view
ALTER VIEW public.category OWNER TO authenticated;

Then your JS snippet should work.

[pjerryhu]

also, will the original query automatically able to see the view?
let { data, error } = await supabase.from("post").select("*, category (*)");
Will the code be able to resolve category here?

For example, I created audit.users view for my public.users table, but my typescript code is still complaining

[pjerryhu]

I also tried to run

-- Extra step for making sure RLS applies to your view
ALTER VIEW audit.users OWNER TO authenticated;

but got error:

ERROR:  42501: permission denied for schema audit

[ConProgramming]

@steve-chavez Any update on this feature? Would be extremely helpful

[damien-schneider]

It seems Supabase is not enough mature for multiple schemas, I always have to find workarround on this or put everything in the public schema which is not ideal at all.

Hope this feature would come soon too !

[JamsheedMistri]

Having the same issue. My public.accounts has foreign primary key of id with table auth.users.

I would like to join public.accounts with auth.users, using this query:

const { data: accountsData, error: accountsError } = await supabase
	.from('accounts')
	.select('id, first_name, last_name, auth.users(email)')
	.eq('company_id', company_id);

But I get this error:

{
  code: 'PGRST100',
  details: 'unexpected "u" expecting "sum", "avg", "count", "max" or "min"',
  hint: null,
  message: '"failed to parse select parameter (id,first_name,last_name,auth.users(email))" (line 1, column 30)'
}

I tried users instead of auth.users and got this error:

{
  code: 'PGRST200',
  details: "Searched for a foreign key relationship between 'accounts' and 'users' in the schema 'public', but no matches were found.",
  hint: null,
  message: "Could not find a relationship between 'accounts' and 'users' in the schema cache"
}

[louisprp]

The simplest thing that I see often is to create a table public.users that reflects the auth.users table, e.g. when a user signs up their id is inserted here, along with possibly the first and last name, which can be retrieved from the sign up metadata, or any other arbitrary fields like username for that matter. One thing you might need to consider is that accounts can be deleted, so setting up a trigger that handles that. See here

[softmarshmallow]

Supabase lacks support on multi schema. event the studio (editor) resets the schema to public everytime it redirects.
As a supabase fan, this is the most thing that wastes my time :(

[damien-schneider]

+1

[yeonjulee1005]

any update here?

[Studentflow]

This is a very annoying problem and that strongly contributes to bad dx. I hope supabase fixes this soon or provide documentation on how to properly handle this.

[H0lz3r-x64]

still no fix in 2025

[brayandoria98]

Just happened to me, three years since the first question and no fix yet

[GaryAustin1]

It is still to use a view or rpc call.

There seems to be some work thinking about this: PostgREST/postgrest#2157

[KrunchMuffin]

you can do it in a function.