Views
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 ORM, 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
Support for views is currently a Preview feature. You can add a view to your Prisma schema with the view
keyword or introspect the views in your database schema with db pull
. You cannot yet apply views in your schema to your database with Prisma Migrate and db push
unless the changes are added manually to your migration file using the --create-only
flag.
For updates on progress with this feature, follow our GitHub issue.
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:
generator client {
provider = "prisma-client"
previewFeatures = ["views"]
}
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:
- Relational databases
- MongoDB
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
profile Profile?
}
model Profile {
id Int @id @default(autoincrement())
bio String
user User @relation(fields: [userId], references: [id])
userId Int @unique
}
model User {
id String @id @default(auto()) @map("_id") @db.ObjectId
email String @unique
name String?
profile Profile?
}
model Profile {
id String @id @default(auto()) @map("_id") @db.ObjectId
bio String
User User @relation(fields: [userId], references: [id])
userId String @unique @db.ObjectId
}
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" AS
SELECT u.id, email, name, bio
FROM "User" u
LEFT 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 Prisma schema with Prisma Migrate or db push
, Prisma ORM 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:
- Relational databases
- MongoDB
view UserInfo {
id Int
email String
name String
bio String
}
view UserInfo {
id String @map("_id") @db.ObjectId
email String
name String
bio String
}
Write by hand
A view
block is comprised of two main pieces:
- The
view
block definition - The view's field definitions
These two pieces allow you to define the name of your view in the generated Prisma Client and the columns present in your view's query results.
Define a view
block
To define the UserInfo
view from the example above, begin by using the view
keyword to define a view
block in your schema named UserInfo
:
view UserInfo {
// Fields
}
Define fields
The properties of a view are called fields, which consist of:
- A field name
- A field type
The fields of the UserInfo
example view can be defined as follows:
- Relational databases
- MongoDB
view UserInfo {
id Int
email String
name String
bio String
}
view UserInfo {
id String @map("_id") @db.ObjectId
email String
name String
bio String
}
Each field of a view
block represents a column in the query results of the view in the underlying database.
Use introspection
Currently only available for PostgreSQL, MySQL, SQL Server and CockroachDB.
If you have an existing view or views defined in your database, introspection will automatically generate view
blocks in your Prisma schema that represent those views.
Assuming the example UserInfo
view exists in your underlying database, running the following command will generate a view
block in your Prisma schema representing that view:
npx prisma db pull
The resulting view
block will be defined as follows:
view UserInfo {
id Int?
email String?
name String?
bio String?
}
Please note for now db pull
will only introspect views in your schema when using PostgreSQL, MySQL, SQL Server or CockroachDB. Support for this workflow will be extended to other database providers.
The views
directory
Introspection of a database with one or more existing views will also create a new views
directory within your prisma
directory (starting with Prisma version 4.12.0). This directory will contain a subdirectory named after your database's schema which contains a .sql
file for each view that was introspected in that schema. Each file will be named after an individual view and will contain the query the related view defines.
For example, after introspecting a database with the default public
schema using the model used above you will find a prisma/views/public/UserInfo.sql
file was created with the following contents:
SELECT
u.id,
u.email,
u.name,
p.bio
FROM
(
"User" u
LEFT JOIN "Profile" p ON ((u.id = p."userId"))
);
Limitations
Introspection
Currently, introspection of views is only available for PostgreSQL, MySQL, SQL Server and CockroachDB. If you are using another database provider, your views must be added manually.
This is a temporary limitation and support for introspection will be extended to the other supported datasource providers.
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',
},
})
findUnique
, cursor-based pagination, and writes (create/update/delete) are not supported on views
.
Special types of views
This section describes how to use Prisma ORM with updatable and materialized views in your database.
Updatable views
Some databases support updatable views (e.g. PostgreSQL, MySQL, and SQL Server). Updatable views allow creating, updating, or deleting entries if the underlying database supports such operations.
Prisma ORM does not allow any mutations (create, update, delete) on views, regardless of the database's capabilities. This change provides guardrails to ensure that views are treated consistently as read-only entities within Prisma Client. As a result, methods to perform writes such as create
, update
, delete
, or upsert
are not generated for view
blocks in your Prisma Client API.
If you need to modify data represented by a view, you must perform the write operations directly on the underlying tables or use raw SQL queries.
Materialized views
Some databases support materialized views, e.g. PostgreSQL, CockroachDB, MongoDB, and SQL Server (where they're called "indexed views").
Materialized views persist the result of the view query for faster access and only update it on demand.
Currently, Prisma ORM does not support materialized views. However, when you manually create a view, you can also create a materialized view with the corresponding command in the underlying database. You can then use Prisma Client's TypedSQL functionality to execute the command and refresh the view manually.
In the future Prisma Client might support marking individual views as materialized and add a Prisma Client method to refresh the materialized view. Please comment on our views
feedback issue with your use case.
Here's a revised ## Restrictions
section with clarity on why these limitations exist:
Restrictions
Prisma ORM treats all view
blocks as read-only representations of database queries rather than true tables. Because of this, several restrictions apply to ensure Prisma Client remains consistent with the behavior of the underlying database:
No identifiers or constraints
Views are virtual tables and do not have inherent primary keys or unique constraints. Adding artificial constraints would create invalid assumptions for Prisma Client and break queries like findUnique
. Hence, you cannot define @id
, @unique
, @@id
, @@unique
, or @index
attributes on a view
block.
No relationships
Relationships in Prisma rely on foreign keys and referential integrity, which views do not enforce. Defining relationships on a view could mislead developers into thinking data integrity is guaranteed. Hence, the @relation
attributes are not supported for views.
Limited Prisma Client API
Views do not store data themselves, so writes and operations requiring unique identifiers cannot be supported. Also:
- Only
findMany
(with filters, ordering, and skip/take pagination) is available. findUnique
, cursor-based pagination, and aggregate queries that depend on unique identifiers are not supported.- All write operations (
create
,update
,delete
,upsert
) are disabled and not generated in the Prisma Client.