Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for database views #678

Open
divyenduz opened this issue Oct 7, 2019 · 115 comments
Open

Add support for database views #678

divyenduz opened this issue Oct 7, 2019 · 115 comments
Assignees
Labels
kind/feature A request for a new feature. status/is-preview-feature This feature request is currently available as a Preview feature. team/client Issue for team Client. team/psl-wg team/schema Issue for team Schema. topic: database-functionality topic: introspection topic: schema topic: views
Milestone

Comments

@divyenduz
Copy link
Contributor

divyenduz commented Oct 7, 2019

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
@divyenduz divyenduz added kind/feature A request for a new feature. kind/improvement An improvement to existing feature and code. and removed kind/feature A request for a new feature. labels Oct 7, 2019
@janpio janpio added kind/feature A request for a new feature. topic: schema topic: introspection and removed kind/improvement An improvement to existing feature and code. labels Dec 16, 2019
@divyenduz divyenduz removed their assignment Dec 17, 2019
@jeffvandyke

This comment was marked as outdated.

@divyenduz

This comment was marked as outdated.

@divyenduz divyenduz changed the title Support for MySQL views Support for views Apr 3, 2020
@albertoperdomo albertoperdomo self-assigned this May 19, 2020
@albertoperdomo albertoperdomo changed the title Support for views Add support for database views May 25, 2020
@felinto-dev

This comment was marked as outdated.

@albertoperdomo

This comment was marked as outdated.

@codingmatty

This comment was marked as outdated.

@pantharshit00

This comment was marked as outdated.

@albertoperdomo albertoperdomo added the team/schema Issue for team Schema. label Nov 25, 2020
@MichalLytek

This comment was marked as outdated.

@albertoperdomo

This comment was marked as outdated.

@MichalLytek

This comment was marked as outdated.

@albertoperdomo

This comment was marked as outdated.

@dualmacops

This comment was marked as outdated.

@MichalLytek

This comment was marked as outdated.

@janpio

This comment was marked as outdated.

@carchrae

This comment was marked as outdated.

@carchrae

This comment was marked as outdated.

@janpio

This comment was marked as outdated.

@carchrae

This comment was marked as outdated.

@imsam
Copy link

imsam commented Dec 1, 2022

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
Copy link

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

@vleandersson
Copy link

@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
Copy link

413n commented Jan 5, 2023

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

@janpio
Copy link
Member

janpio commented Jan 6, 2023

@413n Soon. Very soon.

@pencilcheck
Copy link

woohoo!!!

@VaZark
Copy link

VaZark commented Jan 31, 2023

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
Copy link

sampl commented Jan 31, 2023

Looks like there are some docs!

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

@janpio
Copy link
Member

janpio commented Feb 1, 2023

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
Copy link
Member

janpio commented Feb 1, 2023

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
Copy link

Does it work with materialized views?

@jameskennethrobinson
Copy link

jameskennethrobinson commented Feb 7, 2023 via email

@janpio
Copy link
Member

janpio commented Feb 7, 2023

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
Copy link
Member

janpio commented Feb 7, 2023

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.

@jamiter

This comment was marked as outdated.

@janpio janpio added the status/is-preview-feature This feature request is currently available as a Preview feature. label Mar 27, 2023
@janpio
Copy link
Member

janpio commented Mar 31, 2023

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)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
kind/feature A request for a new feature. status/is-preview-feature This feature request is currently available as a Preview feature. team/client Issue for team Client. team/psl-wg team/schema Issue for team Schema. topic: database-functionality topic: introspection topic: schema topic: views
Projects
None yet
Development

No branches or pull requests