One-to-one relationship typescript types

[mitjans]

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.

Describe the bug

I have a problem with one-to-one relationships. I have 3 tables:

• problems
• problem_stats
• problem_votes

Here are the SQL definitions extracted from Supabase (simplified columns):

problems table

create table problems (
    id bigint generated by default as identity not null,
    owner uuid not null default auth.uid (),
    constraint problems_pkey primary key (id),
    constraint problems_id_key unique (id),
  ) tablespace pg_default;

problem_stats table

create table problem_stats (
    id bigint generated by default as identity not null,
    constraint problem_stats_pkey primary key (id),
    constraint problem_stats_id_key unique (id),
    constraint problem_stats_id_fkey foreign key (id) references problems (id) on delete cascade
  ) tablespace pg_default;

problem_votes table

create table problem_votes (
    id bigint not null,
    user_id uuid not null default auth.uid (),
    constraint problem_reactions_pkey primary key (id, user_id),
    constraint problem_votes_id_fkey foreign key (id) references problems (id),
  ) tablespace pg_default;

So the relations are as follows:

• one-to-one between problems and problem_stats
• one-to-many between problems and problem_votes

This is the query:

  const { data: problem } = await client
    .from('problems')
    .select(
      `
      id,
     problem_stats (*),
     problem_votes (*)
      `
    )
    .single();

This is the query response:

{
  id: 1,
  problem_stats: { ... },
  problem_votes: [
    {
      ...
    }
  ]
}

Notice how the response correctly returns a one-to-one relationship for stats (object) and a one-to-many relationship for votes (array of objects)

Here are the typescript types:

const problem: {
    id: number;
    problem_stats: {
        ...
    }[];
    problem_votes: {
        ...
    }[];
} | null

problem_stats is represented as an array (although the response is an object because of the one-to-one relationship). problem_votes is correctly represented as an array.

I've noticed that when I select from problem_stats (instead of problems) the type is inferred correctly (one-to-one).

  const { data: stats } = await client.from('problem_stats').select(`*, problems(*)`).single();

Now it has type:

const stats: {
    id: number;
    problems: {
        ...
    } | null;
} | null

Now it correctly represents the problems value as an object (one-to-one).

[mitjans]

Any update on this @soedirgo ?

I would love to help, but I'm not that good with Typescript types.

[imownbey]

I believe this has been fixed, you need to use a db-url to generate the types locally for now https://github.com/supabase/postgrest-js/pull/426

[mitjans]

@imownbey I created this issue after adding a comment to that PR, so that it doesn't get lost in closed PR

[imownbey]

Oh woops! My bad

[hawkcookie]

Any update on this @soedirgo ?

[imownbey]

This is actually a pretty gnarly issue because postgrest returns the child object not wrapped in an array but typescript now thinks it is in an array. This makes runtime errors very easy to run into and it hard to trust the typescript types.

[fvermaut]

This appears to still be an issue, as reported also in https://github.com/supabase/postgrest-js/issues/471, and https://github.com/supabase/postgrest-js/issues/408. I tried the workarounds in the second link, but with no luck. I agree this is an annoying issue, and makes the TS types hard to trust. It's also not clear to me if this issue is getting any attention from the Supabase team or not..

[sethwilsonUS]

Running into this too. It's very annoying :(