Wrong type for joins with one-to-one relationships #471

probablykasper · 2023-08-04T23:02:13Z

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

Database typings are not correct on table join for entity to one relationship supabase-js#723

The text was updated successfully, but these errors were encountered:

aslakhellesoy · 2023-08-07T11:32:00Z

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)

aslakhellesoy · 2023-08-25T11:17:26Z

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

FocusCookie · 2023-09-10T11:07:53Z

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
}

timlgl · 2023-09-12T06:08:52Z

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?

orels1 · 2023-12-17T01:48:28Z

Seems to still be an issue

albertonii · 2024-01-30T09:51:38Z

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

Silventino · 2024-03-28T12:45:49Z

Same problem here.

DhenPadilla · 2024-05-09T18:49:53Z

Yep, same here! Please fix when possible

chrissy0 · 2024-05-20T15:19:12Z

Still a problem, please fix!

fvermaut · 2024-06-07T19:05:21Z

same problem for me

sanagurcia · 2024-09-10T11:16:02Z

Please fix!

avallete · 2024-09-11T14:21:38Z

Hi there,

I believe this bug might have been fixed. I tried to reproduce the issue with the following setup:

SQL Setup:

-- Create profiles table
CREATE TABLE profiles (
  id TEXT PRIMARY KEY
);

-- Create customers table with a foreign key to profiles
CREATE TABLE customers (
  id TEXT PRIMARY KEY REFERENCES profiles(id),
  stripe_customer_id TEXT UNIQUE
);

INSERT INTO profiles (id) VALUES ('1');
INSERT INTO customers (id, stripe_customer_id) VALUES ('1', '1');

TypeScript Test:

const profile_result = await supabase
  .from('profiles')
  .select('id, customers (stripe_customer_id)')
  .single();

console.log(profile_result.data);

With this setup, I got the expected results both from a types and runtime perspective:

TypeScript Types:

const profile_result: PostgrestSingleResponse<{
  id: string;
  customers: {
    stripe_customer_id: string | null;
  } | null;
}>

Runtime Output:

{
  "id": "1",
  "customers": {
    "stripe_customer_id": "1"
  }
}

Could you please check on your end if it's fixed for you as well?

probablykasper · 2024-09-11T19:44:40Z

Looks like it's fixed yeah

lorraineS · 2024-09-29T07:58:30Z

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();

Last version used (2.45.4)

hawkcookie · 2024-09-29T08:25:40Z

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.

lorraineS · 2024-09-29T08:39:00Z

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.

hawkcookie · 2024-09-29T10:49:38Z

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

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.

lorraineS · 2024-10-05T12:44:30Z

J'ai vérifié le schéma dans mon environnement et les relations de type générées par Supabase avec le même schéma que vous avez fourni ont été correctement déduites.

D'après votre capture d'écran, j'ai remarqué que non seulement les relations un à un, mais aussi les colonnes de type texte sont déduites de n'importe quel type, ce qui est inquiétant. Les types sont-ils générés correctement par la CLI Supabase et le fichier de sortie est-il correctement généré ?

Voilà ce que j'ai fait

npx create-next-app -e with-supabase
ajoutez .env.local aux variables d'environnement supabase.
exécutez votre schéma SQL dans le tableau de bord.
npx supabase gen types --lang=typescript --project-id XXXXXX --schema public > database.types.ts
ajoutez createServerClient au type de base de données généré par 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.
+                                       }
+                               },
+                       },
+               },
+       );
 };

Je pense qu'il sera difficile d'enquêter sans donner également le schéma SQL associé.

Absolument pas fixe.
Une relation un à un ne devrait pas s'attendre à un tableau dans ce cas.

Vous avez raison, j'ai mis à jour mon message précédent pour inclure les tableaux pour reproduire le problème.

Thank you, that fixed the problem.

elyobo · 2025-02-04T03:11:13Z

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.

avallete · 2025-02-04T03:27:00Z

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

elyobo · 2025-02-04T03:30:26Z

For the .single() vs non-single behaviour, I confirmed with the example SQL (after removing some trailing commas!) above to ensure that it wasn't anything of mine.

I'll check now to see whether !inner also resolves it.

probablykasper added the bug Something isn't working label Aug 4, 2023

steve-chavez transferred this issue from supabase/supabase-js Aug 28, 2023

hexrw mentioned this issue Dec 26, 2023

Wrong response type for referenced table data when using the .single() modifier #512

Closed

2 tasks

This was referenced Jun 9, 2024

Database typings are not correct on table join for entity to one relationship #408

Closed

One-to-one relationship typescript types #434

Closed

jdgamble555 mentioned this issue Jul 3, 2024

Inconsistent Typing for Joined Field in Supabase #546

Closed

2 tasks

probablykasper closed this as completed Sep 11, 2024

supabase locked as resolved and limited conversation to collaborators Feb 4, 2025

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Wrong type for joins with one-to-one relationships #471

Wrong type for joins with one-to-one relationships #471

probablykasper commented Aug 4, 2023 •

edited

Loading

aslakhellesoy commented Aug 7, 2023 •

edited

Loading

aslakhellesoy commented Aug 25, 2023

FocusCookie commented Sep 10, 2023

timlgl commented Sep 12, 2023

orels1 commented Dec 17, 2023

albertonii commented Jan 30, 2024

Silventino commented Mar 28, 2024

DhenPadilla commented May 9, 2024

chrissy0 commented May 20, 2024

fvermaut commented Jun 7, 2024

sanagurcia commented Sep 10, 2024

avallete commented Sep 11, 2024

probablykasper commented Sep 11, 2024

lorraineS commented Sep 29, 2024 •

edited

Loading

hawkcookie commented Sep 29, 2024

lorraineS commented Sep 29, 2024 •

edited

Loading

hawkcookie commented Sep 29, 2024 •

edited

Loading

lorraineS commented Oct 5, 2024

elyobo commented Feb 4, 2025 •

edited

Loading

avallete commented Feb 4, 2025 •

edited

Loading

elyobo commented Feb 4, 2025 •

edited

Loading

Wrong type for joins with one-to-one relationships #471

Wrong type for joins with one-to-one relationships #471

Comments

probablykasper commented Aug 4, 2023 • edited Loading

Bug report

Describe the bug

To Reproduce

Expected behavior

System information

Additional context

aslakhellesoy commented Aug 7, 2023 • edited Loading

aslakhellesoy commented Aug 25, 2023

FocusCookie commented Sep 10, 2023

timlgl commented Sep 12, 2023

orels1 commented Dec 17, 2023

albertonii commented Jan 30, 2024

Silventino commented Mar 28, 2024

DhenPadilla commented May 9, 2024

chrissy0 commented May 20, 2024

fvermaut commented Jun 7, 2024

sanagurcia commented Sep 10, 2024

avallete commented Sep 11, 2024

SQL Setup:

TypeScript Test:

TypeScript Types:

Runtime Output:

probablykasper commented Sep 11, 2024

lorraineS commented Sep 29, 2024 • edited Loading

hawkcookie commented Sep 29, 2024

lorraineS commented Sep 29, 2024 • edited Loading

hawkcookie commented Sep 29, 2024 • edited Loading

lorraineS commented Oct 5, 2024

elyobo commented Feb 4, 2025 • edited Loading

avallete commented Feb 4, 2025 • edited Loading

elyobo commented Feb 4, 2025 • edited Loading

probablykasper commented Aug 4, 2023 •

edited

Loading

aslakhellesoy commented Aug 7, 2023 •

edited

Loading

lorraineS commented Sep 29, 2024 •

edited

Loading

lorraineS commented Sep 29, 2024 •

edited

Loading

hawkcookie commented Sep 29, 2024 •

edited

Loading

elyobo commented Feb 4, 2025 •

edited

Loading

avallete commented Feb 4, 2025 •

edited

Loading

elyobo commented Feb 4, 2025 •

edited

Loading