Postgraphile: Support foreign key joins in views (nested queries).

[aabluedragon]

Suggestion: Support nested column joins (foreign key) in views as well (not only on tables).

e.g

{
  allPeopleView {
    nodes {
      purchasesByPersonId {
        ...
      }
    }
  }
}

Seems that now it's only possible to get the values as they are, not their related nested entities by FK.

[benjie]

Views don't have foreign keys, so we can't do the necessary reflection to determine those relations automatically 😞

[aabluedragon]

Wouldn't it be possible to infer the required data by inspecting the view's origin column and table, and see the foreign key in the origin table?

for example, given the following view

CREATE VIEW data.active_person_view AS
 SELECT person.id,
    person.name,
    person.group_id
   FROM data.person
  WHERE active = true;

PostGraphQL can iterate each column in the view, and check it's origin table (FROM clause) column for a foreign key, when a FK is found for a view column in the origin table (person.group_id), it may register the relation groupByGroupId as a GraphQL attribute, same as it does for a table.

Sharing my thoughts (I haven't looked at the "guts" of PostGraphQL, do not know much of it's internals and how it reflects on a given schema 👀 🤔 💭 ).

If that's actually not possible, it renders views pretty much useless in many cases when used over PostGraphQL.

[benjie]

Can you point to any example OSS project that does this? It would be a good source for inspiration. I think that without an incredibly large amount of work it'd only work for the simplest of views, as soon as you start using sub queries, common table expressions, function calls and so on it'd get a lot more complex. And half supporting it but then removing it when the view evolves is going to be a poor developer experience; I think it'd have to be all or nothing.

[aabluedragon]

I'm not aware or any other project that does this, however I do think it's worthwhile to support that only for bare mirror columns (not for calculated columns or subquery columns).

In many cases I would like to expose "mirror" views to PostGraphQL instead of the origin table to provide another layer of separation between what's exposed and what's hidden from the API, also suggested during a discussion on PostgREST.

The downside is that by supporting relations on views, you end up yet again exposing the table of each column relation anyway.
An alternative might be to support _Computed Columns_ over views as well.

[mgallagher]

Before I tried playing around with PostGraphQL, I saw this bit from the README:

If you fundamentally disagree with a one-to-one mapping of a SQL schema to an API (GraphQL or otherwise) this section is for you... If you still can’t get over the one-to-one nature of PostGraphQL consider the following arguments why putting your business logic in PostgreSQL is a good idea:
...
2) PostgreSQL allows you to hide implementation details with views of your data. ...

From that, I assumed that views were fine to use in PostGraphQL. Indeed they work if you don't need to span relationships, but this is GraphQL we're talking about 😆

It's worth noting that PostgREST supports joins in views. In fact, @calebmer helped improve view relation detection in PostgREST. Here is a simple example database that shows how this works:

CREATE SCHEMA app;
CREATE TABLE app.authors (id serial PRIMARY KEY, name text);
CREATE TABLE app.books (title text, author_id integer REFERENCES app.authors (id));

INSERT INTO app.authors (id, name) VALUES (1, 'Steinbeck');
INSERT INTO app.books (title, author_id) VALUES ('The Grapes of Wrath', 1)

CREATE SCHEMA app_api;
CREATE VIEW app_api.authors AS (SELECT * FROM app.authors);
CREATE VIEW app_api.books AS (SELECT * FROM app.books);

Now if you start PostgREST using the app_api schema, you can perform a GET on localhost:3000/books?select=*,author{*} and it would return this:

[
  {
    "title": "The Grapes of Wrath",
    "author_id": 1,
    "author": {
      "id": 1,
      "name": "Steinbeck"
    }
  }
]

Just some food for thought. Personally I would love to be able to use views in this way.

[calebmer]

Yep, @mgallagher found it. I actually implemented a heuristic to support this feature for PostgREST and it was one of my first major contributions to open source (see: https://github.com/begriffs/postgrest/pull/353). I, like @mgallagher, was very interested in this style of database construction using views as proxies to the underlying tables.

I’d be interested to see someone try and implement this in PostGraphQL. The approach I used in PostgREST used a regular expression to detect matching columns in _the view SQL definition_. I’m sure there is a better way to implement this 😊. We could either use a SQL parser, or see if there are any internal Postgres functions to arrive at a better implementation. I’m sure there is a Postgres function by now that exposes this information, but it might require some source code digging in postgres/postgres itself to find 😣

The reason I don’t find the view approach attractive at the moment now is for one big reason. Row level security. You can’t create row level security policies on views (at least last time I tried, I did submit a proposal to the PG email group, but I don’t think that went anywhere), and if you create a RLS policy on the underlying table it won’t work because the view uses the role of the view definer with the RLS policies. What I ended up doing was creating a second view that restricted what a user could access and allowed mutations on that view, but not on the public view. However, with PostGraphQL it is much easier to define custom mutations then with PostgREST thanks to GraphQL, so this may not even be a problem.

I’d love to hear your thoughts @mgallagher on the RLS problem, and I’d love to see someone investigate further how we can expose references from views 😊. If you can find a way to detect the view’s foreign keys in Postgres then I can direct you to where you need to look in PostGraphQL’s source code to hook up the appropriate references.

Here is where we add the relationships between tables with a foreign key. I assume we would add another block of code with a second heuristic to wire up views with references: https://github.com/calebmer/postgraphql/blob/53c6a3139b9c7247e5dc1d6761f610ddef61f384/src/postgres/inventory/addPgCatalogToInventory.ts#L38-L76

[Tolli81]

As I was assuming the same as @mgallagher about relations of views being supported, this triggered my interest. :)

-- This is my view graphql.tournaments, defined as:
--      SELECT * FROM public.k_tournaments
select oid, * FROM pg_class where relname like 'tournaments'
--> oid=21100

-- In pg_rewrite, the ev_action looks interesting
SELECT * FROM pg_rewrite
WHERE ev_class=21100

-- Analyzing the ev_action structure in this pg_rewrite entry, there are 12
-- occurances of ":resorigtbl 18710"
-- What table might that be?  Let's take a look
SELECT * FROM pg_class WHERE oid=18710
--> relname=k_tournaments - the source table indeed

Taking a closer look, there's a lot more info in the ev_action column, which as per https://www.postgresql.org/docs/9.6/static/catalog-pg-rewrite.html is a query tree:

        :targetList (
        {
            TARGETENTRY 
            :expr 
            {
                VAR 
                :varno 3 
                :varattno 1 
                :vartype 23 
                :vartypmod -1 
                :varcollid 0 
                :varlevelsup 0 
                :varnoold 3 
                :varoattno 1 
                :location 42
            } 
            :resno 1 
            :resname tmt_id 
            :ressortgroupref 0 
            :resorigtbl 18710 
            :resorigcol 1 
            :resjunk false
        } 

So for any given view, by figuring out target table(s) + columns, one might find all keys for said table, and consider those indicies where all the columns used in any given index are present in the view being analyzed.

Expirimenting a bit further, I conclude that resname is the column name in the view, and resorigcol the 1-indexed column number - so the name can be obtained from pg_attribute

SELECT attname FROM pg_attribute WHERE attrelid=18710 AND attnum=1
--> attname=tmt_id

Hope this helps somewhat - did either of you take any stab at this yet @calebmer / @mgallagher ? If not, I might give it a shot.

[benjie]

Nice one @tolli81; dig in!

[benjie]

Sounds like you want to filter to where rulename = '_RETURN'

I was expecting to have to do this figuring out based on the raw SQL of the query (as Caleb did in PostgREST); finding out that PostgreSQL makes it available in this format from a catalog is excellent news!

[Tolli81]

I'll start poking around tonight.

I realized once in bed last night that what I described last night only goes halfway though, as we probably need to gather this info for all the views, and then figure out relations. That way, for my example in my case, a foreign key from table public.competitions to public.tournaments would be disovered from view graphql.competitions to view graphql.tournaments.

Haven't considered anything regarding RLS yet, as I've never taken advantage of that feature.

[calebmer]

@Tolli81 you don’t need to worry about RLS for this. It’s a different (but conquerable) issue with using views 😊

This is awesome news! What I did in PostgREST which you could mimic with a better underlying detection mechanism is that I detected key “synonyms.” So what columns in views were synonymous with the columns/keys in a table. After finding synonyms you can “raise” up all of the relations, creating a new relation wherever we see one using a synonymous table. So as a rough illustration. Let’s say we have the relation:

t1 -> t2

We also have the synonyms:

t1 = v1
t2 = v2

Then when we raise t1 relations to also have a v1 relation we would get:

t1 -> t2
v1 -> t2

When we raise t2 to v2 we would get:

t1 -> t2
v1 -> t2
t1 -> v2
v1 -> v2

Which represents all the relationships that we want for foreign key joins.

This is just a description of what I did. If you can think of a more efficient implementation then by all means do that 😊

[Tolli81]

Thanks for that info @calebmer. I've started the parsing already, hope to move this forward somewhat over the next few days.

[troelsh]

@Tolli81 i hope i did not throw you off your track completely with the view table relations on gitter. Again if there are anything i can do to help please speak up. 👍

[SebAlbert]

I'd also love to see automatic relationship detection between VIEWs (or VIEWs and underlying TABLEs), but I'm still wondering if this can be rigorously possible at all. How does the derivation from pg_catalog handle references in VIEWs that are not simply pulling through the original foreign key columns from the underlying TABLE, but maybe "calculating" a reference?

In the meantime, would it be feasible for the PostGraphQL user to define explicit relationships by computed columns on views that return setof referenced_table (which would then in turn hopefully automatically have its foreign key relationships available)? I.e., if I have a reference v1 -> t2 via computed column on v1 returning setof t2, and a foreign key reference t2 -> t3, can I go from v1 -> t2 -> t3 in GraphQL with my approach? And could this also go from view to view, such as v0 -> v1 and then transitively onwards as before if I have a computed column on v0 returning setof v1?

[benjie]

@SebAlbert If computed columns on views don't already work then that's a good feature suggestion; please check and file it as a separate feature request issue 👍

[SebAlbert]

I'm new to GraphQL entirely, but will see if I can try it out next week.

[Tolli81]

Unfortunately I haven't been able to continue this work for a couple of weeks, but hope to pick up where I left off next week.

[brysgo]

@Tolli81 - we're rooting for you!

[loxy]

Is there any progress on this feature?

[dts]

This is a pretty important use case - using views is a key mechanism for building abstractions inside postgres, and therefore postgraphile! Anything anybody can do to help move this forward?

[benjie]

Plan currently is to add relations via smart comments, this would also be used by #91. Lots to do before I get around to that though. Let me know if you want to sponsor the work 🙂

[dts]

Happy to talk sponsorship - this is great stuff. My short-term use case was solved with this functions, but views are important also.

[benjie]

I'd still love some sponsorship to specifically dig into views 👍 Reach out over email, twitter or gitter if you'd like to discuss this.

[benjie]

Related issue:

https://github.com/graphile/graphile-build/issues/179

[troelsh]

@benjie We reverted away from postgraphile and did our own script that generates "physical" files for each table, that we then can edit or add on top of.

The drawback is that schema changes in the DB will require manual changing of the corresponding files. But that solved our headache for now.

If we hadn't done that i would gladly have talked some sponsorship. :/

[benjie]

It's possible that this plugin may work for view relations; I've not tried it yet:

https://github.com/tngmichael/pg-relation-tag-plugin

[benjie]

@troelsh Fair enough! Have you seen graphile-utils? It gives you a graphql-tools-style way of extending the generated schema: https://www.graphile.org/postgraphile/extending/#the-easy-way-graphile-utils

i.e. you can do

extend type MyView {
  relatedThingByViewColumnId: RelatedThing 
}

and add your own custom resolver for that (which can call into our look-ahead enabled SQL planning if you want).

My ultimate aim is to make it so people building their own GraphQL schemas (as you are doing currently) can still leverage the "look-ahead" planning features that graphile-build enables without having to get down and dirty with our low-level plugin API (which can be somewhat overwhelming!). We've got a way to go before we get there, but we're definitely heading in the right direction!

[troelsh]

@benjie as we reverted away i have been out of the loop with postgraphile. However i have some new projects where it would kick ass to use it.

I will try and revisit det docs.

OT are there any plans of attending any conference or meetups with regards to postgraphile? I have sent you guys a mail at [email protected]

[benjie]

I was at GraphQL EU and I’ll be at GraphQL Finland 👍 I’d love to attend more meetups/etc, but I’ve got quite limited time at the moment. Are there any meetups in particular you’re thinking about?