feat: determine relationship cardinality from types

[soedirgo]

What kind of change does this PR introduce?

Fixes #303

What is the current behavior?

Nested tables are typed as T[] | T | null - it's annoying to always have to use type guards for these.

What is the new behavior?

Infer relationship cardinality (i.e. -to-one or -to-many) so nested tables are typed as either T[] or T | null

• [x] Pending on https://github.com/supabase/postgres-meta/pull/577

[github-actions[bot]]

:tada: This PR is included in version 1.7.0 :tada:

The release is available on:

• GitHub release
• npm package (@latest dist-tag)

Your semantic-release bot :package::rocket:

[imownbey]

omg THANK YOU

[imownbey]

I have a bug where this is rendering a has-one relationship (via tablename_id on that table) is returning as a [], is there some useful information I can provide for the bug report? I don't believe I am doing anything very weird so am not sure why its happening.

Otherwise this works great.

[soedirgo]

@imownbey thanks!

is there some useful information I can provide for the bug report?

Hmm is it a complex relationship? If you can provide an obfuscated SQL for the schema so I can replicate it, that'd be awesome.

Might be worth noting that if you generate the types from a hosted project (e.g. from supabase gen types [--linked|--project-id ...]) it won't have the relationships just yet - we'll update that soon if it works well enough for local databases.

[hawkcookie]

@soedirgo @imownbey I may have the same problem about many to one relationship.

 const supabase = createClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  )
  const { data, error } = await supabase.from(`item_details`).select(`*, items(*)`)
  console.log(data, error)

This is the data and error property log.

[
  {
    id: 1,
    item_id: 1,
    body: 'test item body',
    created_at: '2023-06-01T06:56:56.567318+00:00',
    items: {
      id: 1,
      name: 'test item name',
      created_at: '2023-06-01T06:56:38.229145+00:00'
    }
  }
] null

But, inferred type shows items array.

   "@supabase/supabase-js": "^2.24.0",
    "@types/node": "20.2.5",
    "@types/react": "18.2.7",
    "@types/react-dom": "18.2.4",
    "autoprefixer": "10.4.14",
    "eslint": "8.41.0",
    "eslint-config-next": "13.4.4",
    "next": "13.4.4",
    "postcss": "8.4.24",
    "react": "18.2.0",
    "react-dom": "18.2.0",
    "tailwindcss": "3.3.2",
    "typescript": "5.0.4"

[hawkcookie]

Well, types file automatically generated by npx supabase gen types typescript --project-id "$PROJECT_REF" --schema public > types/supabase.ts has no RelationShip Property.

I added manually that property in its file, which is inferred well.

item_details: {
        Row: {
          body: string
          created_at: string | null
          id: number
          item_id: number
        }
        Insert: {
          body: string
          created_at?: string | null
          id?: number
          item_id: number
        }
        Update: {
          body?: string
          created_at?: string | null
          id?: number
          item_id?: number
        }
===manually added==========
          Relationships: [
            {
              foreignKeyName: 'item_id_fkey'
              columns: ['item_id']
              referencedRelation: 'items'
              referencedColumns: ['id']
            }
          ]
=============
      }

Is my executed command wrong or old?

[mitjans]

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.

[mitjans]

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).

[soedirgo]

@hawkcookie you can try using supabase gen types typescript --db-url <your db connection string> for now. --project-id and --linked are pending on a deploy, we'll update on this ticket once we've done so.