Foreign key relationship isn't one to one
I have two foreign keys, both of which are set to primary in their respective tables. But when I generate the types, the filed "isOneToOne" is set to "false", and I'm getting an array type.
I'm using typescript
9 Replies
Could you please post both table definitions (maybe with pseudonymized table and column names, if necessary)? It's hard to tell what's going on with so little information.
Here are the schemas. In the "my_tickets_table" I have two foreign keys to the two tables below. Both foreign keys are the primary keys in their tables ("id" in REFERENCE 1 and "user_id" in REFERENCE 2). The issue is that supabase isn't creating a one-to-one.
MAIN: “my_tickets” schema:
create table public.my_tickets (
id uuid not null default gen_random_uuid (),
status public.ticket_progress_status not null default 'open'::ticket_progress_status,
created_at timestamp with time zone not null default (now() AT TIME ZONE 'utc'::text),
assigned_to_user uuid null,
business_id uuid null,
constraint my_tickets_pkey primary key (id),
constraint my_tickets_id_key unique (id),
constraint my_tickets_assigned_to_user_fkey foreign KEY (assigned_to_user) references my_users (user_id) on update CASCADE on delete set null,
constraint my_tickets_business_id_fkey foreign KEY (business_id) references my_businesses (id) on update CASCADE on delete set null
) TABLESPACE pg_default;
REFERENCE 1: “my_businesses” schema:
create table public.my_businesses (
id uuid not null default gen_random_uuid (),
name text null,
created_at timestamp with time zone not null default (now() AT TIME ZONE 'utc'::text),
constraint my_businesses_pkey primary key (id),
constraint my_businesses_name_key unique (name)
) TABLESPACE pg_default;
REFERENCE 2: “my_users” schema:
create table public.my_users (
user_id uuid not null default gen_random_uuid (),
created_at timestamp with time zone not null default (now() AT TIME ZONE 'utc'::text),
name text null,
constraint my_users_pkey primary key (user_id)
) TABLESPACE pg_default;
Supabase doesn't create a one-to-one-relationship because this isn't a one-to-one-relationship, as defined. Consider:
This is a 1-to-n-relationship - one business can have N tickets.
If you want to make it a one-to-one-relationship, there needs to be a backlink from business to ticket:
But this makes no sense to me from a domain perspective - certainly a business should have many tickets?
I meant 1 to 1 when I query the tickets directly, since the ticket is referencing one business, I was expecting it to be one to one?
If I do this:
const ticketsQuery = supabase.from('my_tickets').select(
id,
assigned_to_user,
created_at,
status,
companies (
name
)
)
The companies field is an array type
type TicketType = {
id: any;
assigned_to_user: any;
created_at: any;
status: any;
companies: {
name: any;
}[];
yes, that's not my intentionIt's quite funky too that Supabase generates an "any" type here. That's not common.
Something must be broken with your setup, but I don't immediately see what. This is strange behaviour.
Yeah I made a different post (https://discord.com/channels/839993398554656828/1443513371856207922) on the "any" type, kinda beats the whole purpose here
Have you generated a Database.ts?
What's the output of
npx supabase gen types typescript --local?
When calling createClient, did you remember to instantiate it as a generic type (i.e. do createClient<Database>(...), not plain createClient(...)?The issue was that I didn't instantiate the client with the "Database" type. Thank you for solving it 😊
No problem 🙂