How to include views in your Prisma schema

Support for views is currently a very early 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.

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:

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
}

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 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    @unique
  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    @unique
  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:


/// The underlying view does not contain a valid unique identifier and can therefore currently not be handled by Prisma Client.
view UserInfo {
  id    Int?
  email String?
  name  String?
  bio   String?

  @@ignore
}

The view block is generated initially with a @@ignore attribute because there is no unique identifier defined (which is currently a limitation of the views preview feature).

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.

Adding a unique identifier to an introspected view

To be able to use the introspected view in Prisma Client, you will need to select and define one or multiple of the fields as the unique identifier.

In the above view's case, the id column refers to a uniquely identifiable field in the underlying User table so that field can also be used as the uniquely identifiable field in the view block.

In order to make this view block valid you will need to:

Remove the optional flag ? from the id field
Add the @unique attribute to the id field
Remove the @@ignore attribute
Remove the comment Prisma generated warning about an invalid view


/// The underlying view does not contain a valid unique identifier and can therefore currently not be handled by Prisma Client.
view UserInfo {
  id    Int?
  id    Int    @unique
  email String?
  name  String?
  bio   String?
  
  @@ignore
}

When re-introspecting your database, any custom changes to your view definitions will be preserved.

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

Unique Identifier

Currently, Prisma treats views in the same way as models. This means that a view needs to have at least one unique identifier, which can be represented by any of the following:

A unique constraint denoted with @unique
A composite unique constraint denoted with @@unique
An @id field
A composite identifier denoted with @@id

In relational databases, a view's unique identifier can be defined as a @unique attribute on one field, or a @@unique attribute on multiple fields. When possible, it is preferable to use a @unique or @@unique constraint over an @id or @@id field.

In MongoDB, however, the unique identifier 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 a @unique attribute. If another column in the underlying User table had been defined as uniquely identifiable and made available in the view's query results, that column could have been used as the unique identifier instead.

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',
  },
})

Currently, Prisma Client allows you to update a view if the underlying database allows it, without any additional validation.

Was this helpful?

Edit this page on GitHub

Prisma ORM

Prisma Data Platform

Views

Enable the `views` preview feature

Create a view in the underlying database

Use views with Prisma Migrate and `db push`

Add views to your Prisma schema

Write by hand

Define a `view` block

Define fields

Use introspection

Adding a unique identifier to an introspected view

The `views` directory

Limitations

Unique Identifier

Introspection

Query views in Prisma Client

Was this helpful?

Product

Developers

Use Cases

Company

Newsletter

Prisma ORM

Prisma Data Platform

Latest from the blog

Views

Enable the views preview feature

Create a view in the underlying database

Use views with Prisma Migrate and db push

Add views to your Prisma schema

Write by hand

Define a view block

Define fields

Use introspection

Adding a unique identifier to an introspected view

The views directory

Limitations

Unique Identifier

Introspection

Query views in Prisma Client

Was this helpful?

Product

Developers

Use Cases

Company

Newsletter

Enable the `views` preview feature

Use views with Prisma Migrate and `db push`

Define a `view` block

The `views` directory