意见

warning

对视图的支持目前是 预览 非常早期的功能。你可以使用 view 关键字将视图添加到 Prisma 架构中，或使用 db pull 内省数据库架构中的视图。你尚无法使用 Prisma Migrate 和 db push 将架构中的视图应用到数据库，除非使用 --create-only 标志将更改手动添加到迁移文件中。

有关此功能的最新进展，请关注 我们的 GitHub 问题。

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

数据库视图允许你命名和存储查询。在关系数据库中，视图是 存储的 SQL 查询，可能包含多个表中的列或计算值（例如聚合）。在 MongoDB 中，视图是可查询的对象，其中内容由其他集合上的 聚合管道 定义。

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

views 预览功能允许你使用 view 关键字表示 Prisma 架构中的视图。要在 Prisma ORM 中使用视图，请按照以下步骤操作：

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

• 启用 views 预览功能

  ¥Enable the views preview feature

• 在底层数据库中创建视图，直接或作为 手动添加到 Prisma Migrate 迁移文件，或使用现有视图

  ¥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

• 表示 Prisma 架构中的视图

  ¥Represent the view in your Prisma schema

• 在 Prisma 客户端中查询视图

  ¥Query the view in Prisma Client

启用 views 预览功能

¥Enable the views preview feature

对视图的支持目前处于早期预览阶段。要启用 views 预览功能，请将 views 功能标志添加到 Prisma Schema 中 generator 块的 previewFeatures 字段：

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

schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["views"]
}

请在我们专用的 views 预览功能反馈问题 中留下有关此预览功能的反馈。

¥Please leave feedback about this preview feature in our dedicated preview feature feedback issue for views.

在底层数据库中创建视图

¥Create a view in the underlying database

目前，你无法使用 Prisma Migrate 和 db push 将在 Prisma 架构中定义的视图应用到数据库。相反，你必须首先在基础数据库中创建视图（手动或 作为迁移的一部分）。

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

例如，采用以下包含 User 模型和相关 Profile 模型的 Prisma 架构：

¥For example, take the following Prisma schema with a User model and a related Profile model:

Relational databases

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
}

MongoDB

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
}

接下来，在底层数据库中获取 UserInfo 视图，该视图组合了 User 模型中的 email 和 name 字段以及 Profile 模型中的 bio 字段。

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

对于关系数据库，创建该视图的 SQL 语句是：

¥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";

对于 MongoDB，你可以使用以下命令进行 创建一个视图：

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

将视图与 Prisma Migrate 和 db push 结合使用

¥Use views with Prisma Migrate and db push

如果你使用 Prisma Migrate 或 db push 将更改应用到 Prisma 架构，Prisma ORM 不会创建或运行任何与视图相关的 SQL。

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

要在迁移中包含视图，请运行 migrate dev --create-only，然后手动将视图的 SQL 添加到迁移文件中。或者，你可以在数据库中手动创建视图。

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

将视图添加到你的 Prisma 架构

¥Add views to your Prisma schema

要将视图添加到 Prisma 模式，请使用 view 关键字。

¥To add a view to your Prisma schema, use the view keyword.

你可以在 Prisma 模式中表示上面示例中的 UserInfo 视图，如下所示：

¥You can represent the UserInfo view from the example above in your Prisma schema as follows:

Relational databases

view UserInfo {
  id    Int    @unique
  email String
  name  String
  bio   String
}

MongoDB

view UserInfo {
  id    String @id @default(auto()) @map("_id") @db.ObjectId
  email String
  name  String
  bio   String
}

手写

¥Write by hand

view 块由两个主要部分组成：

¥A view block is comprised of two main pieces:

• view 块定义

  ¥The view block definition

• 视图的字段定义

  ¥The view's field definitions

这两部分允许你定义生成的 Prisma 客户端中的视图名称以及视图查询结果中显示的列。

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

定义 view 块

¥Define a view block

要定义上面示例中的 UserInfo 视图，首先使用 view 关键字在你的架构中定义一个名为 UserInfo 的 view 块：

¥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

UserInfo 示例视图的字段可以定义如下：

¥The fields of the UserInfo example view can be defined as follows:

Relational databases

view UserInfo {
  id    Int    @unique
  email String
  name  String
  bio   String
}

MongoDB

view UserInfo {
  id    String @id @default(auto()) @map("_id") @db.ObjectId
  email String
  name  String
  bio   String
}

view 块的每个字段代表底层数据库中视图的查询结果中的一列。

¥Each field of a view block represents a column in the query results of the view in the underlying database.

使用内省

¥Use introspection

warning

目前仅适用于 PostgreSQL、MySQL、SQL Server 和 CockroachDB。

¥Currently only available for PostgreSQL, MySQL, SQL Server and CockroachDB.

如果你的数据库中定义了一个或多个现有视图，introspection 将在你的 Prisma 架构中自动生成代表这些视图的 view 块。

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

假设示例 UserInfo 视图存在于你的底层数据库中，运行以下命令将在你的 Prisma 架构中生成代表该视图的 view 块：

¥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

生成的 view 块将定义如下：

¥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
}

view 块最初是使用 @@ignore 属性生成的，因为 没有定义唯一标识符（当前是视图预览功能的 limitation）。

¥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).

warning

请注意，目前 db pull 仅在使用 PostgreSQL、MySQL、SQL Server 或 CockroachDB 时内省模式中的视图。对此工作流程的支持将扩展到其他数据库提供商。

¥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

为了能够在 Prisma Client 中使用内省视图，你需要选择并定义一个或多个字段作为唯一标识符。

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

在上述视图的情况下，id 列引用基础 User 表中的唯一可识别字段，因此该字段也可以用作 view 块中的唯一可识别字段。

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

为了使这个 view 块有效，你需要：

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

• 从 id 字段中删除可选标志 ?

  ¥Remove the optional flag ? from the id field

• 将 @unique 属性添加到 id 字段

  ¥Add the @unique attribute to the id field

• 删除 @@ignore 属性

  ¥Remove the @@ignore attribute

• 删除生成的有关无效视图的评论警告

  ¥Remove the generated comment 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.

views 目录

¥The views directory

使用一个或多个现有视图对数据库进行内省还将在 prisma 目录中创建一个新的 views 目录（从 Prisma 版本 4.12.0 开始）。该目录将包含一个以数据库架构命名的子目录，其中包含该架构中内省的每个视图的 .sql 文件。每个文件都将以单个视图命名，并将包含相关视图定义的查询。

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

例如，使用上面使用的模型对具有默认 public 模式的数据库进行内省后，你会发现创建了一个包含以下内容的 prisma/views/public/UserInfo.sql 文件：

¥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

目前，Prisma ORM 以与模型相同的方式处理视图。这意味着视图需要至少有一个唯一标识符，可以由以下任意一项表示：

¥Currently, Prisma ORM 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:

• 用 @unique 表示的唯一约束

  ¥A unique constraint denoted with @unique

• 用 @@unique 表示的复合唯一约束

  ¥A composite unique constraint denoted with @@unique

• @id 字段

  ¥An @id field

• 用 @@id 表示的复合标识符

  ¥A composite identifier denoted with @@id

在关系数据库中，视图的唯一标识符可以定义为一个字段上的 @unique 属性，或多个字段上的 @@unique 属性。如果可能，最好使用 @unique 或 @@unique 约束而不是 @id 或 @@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.

然而，在 MongoDB 中，唯一标识符必须是 @id 属性，该属性通过 @map("_id") 映射到底层数据库中的 _id 字段。

¥In MongoDB, however, the unique identifier must be an @id attribute that maps to the _id field in the underlying database with @map("_id").

在上面的示例中，id 字段具有 @unique 属性。如果基础 User 表中的另一列被定义为唯一可识别的并且在视图的查询结果中可用，则该列可以用作唯一标识符。

¥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

目前，视图内省仅适用于 PostgreSQL、MySQL、SQL Server 和 CockroachDB。如果你使用其他数据库提供商，则必须手动添加你的视图。

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

Prisma 客户端中的查询视图

¥Query views in Prisma Client

你可以像查询模型一样在 Prisma Client 中查询视图。例如，以下查询在上面定义的 UserInfo 视图中查找 name 为 'Alice' 的所有用户。

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

目前，Prisma 客户端允许你在基础数据库允许的情况下更新视图，而无需任何额外的验证。

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

特殊类型的视图

¥Special types of views

本节介绍如何将 Prisma ORM 与数据库中的可更新视图和物化视图结合使用。

¥This section describes how to use Prisma ORM with updatable and materialized views in your database.

可更新的视图

¥Updatable views

一些数据库支持可更新视图（例如 PostgreSQL、MySQL 和 SQL Server）。可更新视图允许你创建、更新或删除条目。

¥Some databases support updatable views (e.g. PostgreSQL, MySQL and SQL Server). Updatable views allow you to create, update or delete entries.

目前 Prisma ORM 将所有 view 视为可更新视图。如果底层数据库支持视图的此功能，则操作应该成功。如果视图未标记为可更新，数据库将返回错误，Prisma 客户端将抛出此错误。

¥Currently Prisma ORM treats all views as updatable views. If the underlying database supports this functionality for the view, the operation should succeed. If the view is not marked as updatable, the database will return an error, and Prisma Client will then throw this error.

将来，Prisma 客户端可能支持将单个视图标记为可更新或不可更新。请根据你的用例对我们的 views 反馈问题 发表评论。

¥In the future, Prisma Client might support marking individual views as updatable or not updatable. Please comment on our views feedback issue with your use case.

物化视图

¥Materialized views

一些数据库支持物化视图，例如 PostgreSQL、CockroachDB、MongoDB 和 SQL Server（此处称为 "索引视图"）。

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

目前，Prisma ORM 不支持物化视图。但是，当你使用 手动创建视图 时，你还可以使用底层数据库中的相应命令创建物化视图。然后，你可以使用 Prisma Client 的 TypedSQL 功能 执行命令并手动刷新视图。

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

将来，Prisma 客户端可能支持将单个视图标记为物化，并添加 Prisma 客户端方法来刷新物化视图。请根据你的用例对我们的 views 反馈问题 发表评论。

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