Embedded resource types ignore !inner / is not null

[gwax]

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

When performing a many-to-one join in supabase using either !inner or .not('reference', 'is', null), the returned type is T | null but should be T.

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

Taking the example tables:

create table countries (
  "id" serial primary key,
  "name" text
);

create table cities (
  "id" serial primary key,
  "name" text,
  "country_id" int references "countries"
);

and then create a number of queries against supabase-js using Typescript:

const query1 = await supabase.from('cities').select(`name, countries (name)`);
const query2 = await supabase.from('cities').select(`name, countries!inner (name)`);
const query3 = await supabase.from('cities').select(`name, countries (name)`).not('countries', 'is', null);
const query4 = await supabase.from('cities').select(`name, countries!inner (name)`).not('countries', 'is', null);

Expected behavior

Resulting typescript types should be:

type Query1Type = {
  name: string;
  countries: {
    name: string;
  } | null;  // <- This is debatably nullable given postgres constraints but reasonable
}[] | null;

type Query2Type = {
  name: string;
  countries: {
    name: string;
  };  // <- This should absolutely not be nullable
}[] | null;

type Query3Type = {
  name: string;
  countries: {
    name: string;
  };  // <- This should absolutely not be nullable
}[] | null;

type Query4Type = {
  name: string;
  countries: {
    name: string;
  };  // <- This should absolutely not be nullable
}[] | null;

In all cases, where I have labeled "This should absolutely not be nullable", supabase-js returns a nullable type

Screenshots

If applicable, add screenshots to help explain your problem.

System information

• OS: macOS
• Browser: VSCode TypeScript engine
• Version of supabase-js: 2.39.1
• Version of Node.js: 20.9.0

Additional context

Add any other context about the problem here.

[masda70]

I believe this issue significantly impacts the usability of type inference for queries that involve nested resources, particularly in scenarios where the nested resource's presence is essential for the integrity of the returned data.

To elaborate, even when a database schema explicitly defines a foreign key (e.g., country_id) as NOT NULL, the type of Query1Type.countries could still be nullable. This situation arises not from the database schema itself but from the potential limitations of the client's permissions making the query. Therefore, unless the framework acknowledges the client's permissions level—specifically, whether it operates with sufficient privileges (such as a service role client)—or allows field-specific permissions overrides, marking countries as nullable remains technically correct.

However, the !inner and is not null constructs currently serve as mechanisms to enforce non-nullability constraints on a per-field basis, implicitly handling permission constraints by excluding parent objects lacking sufficient permissions. This approach, while functional, could lead to verbose query syntax due to the frequent need to specify !inner, especially in complex queries where non-nullability is a common requirement.

One potential improvement could be introducing a mechanism to set !inner as the default behavior for certain queries or within the context of specific clients or roles. This change would streamline query construction without sacrificing the flexibility or security model provided by the current permission system.

Considering that !inner aligns with the typical default behavior for Postgres queries, adopting it as a default in our context seems logical. However, this approach might diverge due to choices made in the PostgREST library, which might not set !inner as the default. While I am not fully aware of all the implications such a default behavior might entail, especially when exceptions are necessary for specific fields, I believe it is an idea worth exploring for its potential to simplify query syntax and enhance overall usability.

[chesterhow]

Facing this on my end as well. Seems like a regression though. This issue was supposedly fixed last august supabase/postgrest-js#456

[avallete]

Hey everyone !

We've reworked the result inference in postgrest-js to address the typing issues mentioned above. The changes are available in a canary release, and we're actively seeking your feedback.

To test it out, update your supabase-js to version 2.46.0-rc.1 (which includes postgrest-js 1.17.0-rc.1):

npm install supabase-js@2.46.0-rc.1

Please let us know if you encounter any issues, especially with retro-compatibility, so we can finalize it for the next release.

[IPJT]

@avallete -
Hi,
Just upgraded supabase-js from 2.45.6 => 2.46.0-rc.1

It solved one issue I was having with the type inference but it created another one that I didn't have in 2.45.6.

Solved Issue:

• In 2.45.6 when spreading columns fetched based on a nullable foreign key (many-to-one) the flattened columns were not typed as | null even though the FK was nullable.
• In 2.46.0-rc.1 the inferred types for the spreaded columns were nullable. Great!

New Issue:

• In 2.45.6 the types were correctly inferred for many-to-many relationships

- In `2.46.0-rc.1` the types were correctly inferred for many-to-many relationships this is no longer the case

Let me know if you need any additional info to debug :) GL!

[avallete]

Let me know if you need any additional info to debug :) GL!

Hey @IPJT !

Thanks for the feedback, a few questions to help with debugging this out:

1. Did you regenerated your database types using the latest cli version ? We had a bug previously in introspections results where some cross-schemas foreign keys in relationships duplicating themselves, if there is such duplicates it might be the reason of the error (cf for details)
2. Otherwise, would it be possible for you to link your Database types definitions or a subset of it for both profiles and organizations tables, including the tables/columns and relationships. So I can try to reproduce and debug this issue on my end.

Also I would not expect you to get those ... in the SelectQueryError but an actual error message, so that's the intended DX. I'll see if I can reproduce this as well.

Meanwhile if you do something like:

const async testFunction() {
  const result = await supabase.from('profiles').select('*, organizations(*)').single()
  if (result.data) {
    const value = result.data.organizations['error']
  }
}

Do you get a more useful infos in the value type ?

[IPJT]

Hi @avallete - Thanks for your reply

1. Did you regenerated your database types using the latest cli version ? We had a bug previously in introspections results where some cross-schemas foreign keys in relationships duplicating themselves, if there is such duplicates it might be the reason of the error (cf for details)

Yeah, I removed the types and regenerated them after upgrading to the latest cli version. Didn't help though.

2. Otherwise, would it be possible for you to link your Database types definitions or a subset of it for both profiles and organizations tables, including the tables/columns and relationships. So I can try to reproduce and debug this issue on my end.

Would it be possible for me to just give you access to our Supabase project where the full schema is deployed? Nothing secret in there :P

Do you get a more useful infos in the value type?

The inferred type for result.data.organizations is: organizations: SelectQueryError<"could not find the relation">

As seen by the logs in the image the runtime behaviour is as expected, it does indeed find the relation. Would be happy to get on a call with you if that would in any way help you debug this.

[avallete]

Hey @IPJT !

Thank's for all those additional details.

Would it be possible for me to just give you access to our Supabase project where the full schema is deployed? Nothing secret in there :P

Yes that would be the best way to debug this I think. If that's okay for you we have a procedure for this, you need to fill up a support ticket (here: https://supabase.com/dashboard/support/new)

And tick the Allow Supabase Support to access your project temporarily checkbox. You can simply link to this comment in the message. That way, I will be able to access your project schema and debug this issue much better.

[IPJT]

Hey @IPJT !

Thank's for all those additional details.

@avallete - ofc, a support ticket has been submitted. I gave you access to the project and linked to your last comment on this issue :)

Best of luck!

[avallete]

Hey @IPJT !
Thank's for all those additional details.

@avallete - ofc, a support ticket has been submitted. I gave you access to the project and linked to your last comment on this issue :)

Best of luck!

Thank's to you, this should now be fixed ! Thanks for reporting and providing all details for debugging 👍

[avallete]

The fix has been released in supabase-js v2.46.0. I'm closing this issue, but feel free to reopen if you encounter any further errors.

[IPJT]

Works like a charm. Thanks bud! @avallete