Typings: Foreign tables are wrongly marked as possibly null when cascade deletion enforces existence

[quick007]

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

If you have an API call that also references values in a foreign table, supabase will return value | null (value is just a placeholder) even if database rules ensure it can't be null.

To Reproduce

In this example, I'll have a profiles table and a profiles_classes table. Profiles teams is a merge table with a composite key (not sure if that matters at all), where profile_id references the id in the profiles table. Importantly, I have a rule like this set up:

If you didn't catch it in that screenshot, it says: "Cascade: Deleting a record from public.profiles will also delete any records that reference it in this table". In simple terms, that means if I fetch columns in profiles_classes and then reference ones inside of profiles, the reference has to exist.

This API call: .select("profiles_classes").from("profiles!profiles_classes_profile_id_fkey(id)")

Should have a typing of:

Yet, the typing looks like this: (see the null?)

I realize this is a bit petty, but it's an issue nonetheless. Granted, it can be fixed on my end with an exclamation mark, but I try to avoid using assertions like that.

Expected behavior

It should return the type without the null.

Screenshots

See above

System information

• OS: WIndows 11
• Version of supabase-js: 2.45.0
• Version of Node.js: 22.0.0

Additional context

n/a

[jdgamble555]

I believe this is another duplicate of #546

[quick007]

It's similar and involves the same type, but is asking for another part of the type to be fixed. It's not related to single/multiple returns.

[jdgamble555]

I think your issue is the composite key. Can you share your full schema in SQL?

[quick007]

Unfortunately I can't, sorry. I still think you're misunderstanding my issue though, it has to do with typings being marked as null when they cannot be null. It's a very niche situation I don't think most people will run into.

[jdgamble555]

Ok, so assuming composite key is not the problem here.

Given this:

create table profiles(
    id uuid PRIMARY KEY DEFAULT gen_random_uuid()
);

create table profiles_classes(
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    profile_id uuid NOT NULL REFERENCES profiles(id) ON DELETE CASCADE
);

and the query you have:

.from('profiles_classes').select('profiles!profiles_classes_profile_id_fkey(id)');

gives the same result as:

.from('profiles_classes').select('profiles(id)');

Which does in fact add the potential null. However, this is the exact same problem as my test1, so therefore, this should be closed as to not have too many issues open with the same problem.

(You're coming from the table with the FK and going to the table without it.)

---

Note: A workaround in the meantime for this specific problem:

.from('profiles_classes').select('profiles!inner(id)');

J

[quick007]

Ah ok, that makes sense. I did find that issue when I was looking for potential similarities, and ofc you posted it above as well. Didn't take the time to read through all the comments, but, since you've highlighted it there (among similar issues, which there seem to be a lot of), I'll go ahead and close this one