Inconsistency with inferred Types using Join

[Lucasdsk]

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

Context:

I have the following relationship between the tables categories and sub-categories:

Also the database.types.ts created by the supabase gen types:

categories: {
        Row: {
          color: string | null
          created_at: string
          id: number
          name: string | null
          user_id: string
        }
        Insert: {
          color?: string | null
          created_at?: string
          id?: number
          name?: string | null
          user_id?: string
        }
        Update: {
          color?: string | null
          created_at?: string
          id?: number
          name?: string | null
          user_id?: string
        }
        Relationships: []
      }
      "sub-categories": {
        Row: {
          category_id: number
          color: string | null
          created_at: string
          id: number
          name: string | null
        }
        Insert: {
          category_id: number
          color?: string | null
          created_at?: string
          id?: number
          name?: string | null
        }
        Update: {
          category_id?: number
          color?: string | null
          created_at?: string
          id?: number
          name?: string | null
        }
        Relationships: [
          {
            foreignKeyName: "sub-categories_category_id_fkey"
            columns: ["category_id"]
            isOneToOne: false
            referencedRelation: "categories"
            referencedColumns: ["id"]
          },
        ]
      }

Goal:

Select the sub-categories and its corresponding category using the relationship category_id and categories:id and also get the types definition as described in the doc: https://supabase.com/docs/guides/database/joins-and-nesting?queryGroups=language&language=js#typescript-types-for-joins

const subcategoriesDataQuery = supabase.from('sub-categories').select(`
    id,
    name,
    color,
    categories(
      id,
      name,
      color
    )
  `)

Problems:

The inferred type brings an array of categories instead of a single object:

But the query result brings the categories as an object not an array:

Trying to find a solution for it I found this discussion where one of the suggestions are to use the foreign key in the query: https://github.com/orgs/supabase/discussions/7610#discussioncomment-8513745

Then if I change the query to:

const subcategoriesDataQuery = supabase.from('sub-categories').select(`
    id,
    name,
    color,
    category:categories!sub-categories_category_id_fkey(
      id,
      name,
      color
    )
  `)

I get this error related to the type:

But the query keeps working:

Expected behavior

The inferred types working according to the query structure.

[avallete]

Hi there !

The fact that in your screenshot everything has a type any seems to indicate that the client isn't being instanced with your introspected types.

When you want to use the type inference, you must instantiate the client by giving it the Database schema resulting from your introspection see here.

Otherwise, it'll default to a very broad "type inference" simply based on the query string.