🔥 Support for Postgres Views

[eager ivy]

I am currently working on Directus support for:

• Views
• Materialized Views
• Foreign Tables

And am eager to share my current working version with you here 🙂
Event if there are still a lot to take care, such as:

• better "primary key" identification implementation;
• readonly for views;
• better UX: it would be neat for readonly views and editable tables to display with different icons;
• security definers;
• support for other databases.

Here is the first basic working version of this beauty in video.
What do you think?

[halcyon parcel]

I can hardly contain my excitment. This is a total game changer for us and our rollout of Directus.

The big question though, is how is primary key requirements handled? in postgres it is always possible to create an artificial primary key on a view with row(). not sure about others. and how are updates handled? most of our views would not be considered updateable without custom sql.

THANK YOU FOR SPEARHEADING THIS!!

[eager ivy]

Same here; I need to unlock foreign tables to work with a Directus-powered data-driven solution that allows a main postgres database to sync & process data from other datasources.

[eager ivy]

For now, the views need an "id" column, which is currently hardcoded as the PK. My current focus being to allow accessing all postgres collections at least in readonly mode: not only base tables but also ordinary tables, views, materialized views and foreign tables.
As mentioned, there's plenty to work on and this whole thing will benefit from being reviewed by maintainers or the core team before going too much down the rabbit hole.

[halcyon parcel]

Yes, from what I understand they do have their heart set on this being something that applies to all databases, fits the Directus UX tightly, and handles creation of views from within Directus.

Personally, all my needs would be fullfilled if we could simply see pre-created readonly postgres views/materialize views. I can handle creating, refreshing, and giving them a primary key on our backend. A very sweet bonus would be if I could link views to normal tables (that have a foreign key relationship) in the UI, so that when viewing a readonly view, a user can click into a related normal table to make some edits (which would then likely trigger a postgres function to refresh the view etc etc)

[eager ivy]

update: CRUD now works with views ヽ(´▽`)/

[eager ivy]

update: Just landed the Pull Request that supports CRUD with postgres views, materialized tables and foreign tables.
https://github.com/directus/directus/pull/17174

[halcyon parcel]

🚀

[eager ivy]

update: Now create views, materialized tables and foreign tables from within the Directus admin interface

[burnt sand]

Can't wait to get my hands on this 👌

[eager ivy]

update: Now with live preview of the view definition

[toxic stirrup]

Can't wait to try it!

[rustic willow]

this looks amazing, how can we try it?

[upper trench]

Hi @eager ivy is it available to use?, does it work with MySQL?

[eager ivy]

hi jefferson, I made it available as a fork of a previous directus version ; but this work has not been updated since then

see the "Try it out" section in this discussion: https://github.com/directus/directus/discussions/17265

I only tested with postgresql, so I suppose it won't work as-is with MySQL without doing more work

[eager ivy]

here is the patch: https://github.com/directus/directus/compare/main...arnaud:directus:feat/views-support
it could interest you if you plan on adding mysql support yourself

[upper trench]

So, as long as i undestand, the directus version that i have to use, to let this extension work is 9.22.4? 👀

Is there any way to install it in newest direcuts version?

[eager ivy]

not without rework

[eager ivy]

you may be interested in the following directus event:
https://discord.gg/ZcW23rqT?event=1217434018828648479

[upper trench]

Thank you @eager ivy

[wild plaza]

Hi @eager ivy, is there any update about Postegres Views support? Thank you so much in advance

[eager ivy]

Pretty dope, right?
I released a custom directus version with this feature. You can find it over at https://github.com/directus/directus/discussions/17265
But you need to be aware that it's now an old version, and I don't plan to upgrade it unless there is strong demand.