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, 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   @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 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    @id
  email String
  name  String
  bio   String
}

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.