Database typings are not correct on table join for entity to one relationship

[niels-bosman]

Bug report

Describe the bug

Typings are not correct when joining tables that are always NOT an array. For example, in my application we use the following query to fetch data about figma_comments and link to figma_users and figma_files:

const { data: comments } = await supabase
    .from('figma_comments')
    .select(
      'created_at, resolved_at, comment, node_id, user:figma_users(handle, img_url, email), file:figma_files(file_key, name)',
    )
    .order('created_at', { ascending: false });

From this query, I would expect the built in typings to have both user and file to be an entity of a singular user and a singular file. Since the constraints I set up in Postgres won’t allow it to be multiple entries because the foreign key is unique on the joinable tables.

Here is an overview of the types being generated by supabase CLI:

Tables: {
      figma_comments: {
        Row: {
          comment: string;
          created_at: string;
          figma_user_id: string;
          file_key: string;
          id: number;
          node_id: string;
          order_id: number | null;
          parent_id: string | null;
          resolved_at: string | null;
        };
        Insert: {
          comment: string;
          created_at?: string;
          figma_user_id: string;
          file_key: string;
          id?: number;
          node_id: string;
          order_id?: number | null;
          parent_id?: string | null;
          resolved_at?: string | null;
        };
        Update: {
          comment?: string;
          created_at?: string;
          figma_user_id?: string;
          file_key?: string;
          id?: number;
          node_id?: string;
          order_id?: number | null;
          parent_id?: string | null;
          resolved_at?: string | null;
        };
      };
      figma_files: {
        Row: {
          editor_type: string | null;
          figma_project_id: number | null;
          file_key: string;
          id: number;
          last_modified: string;
          name: string;
          thumbnail_url: string;
          version: string | null;
        };
        Insert: {
          editor_type?: string | null;
          figma_project_id?: number | null;
          file_key: string;
          id?: number;
          last_modified: string;
          name: string;
          thumbnail_url: string;
          version?: string | null;
        };
        Update: {
          editor_type?: string | null;
          figma_project_id?: number | null;
          file_key?: string;
          id?: number;
          last_modified?: string;
          name?: string;
          thumbnail_url?: string;
          version?: string | null;
        };
      };
      figma_users: {
        Row: {
          email: string;
          figma_id: string;
          handle: string;
          id: number;
          img_url: string;
        };
        Insert: {
          email: string;
          figma_id: string;
          handle: string;
          id?: number;
          img_url: string;
        };
        Update: {
          email?: string;
          figma_id?: string;
          handle?: string;
          id?: number;
          img_url?: string;
        };
      };
    };

Expected behavior

I would expect the built in typings to know that user and file in the case of my query is an object if the entity, not an array of objects. This is because figma_files.file_key and figma_users.figma_id are set to UNIQUE.

I understand that making this work might be extremely difficult, but does anyone have any suggestions on how to make the typings work for me so I do not have to cast these types?

System information

• OS: macOS
• Version of supabase-js: 2.10.0
• Version of Node.js: v16.13.0

[selbyk]

👍

And also looking for an elegant way to deal with all the {} | {}[] | null in the types.

[niels-bosman]

Hey @selbyk, I managed to fix it for now by chaining with the .returns<T>(); method. This will overwrite the type that is returned (so it is not really that safe).

type Tables = IDatabase['public']['Tables'];
type TeamsTable = Tables['figma_teams']['Row'];
type FigmaUsersTable = Tables['figma_users']['Row'];

export type Team = {
  team: {
    id: TeamsTable['id'];
    name: TeamsTable['name'];
    projects: [
      {
        files: [
          {
            comments: [
              {
                member: { img_url: FigmaUsersTable['img_url'] };
              },
            ];
          },
        ];
      },
    ];
  };
};

const { data, error } = await (client ?? supabase)
    .from('user_has_figma_team')
    .select(
      `team:figma_teams(
         id,
         name,
         projects:figma_projects(
           files:figma_files(
             comments:figma_comments(
               member:figma_users(img_url)
             )
           )
         )
      )`,
    )
    .eq('user_id', user?.id)
    .returns<Team[]>()
    .order('created_at', { foreignTable: 'figma_teams', ascending: false });

[imownbey]

I am seeing the return types be correct as the #223 states, but the typescript types are still totally ambiguous between array or single response which makes the return types somehow even less useful? Is there a way to get the types to match the return type?

[imownbey]

Ah I see #303 is really where the discussion on this is happening.

[arinmirza]

Ah I see #303 is really where the discussion on this is happening.

That issue was recently closed in favor of this one.

👍

And also looking for an elegant way to deal with all the {} | {}[] | null in the types.

Any updates on this?

Apart from the original problem described in this issue, selecting from a foreign table returns the type {} | {}[] | null for that field, although it must be strictly {}[].

Consider the scenario where we have two tables called students and books, and we have 1:N relationship between these two. The books table has a foreign key referring to the primary key of the students table. So we expect to see multiple books associated with a single student.

const { data } = await supabase.from("students").select("*, books (*)");

• The result of this query must have an array type, that is {}[].
• It cannot be a single object (without an array), that is {}.
• The type of the field books must be an array, that is {}[].
• The type of the field books cannot be a single object, that is {}.
• I assume it also cannot be null, as the database returned a response.

Can I safely assume that these points hold true?

If so, I will use a workaround and override these types, until a fix for this problem is implemented.

[albertonii]

Any updated with this issue?

[albertonii]

I found temporary fix in this discussion comment:

https://github.com/orgs/supabase/discussions/7610#discussioncomment-7517390

[logemann]

This workaround doesnt change anything for me. Weird. Still a super annoying bug which makes using types with supabase nearly impossible. This bug needs more love from the supabase team. because you know.... 1:1 relations are not that uncommon to say the least.

In case the supabase team is looking into this, i ve mentioned the exact same problem in Discord. I replay here:

---

somehow the supabase types generator, generates 1:n types where my DB design has a 1:1 mapping.

Following table structure

Person                      Address
------                      -------
id                          id (PK)
name                        street
address_id (FK)

const myQuery = supabase
    .from("Person")
    .select("name, Address(street)");

    
type PersonWithAddress = QueryData<typeof myQuery>;
const { data: persons, error} = await myQuery;
const personsTyped: PersonWithAddress = persons;

This is basically the example as shown in the supabase docs.
The correct results looks like this when debugging:

{
  id: 1,
  name: "Peter",
  Address: { id: 1, street: "MyStreet" }
}

Now the weird part. The type PersonWithAddress looks like this:

const personsTyped: {id: any, name: any, Address: {id: any, street: any}[]}[]

Look at the array just behind the "Address" type (not the outer array, which is the result set array). Address cant be an array because its a 1:1 relation. This means, when mapping through the array like this:

{personsTyped!.map((person) => (
    <div>{person.Address.id}</div>
)}

is wrong according to typescript linter because he expects: person.Address[0].id. During runtime of course, i can only do person.Address.id to get data.

When looking at my generated typescript file:

Relationships: [
          {
            foreignKeyName: "Person_Address_fkey"
            columns: ["address_id"]
            isOneToOne: false
            referencedRelation: "Address"
            referencedColumns: ["id"]
          },

Why is "OneToOne" false ?? The address_id (FK field) is a number column. This definitely should be true right? Even if i set it to true (manually, which is.a non starter anyhow), it doesnt change the outcome of the linter.

[hawkcookie]

This workaround doesnt change anything for me. Weird. Still a super annoying bug which makes using types with supabase nearly impossible. This bug needs more love from the supabase team. because you know.... 1:1 relations are not that uncommon to say the least.

In case the supabase team is looking into this, i ve mentioned the exact same problem in Discord. I replay here:

somehow the supabase types generator, generates 1:n types where my DB design has a 1:1 mapping.

Following table structure

Person                      Address
------                      -------
id                          id (PK)
name                        street
address_id (FK)

const myQuery = supabase
    .from("Person")
    .select("name, Address(street)");

    
type PersonWithAddress = QueryData<typeof myQuery>;
const { data: persons, error} = await myQuery;
const personsTyped: PersonWithAddress = persons;

This is basically the example as shown in the supabase docs. The correct results looks like this when debugging:

{
  id: 1,
  name: "Peter",
  Address: { id: 1, street: "MyStreet" }
}

Now the weird part. The type PersonWithAddress looks like this:

const personsTyped: {id: any, name: any, Address: {id: any, street: any}[]}[]

Look at the array just behind the "Address" type (not the outer array, which is the result set array). Address cant be an array because its a 1:1 relation. This means, when mapping through the array like this:

{personsTyped!.map((person) => (
    <div>{person.Address.id}</div>
)}

is wrong according to typescript linter because he expects: person.Address[0].id. During runtime of course, i can only do person.Address.id to get data.

When looking at my generated typescript file:

Relationships: [
          {
            foreignKeyName: "Person_Address_fkey"
            columns: ["address_id"]
            isOneToOne: false
            referencedRelation: "Address"
            referencedColumns: ["id"]
          },

Why is "OneToOne" false ?? The address_id (FK field) is a number column. This definitely should be true right? Even if i set it to true (manually, which is.a non starter anyhow), it doesnt change the outcome of the linter.

@logemann

What version of supabase-js are you using? There was a bug previously, but it was fixed in postgrest-js v1.9.0. Then, postgrest-js v1.9.0 was incorporated into the release of supabase-js v2.39.2.

https://github.com/supabase/postgrest-js/releases/tag/v1.9.0
https://github.com/supabase/supabase-js/releases/tag/v2.39.2

And, how are you generating the schema through supabase cli? There was such an exchange in this issue.

#303 (comment)

I'm doing fine with the following cli command:
supabase gen types typescript --local > schema.ts

plz also check supabse cli version.

[logemann]

oh you are right! I was on SDK 2.39.0 ... so slightly before the fix. Damn ;-) I should have upgraded before asking. Thanks for pointing out.

I tested my code again and now all works as expected.

[ardabeyazoglu]

I updated supabase-cli and supabase-js to latest versions and the problem still persists.
Generated type relation is still not one-to-one (supabase gen types typescript --linked --schema public > schema.ts).

[hawkcookie]

I updated supabase-cli and supabase-js to latest versions and the problem still persists.
Generated type relation is still not one-to-one (supabase gen types typescript --linked --schema public > schema.ts).

I haven't checked what it's like now, but to correctly generate types, please try using the --local option.

#303 (comment)

[oussamabenkortbi]

Same here, type error when two level of joins!
// Property 'last_name' does not exist on type 'string & {}[]'.ts(2339)

[XxStunner]

Having the same issue here.

[niels-bosman]

I don’t know why people are not talking about this. This was single handedly the reason why using Supabase was not feasible for our team..

[jdgamble555]

@soedirgo - You can probably close this one as related to #536 considering there is a work around now.

[ardabeyazoglu]

I updated supabase-cli and supabase-js to latest versions and the problem still persists.
Generated type relation is still not one-to-one (supabase gen types typescript --linked --schema public > schema.ts).

I haven't checked what it's like now, but to correctly generate types, please try using the --local option.

#303 (comment)

Doesnt matter, it is the same.

[jdgamble555]

I updated supabase-cli and supabase-js to latest versions and the problem still persists.
Generated type relation is still not one-to-one (supabase gen types typescript --linked --schema public > schema.ts).

I haven't checked what it's like now, but to correctly generate types, please try using the --local option.
#303 (comment)

Doesnt matter, it is the same.

What query and data model are you using? Did you check out my post #536 ?

J

[ardabeyazoglu]

@jdgamble555 I checked that issue and tried the recommended method with no luck.

Using city:cities!city_id(name) results as following:

(property) city: {
    name: any;
}[]

I have relation like this in generated schema.ts, for account table.

Relationships: [
          {
            foreignKeyName: "public_accounts_city_id_fkey"
            columns: ["city_id"]
            isOneToOne: true
            referencedRelation: "cities"
            referencedColumns: ["id"]
          }
]

EDIT: Might be an issue with my ide or compiler, i ll start from scratch and try again first.

[jdgamble555]

@ardabeyazoglu - Did you try?

city:cities!inner(name)

J

[ardabeyazoglu]

@jdgamble555 finally it worked with using !inner, after regenerating types and creating client with createClient.

I think the issue can be closed.

[fvermaut]

I tried everything in this thread, as well as in other threads, but I still have the issue. Essentially, for 1-to-1 relationships, the typing expects a T[] for the nested data, while the data effectively returned is a single T.

I updated both the CLI and supabase-js to latest versions, re-generated the types, but no luck. I must admit this is quite annoying, and i's not clear to me if the issue is fixed, or if there is workaround, and in which case what the workaround is ...

[jdgamble555]

@fvermaut - What is your data model and query?

[fvermaut]

Well essentially it happens between those 2 tables:

CREATE TABLE IF NOT EXISTS "public"."user_projects" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "name" "text",
    "req_status" "public"."project_status" DEFAULT 'CREATED'::"public"."project_status",
    "req_hyperparams_map" "json",
    "req_start_date" "date",
    "req_settings" "json",
    "req_type" "public"."project_types" DEFAULT 'LIVE'::"public"."project_types" NOT NULL,
    "req_model_id" "uuid",
    "user_id" "uuid" DEFAULT "auth"."uid"(),
    "req_source_id" "uuid"

CREATE TABLE IF NOT EXISTS "public"."projects" (
    "id" "uuid" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "name" "text",
    "hyperparams_map" "json",
    "evaluation_criterias" "json",
    "type" "public"."project_types" DEFAULT 'LIVE'::"public"."project_types",
    "start_date" "date",
    "last_executed_at" timestamp with time zone,
    "status" "public"."project_status" DEFAULT 'INIT'::"public"."project_status" NOT NULL,
    "target_status" "public"."project_status" DEFAULT 'INIT'::"public"."project_status" NOT NULL,
    "error_message" "text",
    "settings" "json",
    "source_id" "uuid"
);

Note that the "id" of the second table is both a primary key, and a foreign key to the first one:

ALTER TABLE ONLY "public"."projects"
    ADD CONSTRAINT "project_pkey" PRIMARY KEY ("id");

ALTER TABLE ONLY "public"."projects"
    ADD CONSTRAINT "public_projects_id_fkey" FOREIGN KEY ("id") REFERENCES "public"."user_projects"("id") ON UPDATE CASCADE ON DELETE CASCADE;

My debug query is this one:

const supabase = createClient();

const respProjects = await supabase
    .from("user_projects")
    .select("id, project:projects!inner(*)");

if (!respProjects.error) {
    const projects = respProjects.data;
    console.log(projects[0].project.name);

What is the problem: the "name" field is appearing red in VScode, even if it is running fine - because typing expects an array there. The only way to remove the typescript error is ito replace it with 'projects[0].project[0].name', which though doesn't work at runtime.

On a side note, the logic behind my model (but should not be relevant to current issue): I want to give WRITE access to authenticated users( through RLS) only to the "users_projects" table - and I have a custom backend process using the service key, that manages the update of the "projects" table based on the requested status ("req_status") set on the "user_projects" row.

[jdgamble555]

@fvermaut - I'm not having that problem. I simplified your schema to:

CREATE TABLE IF NOT EXISTS "public"."user_projects"(
    "id" "uuid" PRIMARY KEY DEFAULT "gen_random_uuid"() NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "name" "text",
    "req_hyperparams_map" "json",
    "req_start_date" "date",
    "req_settings" "json",
    "req_model_id" "uuid",
    "user_id" "uuid" DEFAULT "auth"."uid"(),
    "req_source_id" "uuid"
);

CREATE TABLE IF NOT EXISTS "public"."projects"(
    "id" "uuid" NOT NULL PRIMARY KEY REFERENCES public.user_projects(id),
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "name" "text",
    "hyperparams_map" "json",
    "evaluation_criterias" "json",
    "start_date" "date",
    "last_executed_at" timestamp with time zone,
    "error_message" "text",
    "settings" "json",
    "source_id" "uuid"
);

Here is the types you get for:

await supabase.from('user_projects').select("id, project:projects!inner(*)")

PostgrestSingleResponse<{
    id: string;
    project: {
        created_at: string;
        error_message: string | null;
        evaluation_criterias: Json;
        hyperparams_map: Json;
        id: string;
        last_executed_at: string | null;
        ... 4 more ...;
        updated_at: string | null;
    } | null;
}[]>

The reason it is possibly null is because user_projects don't have an FK that mandates it exists. If you put the FK the other way around, you won't that problem. Either way, I'm not getting an array as an option.

https://github.com/jdgamble555/supabase-types/blob/master/src/lib/index.ts

J

[bnjmnt4n]

@fvermaut - The only solution to get the right code is to use !inner as I showed. This is not a work around, but the solution.

To me, this is definitely a workaround. There's no reason why you'd have to change your query to get correct types.

[jdgamble555]

@bnjmnt4n - Technically, the only types that do not work correctly are:

Querying embedded tables through columns (user:user_id(*)) and using fkey columns as hints

from #536.

Using inner for that use case is definitely a work-around. However, @fvermaut is not getting the work-around to work. My point is that we need to stick to the issue at hand as these posts get overwhelmed with 50 different issues and comments, which sometimes are inundated with incorrect information.

If we don't stick to the exact problem, it will never get fixed.

@fvermaut's code is all over the place, for example, so I suspect something else is going on with his code. If there is a bug with the work-around, we need to know.

J

[fvermaut]

Sorry, again as I said I won't spend more time to debug this issue, as unfortunately I simply don't have time for this. If the only solution is a workaround, then I found a simpler workaround by just skipping the join.
On a side note, and IMHO: typing should be always consistent with the data returned from the API. You shouldn't have to write your queries in a particular way to make typing work. Typing is supposed to validate the queries, not the other way around. If you have to write your queries in a particular way to make typing consistent with the results of the query, then what's the point of using typing in the first place?
And also, why do you say my code is not "all over the place"?? the SQL I shared is all generated by Supabase Studio, and the JS is the same as the one you shared.

[jdgamble555]

@fvermaut - Just trying to solve the problem. 😄 You pasted the generated code from postgres instead of your schema directly, you left out some things, and I still don't know the PK situation. More than one thing is missing and not clear. FWI, this is the advantage of developing locally first.

The reason you want to debug this is so you (and the benefit of those on this post) can have a better understanding of how to do it correctly. Maybe this time you can avoid the join, but you inevitably will have to do an inner join like that in the future, so you should understand how to do it (even with the work-around) to appease the Type system.

100% we need the Types to work when querying a FK directly, but that is a very specific issue. It does not mean the whole system is broken. People will find this post (and others) and assume that is the case, and nothing gets solved.

If you're not sticking with Supabase, the time involved would not be worth it. If you are, I think we can debug it.

J

[fvermaut]

I agree that to get down to the issue you have to investigate every cases, I'm just saying that I don't have time for this, especially if the point is to validate a workaround. I use (and pay for) Supabase as a BaaS, on the premise that you can "build in a weekend" (I know it's a marketing stunt, but still the idea is there). So by principle I don't want to spend weekends debugging things like this. I'll keep watching the thread if something clicks at some point. Thanks again for your help.

[tillka]

@fvermaut - The only solution to get the right code is to use !inner as I showed. This is not a work around, but the solution. Technically, the problem going from the other direction is a different problem. We don't want to leave five of these posts open, as it will never get solved the right way.

Let's diagnose why yours won't work. You can clearly spin up my Supabase instance above and it works, so your schema has to be significantly different in the PKs and / or the FKs.

What happens when you run this query after regenerating the database.d.ts file?

await supabase.from('user_projects').select("id, project:projects!inner(*)")

What is the result?

Is the id field in public.user_projects a PK? You didn't state this, but I assumed it. You also didn't finish posting the whole schema, so I'm assuming there are no other fields.

Something is different, as you shouldn't have this problem. Please give more information.

Thanks,

J

I came to this thread after googling my supabase error.

!inner works fine if you have only one reference from one table to another but breaks if you have two references in one table.

E.g. my listings have two references to profiles, one for the creator_id and one for owner_id. In this case a owner:profiles!owner_id(*) actually pulls the right data (a single profile) but provides the wrong type {}[]. Using owner:profiles!inner(*) provides the correct type (a single profile) but throws an error: "Could not embed because more than one relationship was found for 'listings' and 'profiles'". Something is off.

Is there a way to further disambiguate the inner join? I would expect either owner:profiles!inner!owner_id(*) or owner:profiles!owner_id!inner(*) to work and provide the corrext type.

[jdgamble555]

@tillka - Yeah that makes sense. There would be no way for the query to know the name of the column coming from the other direction. This is an impossible type scenario.

@soedirgo - This is an example of an impossible situation, and why the types need to work coming from BOTH directions.

J

[tillka]

@tillka - Yeah that makes sense. There would be no way for the query to know the name of the column coming from the other direction. This is an impossible type scenario.

Thought so, but shouldn't this work (correctly) then?

owner:profiles!owner_id(*) actually pulls the right data (a single profile) but provides the wrong type {}[]

[jdgamble555]

Yes, it should, but Supabase currently doesn't support typing from a foreign key field.

I guess they figured there is a work around so they haven't prioritized it, but now we know one at least one situation without a work-around.

J

[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/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.

[jdgamble555]

This seems to fix my test data! However, I did not test any regular features for breaking changes.

For anyone testing this:

npm i -D @supabase/supabase-js@2.46.0-rc.2

Make sure to add @supabase to it.

Thanks for working on this!

J

[soedirgo]

That’s great to hear, thanks for testing!

[B-E-A-F]

This seems to fix my test data! However, I did not test any regular features for breaking changes.

For anyone testing this:

npm i -D @supabase/supabase-js@2.46.0-rc.2

Make sure to add @supabase to it.

Thanks for working on this!

J

This one didn't work for me. I'm now getting the type:
SelectQueryError<"Invalid Relationships cannot infer result type">[] | null

I'm not sure if there is an additional option I'm supposed to add or something.

Update: I am also seeing this on rc.1

[soedirgo]

@B-E-A-F does it help if you regenerate your types?

[B-E-A-F]

@B-E-A-F does it help if you regenerate your types?

It doesn't seem to fix itself after restarting my typescript server. Is that what you mean by regenerating my types?

[soedirgo]

@B-E-A-F not exactly - if you’re using the CLI you need to re-run this command.

If you’re developing locally, it would help to know your CLI version:

supabase —version
supabase services

[whollacsek]

Does generating against a local Supabase instance same as against cloud instance? I haven't seen any change in the generated schema recently and my Supabase CLI is up to date

[B-E-A-F]

@B-E-A-F not exactly - if you’re using the CLI you need to re-run this command.

If you’re developing locally, it would help to know your CLI version:

supabase —version
supabase services

Sorry to be difficult. I don't really want to do this. My type inference "works" until I update to the release candidate though. If I had to run the supabase CLI to regenerate the types wouldn't that be a breaking change?

[soedirgo]

Does generating against a local Supabase instance same as against cloud instance?

They may not always be the same - for local instances the type generator’s version is tied to the CLI version, so it can get out of date if you’re using a very old CLI version.

Sorry to be difficult. I don't really want to do this. My type inference "works" until I update to the release candidate though. If I had to run the supabase CLI to regenerate the types wouldn't that be a breaking change?

Not at all! I think you’re right here - based on the error message I think I know where the breaking change comes from. We’ll release a new RC for you to try out.

[soedirgo]

@B-E-A-F just to check, do tables in your generated types have a Relationships field? E.g.:

export type Database = {
  public: {
    Tables: {
      mytable: {
        Row: { /* ... */ }
        Insert: { /* ... */ }
        Update: { /* ... */ }
        Relationships: [ /* ... */ ]
      }
    }
  }
}

[B-E-A-F]

There is a Relationships field. I just generated and looked at the types from the dashboard.

[soedirgo]

Must be something else then. Can I trouble you to file a support ticket (ticking “Allow support access”)? It’d be difficult to troubleshoot the issue otherwise.

[avallete]

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

@B-E-A-F without feedbacks from your side, we couldn't reproduce your issue. Meanwhile we passed a fix for many-to-many relationships that will hopefully resolve the issue you encountered. Please open a new issue and/or a support ticket on supabase side if your problem still persist on the new version.

[shawnphoffman]

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

I'm currently collecting more information but v2.46.0 seems to have obliterated my previously-working project. I use a lot of views and it previously worked by simply inferring the columns defined in the select as type any. While not perfect, the newest release is no longer doing that and I have Invalid Relationships cannot infer result type on basically everything that doesn't query a table(s) directly

[avallete]

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

I'm currently collecting more information but v2.46.0 seems to have obliterated my previously-working project. I use a lot of views and it previously worked by simply inferring the columns defined in the select as type any. While not perfect, the newest release is no longer doing that and I have Invalid Relationships cannot infer result type on basically everything that doesn't query a table(s) directly

I'm really sorry for the inconvenience. To help us pinpoint what's going on, could you share your database types and any example queries to views that were working in the previous version? If possible, submitting this via a Supabase support ticket (making sure to check “Allow support access”) would be ideal, as it helps us troubleshoot directly. We’ll prioritize getting this fixed ASAP. Thanks!

[daveycodez]

How can we insure isOneToOne gets set to true? And what exactly does isOneToOne mean? Does that mean it is a unique key? Like user_id can only exist once in articles if its isOneToOne?

{ articles: {
        Row: {
          content: Json
          created_at: string | null
          id: string
          locale: string
          published: boolean | null
          published_at: string | null
          slug: Json
          summary: Json | null
          tags: string[] | null
          thumbnail_url: string | null
          title: Json
          updated_at: string | null
          user_id: string | null
        }
        Insert: {
          content: Json
          created_at?: string | null
          id?: string
          locale?: string
          published?: boolean | null
          published_at?: string | null
          slug: Json
          summary?: Json | null
          tags?: string[] | null
          thumbnail_url?: string | null
          title: Json
          updated_at?: string | null
          user_id?: string | null
        }
        Update: {
          content?: Json
          created_at?: string | null
          id?: string
          locale?: string
          published?: boolean | null
          published_at?: string | null
          slug?: Json
          summary?: Json | null
          tags?: string[] | null
          thumbnail_url?: string | null
          title?: Json
          updated_at?: string | null
          user_id?: string | null
        }
        Relationships: [
          {
            foreignKeyName: "articles_user_id_fkey"
            columns: ["user_id"]
            isOneToOne: false
            referencedRelation: "profiles"
            referencedColumns: ["id"]
          },
        ]
      }
}

create table
  public.articles (
    id uuid not null default gen_random_uuid (),
    user_id uuid null,
    title jsonb not null,
    slug jsonb not null,
    content jsonb not null,
    locale text not null default 'en'::text,
    created_at timestamp with time zone null default now(),
    updated_at timestamp with time zone null default now(),
    summary jsonb null,
    thumbnail_url text null,
    published_at timestamp with time zone null,
    published boolean null default false,
    tags text[] null default '{}'::text[],
    constraint articles_pkey primary key (id),
    constraint articles_slug_key unique (slug),
    constraint articles_user_id_fkey foreign key (user_id) references profiles (id) on delete set null
  ) tablespace pg_default;

create trigger handle_updated_at before
update on articles for each row
execute function extensions.moddatetime ('updated_at');

[daveycodez]

Also I have a Whispers table that has user_id AND recipient_id

I need to get profiles!user_id() and profiles!recipient_id() but they are both returning arrays or {}[]

I tried profiles!inner() for other tables and that fixed the array issue. But I cannot do profiles!recipient_id() or profiles!recipient_id!inner(*) :/

EDIT -

Noticed it works if I use the foreign key names like this:

supabaseClient
.from("whispers")
.select("*,
user:profiles!whispers_user_id_fkey(*),
recipient:profiles!whispers_recipient_id_fkey(*)
")

Is it not supposed to work with column names? Will it ever? Also it returns as potentially null even though the column is not nullable?

create table
  public.whispers (
    id uuid not null default gen_random_uuid (),
    user_id uuid not null,
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now(),
    recipient_id uuid not null,
    content jsonb not null default '{}'::jsonb,
    locale text not null default 'en'::text,
    constraint whispers_pkey primary key (id),
    constraint whispers_recipient_id_fkey foreign key (recipient_id) references profiles (id) on delete cascade,
    constraint whispers_user_id_fkey foreign key (user_id) references profiles (id) on delete cascade
  )

EDIT AGAIN -

Had to add !inner to make it not nullable. I guess that makes sense?

"user:profiles!whispers_user_id_fkey!inner()",
"recipient:profiles!whispers_recipient_id_fkey!inner()"

phew