Support materialized views

[janpio]

We implemented support for views via #678 and #17335, but as materialized views are special (they need some Client API and would also need to be migrated differently) we currently do not introspect them yet at all and also have no special construct in PSL to differentiate they from normal views.

(If you handwrite a views definition, it might work to be able to access the views. No guarantees though.)

[pimeys]

PostgreSQL has view definitions in pg_views, but this misses the materialized views. To get those, one must look into pg_matviews.

[pimeys]

There are some interesting questions when implementing this feature, one is what is the hasindexes column and can we somehow get the index information to the PSL.

[pimeys]

• PostgreSQL: docs
• MySQL: no support
• SQLite: no support
• SQL Server: there are indexed views which is kind of similar...
• MongoDB: docs

[janpio]

And Cockroach: https://www.cockroachlabs.com/docs/stable/views.html#materialized-views

[mentos1386]

@janpio what's the progress on this?

[janpio]

We are currently gathering feedback to our views implementation via #678 and #3108.

You can most probably create a manual model representing your materialized view (even via Introspection if you for example create a non-materialized copy of it) and then use a raw query to trigger the materialization.

[teaforchris]

As a temporary work-around specifically for PostgreSQL, until materialized views are implemented, I found the following worked pretty well for me. I had a really complicated matview with over 30 cols and didn't fancy manually creating it.

• Create a shadow table which matches the structure of your matview

create table my_matview_shadow as
 select * from my_matview
 where null is not null
;

ℹ️ where null is not null always returns false, so no data is added, since we're only interested in the structure

• Introspect the db in the usual way

prisma db pull

• Manually rename the generated relation from model my_matview_shadow to view my_matview
• Deal with the unique identifier issue by adding @unique to the relevant field (typically id) and removing @@ignore
• Manually add indexes if you have them on your view
• Regenerate

prisma generate

...and away you go, query it like a normal view.

[KilianB]

We are also extensively using materialized views. Setting up the views by hand once is fine and we can work around it. What we have found to be quite unexpected and inconvenient is the fact that introspection is removing the manually added views again.

Maybe the feature request could be split into a task of 2. generating view definitions and 1. not removing materialized view definitions upon introspection

[leppaott]

Try a view on top of the materialized view.

CREATE VIEW "PrismaView" AS
  SELECT * FROM "MaterializedView";

This seems to work at least migrator-wise. Hmm even access without this to materialized view with the views definition works.