Self-referencing fk not recognized in Supabase query 🐛

[sarah-vanderlaan]

When querying a table with a self-referencing foreign key using Supabase’s JavaScript client, the expected relational data is not returned. Instead, the returned object is empty, even though the related data exists in the database.

const { data, error } = await supabase
  .from("pp_user")
  .select(`
    id, 
    first_name, 
    last_name, 
    crewboss:pp_user(id, first_name, last_name)
  `)
  .eq("id", id)
  .single();

Observed Behavior:

The crewboss field is an empty array instead of returning the related user.

Expected Beahvior:

{
"id": "11111111-1111-1111-1111-111111111111",
"first_name": "John",
"last_name": "Doe",
"crewboss": {
  "id": "22222222-2222-2222-2222-222222222222",
  "first_name": "Captain",
  "last_name": "Jack"
}
}

Additional Debugging Findings

• The foreign key exists in the database (pp_user.crewboss_id → pp_user.id).
• Running this SQL query correctly returns the expected results
• Also tried to do crewboss:pp_user!pp_user_crewboss_id_fkey(id, first_name, last_name) but received error :

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

[sarah-vanderlaan]

opened github issue in supabase-js supabase/supabase-js#1370