postgrest-js: Database typings are not correct on table join for entity to one relationship

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

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Reactions: 18
  • Comments: 56 (10 by maintainers)

Most upvoted comments

👍

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

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…

@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.

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.

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 });

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.

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.

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

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

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

@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

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.