-
Notifications
You must be signed in to change notification settings - Fork 1.5k
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
Preview feature feedback: Views #17335
Comments
The general process is that we add the SQL to generate the views by hand to our migrations, and this new feature gives us a way to describe those views, and query them with the prisma client. Is this correct? |
@Ustice Exactly - the next step is we add introspection support. |
I love this feature, but I have some questions. How do views determine field associations? In the following, how would the view UserInfo {
id Int @id
name String
type String
}
model User {
id Int @id @default(autoincrement())
name String
type String?
profile Profile?
}
model Profile {
id Int @id @default(autoincrement())
type String
user User @relation(fields: [userId], references: [id])
userId Int @unique
} |
@jonschlinkert That happens via the underlying SQL query that is used to create a view Right now you can not define this SQL query in Prisma schema at all to migrate it (via But, we are already pretty certain that we will not be able to represent these queries with Prisma schema syntax, so for example a "mapping" or "field associations between Does that explain the current state and where this might go? |
Yes, got it. I seem to have missed the part that the SQL query needs to be created manually, which makes sense. It's much more clear now, thank you. |
❤️ I love this feature. I was able to move complex multi-table pricing logic, which had to be repeated multiple times in the code to a centralized view. What I liked the most is that I can still model relationships and they just work - which means I can create views which only hold custom computed values, and join them with the original model to get the non-computed fields. The only annoyance I had so far is the requirement to have an @id field, which doesn't make sense for my table, but I had to create a fake column anyway. I don't expect this feature to be super popular because it requires SQL knowledge and is probably only beneficial for more complex table structures, but for me it is very important and simplifies a lot, so thank you! |
Would you have been able to easily identify any of the columns as |
My view is a cross join between 2 tables, so I could use compound key (id from first table, id from second table), but not a single column. I create a fake id column like this: (first.id - 1) * (( SELECT max(id) AS max
FROM second)) + second.id AS id |
You would also be able to use |
@Ustice @jonschlinkert If you are using PostgreSQL, have a look at this comment I just posted at the feature request issue: #678 (comment) |
I've found materialized views highly useful in the past, and I'm working on an app right now where I'd have loved to have materialized views (so that I can have indexes on a couple columns). What's the timeline for materialized views? |
We do not communicate concrete timelines - that never ends well. It is probably more towards the bottom of our feature list for views, because: You can already create a materialized view today (just create one instead of a "normal" view) and represent it with the |
I think this feature, combined with the multi-schema support, is going to be a huge benefit for us poor souls who have been trying to use Prisma with Supabase! I might try to hack something together this weekend and report back the results. |
Offer the option to reference a column by the table name as an optional alias, for example view UserInfo {
id Int @id
name String
User.type String
} |
This is a great start! I love it. Comming from SQL world, I'm using Prisma to speed up development (and type safety), not to avoid SQL. It works extremely well for simple stuff which covers 90% of cases. But complex, nested and aggregated queries were a pain to write in Prisma. Even more pain to realize how many individual queries Prisma creates for those. So raw queries and manual types was the only way out. Now this seems to be the best from both worlds and saves a ton of time! |
Quick question do not know how feasible this is. So one thing is noted that will be difficult to write the SQL from the Prisma schema itself. Would it be possible if we have to go down the create-only flag route, that we are able to put the SQL files alongside the schema that create the views themselves.Then just tell the Prisma schema which script file it need to execute to create it? Just to avoid having to do this each time.
or maybe if a view is made up of one or more views.
This would just mean it keeps this central. Unless I missed something. Just a thought. |
Yes, something in that direction is also our current thoughts re how to support creation and migration of views @luke-cbs. We can not "generate" the SQL from the |
Yeah I think there will have to be some give and take from the dev's point of view as you have eluded to. Our responsibility will be by the sounds of it to ensure that our select from these views that we create matches what we define in the .schema. I think for the most part the diff would be nice. I think even a rough point of re-creating the views when we doing migrations and stuff so that we don't to think why something may be missing or creating some form of area as a source of truth of what the db requires. But yeah sounds good to me! Thanks for this. |
Seems that https://plugins.jetbrains.com/plugin/20686-prisma-orm has not been updated with the latest features yet. Source code lives here: https://github.com/JetBrains/intellij-plugins/tree/master/prisma if you maybe want to help out. Our own VS Code extension is fully updated. |
This comment was marked as outdated.
This comment was marked as outdated.
This comment was marked as outdated.
This comment was marked as outdated.
This comment was marked as outdated.
This comment was marked as outdated.
This comment was marked as outdated.
This comment was marked as outdated.
My greatest apologies: I missed the piece about enabling the preview feature ... I just checked against a pretty complex MSSql database: scripts are correct. I also compared the views definitions with the ones I had manually added to the schema so I could use them (as tables): only |
Hello, I added this as an issue, but perhaps just a comment is more appropriate. I am trying to run this migration but I get the error below. Is security_invoker not supported? Or do I perhaps have the syntax wrong?
Error: P3006 Migration 20230522152040_alter_view_xxxx failed to apply cleanly to the shadow database. |
Not sure if I understood the concept of views correctly (see discussion), but if I am right this would be a great addition to Prisma! Thanks for working on it! |
Hello |
@aruns05 So if you can't add an |
Predefining
When i do this, "db pull" reverts the fields in my composite id (i.e. expressed via @@id([...]) back to optional, breaking the constraint that the fields must be required. An initial manual edit of my .schema to fix up the view definition is fine, but I can't keep deleting those optional flags ("?") every time i run "db pull". Am I missing something @janpio? |
Can you share a before and after example? I have trouble imagining the details of what you describe. |
Sure. Here are some steps describing the issue, with attached outputs:
Note that then 2nd "db pull" doesn't completely override the view definition as the "@@unique" tag persists. Sql Server |
Hello! Thank you for the cool feature and for Prisma itself :) Do you have plans to support some kind of client-side views? The main idea is to minimize all the hassle with migrations for any kind of read-only tasks, such as views. It seems like a really cheap improvement; you only need to provide a method to replace the view name with a raw query. This could be specified directly in the Prisma client configuration code. |
That has not been requested yet, @eugmakhnev, so we have not considered or planned that yet. Please open a new feature request issue! |
Thank you! New feature request |
This comment was marked as outdated.
This comment was marked as outdated.
👋 Loving views so far! Been using them in prod since they launched, and the increased type-safety + being able to define relations (and query them as such) has been delightful. Some feedback after months of using them: Relations are great, but they don't really reflect the database schema Again, being able to define relations within views and query them like regular tables is truly awesome and has helped fill some of the gaps Prisma has when writing somewhat complex queries. However, the way they are defined in the Schema doesn't match what's really happening in the database (at least on Postgres). For example, see how model User {
id Int @id @default(autoincrement())
posts Post[]
powerUser TopUser[]
}
model Post {
id Int @id @default(autoincrement())
user User @relation(fields: [userId], references: [id])
userId Int
}
view TopUser {
user User @relation(fields: [userId], references: [id])
userId Int @unique
points Int
} Migrations are quite difficult and potentially destructive Schema changes to a view do not apply to migrations and one must generate the migration manually via We've seen some friction on fast-changing repositories with lots of migrations, often by different people, where one can get lost when trying to update an existing view. We must check migration by migration in order to find the last to modify the view, then copy it and paste it in a new migration, which can lead to someone copying the wrong version and messing something up — the damage probably won't be that significant, but can happen. Prisma could (A) expose a CLI option to create a migration for a view based on the latest CREATE state of that view, or (B) ideally intercept changes in the view within the schema and pre-fill the migration with the latest CREATE state of the view. Not sure if the CREATE statement can be pulled from the database, probably not, so the best way could be to just find the latest migration that touched that view. Hope it helps 🙌🏼 |
I don't really have enough data to report an issue, but I run into issues on Prisma with SQL Server when I use views. I get data conversion errors, e.g.:
I'm able to fix it by deleting the view in my database, and then recreating it. This happens consistently when I make any changes to the underlying table that the view queries. |
I love this feature. Thank you for your work! I currently struggling to find a way to distinguish table and view with an internal If you can provide any hint like ref: https://github.com/redwoodjs/redwood/blob/main/packages/testing/config/jest/api/jest.setup.js#L136 |
How are you guys/gals able to make relationship works? Whenever I try with something like:
I get an array on |
works great for me, thanks |
Hello! I've been testing this feature in my setup for the first time today and one question came to my mind regarding the views directory which is created after Is there a way to make this subdirectory's name custom? If not, perhaps some custom folder naming and then using some environment variable to insert the name of the schema would be useful. Thanks! |
Hi. Thank you for this great feature. I really appreciate this ❤️ I found an issue with one to many relation between model and view. Currently prisma.schema requires FK is defined for one to many relation, however, views shouldn't have any FK since its underlying model has it. So I suppose the rule to enforce FK to one to many relation would be better to be ignored for relation between view and model. Thank you! |
I think I'm here with the same concern as @majimaccho My case is something like this:
Where I'd like Experiment and ExperimentWithOtherData to both have the same list of conditions, since their id fields are the same. Currently I don't see a clean way to do that. |
Hello, has someone face issue when deleting the original row from the table, but it's throwing error saying "can't delete from join view" from the view? I've checked it directly from the MySQL, the updatable is true for the view, and in fact, I could directly delete it with mySQL shell, but I can't when I want to delete it through Prisma. it's funny when I tried with raw query, |
Hey! I am trying to use with materialized view at Postgresql but I am having some issues. Right after I deployed my materialized view, when I try to create a new migration or even reset the database I receive the following error:
Looks like the reset command should use CASCADE or the view should be consider at the reset process, being deleted before any of its relations. There is something that I am missing here? |
Views support is in early preview and you can learn about it in our documentation.
Please share your feedback about the
views
functionality released in v4.9.0 in this issue.The text was updated successfully, but these errors were encountered: