Views
Support for views is currently a very early preview feature. You can add a view to your Prisma schema with the view
keyword, but you cannot introspect your schema with db pull
or apply your schema to your database with Prisma Migrate and db push
.
For updates on progress with this feature, follow our GitHub issue.
Database views allow you to name and store queries. In relational databases, views are stored SQL queries that might include columns in multiple tables, or calculated values such as aggregates. In MongoDB, views are queryable objects where the contents are defined by an aggregation pipeline on other collections.
The views
preview feature allows you to represent views in your Prisma schema with the view
keyword. To use views in Prisma, follow these steps:
- Enable the
views
preview feature - Create a view in the underlying database, either directly or as a manual addition to a Prisma Migrate migration file, or use an existing view
- Represent the view in your Prisma schema
- Query the view in Prisma Client
Enable the views
preview feature
Support for views is currently in an early preview. To enable the views
preview feature, add the views
feature flag to the previewFeatures
field of the generator
block in your Prisma schema file:
schema.prisma
1generator client {2 provider = "prisma-client-js"+ previewFeatures = ["views"]4}
Please leave feedback about this preview feature in our dedicated preview feature feedback issue for views
.
Create a view in the underlying database
Currently, you cannot apply views that you define in your Prisma schema to your database with Prisma Migrate and db push
. Instead, you must first create the view in the underlying database, either manually or as part of a migration.
For example, take the following Prisma schema with a User
model and a related Profile
model:
model User {id Int @id @default(autoincrement())email String @uniquename String?profile Profile?}model Profile {id Int @id @default(autoincrement())bio Stringuser User @relation(fields: [userId], references: [id])userId Int @unique}
Next, take a UserInfo
view in the underlying database that combines the email
and name
fields from the User
model and the bio
field from the Profile
model.
For a relational database, the SQL statement to create this view is:
CREATE VIEW "UserInfo" ASSELECT u.id, email, name, bioFROM "User" uLEFT JOIN "Profile" p ON u.id = p."userId"
For MongoDB, you can create a view with the following command:
db.createView('UserInfo', 'User', [{$lookup: {from: 'Profile',localField: '_id',foreignField: 'userId',as: 'ProfileData',},},{$project: {_id: 1,email: 1,name: 1,bio: '$ProfileData.bio',},},{ $unwind: '$bio' },])
Use views with Prisma Migrate and db push
If you apply changes to your schema with Prisma Migrate or db push
, Prisma does not create or run any SQL related to views.
To include views in a migration, run migrate dev --create-only
and then manually add the SQL for views to your migration file. Alternatively, you can create views manually in the database.
Add views to your Prisma schema
To add a view to your Prisma schema, use the view
keyword.
You can represent the UserInfo
view from the example above in your Prisma schema as follows:
view UserInfo {id Int @idemail Stringname Stringbio String}
Limitations
Currently, Prisma treats views in the same way as models. This means that a view needs to have a unique identifier. In relational databases, this can be an @id
or @unique
attribute on one field, or an @@id
or @@unique
attribute on multiple fields. In MongoDB, this must be an @id
attribute that maps to the _id
field in the underlying database with @map("_id")
.
In the example above, the id
field has an @id
attribute.
Prisma does not currently support introspection of views with db pull
. Instead, you must manually add views to your Prisma schema.
Currently, if you introspect your database, Prisma removes any existing view
blocks in your schema file. To reinstate the views, you must revert this change.
Query views in Prisma Client
You can query views in Prisma Client in the same way that you query models. For example, the following query finds all users with a name
of 'Alice'
in the UserInfo
view defined above.
const userinfo = await prisma.userInfo.findMany({where: {name: 'Alice',},})
Currently, Prisma Client allows you to update a view if the underlying database allows it, without any additional validation.