[RFC] Proposal: Support views, materialized views and foreign tables

[arnaud]

In this proposal, any mention of "views" will reference "views, materialized views and foreign tables", as long as the
vendors support it.

Goal

The general idea is to support views, materialized views and foreign tables as first-class citizens in Directus, and be able to create, query, and CRUD them from Directus.

Features

• Discoverability for pre-existing views: Directus should let me configure an already-existing SQL view;
• Create views with SQL: Create views from the admin app with custom-crafted SQL and a live preview;
• Create views in no-code: Create views from the admin app with a query builder and a live preview (with metabase as an inspiration);
• Edit view collections: Views are editable as long as the data is mapped with unique ids;
• Calculated fields: Implicit support for calculated fields (fields with values based on other fields);

Use cases

• Manage permissions;
• Merge data from multiple related tables;
• Use views with calculated fields;
• Thanks to foreign table support:
  • Use Directus as a data aggregation platform, with some help of foreign-data wrappers;
  • Enable some git-based workflows with Directus;

Requirements

• Vendor agnostic
• Documented
• No-code
• Integrates well with existing features (admin app, API, flows, dashboards)

Approach

• Currently, only tables are collections; Views would be collections too;
• Implement Views support into Knex itself, as per @rijkvanzanten;
• Declare/configure vendor support at knex-level in the schema package;
• Primary key identification:
  • Handle the presence of an "id" field as the PK;
  • On-the-fly PK generation for views with SELECT ROW_NUMBER() OVER() AS id;
  • Make the user choose the PK from available fields;

General information

Definitions

• View : view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query. (reference)
• Materialized View : materialized view of a query. The query is executed and used to populate the view at the time the command is issued and may be refreshed later. (reference)
• Foreign Table : A foreign table references an external data, either from the table of another remote database, or a different schema, from a remote API or even a parseable local file. (reference)

Vendors support

Vendor	Views	Materialized views	Foreign tables
postgres	✔	✔	✔
mysql	✔
cockroachdb	✔	✔
sqlite3	✔
oracledb	✔	✔
mssql	✔	✔

Proof of concept

Views discoverability	Create views with SQL	Live preview the view definition

Try it out

Support for postgres views has been implemented in the following custom Docker image:

docker pull codename/directus:9.22.4-views-beta.8

⚠️ This is a proof of concept under heavy ongoing development; Always be cautious with unofficial images.

---

• ✨ Postgres support for views #17174
• Support SQL Views #3097
• 💬 Discord

[rijkvanzanten]

Dig this! The only thing I'm not on board with here is the reliance on raw SQL to create the views. Even though it gives the most flexibility, it also opens up a pretty nasty attack vector¹, and means that it's very easy to add parts to a view that may or may not be supported by Directus (yet). It also means that the exact way to configure a view is different between various vendors, which makes documentation very tricky. It also means that we expect the user to be intimately familiar with SQL, which goes against the no-code/low-code approach of the app. I'm wondering how we can rely on a more user friendly UX that configures this through the API methods rather than SQL. (With the knowledge that Directus will still rely on mirroring of the DB, so advanced usage can be achieved by running the SQL directly in-DB)

Footnotes

1. Even though data model is admin-only, an Admin in Directus isn't necessarily a sys-admin with root access to the database. Even if you are somehow able to elevate your permissions to the admin role through another vulnerability, you're still bound to what the Directus API can do to the database. When we add a submit-raw-sql-which-is-executed endpoint like above, it opens up the opportunity for admins to do massive damage, as they now have way more permissions to the DB. There's also no way for us to make sure an admin user doesn't execute anything but the creation of a View. Somebody could enter a DROP DATABASE x; and it'll happily execute thinking a view will be previewed. ↩

[claytongulick]

@rijkvanzanten totally get the concern with not wanting to allow DDL through the web UI. We don't want a Bobby Tables moment (https://xkcd.com/327/).

As a first step though, I'd love to see a maximally minimal approach where Directus was able to recognize existing views, keep them read-only, but treat them in other ways like a collection (permissions, api access, etc...).

It would be an enormous time saver and eliminate a whole class of security risks from rolling custom endpoints for each view we make. Just having declarative permissions on read-only views would be a massive step forward, imho.

[arnaud]

Although I'd love to help with coding, I don't have the chops for that. But I can help with QA. And this issue may not be caused by your fork, but it is illuminated by it.

We have many hundreds of Views and Materialized Views.

In this fork, I notice that Directus has more of a lag in most action (page transitions, loading, saving etc).

Granted I have all caching / schema caching disabled as I'm doing active development. I suspect that may be a culprit.

It seems odd though, that Directus would seem to want to be parsing the all tables/views for each page load.

Wouldn't that be something only necessary on the Admin Data Model page?

Anyhoo the solution is to exclude all the Views in the .env file down to only those I want Directus to manage.

And thank you for your efforts on this RFC!!

As the directus implementation lets you choose from which schema to pull tables and views, I assume you should be able to split and rearrange the views into dedicated and separate schemas? Directus will only show those that belong to your Directus schema.

[bamboowonder]

Unfortunately it would break a tonne of legacy apps by moving all those 100s of views to a different schema just to accommodate Directus.
Upon further investigation, by starting Directus with npx instead of systemd, after any schema change (add a field to a collection for instance), I watch the terminal fill up for nearly a minute as Directus parses every column of every view/table/mat.view, despite having listed them all in the excluded tables in the .env
Is that something that Directus does independently of your fork?
I guess this is partly solved by letting Directus cache schema changes which I'll do after I'm done the heavy lifting of getting this implimentation off the ground.

[rijkvanzanten]

@rijkvanzanten totally get the concern with not wanting to allow DDL through the web UI. We don't want a Bobby Tables moment (https://xkcd.com/327/).

As a first step though, I'd love to see a maximally minimal approach where Directus was able to recognize existing views, keep them read-only, but treat them in other ways like a collection (permissions, api access, etc...).

It would be an enormous time saver and eliminate a whole class of security risks from rolling custom endpoints for each view we make. Just having declarative permissions on read-only views would be a massive step forward, imho.

Totally! I think we might want to "split up" this feature request in two new ones. One for how Directus should behave when it comes to making views accessible read only, and one for how you can create/modify views from Directus

[claytongulick]

What sort of realistic appetite is there for this from the team, do you think?

I don't mind working on a PR, but I don't want to put all the time & effort in to have it languish, and we're not operating from a fork anymore so we wouldn't be able to use it if it wasn't merged.

[JonL1]

@rijkvanzanten I don't see any problem with creating a virtual collections based on raw SQL(or via JS for that matter). At least for self-hosted.

You already allow extensions to be loaded and you also allow to run JS through the UI via the "Run script" operation which even if using vm/vm2 it is not bulletproof.

Both (extensions and "Run Script") are considered advanced features and both can be restricted in some way. This would be just another feature that needs good handling.

Also, it is something other "internal tools" competitors offer out-of-the-box.

[JonL1]

@rijkvanzanten also ENV variables are now accesible in the "Run Script" operation. I think we are already way passed the line of "someone with bad intentions or by mistake can do serious damage via directus".

In the end it's a matter of the directus admin role to prevent giving access to people that could break things and having a disaster recovery plan in place :)

Just as with any kind of production software.

[rijkvanzanten]

It's not that black and white. Modules and env var access in the run-script operation is controlled on an explicit allow-list basis configured in env, with nothing allowed by default.

[JonL1]

Same with collections, right? They are not allowed to be created by default. So a collection created from SQL would be restricted to admin role unless explicitly granted to other roles. Or am I missing something (probably I am)?

[AMB-Informacio]

We are very interested in being capable of showing views as collections. When talking about self-hosted Directus, I think it is less important to be able to create the views through it. In most cases you have acces to the DB and it's not a problem to create the view.

I think the problem is to fit this feature in Directus Cloud to make sense there, where you will need to allow views creation. I understand making this feature suitable for Directus Cloud is what will make this interesting to develop.

The key here, as @claytongulick already said: "As a first step though, I'd love to see a maximally minimal approach where Directus was able to recognize existing views, keep them read-only, but treat them in other ways like a collection (permissions, api access, etc...)."

To make it work, what I understood that has to be solved somehow is the need of an ID for each collection item. Is that right? If detail view is disabled can be ignored?

At this time, a workaround that can be valid in some cases is to create a table where to insert the results of querying the view. Like a kind of materialized view (or view cache). With a global table to maintain a state (dirty/stale/uptodate) for each of theses tables (views caches). Then with triggers make it dirty when changing a table/items the view depends upon (or maybe when an amount of time has passed), and "rehydrate" when wanting to view items (if it's dirty). You also can, adding a cache_ts field, even maintain versions of the view results!

I think that trying to make possible to create views, in a controlled way, with UI is too complex. But if the admin user have a spot to make queries directly to the underlying db system, with properly controlled permissions, then he could be able to create views. A thing that is not performed in an operational basis.

[avimar]

"As a first step though, I'd love to see a maximally minimal approach where Directus was able to recognize existing views, keep them read-only, but treat them in other ways like a collection (permissions, api access, etc...)."

Is there a blocker to manually creating a view in postgres and marking it all read-only in directus, and just using it for read-only?

(What is "mirroring" a collection I've seen referenced in various places?)

EDIT: I tried it - I made a collection inside directus then replaced it with the view inside postgres. But it shows up only as a folder or something inside directus. I assume the data-introspection realized it's not a real table and deleted the fields...

[BrianJM]

manually creating a view in postgres and marking it all read-only in directus

I came here to see if this was a viable workaround.

[bamboowonder]

While I had great hopes for the View support, ultimately we dropped the fork we'd made that had this View support. We needed production stability and without the hassle of merging updates.

Our workaround, is that we have triggers that drop and regenerate postgres "unlogged" tables. And oddly enough, this has actually worked really well. It is more or less the same as how a materialized view works, and has all the performance benefits versus tradition views (as the underlying query to build these is quite complex and resource intensive). And for the most part, the bookmarks and saved queries in Directus are good enough that we've just phased out the extensive use of views we previously had.

It's one of many examples of how we've re-architected our database design to function better with Directus, because Directus is such a good fit for our internal apps and db management. It was worth it.

I'd recommend anyone stuck by the lack of view support to consider "unlogged tables" with a trigger or cronjob to regenerate them.

[rijkvanzanten]

Heya! Thanks for opening this feature request!

This feature request has received over 15 votes from the community. This means we'll move this feature request to the Under Review state!

The Core team will schedule a meeting to review this request as soon as possible. The discussion will then be approved or denied. You may or may not be invited to join this meeting with the core team.

For more information, see our Feature Request Process.

(Also ref #3097 for more discussion on the same topic)

[lostation]

@arnaud What a great idea you had Thanks !!! @rijkvanzanten We absolutely want that powerful feature !!! I totally agree about breaking changes of a lot of legacy running apps...but if the extended new view feature could be only selected by .env selector or any ui toggler... Then we obsiously take the responsability of messing up the app or not and it won't break anything by default. For my case, I need to integrate an external foreign table from a distant server and I only want to list the content and join some rows with local directus entities. Is that sounds so terrible for you ? Thanks

[dorian100]

I have exactly the same needs, it would be great and very powerful for us. Thanks for this good idea !

[mabril]

We really need this to continue working with directus.

[AMB-Informacio]

I think we must split it up in, at least, two parts. The first one considering only the case in which the views are created outside Directus. Then take care of collection view only, and not the detail view. Let collection items have no ID but restricting then the possibility of item creation or detail view. I think it would be a good first step on that will let build upon it. Enviat des de l'Outlook per a l'iOS<https://aka.ms/o0ukef> Daniel Valls Estella Tècnic Especialista Via Laietana, 33, 4t 1a - 08003 Barcelona Tel. (+34) 93 291 80 96 ***@***.*** www.ambinformacio.cat La informació inclosa en aquest correu electrònic és CONFIDENCIAL, i és per a ús exclusiu del destinatari. Si vostè llegeix aquest missatge i no n'és el destinatari, li informem que està totalment prohibida la divulgació, distribució o reproducció d'aquesta comunicació, i li demanem que ens ho notifiqui i ens retorni aquest missatge, i a més que l'esborri. Gràcies. Abans d'imprimir aquest e-mail pensi en el medi ambient. La información incluida en este correo electrónico es CONFIDENCIAL, siendo para uso exclusivo del destinatario. Si Vd. lee este mensaje y no es el destinatario, le informamos de que está totalmente prohibida la divulgación, distribución o reproducción de este comunicado y le agradeceríamos lo notificara y lo devolviera a la dirección arriba indicada, borrando el mensaje original. Gracias. Antes de imprimir este e-mail piense en el medio ambiente. This email and the information within are CONFIDENTIAL and it is intended exclusively for the addressee. If this message has been received in error, you are not entitled to use, disclose, distribute copy or rely on this email in any way. Please notify us immediately by email and delete it from your system. Please consider the environment before printing this email.

…

________________________________ De: Arnaud Leymet ***@***.***> Enviat el: Wednesday, October 18, 2023 8:53:07 AM Per a: directus/directus ***@***.***> A/c: informatica ***@***.***>; Comment ***@***.***> Tema: Re: [directus/directus] [RFC] Proposal: Support views, materialized views and foreign tables (Discussion #17265) Sadly, none on my end. I suspect this matter needs more traction in order for the directus core team to address this challenge. — Reply to this email directly, view it on GitHub<#17265 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AT6SGH27XPIPNDEGXTUD6KLX754FHAVCNFSM6AAAAAAUCUQ25OVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TGMJRGQ3DC>. You are receiving this because you commented.Message ID: ***@***.***>

[julian-hecker]

Hi all, I've been trying to get views in directus as it would enable some essential functionality for the use case I'm working on. I've created some views using raw SQL and tried everything I could to get it recognized as a collection, including modifying the directus_collections and directus_fields tables but wasn't able to get it to work. I get a 403 Forbidden error when trying to access the view's records. It would be great if Directus could at least recognize existing views. Has anyone had success with this?

[arnaud]

Yup #17265 (comment)

[bamboowonder]

While I really liked what @arnaud created, using it meant the added hassle of maintain our own fork, which just wasn't worth it.
The way we shoehorned around this, is writing some stored procedures in postgres that generate unlogged tables (basically the equivalent of a materialized view as far as performance etc). and directus doesn't even notice when it is regenerated in the background. some of the unlogged tables are generated using a cronjob, others are generated when an update to the underlying data occurs. It was a pain to setup but it meant we could use the full functionality of directus without forks. Not having proper view support has been a razors edge of us often reevaluating whether directus is worth it or not.

[w0kyj]

Short Summary of Request Review April 18, 2024

Generally this is a very, very complex feature and discussion. We will work to break this into more manageable parts.

• Views are SQL specific and support varies by vendor
  • supporting views as a read-only concept makes sense
    • Things that must be considered:
      • data access permissions (view may aggregate data from multiple tables and the Directus user should/may not have access to all the data)
      • document security, permissions, and caveats for the Directus administrators
      • views would be created via direct SQL by the database system admin
      • restrict directus role permissions to Read configuration only Create/Update/Delete disabled
  • creating/updating views is not planned currently due to role/security concerns:
    • crosses boundary into system administration role
    • enables sql injection
• Discussed possibility of elevating Bookmarks to first class
  • bookmarks are a limited view feature already
  • adding ability to aggregate/join data/collections could be a way to implement Directus "views" as a normalized construct regardless of underlying data source/vendor

Lots of great discussion and as always feedback welcome!