CODEWITHSUNDEEP
supabasepostgrest
jameshfisher
jameshfisher

Reputation: 36559

Supabase PostgREST query returns null for all referenced rows

I'm making this API request, with the PostgREST query game?select=id,club(slug):

https://MY_DB.supabase.co/rest/v1/game?select=id,club(slug)&apikey=MY_API_KEY

I get this response:

[{"id":"b4771c4b-e8a6-425d-8720-f53a9f93af77","club":null}, 
 {"id":"df5d0c95-47bc-4db3-b29b-eed7d4b286c1","club":null}, 
 {"id":"c9d1b696-5a44-4097-89e1-da2e9b49b7d5","club":null}]

All returned rows have "club":null, but this is unexpected. Each of those rows has club_id=1, which references the id column of the club table. The response should therefore be:

[{"id":"b4771c4b-e8a6-425d-8720-f53a9f93af77","club":{"slug":"club1"}}, 
 {"id":"df5d0c95-47bc-4db3-b29b-eed7d4b286c1","club":{"slug":"club1"}}, 
 {"id":"c9d1b696-5a44-4097-89e1-da2e9b49b7d5","club":{"slug":"club1"}}]

Here is my database:

postgres=> select id, club_id from game;
                  id                  | club_id
--------------------------------------+---------
 b4771c4b-e8a6-425d-8720-f53a9f93af77 |       1
 df5d0c95-47bc-4db3-b29b-eed7d4b286c1 |       1
 c9d1b696-5a44-4097-89e1-da2e9b49b7d5 |       1
(3 rows)

postgres=> select id, slug from club;
 id |     slug
----+---------------
  1 | club1
...
(4 rows)

postgres=> \d game
...
Foreign-key constraints:
    "game_club_id_fkey" FOREIGN KEY (club_id) REFERENCES club(id)
...

My understanding/guess is that the Supabase query game?select=id,club(slug) will magically detect the game_club_id_fkey constraint. Using this, it should generate a SQL query like:

postgres=> select g.id, c.slug from game g join club c on g.club_id = c.id;
                  id                  |   slug
--------------------------------------+-----------
 b4771c4b-e8a6-425d-8720-f53a9f93af77 | club1
 df5d0c95-47bc-4db3-b29b-eed7d4b286c1 | club1
 c9d1b696-5a44-4097-89e1-da2e9b49b7d5 | club1
(3 rows)

Clearly this is not what is happening. But how do I debug this?

I've tried to find the specification for the query language game?select=id,club(slug). But where are those docs? All I can see is https://supabase.com/docs/reference/javascript/select, which is just a list of examples with no explanation.

I've tried to find logs, which might show the underlying SQL query. I tried https://supabase.com/dashboard/project/MY_DB/logs/postgrest-logs, but this just shows "No results".

Where is the spec for the PostgREST query language? What can cause null referenced rows? How do I see the generated SQL? How do I debug a Supabase/PostgREST query?

Upvotes: 1

Views: 626

Answers (1)

jameshfisher
jameshfisher

Reputation: 36559

The problem was row-level security. The policies for the club table were not allowing access. I fixed this by adding a new policy allowing anonymous read access to the club table.

The underlying "debuggability" problem is still unsolved. This would have been much faster if I were able to see and run SQL, instead of fighting an undocumented custom query language.

Upvotes: 2

Related Questions