1:1 Relationship Nullability

[olirice]

          I'm running `pg_graphql` version **1.5.7** and no tables have RLS installed.

Yes, the relationships are as you described: party.id = organisation.id and party.id = contact.id. This sets up a one-to-one relationship where a party can be either a contact or an organisation, but not necessarily both.

The issue is that in the generated GraphQL schema, the Party type has non-nullable contact and organisation fields. This implies that both fields are always present, which isn't the case in our data model. As a result, Apollo Client expects both contact and organisation fields on Party to be non-null, leading to errors when one of them is null.

Here's a simplified SQL schema to reproduce the issue:

-- Create the party table
CREATE TABLE party (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    kind VARCHAR NOT NULL  -- Indicates whether the party is a 'contact' or an 'organisation'
);

-- Create the contact table
CREATE TABLE contact (
    id UUID PRIMARY KEY,  -- Also a foreign key to party.id
    given_name TEXT,
    family_name TEXT,
    FOREIGN KEY (id) REFERENCES party(id) ON DELETE CASCADE
);

-- Create the organisation table
CREATE TABLE organisation (
    id UUID PRIMARY KEY,  -- Also a foreign key to party.id
    name TEXT NOT NULL,
    FOREIGN KEY (id) REFERENCES party(id) ON DELETE CASCADE
);

-- Insert sample data
-- Party as a contact
INSERT INTO party (id, kind) VALUES ('uuid-contact-1', 'contact');
INSERT INTO contact (id, given_name, family_name) VALUES ('uuid-contact-1', 'John', 'Doe');

-- Party as an organisation
INSERT INTO party (id, kind) VALUES ('uuid-org-1', 'organisation');
INSERT INTO organisation (id, name) VALUES ('uuid-org-1', 'Acme Corp');

And here's the resulting GraphQL schema generated by pg_graphql:

type Party implements Node {
  nodeId: ID!
  id: UUID!
  kind: String!
  contact: Contact!           # Non-nullable
  organisation: Organisation! # Non-nullable
  # Other fields...
}

type Contact implements Node {
  nodeId: ID!
  id: UUID!
  givenName: String
  familyName: String
  # Other fields...
}

type Organisation implements Node {
  nodeId: ID!
  id: UUID!
  name: String!
  # Other fields...
}

As you can see, both contact and organisation fields on the Party type are non-nullable (Contact! and Organisation!). In our data model, since a Party is either a Contact or an Organisation, one of these fields will be null. This mismatch causes Apollo Client to throw errors when it encounters a null value where it expects a non-nullable field.

Cheers.

_Originally posted by @fourbytes in https://github.com/supabase/pg_graphql/issues/409#issuecomment-2375588014_

[fourbytes]

Cheers for the quick fix