Unable to query foreign table using supabase-js

Question

I'm trying to use the supabase-js package (with next js) to query data in my supabase postgres DB. I have set up 2 tables. One (users) with a foreign key referencing another (organisations). I've tried querying the data in the following ways:

const { data, error } = await supabase.from('users').select(`
  *,
  organisations (
    *
  )
`);

// or

const { data, error } = await supabase.from('users').select('*, organisations(*)');

The supabase object above is created using createServerComponentClient from @supabase/auth-helpers-nextjs.

But I always get null back for data.organisations and for error. The rest of the user data is there.

Also, if I query the data directly using an SQL join (... JOIN organisations ON users.organisation_id = organisations.id) I am able to fetch the organisation, so I know that the foreign key reference is working in the database. Can someone tell me what I'm doing wrong?

"next": "13.4.19",
"@supabase/supabase-js": "^2.37.0",
"@supabase/auth-helpers-nextjs": "^0.8.1"

Next.js is a server-side rendering (SSR) framework, so this means that you can't just use the normal @supabase/supabase-js with it in order to get the fullness of the framework. You need to use the @supabase/auth-helpers-nextjs, going through the getting started here will get you up to speed with the auth-helpers and nextjs supabase.com/docs/guides/getting-started/tutorials/with-nextjs — Andrew Smith
– Andrew Smith, Commented Oct 5, 2023 at 8:53
Hi Andrew, thanks for the reply! I am using the auth-helpers-nextjs package, but that in turn is using the supabase-js package. I should have made that more clear. I'm using createServerComponentClient from @supabase/auth-helpers-nextjs (version: ^0.8.1) to create the client that I am using the code in the original question. — Haydn Morris
– Haydn Morris, Commented Oct 6, 2023 at 9:02

Haydn Morris · Accepted Answer · 2023-10-06 13:29:54Z

1

In the end the issue was not related to @supabase/supabase-js or @supabase/auth-helpers-nextjs, but the row-level-security (RLS) settings on the table in the database.

When creating a new table via the Studio RLS is set to true by default. If you don't then apply any RLS rules it is not possible to access the data. When I added a rule to allow anyone to read that table and the query worked.

answered Oct 6, 2023 at 13:29

Haydn Morris

838 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sebastian Over a year ago

omg! ty. i wish i had found your post faster. it would have saved me 4 hours of "shouting in disbelieve at he docs"

Suvasish Majumder · Accepted Answer · 2025-04-22 06:58:42Z

0

Disable the RLS on both the referencing table and the refrenced table for now. It will solve this problem temporarily

answered Apr 22 at 6:58

Suvasish Majumder

1

Collectives™ on Stack Overflow

Unable to query foreign table using supabase-js

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related