Preview feature feedback: Views

[floelhoeffel]

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.

• If you encounter a bug, please open a bug report in this repo.
• If the feature is working well for you, please share this in a comment below or leave a 👍 on this issue.
• If you have any questions, don't hesitate to ask them in the Prisma Slack community

[Ustice]

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?

[floelhoeffel]

@Ustice Exactly - the next step is we add introspection support.

[jonschlinkert]

I love this feature, but I have some questions. How do views determine field associations? In the following, how would the UserInfo view know which table the type field belongs to, and how does UserInfo know that it's associated with User?

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
}

[janpio]

@jonschlinkert That happens via the underlying SQL query that is used to create a view UserInfo that has three fields id, name and type? See this documentation section for an example for exactly this model: https://www.prisma.io/docs/concepts/components/prisma-schema/views#how-to-add-views-to-your-prisma-schema The view block just tells Prisma which fields to build from the columns the view returns.

Right now you can not define this SQL query in Prisma schema at all to migrate it (via migrate dev or db push and have to create this manually, outside of Prisma. In the future that might change.

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 UserInfo and User". A view can be defined via any SQL query - which includes an infinite number of possibilities, so we found no way to reliably represent that in Prisma schema.

Does that explain the current state and where this might go?

[jonschlinkert]

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.

[sixers]

❤️ 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!

[janpio]

Would you have been able to easily identify any of the columns as @unique in your view? It will be quite some while until we can fully get rid of the limitation of needing a field to uniquely identify a row, but maybe @unique is the better choice than @id.

[sixers]

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

[janpio]

You would also be able to use @@unique([first_id, second_id]) as a compound unique if you include both fields in the view. (Sorry, missed to mention that above - we would of course support both @unique for individual columns but also @@unique for when columns need to be combined.)

[janpio]

@Ustice @jonschlinkert If you are using PostgreSQL, have a look at this comment I just posted at the feature request issue: #678 (comment)

[dchenk]

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?

[janpio]

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 view keyword, and then call the "refresh" command via our raw query APIs. That should work. (Would be great if you could try it and give feedback!) When we add materialized view support, we will add an explicit Client API for the refresh command.

[5hee75]

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.

[nmfrankel]

@jonschlinkert That happens via the underlying SQL query that is used to create a view UserInfo that has three fields id, name and type? See this documentation section for an example for exactly this model: https://www.prisma.io/docs/concepts/components/prisma-schema/views#how-to-add-views-to-your-prisma-schema The view block just tells Prisma which fields to build from the columns the view returns.

Right now you can not define this SQL query in Prisma schema at all to migrate it (via migrate dev or db push and have to create this manually, outside of Prisma. In the future that might change.

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 UserInfo and User". A view can be defined via any SQL query - which includes an infinite number of possibilities, so we found no way to reliably represent that in Prisma schema.

Does that explain the current state and where this might go?

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
}

[psiho]

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!

[cbs-l]

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.

view UserInfo {
  script = "./UserInfoView.sql" 
  id    Int    @unique
  email String
  name  String
  bio   String
}

or maybe if a view is made up of one or more views.

view UserInfo {
  script = ["./UserInfoView.sql"]
  id    Int    @unique
  email String
  name  String
  bio   String
}

This would just mean it keeps this central. Unless I missed something.

Just a thought.

[janpio]

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 view definition as we can for model as the SELECT query can be anything. Putting the SQL in a file still is challenging though, as the SQL the database stores is slightly different what you put in, and we need to be able to diff that to see if we need to update the view and so on - but that is what we will look at after most of the other things work for views. (No ETA, but definitely as one of the later things in this project.)

[cbs-l]

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 view definition as we can for model as the SELECT query can be anything. Putting the SQL in a file still is challenging though, as the SQL the database stores is slightly different what you put in, and we need to be able to diff that to see if we need to update the view and so on - but that is what we will look at after most of the other things work for views. (No ETA, but definitely as one of the later things in this project.)

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.

[cbs-l]

One other query I have with support in Webstorm. As currently using the plugin there. Not sure when support will be added for this?

When run they work perfect. Just complains.

[janpio]

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.

[nkovacic]

@janpio we are testing prisma db pull and it seems it does not support materialized views in PostgreSQL (views folders is empty)?
We are using version 4.12.0.

[janpio]

Good point @nkovacic, we currently do not introspect materialized views at all yet, I opened an issue to make that explicit: #18758 We'll add that a bit later, but it will be part of the full views support that we are working on.

[pmosconi]

My greatest apologies: I missed the piece about enabling the preview feature ...
After I set it, automagically I have view folder with scripts and views definition in schema prisma.

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 @id definitions are missing, but I guess it's not easy to infer them from query, if possible at all.

[muzzsteak]

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?

ALTER VIEW IF EXISTS view_xxxx
SET (security_invoker = true);

Error: P3006

Migration 20230522152040_alter_view_xxxx failed to apply cleanly to the shadow database.
Error:
db error: ERROR: unrecognized parameter "security_invoker"
0: sql_migration_connector::validate_migrations
with namespaces=None
at migration-engine\connectors\sql-migration-connector\src\lib.rs:301
1: migration_core::state::DevDiagnostic
at migration-engine\core\src\state.rs:269

[rwieruch]

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!

[aruns05]

Hello
We are not able to import the view as we cannot define any unique constraint
Can you advise on that ?

[Jolg42]

@aruns05 So if you can't add an @unique, alternatively you can add an @id or @@id([field1, field2]) or @@unique([field1, field2]).

[dfoley-greenlight]

@janpio

Predefining

If you are open to modifying your Prisma schema file manually before running db pull, you can just put a skeleton view in there that already includes @(@)id, @(@)unique and the fields with types and ? - or not. The optionality and constraint information will all stick around after you run db pull (as we know we can not get that information from the database).

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?

[janpio]

Can you share a before and after example? I have trouble imagining the details of what you describe.

[dfoley-greenlight]

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:

1. Run "db pull" to create the initial view definition in the schema.prisma file - this works as per your documentation (File: Initial.txt)
2. Edit the initial view definition created in step 1 as per your documentation - remove "@@ignore", create a composite unique and/or id tag via "@@unique"/"@@id", make fields in composite key required (i.e. delete "?" for those fields). (File: "Edited.txt")
3. Run db-pull again at some point in the future. Result: Composite fields are overridden with their optional flags (i.e. "?") again. (File: 2nd Db Pull.txt)
   Initial.txt
   Edited.txt
   2nd Db Pull.txt

Note that then 2nd "db pull" doesn't completely override the view definition as the "@@unique" tag persists.

Sql Server
Prisma 5.1

[eugmakhnev]

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.

[janpio]

That has not been requested yet, @eugmakhnev, so we have not considered or planned that yet. Please open a new feature request issue!

[eugmakhnev]

Thank you! New feature request

[ajmnz]

👋

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 User.powerUser looks like a regular model relation when in reality is just a "virtual" relation, since it's pointing to a view. These don't exist in the database, so maybe a keyword for clarification can be required (ie. @view)

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 --create-only, which, don't get me wrong, is alright.

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 🙌🏼

[jlkravitz]

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.:

Message: Invalid `prisma.scores.findMany()` invocation: Error occurred during query execution: ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(TokenError { code: 245, state: 1, class: 16, message: "Conversion failed when converting the varchar value '0cfc75b54b0ada70960afe23162fb674' to data type int.", server: "xxxx", procedure: "", line: 1 }), transient: false })

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.

[majimaccho]

I love this feature. Thank you for your work!

I currently struggling to find a way to distinguish table and view with an internal getDMMF method.
Since Redwood.js, which I'm currently using, handles data deletion in each api test with models retrieved from getDMMF.

If you can provide any hint like isView, I can fix view handling inside Redwood.js.

ref: https://github.com/redwoodjs/redwood/blob/main/packages/testing/config/jest/api/jest.setup.js#L136

[shoooe]

How are you guys/gals able to make relationship works? Whenever I try with something like:

model User {
  id        Int       @id @default(autoincrement())
  posts     Post[]
  powerUser TopUser[]
}

view TopUser {
  user     User   @relation(fields: [userId], references: [id])
  userId   Int    @unique
  points   Int
}

I get an array on powerUser and I'm not able to make it a 1-to-1 relationship even if the foreign key is unique.

[danbord]

works great for me, thanks

[dawid-bobowski]

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 npx prisma db pull.
It creates a new subdirectory named after the database schema. In my case I wouldn't like it to be named this way, because of how my database provider enforces naming of my schemas. They use the name of the main MySQL account as a prefix and I wouldn't like to keep it exposed in my repository.

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!

[majimaccho]

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!

[Brokemia]

I think I'm here with the same concern as @majimaccho

My case is something like this:

model Experiment {
  id          Int         @id @default(autoincrement())
  ...
  conditions  Condition[] 
}

view ExperimentWithOtherData {
  id          Int         @id
  ...
  conditions  Condition[] 
}

model Condition {
  id           Int        @id @default(autoincrement())
  experiment   Experiment @relation(fields: [experimentId], references: [id], onDelete: Cascade)
  experimentId Int
  ...
}

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.