Add support for database views

[divyenduz]

Problem

In a database, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated dynamically from data in the database when access to that view is requested. Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view.

Prisma does not currently support database views.

Suggested solution

No suggestion.

Alternatives

The current workaround available is to manage views manually and query views by using the raw SQL API.

Or use the documented workarounds https://www.prisma.io/docs/guides/database/advanced-database-tasks/sql-views-postgres or https://www.prisma.io/docs/guides/database/advanced-database-tasks/sql-views-mysql but make sure to read the "Limitations" section properly!

Alternatively you can treat a "fake model" to your Prisma schema that has the same structure than the view results. Note that future Introspection might drop this "model" and future Migrations might try to create a table of that name - so make sure to handle that.

Additional context

Frameworks like Ruby on Rails don't support views out of the box, but there are 3rd party libraries available (scenic for Rails).

Original issue

Prisma 2 init doesn't print MySQL views into the Prisma schema, this issue tracks support for introspection of views for being able to use it with brownfield projects.

This ties into this larger epic: #156

We also need to (possibly) add support for creating views in Prisma schema file, this issue tracks that for the time being before we have clarity on this topic and we can split it into multiple issues.

This issue also tracks views across all connectors:

• MySQL
• Postgres
• SQLite

[imsam]

I have been able to submit the Typeform form just now. I didn't even try to include any SQL in the question about examples of views, due to the warning they gave in the question text.

[JustMaier]

Yeah, I ended up just linking to a gist instead. At least TypeForm was nice enough to remember my responses between refreshes.

[vleandersson]

@floelhoeffel seeing you are making some progress on this feature! :D neat! Give me a shout if you want to jump on a 15-30 min call to bounce some ideas. My use-case is serverless, but more importantly my users are navigating my DB through SQL, but might not have full knowledge of the db nor the ins and outs of SQL. Views and materialized views would be a great way to allow users to easier use the DB for their work.

The project is fairly greenfield, so might be a good one for a dummy test of any beta features you might have ! :)

[413n]

I know this feature is in development (and that's awesome!), but is there any ETA for the (BETA) release?

[janpio]

@413n Soon. Very soon.

[pencilcheck]

woohoo!!!

[VaZark]

Yess !!

I just started a new project and checked this thread to discover this pleasant surprise. Just being able to avoid the hassle with the db migrate is a great step forward.

[sampl]

Looks like there are some docs!

https://www.prisma.io/docs/concepts/components/prisma-schema/views

[janpio]

Oh, seems we forgot to post about that here! Ahem...

Yes indeed, 4.9.0 brought initial support for Views in Prisma ORM. Based on the survey we prioritized introducing the view keyword that is for now works in Prisma Client and is without function in Migrations (as a replacement for the hacky workarounds). It works on all our supported databases, as long as you add it manually to your Prisma schema file and make sure the defined columns match what your view query returns.

Read more in our documentation: https://www.prisma.io/docs/concepts/components/prisma-schema/views
And if you try it (please do!) please give us feedback here (both comments with problems or just 👍 that everything works are welcome: #17335

[janpio]

And while I am here:

We are adding Introspection support for views for PostgreSQL with 4.10.0 next Tuesday.
If you have a PostgreSQL database, it would help us a lot if you could test if it introspects your views properly:

1. Create a dummy project or temporarily upgrade your Prisma CLI in your project to our dev version: npm install prisma@dev
2. Add previewFeatures = ["views"] to the generator block of your Prisma Schema (if you haven't yet)
3. Run npx prisma db pull and observe the changes in your Prisma schema
4. Give feedback if and how this worked via our feedback issue or a new issue if something explodes.
5. If you used your real project, uninstall the dev version and wait until Tuesday to upgrade again.

Thanks.

[lcswillems]

Does it work with materialized views?

[jameskennethrobinson]

@lcswillems Seems to work great for me with materialized views so far in Postgres!

…

On Tue, Feb 7, 2023 at 12:11 PM Lucas Willems ***@***.***> wrote: Does it work with materialized views? — Reply to this email directly, view it on GitHub <#678 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AB53IRM5HWFK5KCP2HQAK73WWKT63ANCNFSM4I6E26EA> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[janpio]

Yes, you only need to call the refresh function to refresh the materialized views manually for now via raw queries. We might make that more convenient later.

[janpio]

We added support for Introspection of views in PostgreSQL today: https://github.com/prisma/prisma/releases/tag/4.10.0
Updated documentation: https://www.prisma.io/docs/concepts/components/prisma-schema/views#use-introspection
Try it out and let us know your thoughts in our feedback issue: #17335.

[janpio]

In Prisma 4.12.0 we added support for creating files based on the introspected views with their SQL query they run, and we are looking for feedback if this works as expected. More information here: #17335 (comment)