[supabase-js v2] Improve Types Implementation for Nested Queries

[kryptovergleichde]

Feature request

Is your feature request related to a problem? Please describe.

Hi there, I tried implementing supabase-js v2, and first was happy to see the type generation out of select queries. But the real issues I have are still not solved with the new approach, these are nested tables types.

// I saw this example on the new docs: https://supabase.com/docs/reference/javascript/next/typescript-support.
import supabase from '~/lib/supabase'
import type { Database } from '~/lib/database.types'

async function getMovies() {
  return await supabase.from('movies').select('id, title, actors(*)')
}

type actors = Database['public']['Tables']['actors']['Row']
type MoviesResponse = Awaited<ReturnType<typeof getMovies>>
type MoviesResponseSuccess = MoviesResponse['data'] & {
  actors: actors[]
}

So let's imagine you have this data structure id, title, actors(name, birth_date), typescript will catch the id and title, of the movie, but not the name and birth_date of the nested relation. If I am now going to add this info manually like this:

// ...

async function getMovies() {
  return await supabase.from('movies').select('id, title, actors(name, birth_date)')
}

type actors = Database['public']['Tables']['actors']['Row']
type MoviesResponse = Awaited<ReturnType<typeof getMovies>>
type MoviesResponseSuccess = MoviesResponse['data'] & {
  actors: Pick<actors, 'name' | 'birth_date'>[]
}

there will be a lot of redunancy in the implementation. Since we have to define the table cols twice.

Describe the solution you'd like

Of course it would be perfect if nested relations would work out of the box. I see that this would be very difficult to do, and that's maybe why you didn't.

I would essentially suggest to export this type, to create something like this:

// (!) Pseudocode
// ...

import { ResolveRelationQuery } from '@supabase/supabase-js'

const actorsRelation = 'actors(name, birth_date)'
async function getMovies() {
  return await supabase.from('movies').select(`id, title, ${actorsRelation}`)
}

type MoviesResponse = Awaited<ReturnType<typeof getMovies>>
type MoviesResponseSuccess = MoviesResponse['data'] & {
  actors: ResolveRelationQuery<typeof actorsRelation>[]
}

Describe alternatives you've considered

I peeked into the type definitions of the new supabase-js v2 library, and saw a GetResult type coming from @supabase/postgrest-js/dist/module/select-query-parser.

I have implemented something with this, which technically works, but is not very developer friendly since everyone have to define lots of types in the repo. See https://github.com/supabase/postgrest-js/issues/303

[cdimitroulas]

@presedo93 you can try something like this, assuming your code snippet was wrapped in a function called myFunction:

const myFunction = () => {
  return workoutsSB
    .from('section')
    .select('*, superset!inner (*, exercise!inner))')
}
export type MyData = Awaited<ReturnType<typeof myFunction>>['data']

[cdimitroulas]

@soedirgo

Hey all, we've made some changes to improve the types of nested tables. Atm this is only available when generating types for the local development database with supabase gen types typescript --local.

If you have the Supabase CLI set up, can you upgrade it to v1.64.1 and upgrade supabase-js v2.23.0 and give it a spin? 🙏 If all goes well we'll release it on the platform and close this.

Just upgraded supabase-js and this is much better now 👍 thanks for the update! It still seems like a non-nullable foreign key results in the joined data being potentially null. For example, this is the result from a simple query:

  const { data, error } = client
    .from('transactions')
    .select('amount, transcripts(form_number)');

  // data is typed as: Array<{ amount: number; transcripts: { form_number: string } | null }>

[Feel-ix-343]

Upgrading worked for me.

I did paru -S supabase-git, then killed the docker container, supabase stop (not sure why I had to do this), then supabase start. It showed postgres... upgraded, then rm database.types.ts && supabase gen types typescript --local >> database.types.ts

[cdedreuille]

Not sure if I'm missing something but this is what I get with the following query:

const supabase = createServerComponentClient<Database>({ cookies });

const { data: threads_users } = await supabase
    .from("threads_users")
    .select(`*, thread(*)`)
    .eq("user", user?.id);

Types I get

const threads_users: {
    created_at: string;
    id: number;
    thread: number & {}[];
    user: string;
}[] | null

thread has a weird type with an empty object. Am I missing something? I'm using "@supabase/supabase-js": "^2.33.1"

[gregfromstl]

Not sure if I'm missing something but this is what I get with the following query:

const supabase = createServerComponentClient<Database>({ cookies });

const { data: threads_users } = await supabase
    .from("threads_users")
    .select(`*, thread(*)`)
    .eq("user", user?.id);

Types I get

const threads_users: {
    created_at: string;
    id: number;
    thread: number & {}[];
    user: string;
}[] | null

thread has a weird type with an empty object. Am I missing something? I'm using "@supabase/supabase-js": "^2.33.1"

Try this

const { data: threads_users } = await supabase
    .from("threads_users")
    .select(`*, thread: threads!threads_users_thread_fkey(*)`)
    .eq("user", user?.id);

[nicetomytyuk]

In this code:

    const { data, error } = await this.service.supabase.from('purchased_products')
      .select('id, product, price, discounts, promotion:company_promotion!inner(id, company)')
      .match(
        {
          'promotion.company': company,
          'product': product,
        })
      .gte('quantity', 1)
      .limit(1)
      .maybeSingle();

The returned type is:

const data: {
    id: any;
    product: any;
    price: any;
    discounts: any;
    promotion: {
        id: any;
        company: any;
    }[];
} | null

When the returned object promotion is just an object and not the array, so the correct type should be:

const data: {
    id: any;
    product: any;
    price: any;
    discounts: any;
    promotion: {
        id: any;
        company: any;
    };
} | null

[otang]

Following. Would be nice to have stronger typing here

[TimurKr]

Hey, found a workaround for some of the problems mentioned in this thread, I opened an issue in the supabase-js repository to add it to the documentation where it is explained.

[MaximusMcCann]

Would love this to work OOtB.
Casting all my db queries to PostgrestSingleResponse<Database['public']['Tables']['blahblahblah']> (or similar) is a pain.

[MaximusMcCann]

For the next person. Ensure you instantiate your supabase client with SupabaseClient<Database> as opposed to example code on the internet that may show SupabaseClient<any, 'public', any>

[miketeix]

@soedirgo

Hey all, we've made some changes to improve the types of nested tables. Atm this is only available when generating types for the local development database with supabase gen types typescript --local. If you have the Supabase CLI set up, can you upgrade it to v1.64.1 and upgrade supabase-js v2.23.0 and give it a spin? 🙏 If all goes well we'll release it on the platform and close this.

Just upgraded supabase-js and this is much better now 👍 thanks for the update! It still seems like a non-nullable foreign key results in the joined data being potentially null. For example, this is the result from a simple query:

  const { data, error } = client
    .from('transactions')
    .select('amount, transcripts(form_number)');

  // data is typed as: Array<{ amount: number; transcripts: { form_number: string } | null }>

Same here with the foreign table being potentially null. E.g.

(property) Table: {
    prop: number;
    NestedTable: {
        prop: string | null;
    } | null;
} | null

and I'm using a slightly more complex query referencing the foreign key:

select('
*,
Table:Table!RootTable_tableId_fkey(*, NestedTable:NestedTable!Table_nestedTableId_fkey(*))
')

The "} | null" seems to prevent destructuring with and "Property does not exist" typescript error. e.g. :

const {
      prop,
      NestedTable: { contractId },
    } = Table;

yields: "Property 'prop' does not exist on type '{ prop: number;... } | null'"

I'm not certain, but I think the property isn't being recognized because of the "} | null"

My workaround is to manually create complex type:

import { Tables } from "../supabase_generated_types";
export interface TableWithNestedTable extends Tables<"Table"> {
  NestedTable: Tables<"NestedTable">;
}

select using a less complex query:

*
Table(*, NestedTable(*))
')

and destructure like so:

const {
      prop,
      NestedTable: { contractId },
    } = Table as TableWithNestedTable;

[jdgamble555]

@soedirgo - This should be closed as well as original issue is fixed.