Interfaces/Union

[jlukas99]

Hi, how do I need to create the tables to generate my interfaces and unions, or how do I edit the scheme file so I can implement this myself?

[olirice]

so sorry for the delay getting back to you! that isn't usual, I had notifications misconfigured

There will likely be a node interface once a global id has been implemented but one of the goals for pg_graphql is to be as configuration free as possible, so we don't have any plans to implement user defined interfaces.

as far as I can tell that is consistent with similar "reflect graphql from db" projects but if you're aware of any prior art e.g. from Graphile, Hasura etc, for how they do it I'd love to take a look!

[bryanmylee]

@olirice Just wanted to bring this up again, but it would be really useful to generate type unions based on PostgreSQL's table inheritance.

Assuming I have a person table and a employee table that inherits from it. These tables automatically generate the Person and Employee GraphQL types but makes no relationships between them. Ideally, the generated schema would look like:

type Employee implements Person {
  salary: Int!
}

which would allow a query returning [Person!]! to include Employee objects in the result and allow inline fragments to discriminate the types properly.

[bryanmylee]

I would love to contribute to the project @olirice, but could use some help understanding where I should look with regards to the PSQL -> GraphQL type generation, and the GraphQL -> PSQL query transformation.

[olirice]

using table inheritance for type unions is a very interesting idea @bryanmylee

table inheritance is pretty out-of-fashion and I recall being told that its no longer recommended. I'll ask the postgres team and see if they have any strong opinions

There could be some edge cases around computed columns and views not being able to conform to the interface

any thoughts @alaister?

---

help understanding where I should look with regards to the PSQL -> GraphQL type generation

Contributions to the project would be great. This feature is pretty significant though so possibly not the best place to start:

These would be the steps:

1. Update the query that loads the database schema state to track table inheritance https://github.com/supabase/pg_graphql/blob/master/sql/load_sql_context.sql (must not have significant impact on query performance)

2. Update the relevant SQL types to deserialize it https://github.com/supabase/pg_graphql/blob/master/src/sql_types.rs

3. Add a new generic __Type to represent inherited interfaces. You could model it after __Type::NodeInterface and NodeInterfaceType

4. Add the interface/s to the relevant NodeTypes https://github.com/supabase/pg_graphql/blob/f07ac71bf95907e5cc3e684c323d176b96908e6d/src/graphql.rs#L1676-L1689

I don't think any of the resolver logic would need to change off the top of my head since all the relevant fields are already present on the inherited types

[alaister]

Interfaces + Unions (Polymorphism) would certainly be a useful feature addition. However, I think inherited tables are not the best way of implementing them. Personally, I don't find them to be very ergonomic.

Perhaps we could hint to pg_graphql (via the @graphql({}) comment) that we want a type to appear as part of an interface/union. This is similar to how Postgraphile V5 is handling this.

[bryanmylee]

I've only started using PostgreSQL extensively recently so I'm curious: what seems to be the issue with inherited tables?

[olirice]

IIRC its a bunch of little things

• can negatively impact query performance when there are lots of child tables
• constraints/indexes aren't inherited
• can't cascade alter statements to child tables
• a big fraction of its use-case was supplanted by declarative partitioning in pg 10
• limited support in ORMS
• can be complex

but if you know what you're doing I don't know of anything that's really wrong with inheritance in pg

[bryanmylee]

Just as an update for others who have the same problem to solve, our team has gotten rid of table inheritance in favor of composed tables due to some glaring issues with inheritance.

The most egregious behavior was that entries in child tables will show up in queries on the parent table, but those entries will not be referencable in foreign keys, which broke our use case.

create table user (
  id uuid not null default gen_random_uuid (),
  constraint user_pkey primary key (id)
);

create table employee (
  name text not null
) inherits (user);

create table member (
  user_id uuid not null,
  constraint member_user_id_fk foreign key (user_id)
    references user (id) on delete cascade
);

insert into employee(id, name)
  values ('b28e7d43-d46f-4d20-8a06-db6342569553', 'Bryan');

select * from user; /**
+--------------------------------------+
|                                   id |
+--------------------------------------+
| b28e7d43-d46f-4d20-8a06-db6342569553 |
+--------------------------------------+
*/

insert into member(user_id)
  values ('b28e7d43-d46f-4d20-8a06-db6342569553'); /**
'b28e7d43-d46f-4d20-8a06-db6342569553' does not exist in foreign table user.
*/

Composition resolves all our use cases, and is compatible with pg_graphql's interface as it is now.

create table user (
  id uuid not null default gen_random_uuid (),
  constraint user_pkey primary key (id)
);

create table employee (
  id uuid not null default gen_random_uuid (),
  constraint employee_pkey primary key (id),
  -- the unique constraint allows pg_graphql to create a 1-to-1
  user_id uuid not null unique,
  constraint employee_user_id_fk foreign key (user_id)
    references user (id) on delete cascade,
  name text not null
) inherits (user);

create table member (
  user_id uuid not null,
  constraint member_user_id_fk foreign key (user_id)
    references user (id) on delete cascade
);

insert into user(id)
  values ('b28e7d43-d46f-4d20-8a06-db6342569553');
insert into employee(id, user_id, name)
  values (
    'b28e7d43-d46f-4d20-8a06-db6342569553',
    'b28e7d43-d46f-4d20-8a06-db6342569553',
    'Bryan'
  );

-- works fine now.
insert into member(user_id)
  values ('b28e7d43-d46f-4d20-8a06-db6342569553');

query {
  userCollection {
    edges { 
      node {
        employee { # any extra child fields from employees
          name
        }
      }
    }
  }
}

One small rough edge is that we have to insert records into two tables whenever we want to create new entries, but that's something that can be worked around, and #294 will solve the pain points eventually.