Graphql-engine: determine a view column's nullability from underlying tables

[elnygren]

Every field of a schema generated from SQL View is nullable/optional even though the underlying table might have NOT NULL fields that are NOT-nullable if the table we're tracked directly.

I've tested and found this problem on the alpha42 version.

This is a problem when working with statically typed languages such as ReasonML. You really don't want to spam option monad's map/flatmap if you don't need to. Basically, this ruins SQL views in a ReasonML / TypeScript (autogenerated types) setup entirely as suddenly everything is nullable.

[0x777]

@elnygren Unfortunately without postgres providing this information, we can't do much at graphql-engine's layer. https://stackoverflow.com/questions/17301323/why-are-my-views-columns-nullable

[elnygren]

@0x777 thanks for the answer!

Perhaps there should be some kind of way to produce the GraphQL schema based on
a) postgresql schema; and
b) user defined settings ?

With b) you could also address other issues such as https://github.com/hasura/graphql-engine/issues/981

Anyhow, I'll leave this into your good hands. :)

[cbzehner]

@0x777 in the discussion you link to they explain

If you tilt your head and squint just right, that kind of makes sense. The column in the view isn't declared NOT NULL. Just the column in the base table.
The column pg_attribute.attnotnull is updatable. You can set it to TRUE, and that change seems to be reflected in the information_schema views.

While this makes sense, it does appear it should be possible to derive the null property of the view columns based on the underlying table. This feels like something that Hasura could provide, even if it's not an immediate priority for y'all.

Could we get this on the long-term roadmap for enhancements or something similar?

[0x777]

@cbzehner

While this makes sense, it does appear it should be possible to derive the null property of the view columns based on the underlying table.

Unfortunately this is not easy. Mapping underlying table's columns to columns exposed through a view is only possible in the simplest of the cases and I'm not quite sure if the metadata exposed by postgres about a view is sufficient to determine this.

@elnygren

Perhaps there should be some kind of way to produce the GraphQL schema based on
a) postgresql schema; and
b) user defined settings ?

This should definitely be possible.

[drewwestphal]

This is also an issue for me. I, too, am using a strongly typed language and this renders views practically unusable.

I wonder if an override thru the metadata system which implicitly adds not nulls in the query to protect the integrity of the result-set would be a more feasible workaround compared to complex introspection which even postgres opts not to do.

[drewwestphal]

Adding in a note from @coco98 in the discord, in case it helps here. Given the below perhaps the initial issue name fits this more (as introspecting this may not be possible).

[davetapley]

Ha, I went to all the trouble of making a repro repo/explanation for this, and then found this existing issue 🤪

I might have some more insight though:

I see @0x777 already linked this: https://stackoverflow.com/questions/17301323/why-are-my-views-columns-nullable
I also found that, and a workaround involving the pg_attribute system table. I was able to implement that workaround and confirm that (at least in psql) columns in a view will show up as not null.

I thought that was the solution, but unfortunately Hasura still makes the fields nullable in the the GraphQL schema 😞

I've made all this available and documented here:
https://github.com/happymondayco/pg-nullable-view-hasura

[devuxer]

@coco98, Is this still a priority?