`select()` with reverse relation returns null unless explicit foreign key is defined (but forward query works fine)

[ivanasetiawan]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Hi Supabase team 👋

I'm encountering a problem with .select() and nested relationships using Supabase JS.

When I run the following query from the inventory_product table:

const { data, error } = await supabase
  .from('inventory_product')
  .select(`
    pbn_id,
    uuid,
    lot_id,
    hash_id,
    date_received,
    product: product_id(name),
    product_sub: product_subvariant_id(name),
    tag,
    label: label_box_single(uuid)
  `)
  .eq('company_id', session?.user.app_metadata?.company_id)
  .order('date_created', { ascending: false })
  .limit(100);

It returns null for label_box_single on production, even though there are related rows. (local works fine).

However, if I run the inverse query from label_box_single:

const { data, error } = await supabase
  .from('label_box_single')
  .select(`
    inventory_product_uuid(
      pbn_id,
      uuid,
      lot_id,
      hash_id,
      date_received,
      product: product_id(name),
      product_sub: product_subvariant_id(name),
      tag
    )
  `)
  .eq('company_id', session?.user.app_metadata?.company_id)
  .order('date_created', { ascending: false })
  .limit(100);

I assume this is because the foreign key only exists from label_box_single.inventory_product_uuid → inventory_product.uuid, and not the other way around.

Since I can’t create a reverse FK (there's no single row on the inventory_product side), I can’t make a bidirectional relationship via constraints. Is there any way to make Supabase JS resolve this correctly when querying from the parent side?

Would love guidance on how to handle this case.

Thanks in advance!

To Reproduce

1. Create two tables:

-- inventory_product
create table inventory_product (
  uuid uuid primary key,
  pbn_id text,
  company_id bigint
);

-- label_box_single
create table label_box_single (
  uuid uuid primary key,
  inventory_product_uuid uuid references inventory_product (uuid),
  company_id bigint
);

2. Insert a row into inventory_product:

insert into inventory_product (uuid, pbn_id, company_id)
values ('11111111-1111-1111-1111-111111111111', 'PBN123', 1);

3. Insert related rows into label_box_single:

insert into label_box_single (uuid, inventory_product_uuid, company_id)
values 
  ('22222222-2222-2222-2222-222222222222', '11111111-1111-1111-1111-111111111111', 1),
  ('33333333-3333-3333-3333-333333333333', '11111111-1111-1111-1111-111111111111', 1);

4. Query label_box_single with a nested inventory_product field (✅ works):

supabase
  .from('label_box_single')
  .select(`
    inventory_product_uuid (
      pbn_id
    )
  `)
  .eq('company_id', 1);

5. Query inventory_product with a nested label_box_single field (❌ returns null):

supabase
  .from('inventory_product')
  .select(`
    pbn_id,
    label_box_single (uuid)
  `)
  .eq('company_id', 1);

Expected behavior

Since there is a valid foreign key from label_box_single.inventory_product_uuid → inventory_product.uuid, I expect Supabase to infer the reverse relation (or allow a way to manually define it) so that:

.from('inventory_product')
.select(`
  label_box_single (uuid)
`)

…returns all label_box_single rows that reference that product — instead of null.
Ideally, Supabase should either:
Automatically infer the reverse relationship from the FK (as some ORMs do), or
Allow a manual declaration in the UI or via metadata so this nested select is possible without a physical reverse FK.

System information

• Version of supabase-js: 2.39.7
• Version of Node.js: 22.12.0

[laurenceisla]

Hi Supabase team 👋

Hey!

I assume this is because the foreign key only exists from label_box_single.inventory_product_uuid → inventory_product.uuid, and not the other way around.

This is detected by default, so it should work as you expect.

5. Query inventory_product with a nested label_box_single field (❌ returns null):

Hmm, I cannot reproduce it, I get a response for this request:

let {data, error} = await supabase
  .from('inventory_product')
  .select(`
    pbn_id,
    label_box_single (uuid)
  `)
  .eq('company_id', 1);

console.log("data: ", JSON.stringify(data))
console.log("error: ", error)

data:  [{"pbn_id":"PBN123","label_box_single":[{"uuid":"22222222-2222-2222-2222-222222222222"},{"uuid":"33333333-3333-3333-3333-333333333333"}]}]
error:  null

If you get a null response, then you're getting an error, probably a disambiguation one. Can you post the error here to check?

[amrithasnidhi]

Hi Supabase team👋

I'm encountering this same issue when using .select() with nested reverse relationships.

when i did it :
label_box_single.inventory_product_uuid → inventory_product.uuid is defined as a Foreign Key.

Querying from label_box_single and nesting inventory_product works ✅ but querying from inventory_product and nesting label_box_single returns null ❌
As this relationship is one-to-many and there's no Foreign Key going the other way, Supabase doesn't infer the reverse relation.

My question :
Is there any way currently to manually define reverse relationships in Supabase JS or via the Supabase dashboard, so we can perform nested .select() queries from the parent side?

I'd love to contribute in any way I can , if there's a particular direction you'd recommend for contributors to explore (whether it's fixing this behavior, improving docs, or writing tests), I'd be happy to take a stab at it!
Thanks!

[laurenceisla]

Hey @amrithasnidhi.

As this relationship is one-to-many and there's no Foreign Key going the other way, Supabase doesn't infer the reverse relation.

This isn't expected, the reverse relation should be inferred by default. Can you please provide the query and error message you're getting to better debug this issue? (see my previous comment for an example).