Relationships between tables are not typed correctly

[Evaldas-B]

Bug report

Relationships (one to one, one to many, many to many) are picked up by Postgrest but not represented by generated types.

Describe the bug

One to one relationship

movies table forms one to one relationship with finance table and that is correctly picked up by Postgrest, but generated type is for array OR single item whereas it should be just a single item type:

EXAMPLE

##### Query ```javascript const { data: movies, error } = await supabaseClient.from('movies').select('*, finance(*)') ``` ##### Generated type ```typescript movies: ({ id: number; release_year: number | null; title: string | null; } & { finance: { box_office: number | null; budget: number | null; movie_id: number; } // ⌄⌄⌄⌄⌄ This type should NOT be here ⌄⌄⌄⌄⌄ | { box_office: number | null; budget: number | null; movie_id: number; }[] | null; // ------------------------------------- })[] | null ``` ##### Response ```javascript [ { id: 1, title: 'The Shawshank Redemption', release_year: 1994, finance: { movie_id: 1, budget: 25000000, box_office: 28341469 } // Recognized correctly that this is one to one relationship } ] ```

One to many relationship

movies table forms one to many relationship with reviews table and that is correctly picked up by Postgrest, but generated type is for array OR single item whereas it should be array type:

EXAMPLE

##### Query ```javascript const { data: movies, error } = await supabaseClient.from('movies').select('*, reviews(*)') ``` ##### Generated type ```typescript movies: ({ id: number; release_year: number | null; title: string | null; } & { reviews: // ⌄⌄⌄⌄⌄ This type should NOT be here ⌄⌄⌄⌄⌄ { comment: string | null; date: string | null; id: number; movie_id: number | null; } | // ------------------------------------- { comment: string | null; date: string | null; id: number; movie_id: number | null; }[] | null; })[] | null ``` ##### Response ```javascript [{ id: 1, title: 'The Shawshank Redemption', release_year: 1994, reviews: [{ // Recognized correctly that this is one to many relationship id: 1, movie_id: 1, comment: 'One of the best movies ever made.', date: '2022-01-01' }, { id: 4, movie_id: 1, comment: 'Powerful performances and a compelling story.', date: '2022-01-04' } ] }] ```

Many to many relationship

movies table forms many to many relationship with actors table and that is correctly picked up by Postgrest, but generated type is for array OR single item whereas it should be array type:

EXAMPLE

##### Query ```javascript const { data: movies, error } = await supabaseClient.from('movies').select('*, actors(*)') ``` ##### Generated type ```typescript movies: ({ id: number; release_year: number | null; title: string | null; } & { actors: // ⌄⌄⌄⌄⌄ This type should NOT be here ⌄⌄⌄⌄⌄ { birth_date: string | null; id: number; name: string | null; } | // ------------------------------------- { birth_date: string | null; id: number; name: string | null; }[] | null; })[] | null ``` ##### Response ```javascript [{ id: 1, title: 'The Shawshank Redemption', release_year: 1994, actors: [{ // Recognized correctly that this is many to many relationship id: 4, name: 'Morgan Freeman', birth_date: '1937-06-01' }, { id: 5, name: 'Tim Robbins', birth_date: '1958-10-16' } ] } ] ```

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. Create a new local project using npx supabase init
2. Start supabase localy npx supabase start
3. Create tables that represent all 3 relationship types
4. Generate types using npx supabase gen types typescript --local > src/database.d.ts

Expected behavior

The generated types should generate table relationships the same way Postgrest does

Screenshots

System information

• supabase CLI: 1.27.7
• Version of supabase-js: 2.2.1
• Version of Postgrest: 10.1.1

Additional context

Migration file

```sql -- This script was generated by the Schema Diff utility in pgAdmin 4 -- For the circular dependencies, the order in which Schema Diff writes the objects is not very sophisticated -- and may require manual changes to the script to ensure changes are applied in the correct order. -- Please report an issue for any failure with the reproduction steps. CREATE TABLE IF NOT EXISTS public.movies ( id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ), title text COLLATE pg_catalog."default", release_year integer, CONSTRAINT movies_pkey PRIMARY KEY (id) ) TABLESPACE pg_default; ALTER TABLE IF EXISTS public.movies OWNER to postgres; GRANT ALL ON TABLE public.movies TO anon; GRANT ALL ON TABLE public.movies TO authenticated; GRANT ALL ON TABLE public.movies TO postgres; GRANT ALL ON TABLE public.movies TO service_role; CREATE TABLE IF NOT EXISTS public.reviews ( id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ), movie_id bigint, comment text COLLATE pg_catalog."default", date date, CONSTRAINT reviews_pkey PRIMARY KEY (id), CONSTRAINT reviews_movie_id_fkey FOREIGN KEY (movie_id) REFERENCES public.movies (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) TABLESPACE pg_default; ALTER TABLE IF EXISTS public.reviews OWNER to postgres; GRANT ALL ON TABLE public.reviews TO anon; GRANT ALL ON TABLE public.reviews TO authenticated; GRANT ALL ON TABLE public.reviews TO postgres; GRANT ALL ON TABLE public.reviews TO service_role; CREATE TABLE IF NOT EXISTS public.finance ( movie_id bigint, budget numeric, box_office numeric, CONSTRAINT finance_pkey PRIMARY KEY (movie_id), CONSTRAINT finance_movie_id_fkey FOREIGN KEY (movie_id) REFERENCES public.movies (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) TABLESPACE pg_default; ALTER TABLE IF EXISTS public.finance OWNER to postgres; GRANT ALL ON TABLE public.finance TO anon; GRANT ALL ON TABLE public.finance TO authenticated; GRANT ALL ON TABLE public.finance TO postgres; GRANT ALL ON TABLE public.finance TO service_role; CREATE TABLE IF NOT EXISTS public.actors ( id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ), name text COLLATE pg_catalog."default", birth_date date, CONSTRAINT actors_pkey PRIMARY KEY (id) ) TABLESPACE pg_default; ALTER TABLE IF EXISTS public.actors OWNER to postgres; GRANT ALL ON TABLE public.actors TO anon; GRANT ALL ON TABLE public.actors TO authenticated; GRANT ALL ON TABLE public.actors TO postgres; GRANT ALL ON TABLE public.actors TO service_role; CREATE TABLE IF NOT EXISTS public.movie_actors ( movie_id bigint NOT NULL, actor_id bigint NOT NULL, CONSTRAINT movie_actors_pkey PRIMARY KEY (movie_id, actor_id), CONSTRAINT movie_actors_actor_id_fkey FOREIGN KEY (actor_id) REFERENCES public.actors (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT movie_actors_movie_id_fkey FOREIGN KEY (movie_id) REFERENCES public.movies (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) TABLESPACE pg_default; ALTER TABLE IF EXISTS public.movie_actors OWNER to postgres; GRANT ALL ON TABLE public.movie_actors TO anon; GRANT ALL ON TABLE public.movie_actors TO authenticated; GRANT ALL ON TABLE public.movie_actors TO postgres; GRANT ALL ON TABLE public.movie_actors TO service_role; ```

Seed file

```sql -- Insert movies INSERT INTO public.movies (title, release_year) VALUES ('The Shawshank Redemption', 1994), ('The Godfather', 1972), ('The Godfather: Part II', 1974); -- Insert actors INSERT INTO public.actors (name, birth_date) VALUES ('Marlon Brando', '1924-04-03'), ('Robert De Niro', '1943-08-17'), ('Al Pacino', '1940-04-25'), ('Morgan Freeman', '1937-06-01'), ('Tim Robbins', '1958-10-16'), ('Tom Hanks', '1956-07-09'); -- Insert reviews INSERT INTO public.reviews (movie_id, comment, date) VALUES (1, 'One of the best movies ever made.', '2022-01-01'), (2, 'A true classic.', '2022-01-02'), (3, 'An epic tale of family and power.', '2022-01-03'), (1, 'Powerful performances and a compelling story.', '2022-01-04'), (2, 'A must-see for any movie fan.', '2022-01-05'); -- Insert movie-actor relationships INSERT INTO public.movie_actors (movie_id, actor_id) VALUES (1, 4), (1, 5), (2, 1), (2, 3), (3, 1), (3, 2), (3, 3); -- Insert finance data INSERT INTO public.finance (movie_id, budget, box_office) VALUES (1, 25000000, 28341469), (2, 6000000, 134821952), (3, 13000000, 549766000); ```

[5hee75]

The best way I've found around this is to use the explicit returns() method on the query, so something like this in your case:

// type "Database" is output from CLI generated types
type Movie = Database["public"]["Tables"]["movies"]["Row"];
type Finance = Database["public"]["Tables"]["finance"]["Row"];

const { data } = await supabaseClient
    .from('movies')
    .select('*, finance(*)')
    .returns<Movie & { finance: Finance }>();  // "data" will now be typed with a single object on "finance" field

This is definitely not ideal, because the generated types are not very convenient to use and if you have any sort of select options then you'd be better off just writing your own types, probably. I'd love to see this improved.

[RogerTerrazas]

Following what @5hee75 mentioned, this is what I did for a list of objects with a nested 1 to 1 relationship:

type Event = Database["public"]["Tables"]["Events"]["Row"];
type Participation = Database["public"]["Tables"]["Participations"]["Row"];
type NestedEvent = Event & {Participations: Participation};

const fetcher = async (url) =>
  await supabase
    .from("Events")
    // select all columns and the participations column as one to one relationship
    .select("*, Participations(\*)")
    .eq("organization", organizationID)
    .returns<NestedEvent[]>();

const { data, error, isLoading } = useSWR("/events", fetcher);

[periman2]

Any updates on this issue?

[Eldynn]

I have a similar issue where I think the typing in relation is wrong (maybe it is just me that is really):

• I have a one to many relationship from table A to table B
• When I select like:

  await supabaseClientPublic
      .from("A")
      .select("B ( name )")
      .eq("id", 'some_super_id')

The typing will say the return is:

{
    B: {
        name: string;
    } | null;
}[] | null

But since my relationship is restricted so I cannot delete B if some records in A have it referenced I would expect this typing:

{
    B: {
        name: string;
    };
}[] | null

Since B must (in the meaning "can only") exist, am I missing something?

[tonoli]

Any update on this one?

[rtman]

Just encountered this issue as well, any chance of prioritizing this one?

[JonL1]

Typescript support for this library definitively needs some love.

[albertonii]

It seems that the error could be fixed also as mentioned in this discussion: https://github.com/orgs/supabase/discussions/7610

But it's an inconvenient to update the schema eachtime it's generated...

[branaust]

This is annoying. IMO all relationships should be included in the predefined types coming from Supabase we shouldn't need to manually add them

[soedirgo]

Thanks for the detailed repro steps, and sorry this took a long time to fix! I've confirmed that this is no longer an issue on the latest version of CLI:

One to one relationship

const movies: {
    id: number;
    release_year: number | null;
    title: string | null;
    finance: {
        box_office: number | null;
        budget: number | null;
        movie_id: number;
    } | null;
}[] | null

One to many relationship

const movies: {
    id: number;
    release_year: number | null;
    title: string | null;
    reviews: {
        comment: string | null;
        date: string | null;
        id: number;
        movie_id: number | null;
    }[];
}[] | null

Many to many relationship

const movies: {
    id: number;
    release_year: number | null;
    title: string | null;
    actors: {
        birth_date: string | null;
        id: number;
        name: string | null;
    }[];
}[] | null

[hasanaktas]

Why is this topic marked complete? Is there something I missed?

My tables

create table
  products (
    id uuid primary key default gen_random_uuid (),
    name text not null,
    ...
  );

create table
  cart_items (
    id uuid primary key default gen_random_uuid (),
    product_id uuid not null references products on delete cascade on update cascade,
   ...
  );

I write my query like this

supabase
    .from("cart_items")
    .select("*,product:products(*)")

The product has been typed as nullable. but actually it should be.

    id: string;
    product_id: string;
    ...
    product: {
        ...;
    } | null;

[sweatybridge]

@hasanaktas does it work if you add inner join? ie.

supabase
    .from("cart_items")
    .select("*,product!inner(*)")

[hasanaktas]

@sweatybridge

Yes, it was solved this way. So what is the reason for this? I'm not very familiar with SQL, I usually communicate with the database via orm. Shouldn't the code I wrote automatically capture the relationship?

[sweatybridge]

Shouldn't the code I wrote automatically capture the relationship?

I know PostgREST defaults to left join when selecting from an embedded table, which is why I suggested to use !inner. But given your table definition, I think it should be possible to capture the relationship automatically because the foreign key is not nullable.

Perhaps you can try asking on https://github.com/PostgREST/postgrest/issues to see if there's any technical explanation for this.