Wrong response type for referenced table data when using the .single() modifier

[hexrw]

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

Referenced tables (docs) are not typed correctly when used with .single(). The types don't match with the actual response.

May possibly actually be an issue with the underlying PostREST library, can't say for sure. confirmed

To Reproduce

create table
  countries (id int8 primary key, name text);
create table
  cities (
    id int8 primary key,
    country_id int8 not null references countries,
    name text
  );

insert into
  countries (id, name)
values
  (1, 'Germany'),
  (2, 'Indonesia');
insert into
  cities (id, country_id, name)
values
  (1, 2, 'Bali'),
  (2, 1, 'Munich');

const { data } = await supabase
  .from('countries')
  .select(`
    name,
    cities (
      name
    )
  `)
  .eq('name', 'Germany')
  .limit(1)
  .single()

Expected behavior

NOTE: To reduce the scope of where the issue originates from, I am not passing any types to the client instance. thus any is present

// Response
{
  "data": {
    "name": "Germany",
    "cities": {
      "name": "Munich"
    }
  }
}

// Type of `.data`
{
    name: any;
    cities: {
        name: any;
    };
}

Actual behavior

// Response
{
  "data": {
    "name": "Germany",
    "cities": {
      "name": "Munich"
    }
  }
}

// Type of `.data`
{
    name: any;
    cities: {
        name: any;
    }[];
}

(TS2339) | Property name does not exist on type { name: any; }[].

System information

• OS: Ubuntu 22.04
• Browser: Firefox (120)
• Build tool: Vite
• Server: Vite dev server, Vite preview server (prod build), Vercel
• Version of supabase-js: 2.39.0

Additional context

Using this inside a Vue 3 (Composition API) application with Vite and TypeScript. The client is initialized the recommended way, as in the demos on GitHub that the official docs link to.

[hexrw]

Possibly related

• Wrong type for joins with one-to-one relationships #471
• Database typings are not correct on table join for entity to one relationship supabase-js#723
• One-to-one relationship typescript types #434

[DhenPadilla]

Thanks for opening this @hexrw - This is also what I'm running into;

Any update to this supabase team?

[avallete]

Hi there,

I believe the issue was fixed some time ago, but not in the way initially described. The relationship in question is actually one-to-many, so both the runtime and type results should correctly yield an "array type" output.

For example, with the following seed data, we should have multiple cities for "Germany":

INSERT INTO countries (id, name) VALUES
  (1, 'Germany'),
  (2, 'Indonesia');

INSERT INTO cities (id, country_id, name) VALUES
  (1, 2, 'Bali'),
  (2, 1, 'Munich'),
  (3, 1, 'Berlin');

When I tried to reproduce the reported bug using this script:

import type { Database } from "./database.types";
import { createClient } from "@supabase/supabase-js";

const supabase = createClient<Database>('<supabase-url>', '<anon-key>');

async function main() {
  const { data } = await supabase
    .from('countries')
    .select(`
      name,
      cities (
        name
      )
    `)
    .eq('name', 'Germany')
    .limit(1)
    .single();

  console.log(data);
}

main();

I received the expected result, both in terms of types:

const data: {
  name: string | null;
  cities: {
    name: string | null;
  }[];
} | null;

And at runtime:

{
  "name": "Germany",
  "cities": [
    { "name": "Munich" },
    { "name": "Berlin" }
  ]
}

Thank you for taking the time to report this. I’m closing the issue for now, but please feel free to reopen it if you think I've missed something.