postgrest-js: Wrong type for joins with one-to-one relationships

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

I have a profiles and a customers table, with a one-to-one relationship:

profiles: {
  id: text, primary key
  ...
}
customers: {
  id: references profiles.id, primary key
  stripe_customer_id: text, unique
  ...
}

When doing a join between these, TypeScript thinks the type of customers is this:

{
    stripe_customer_id: string;
}[]

But when I run the code, this is the actual type I get:

{
    stripe_customer_id: string;
}

So when accessing the stripe_customer_id property, I get this type error:

Property ‘stripe_customer_id’ does not exist on type ‘{ stripe_customer_id: string; }[]’.

To Reproduce

const profile_result = await supabase
	.from('profiles')
	.select('id, customers ( stripe_customer_id )')
	.eq('id', params.id)
	.single()
if (profile_result.data) {
	console.log(profile_result.data.customers.stripe_customer_id)
    //                                        ^
    // Property 'stripe_customer_id' does not exist on
    // type '{ stripe_customer_id: string; }[]'.ts(2339)
}

Expected behavior

The TypeScript type should not be an array for one-to-one relationships. It should match the actual type

System information

  • OS: macOS
  • Version of supabase-js: 2.31.0
  • Version of Node.js: 18.14.2

Additional context

Related:

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Reactions: 25
  • Comments: 21 (2 by maintainers)

Most upvoted comments

Seems to still be an issue

I encountered the same issue today and I found another workaround.

const profile_result = await supabase
	.from('profiles')
	.select('id, customers ( stripe_customer_id )')
	.eq('id', params.id)
	.single()
        .returns<YourType[]>();

# type definition
type YourType {
    the type that you expect without the array
}

Update: I’ve changed my workaround to this:

export function fixOneToOne<T>(objectOrNull: T[]): T | null {
  return (objectOrNull as T) || null;
}

I’ve changed the return type to T | null because the relationship may indeed be null. In the example above, this would happen when there is no customers record for the given profile.

In the example above, the correct type should be:

{
    stripe_customer_id: string;
} | null

I am experiencing the exact same issue. As my query is rather nested, workarounds are very inconvenient…

Does anyone know, whether an older version does not have this bug included?

I’m using this as a workaround for now:

export function cast<T>(notAnArray: T[]): T {
  return notAnArray as T;
}

And then:

console.log(cast(profile_result.data.customers).stripe_customer_id)

Yep, same here! Please fix when possible

Same problem over here, is there a fix for this issue??

I checked the schema in my environment, and the type relationships generated by Supabase with the same schema you provided were correctly inferred.

スクリーンショット 2024-09-29 19 46 12

Based on your screenshot, I noticed that not only the one-to-one relationships, but also the text type columns are inferred as any type, which is concerning. Are the types being generated correctly by the Supabase CLI, and is the output file properly generated?

Here’s what I did

  • npx create-next-app -e with-supabase
  • add .env.local to supabase envrioment variables.
  • execute your schema sql in dashboard.
  • npx supabase gen types --lang=typescript --project-id XXXXXX --schema public > database.types.ts
  • add createServerClient to Database type generated by supabase cli.
 export const createClient = () => {
-  const cookieStore = cookies();
+       const cookieStore = cookies();

-  return createServerClient(
-    process.env.NEXT_PUBLIC_SUPABASE_URL!,
-    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
-    {
-      cookies: {
-        getAll() {
-          return cookieStore.getAll();
-        },
-        setAll(cookiesToSet) {
-          try {
-            cookiesToSet.forEach(({ name, value, options }) => {
-              cookieStore.set(name, value, options);
-            });
-          } catch (error) {
-            // The `set` method was called from a Server Component.
-            // This can be ignored if you have middleware refreshing
-            // user sessions.
-          }
-        },
-      },
-    },
-  );
+       return createServerClient<Database>(
+               process.env.NEXT_PUBLIC_SUPABASE_URL!,
+               process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
+               {
+                       cookies: {
+                               getAll() {
+                                       return cookieStore.getAll();
+                               },
+                               setAll(cookiesToSet) {
+                                       try {
+                                               cookiesToSet.forEach(({ name, value, options }) => {
+                                                       cookieStore.set(name, value, options);
+                                               });
+                                       } catch (error) {
+                                               // The `set` method was called from a Server Component.
+                                               // This can be ignored if you have middleware refreshing
+                                               // user sessions.
+                                       }
+                               },
+                       },
+               },
+       );
 };

I think it will be difficult to investigate without also giving the related SQL schema.

Absolutely not fixed. A one-to-one relationship should not expect an array in this case.

You are right, I updated my previous message to include the tables to reproduce the issue.

Absolutely not fixed. A one-to-one relationship should not expect an array in this case.

Tables

CREATE TABLE companies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    scraped_site_id UUID,
    description TEXT,
);

CREATE TABLE surveys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID REFERENCES companies(id),
);

CREATE TABLE scraped_sites (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    raw_data JSONB
);

Typescript

const { data, error } = await supabase
      .from("surveys")
      .select(
        `
        company:companies ( name, description, scraped_site:scraped_sites ( raw_data ) )
      `
      )
      .eq("id", surveyId)
      .single();

Capture d’écran 2024-09-29 à 09 57 31

Last version used (2.45.4)

Same problem here.

Seems fixed with .single() but not without it - the relationship is the same whether .single() or not though, it’s surprising that you’d get an object with .single() and an array normally. Using a foreign key to a primary key on another table is very common and it’s very weird that you end up getting an array of results for that.

Using !inner also resolves it, which is also weird, because the inner join doesn’t change the cardinality.

Hi there, would you mind providing a minimal reproduction example of your case with both the SQL to generate the relation and the select query you’re running ?

.single should only affect the “root result” not the underlying relationship type. If I can reproduce I could investigate into this, please feel free to open a new issue and ping me on it.

I’m locking this issue as the original problem is fixed and similar ones will likely be under a different scope. Please open a new issue following the typing bug reporting infos:

Bug Reporting: You can open an issue on GitHub. Please include:

The generated Database type used to instantiate the client (e.g., createClient<Database>(process.env.SUPABASE_URL, process.env.SUPABASE_ANON_KEY)). If possible a minimal SQL declaration resulting in such Database type. The query where type inference failed (e.g., .from(‘which-table’).select(‘which-query’)). Your TypeScript version (npx tsc -v).

Please fix!

same problem for me