Types incorrectly inferred as array on a single select query with multiple one to many joins to the same table

[RyanClementsHax]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

The closest I found to this problem is the following discussions

• https://github.com/orgs/supabase/discussions/23217
• https://github.com/orgs/supabase/discussions/7610
• https://github.com/orgs/supabase/discussions/21352

Describe the bug

Let's say I have the following two tables.

create table
  public.profiles (
    id uuid not null,
    name text null,
    avatar_url text null,
    constraint profiles_pkey primary key (id),
    constraint profiles_id_fkey foreign key (id) references auth.users (id)
  ) tablespace pg_default;

create table
  public.time_off_requests (
    id bigint generated by default as identity,
    created_at timestamp with time zone not null default now(),
    profile_id uuid not null,
    start_date date not null,
    end_date date not null,
    approver_id uuid null,
    approved_date date null,
    constraint time_off_requests_pkey primary key (id),
    constraint public_time_off_requests_approver_id_fkey foreign key (approver_id) references profiles (id) on update cascade on delete cascade,
    constraint public_time_off_requests_profile_id_fkey foreign key (profile_id) references profiles (id) on update cascade on delete cascade
  ) tablespace pg_default;

The following query...

const { data } = await supabase.from("time_off_requests").select(`
  id,
  user:profiles!profile_id (
    id,
    name,
    avatar_url
  ),
  approver:profiles!approver_id (
    id,
    name,
    avatar_url
  )
`);

...resolves as...

const data: {
    id: number;
    user: {
        id: string;
        name: string;
        avatar_url: string;
    }[];
    approver: {
        id: string;
        name: string;
        avatar_url: string;
    }[];
}[]

...but the data comes back as...

[
  {
    "id": 1,
    "user": {
      "id": "bc6819ba-7161-4656-bbee-57d77663c485",
      "name": "Bob",
      "avatar_url": "profile-pic-1.png"
    },
    "approver": {
      "id": "ad5d6229-a40a-47a6-a141-a74be92bf280",
      "name": "Sally",
      "avatar_url": "profile-pic-2.png"
    }
  },
  {
    "id": 2,
    "user": {
      "id": "5049bccc-ad97-443b-bbb3-c953f9544962",
      "name": "Frank",
      "avatar_url": "profile-pic-3.png"
    },
    "approver": null
  }
]

To Reproduce

See above steps

Expected behavior

One to many joins of this type shouldn't be typed as arrays i.e.

const data: {
    id: number;
    user: {
        id: string;
        name: string;
        avatar_url: string;
    };
    approver: {
        id: string;
        name: string;
        avatar_url: string;
    };
}[]

Screenshots

N/A

System information

• OS: WSL
• Browser (if applies): N/A
• Version of supabase-js: 1.163.2
• Version of Node.js: 20.11.0

Additional context

Temporary workaround I'm using

const { data } = await supabase.from("time_off_requests").select(`
  id,
  user:profiles!profile_id (
    id,
    name,
    avatar_url
  ),
  approver:profiles!approver_id (
    id,
    name,
    avatar_url
  )
`);

type RowType = (typeof data)[number];
type CastedData = Omit<RowType, "user" | "approver"> & {
  user: RowType["user"][number];
  approver: RowType["approver"][number];
};
const castedData = data as unknown as CastedData[];

[wafs]

Correct me if I'm wrong, but you will always get an array of results unless you use .single() at the end of a query,

i.e

const { data } = await supabase.from("time_off_requests").select(`
  id,
  user:profiles!profile_id (
    id,
    name,
    avatar_url
  ),
  approver:profiles!approver_id (
    id,
    name,
    avatar_url
  )
`).single() // <-------;

https://supabase.com/docs/reference/javascript/single

[RyanClementsHax]

Oh, I'd like an array to be returned for the table I'm querying, but I'd like the approver field on every row to be a single object. The client returns the data just fine but the typescript types don't line up.

[therealsujitk]

I'm facing this bug too, any update in this?

Correct me if I'm wrong, but you will always get an array of results unless you use .single() at the end of a query,

@wafs This is only the case for the outer query (or table), this issue talks about the referenced table.

Edit: Seems like this issue has already been opened several times before - https://github.com/supabase/postgrest-js/issues/546

[alfredomariamilano]

I've commented with a temporary fix here https://github.com/supabase/postgrest-js/issues/546#issuecomment-2305247236