Skip to main content

PostgreSQL 中的模式不兼容

PostgreSQL

概述​

¥Overview

本页的每个部分都描述了从 Prisma 1 升级到 Prisma ORM 2.x 及更高版本时的潜在问题，并解释了可用的解决方法。

¥Each section on this page describes a potential problem when upgrading from Prisma 1 to Prisma ORM 2.x and later and explains the available workarounds.

默认值未在数据库中表示​

¥Default values aren't represented in database

问题​

¥Problem

在 Prisma 1 数据模型中添加 @default 指令时，该字段的默认值由 Prisma 1 服务器在运行时生成。数据库列中没有添加 DEFAULT 约束。由于这个约束没有体现在数据库本身中，Prisma ORM 2.x 及更高版本的内省无法识别它。

¥When adding the @default directive in a Prisma 1 datamodel, the default values for this field are generated by the Prisma 1 server at runtime. There's no DEFAULT constraint added to the database column. Because this constraint is not reflected in the database itself, the Prisma ORM 2.x and later versions of introspection can't recognize it.

示例​

¥Example

Prisma 1 数据模型​

¥Prisma 1 datamodel

type Post {
  id: ID! @id
  published: Boolean @default(value: false)
}

Prisma 1 生成的 SQL 迁移​

¥Prisma 1 generated SQL migration

CREATE TABLE "Post" (
  id VARCHAR(25) PRIMARY KEY NOT NULL,
  published BOOLEAN NOT NULL
);

Prisma ORM 2.x 及更高版本中内省的结果​

¥Result of introspection in Prisma ORM versions 2.x and later

schema.prisma
model Post {
  id        String  @id
  published Boolean
}

由于在将 Prisma 1 数据模型映射到具有 prisma deploy 的数据库风格未将 DEFAULT 约束添加到数据库中，因此 Prisma ORM v2（及更高版本）在内省期间无法识别它。

¥Because the DEFAULT constraint has not been added to the database when mapping the Prisma 1 datamodel to the database with prisma deploy, Prisma ORM v2 (and later versions) doesn't recognize it during introspection.

解决方法​

¥Workarounds

手动向数据库列添加 DEFAULT 约束​

¥Manually add a DEFAULT constraint to the database column

你可以更改该列以添加 DEFAULT 约束，如下所示：

¥You can alter the column to add the DEFAULT constraint as follows:

ALTER TABLE "Post"
	ALTER COLUMN published SET DEFAULT false;

调整后，你可以重新检查你的数据库，@default 属性将被添加到 published 字段中：

¥After this adjustment, you can re-introspect your database and the @default attribute will be added to the published field:

schema.prisma
model Post {
  id        String  @id
  published Boolean @default(false)
}

手动向 Prisma 模型添加 @default 属性​

¥Manually add a @default attribute to the Prisma model

你可以将 @default 属性添加到 Prisma 模型中：

¥You can add the @default attribute to the Prisma model:

schema.prisma
model Post {
  id        String
  published Boolean @default(false)
}

如果在 Prisma 架构中设置了 @default 属性并且你运行 prisma generate，则生成的 Prisma 客户端代码将在运行时生成指定的默认值（类似于 Prisma 1 服务器在 Prisma 1 中所做的操作）。

¥If the @default attribute is set in the Prisma schema and you run prisma generate, the resulting Prisma Client code will generate the specified default values at runtime (similar to what the Prisma 1 server did in Prisma 1).

生成的 CUID，因为 ID 值未在数据库中表示​

¥Generated CUIDs as ID values aren't represented in database

问题​

¥Problem

当使用 @id 指令进行注释时，Prisma 1 会自动生成 ID 值作为 ID 字段的 CUID。这些 CUID 由 Prisma 1 服务器在运行时生成。由于这种行为并未反映在数据库本身中，因此 Prisma ORM 2.x 及更高版本中的内省无法识别它。

¥Prisma 1 auto-generates ID values as CUIDs for ID fields when they're annotated with the @id directive. These CUIDs are generated by the Prisma 1 server at runtime. Because this behavior is not reflected in the database itself, the introspection in Prisma ORM 2.x and later can't recognize it.

示例​

¥Example

Prisma 1 数据模型​

¥Prisma 1 datamodel

type Post {
  id: ID! @id
}

Prisma 1 生成的 SQL 迁移​

¥Prisma 1 generated SQL migration

CREATE TABLE "Post" (
  id VARCHAR(25) PRIMARY KEY NOT NULL
);

Prisma ORM 2.x 及更高版本中内省的结果​

¥Result of introspection in Prisma ORM versions 2.x and later

schema.prisma
model Post {
  id String @id
}

由于数据库中没有 CUID 行为的指示，Prisma ORM 的内省无法识别它。

¥Because there's no indication of the CUID behavior in the database, Prisma ORM's introspection doesn't recognize it.

解决方法​

¥Workaround

作为解决方法，你可以手动将 @default(cuid()) 属性添加到 Prisma 模型中：

¥As a workaround, you can manually add the @default(cuid()) attribute to the Prisma model:

schema.prisma
model Post {
  id String @id @default(cuid())
}

如果在 Prisma 架构中设置了 @default 属性并且你运行 prisma generate，则生成的 Prisma 客户端代码将在运行时生成指定的默认值（类似于 Prisma 1 服务器在 Prisma 1 中所做的操作）。

¥If the @default attribute is set in the Prisma schema and you run prisma generate, the resulting Prisma Client code will generate the specified default values at runtime (similar to what the Prisma 1 server did in Prisma 1).

请注意，你必须在每次内省后重新添加该属性，因为内省会删除它（因为以前版本的 Prisma 架构被覆盖）！

¥Note that you'll have to re-add the attribute after each introspection because introspection removes it (as the previous version of the Prisma schema is overwritten)!

@createdAt 未在数据库中表示​

¥@createdAt isn't represented in database

问题​

¥Problem

当使用 @createdAt 指令注释时，Prisma 1 会自动生成 DateTime 字段的值。这些值由 Prisma 1 服务器在运行时生成。由于这种行为并未反映在数据库本身中，因此 Prisma ORM 2.x 及更高版本中的内省无法识别它。

¥Prisma 1 auto-generates values for DateTime fields when they're annotated with the @createdAt directive. These values are generated by the Prisma 1 server at runtime. Because this behavior is not reflected in the database itself, the introspection in Prisma ORM 2.x and later can't recognize it.

示例​

¥Example

Prisma 1 数据模型​

¥Prisma 1 datamodel

type Post {
  id: ID! @id
  createdAt: DateTime! @createdAt
}

Prisma 1 生成的 SQL 迁移​

¥Prisma 1 generated SQL migration

CREATE TABLE "Post" (
  id VARCHAR(25) PRIMARY KEY NOT NULL,
  "createdAt" TIMESTAMP NOT NULL
);

Prisma ORM 2.x 及更高版本内省的结果​

¥Result of introspection in Prisma ORM 2.x and later versions

schema.prisma
model Post {
  id        String   @id
  createdAt DateTime
}

解决方法​

¥Workarounds

手动将 DEFAULT CURRENT_TIMESTAMP 添加到数据库列​

¥Manually add DEFAULT CURRENT_TIMESTAMP to the database column

你可以更改该列以添加 DEFAULT 约束，如下所示：

¥You can alter the column to add the DEFAULT constraint as follows:

ALTER TABLE "Post"
	ALTER COLUMN "createdAt" SET DEFAULT CURRENT_TIMESTAMP;

调整后，你可以重新检查你的数据库，@default 属性将被添加到 createdAt 字段中：

¥After this adjustment, you can re-introspect your database and the @default attribute will be added to the createdAt field:

schema.prisma
model Post {
  id        String
  createdAt DateTime @default(now())
}

手动将 @default(now()) 属性添加到 Prisma 模型​

¥Manually add the @default(now()) attribute to the Prisma model

作为解决方法，你可以手动将 @default(now()) 属性添加到 Prisma 模型中：

¥As a workaround, you can manually add the @default(now()) attribute to the Prisma model:

schema.prisma
model Post {
  id        String   @id
  createdAt DateTime @default(now())
}

如果在 Prisma 架构中设置了 @default 属性并且你运行 prisma generate，则生成的 Prisma 客户端代码将在运行时生成指定的默认值（类似于 Prisma 1 服务器在 Prisma 1 中所做的操作）。

¥If the @default attribute is set in the Prisma schema and you run prisma generate, the resulting Prisma Client code will generate the specified default values at runtime (similar to what the Prisma 1 server did in Prisma 1).

请注意，你必须在每次内省后重新添加该属性，因为内省会删除它（因为以前版本的 Prisma 架构被覆盖）！

¥Note that you'll have to re-add the attribute after each introspection because introspection removes it (as the previous version of the Prisma schema is overwritten)!

@updatedAt 未在数据库中表示​

¥@updatedAt isn't represented in database

问题​

¥Problem

当使用 @updatedAt 指令注释时，Prisma 1 会自动生成 DateTime 字段的值。这些值由 Prisma 1 服务器在运行时生成。由于这种行为并未反映在数据库本身中，Prisma ORM 2.x 及更高版本中的内省无法识别它。

¥Prisma 1 auto-generates values for DateTime fields when they're annotated with the @updatedAt directive. These values are generated by the Prisma 1 server at runtime. Because this behavior is not reflected in the database itself, the introspection in Prisma ORM 2.x and later can't recognize it..

示例​

¥Example

Prisma 1 数据模型​

¥Prisma 1 datamodel

type Post {
  id: ID! @id
  updatedAt: DateTime! @updatedAt
}

Prisma 1 生成的 SQL 迁移​

¥Prisma 1 generated SQL migration

CREATE TABLE "Post" (
  id VARCHAR(25) PRIMARY KEY NOT NULL,
  updatedAt TIMESTAMP
);

Prisma ORM 2.x 及更高版本内省的结果​

¥Result of introspection in Prisma ORM 2.x and later versions

schema.prisma
model Post {
  id        String   @id
  updatedAt DateTime
}

解决方法​

¥Workarounds

手动将 @updatedAt 属性添加到 Prisma 模型​

¥Manually add the @updatedAt attribute to the Prisma model

作为解决方法，你可以手动将 @updatedAt 属性添加到 Prisma 模型中：

¥As a workaround, you can manually add the @updatedAt attribute to the Prisma model:

schema.prisma
model Post {
  id        String   @id
  updatedAt DateTime @updatedAt
}

如果在 Prisma 架构中设置了 @updatedAt 属性并且你运行 prisma generate，则生成的 Prisma 客户端代码将在更新现有记录时自动生成此列的值（类似于 Prisma 1 服务器在 Prisma 1 中所做的操作）。

¥If the @updatedAt attribute is set in the Prisma schema and you run prisma generate, the resulting Prisma Client code will automatically generate values for this column when an existing record is updated (similar to what the Prisma 1 server did in Prisma 1).

请注意，你必须在每次内省后重新添加该属性，因为内省会删除它（因为以前版本的 Prisma 架构被覆盖）！

¥Note that you'll have to re-add the attribute after each introspection because introspection removes it (as the previous version of the Prisma schema is overwritten)!

内联 1-1 关系被识别为 1-n（缺少 UNIQUE 约束）​

¥Inline 1-1 relations are recognized as 1-n (missing UNIQUE constraint)

问题​

¥Problem

在 Prisma ORM v1.31 中引入的 数据模型 v1.1 中，1-1 关系可以声明为内联。在这种情况下，关系将不会通过 关系表 维护，而是通过所涉及的两个表之一上的单个外键维护。

¥In the datamodel v1.1 that was introduced in Prisma ORM v1.31, 1-1 relations can be declared as inline. In that case, the relation will not be maintained via a relation table but via a single foreign key on one of the two tables involved.

使用此方法时，Prisma ORM 不会向外键列添加 UNIQUE 约束，这意味着在 Prisma ORM 版本 2.x 及更高版本中进行内省后，这种以前的 1-1 关系将被添加为 1-n 关系 到 Prisma 架构。

¥When this approach is used, Prisma ORM doesn't add a UNIQUE constraint to the foreign key column which means that after introspection in Prisma ORM version 2.x and later, this former 1-1 relation will be added as a 1-n relation to the Prisma schema.

示例​

¥Example

Prisma ORM 数据模型 v1.1（可从 Prisma ORM v1.31 获取）​

¥Prisma ORM datamodel v1.1 (available from Prisma ORM v1.31)

type User {
  id: ID! @id
  profile: Profile @relation(link: INLINE)
}

type Profile {
  id: ID! @id
  user: User
}

请注意，在这种情况下省略 @relation 指令将导致相同的行为，因为 link: INLINE 是 1-1 关系的默认值。

¥Note that omitting the @relation directive in this case would result in the same behavior because link: INLINE is the default for 1-1 relations.

Prisma 1 生成的 SQL 迁移​

¥Prisma 1 generated SQL migration

CREATE TABLE "User" (
  id VARCHAR(25) PRIMARY KEY NOT NULL
);

CREATE TABLE "Profile" (
  id VARCHAR(25) PRIMARY KEY NOT NULL,
  "user" VARCHAR(25),
  FOREIGN KEY ("user") REFERENCES "User"(id)
);

Prisma ORM 2.x 及更高版本内省的结果​

¥Result of introspection in Prisma ORM 2.x and later versions

schema.prisma
model User {
  id      String    @id
  Profile Profile[]
}

model Profile {
  id   String  @id
  user String?
  User User?   @relation(fields: [user], references: [id])
}

因为在 user 列（代表此关系中的外键）上没有定义 UNIQUE 约束，Prisma ORM 的内省将该关系识别为 1-n。

¥Because there's no UNIQUE constraint defined on the user column (which represents the foreign key in this relation), Prisma ORM's introspection recognizes the relation as 1-n.

解决方法​

¥Workaround

手动向外键列添加 UNIQUE 约束​

¥Manually add UNIQUE constraint to the foreign key column

你可以更改外键列以添加 UNIQUE 约束，如下所示：

¥You can alter the foreign key column to add the UNIQUE constraint as follows:

ALTER TABLE "Profile"
  ADD CONSTRAINT userId_unique UNIQUE ("user");

调整后，你可以重新检查你的数据库，并且 1-1 关系将被正确识别：

¥After this adjustment, you can re-introspect your database and the 1-1 relation will be properly recognized:

schema.prisma
model User {
  id      String   @id
  Profile Profile?
}

model Profile {
  id   String  @id
  user String? @unique
  User User?   @relation(fields: [user], references: [id])
}

所有非内联关系都被识别为 m-n​

¥All non-inline relations are recognized as m-n

问题​

¥Problem

Prisma 1 大多数时候将关系表示为关系表：

¥Prisma 1 represents relations as relation tables most of the time:

  • Prisma 1 数据模型 v1.0 中的所有关系都表示为关系表

    ¥All relations in the Prisma 1 datamodel v1.0 are represented as relation tables

  • 在数据模型 v1.1 中，所有 m-n 关系以及声明为 link: TABLE 的 1-1 和 1-n 关系都表示为关系表。

    ¥In datamodel v1.1, all m-n relations as well as the 1-1 and 1-n relations declared as link: TABLE are represented as relation tables.

由于这种表示形式，Prisma ORM 版本 2.x 及更高版本中的内省会将所有这些关系识别为 m-n 关系，即使它们可能已在 Prisma 1 中声明为 1-1 或 1-n。

¥Because of this representation, introspection in Prisma ORM version 2.x and later will recognize all these relations as m-n relations, even though they might have been declared as 1-1 or 1-n in Prisma 1.

示例​

¥Example

Prisma 1 数据模型​

¥Prisma 1 datamodel

type User {
  id: ID! @id
  posts: [Post!]!
}

type Post {
  id: ID! @id
  author: User! @relation(link: TABLE)
}

Prisma 1 生成的 SQL 迁移​

¥Prisma 1 generated SQL migration

CREATE TABLE "User" (
  id VARCHAR(25) PRIMARY KEY NOT NULL
);

CREATE TABLE "Post" (
  id VARCHAR(25) PRIMARY KEY NOT NULL
);

CREATE TABLE "_PostToUser" (
  "A" VARCHAR(25) NOT NULL REFERENCES "Post"(id) ON DELETE CASCADE,
  "B" VARCHAR(25) NOT NULL REFERENCES "User"(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX "_PostToUser_AB_unique" ON "_PostToUser"("A" text_ops,"B" text_ops);
CREATE INDEX "_PostToUser_B" ON "_PostToUser"("B" text_ops);

Prisma ORM 2.x 及更高版本内省的结果​

¥Result of introspection in Prisma ORM 2.x and later versions

schema.prisma
model User {
  id   String @id
  Post Post[] @relation(references: [id])
}

model Post {
  id   String @id
  User User[] @relation(references: [id])
}

由于 Prisma 1 创建的关系表使用与 Prisma ORM 版本 2.x 及更高版本中相同的 关系表的约定，因此该关系现在被识别为 m-n 关系。

¥Because the relation table that was created by Prisma 1 uses the same conventions for relation tables as in Prisma ORM version 2.x and later, the relation now gets recognized as a m-n relation.

解决方法​

¥Workaround

作为解决方法，你可以将数据迁移到与 Prisma ORM 的 1-n 关系兼容的结构中：

¥As a workaround, you can migrate the data into a structure that's compatible with Prisma ORM's 1-n relation:

  1. 在 Post 表上创建新列 authorId。该列应该是引用 User 表的 id 字段的外键：

    ¥Create new column authorId on the Post table. This column should be a foreign key that references the id field of the User table:

    ALTER TABLE "Post"  ADD COLUMN "authorId" VARCHAR(25);
    ALTER TABLE "Post"
    ADD CONSTRAINT fk_author
    FOREIGN KEY ("authorId")
    REFERENCES "User"("id");

  2. 编写一个 SQL 查询，从 _PostToUser 关系表中读取所有行，并针对每一行：

    ¥Write a SQL query that reads all the rows from the _PostToUser relation table and for each row:

    1. 通过查找 A 列中的值来查找相应的 Post 记录

      ¥Finds the respective Post record by looking up the value from column A

    2. 将 B 列中的值作为 authorId 的值插入到该 Post 记录中

      ¥Inserts the value from column B as the value for authorId into that Post record

    UPDATE "Post" post
    SET "authorId" = post_to_user."B"
    FROM "_PostToUser" post_to_user
    WHERE post_to_user."A" = post."id";

  3. 删除 _PostToUser 关系表

    ¥Delete the _PostToUser relation table

    DROP TABLE "_PostToUser";

之后，你可以检查你的数据库，关系现在将被识别为 1-n：

¥After that you can introspect your database and the relation will now be recognized as 1-n:

schema.prisma
model User {
  id   String @id
  Post Post[]
}

model Post {
  id       String @id
  User     User   @relation(fields: [authorId], references: [id])
  authorId String
}

Json 类型在数据库中表示为 TEXT​

¥Json type is represented as TEXT in database

问题​

¥Problem

Prisma 1 在其数据模型中支持 Json 数据类型。然而，在底层数据库中，类型 Json 的字段实际上使用底层数据库的 TEXT 数据类型存储为纯字符串。对存储的 JSON 数据的任何解析和验证都是由 Prisma 1 服务器在运行时完成的。

¥Prisma 1 supports the Json data type in its datamodel. However, in the underlying database, fields of type Json are actually stored as plain strings using the TEXT data type of the underlying database. Any parsing and validation of the stored JSON data is done by the Prisma 1 server at runtime.

示例​

¥Example

Prisma 1 数据模型​

¥Prisma 1 datamodel

type User {
  id: ID! @id
  jsonData: Json
}

Prisma 1 生成的 SQL 迁移​

¥Prisma 1 generated SQL migration

CREATE TABLE "User" (
  id VARCHAR(25) PRIMARY KEY NOT NULL,
  jsonData TEXT
);

Prisma ORM 2.x 及更高版本内省的结果​

¥Result of introspection in Prisma ORM 2.x and later versions

schema.prisma
model User {
  id       String  @id
  jsonData String?
}

解决方法​

¥Workaround

你可以手动将列的类型更改为 JSON

¥You can manually change the type of the column to JSON

ALTER TABLE "User" ALTER COLUMN "jsonData" TYPE JSON  USING "jsonData"::json;

调整后，你可以重新检查你的数据库，该字段现在将被识别为 Json：

¥After this adjustment, you can re-introspect your database and the field will now be recognized as Json:

schema.prisma
model User {
  id       String @id
  jsonData Json?
}

枚举在数据库中表示为 TEXT​

¥Enums are represented as TEXT in database

问题​

¥Problem

Prisma 1 在其数据模型中支持 enum 数据类型。然而，在底层数据库中，声明为 enum 的类型实际上使用底层数据库的 TEXT 数据类型存储为纯字符串。对存储的 enum 数据的任何验证均由 Prisma 1 服务器在运行时完成。

¥Prisma 1 supports the enum data type in its datamodel. However, in the underlying database, types declared as enum are actually stored as plain strings using the TEXT data type of the underlying database. Any validation of the stored enum data is done by the Prisma 1 server at runtime.

示例​

¥Example

Prisma 1 数据模型​

¥Prisma 1 datamodel

type User {
  id: ID! @id
  role: Role
}

enum Role {
  ADMIN
  CUSTOMER
}

Prisma 1 生成的 SQL 迁移​

¥Prisma 1 generated SQL migration

CREATE TABLE "User" (
  id VARCHAR(25) PRIMARY KEY NOT NULL,
  role TEXT
);

Prisma ORM 2.x 及更高版本内省的结果​

¥Result of introspection in Prisma ORM 2.x and later versions

schema.prisma
model User {
  id   String  @id
  role String?
}

解决方法​

¥Workaround

你可以手动将 role 列转换为具有所需值的枚举：

¥You can manually turn the role column into an enum with your desired values:

  1. 在数据库中创建一个 enum，它镜像你在 Prisma 1 数据模型中定义的 enum：

    ¥Create an enum in your database that mirrors the enum you defined in the Prisma 1 datamodel:

    CREATE TYPE "Role" AS ENUM ('CUSTOMER', 'ADMIN');

  2. 将类型从 TEXT 更改为新的 enum：

    ¥Change the type from TEXT to your new enum:

    ALTER TABLE "User" ALTER COLUMN "role" TYPE "Role"
    USING "role"::text::"Role";

经过内省，该类型现在被正确识别为枚举：

¥After introspection, the type is now properly recognized as an enum:

schema.prisma
model User {
  id   String @id
  role Role?
}

enum Role {
  ADMIN
  CUSTOMER
}

CUID 长度不匹配​

¥Mismatching CUID length

问题​

¥Problem

Prisma 1 使用 CUID 作为所有数据库记录的 ID 值。在底层数据库中，这些 ID 表示为最大长度为 25 个字符的字符串（如 VARCHAR(25)）。但是，当使用 @default(cuid()) 在 Prisma ORM 2.x（或更高版本）架构中配置默认 CUID 时，生成的 ID 值可能会超出 25 个字符的限制（最大长度可能为 30 个字符）。为了使你的 ID 成为 Prisma ORM 2.x（或更高版本）的证明，你需要将列类型调整为 VARCHAR(30)。

¥Prisma 1 uses CUIDs as ID values for all database records. In the underlying database, these IDs are represented as strings with a maximum size of 25 characters (as VARCHAR(25)). However, when configuring default CUIDs in your Prisma ORM 2.x (or later versions) schema with @default(cuid()) the generated ID values might exceed the limit of 25 characters (the maximum length might be 30 characters). To make your IDs proof for Prisma ORM 2.x (or later versions), you therefore need to adjust the column type to VARCHAR(30).

示例​

¥Example

Prisma 1 数据模型​

¥Prisma 1 datamodel

type User {
  id: ID! @id
}

Prisma 1 生成的 SQL 迁移​

¥Prisma 1 generated SQL migration

CREATE TABLE "User" (
  id VARCHAR(25) PRIMARY KEY NOT NULL
);

Prisma ORM 2.x 及更高版本内省的结果​

¥Result of introspection in Prisma ORM 2.x and later versions

schema.prisma
model User {
  id String @id
}

解决方法​

¥Workaround

你可以手动将 VARCHAR(25) 列转换为 VARCHAR(30)：

¥You can manually turn the VARCHAR(25) columns into VARCHAR(30):

ALTER TABLE "User" ALTER COLUMN "id" SET DATA TYPE character varying(30);

注意：使用 升级命令行接口 修复此问题时，生成的 SQL 语句将继续出现在升级 CLI 中，即使你更改了基础数据库中的列类型也是如此。这是升级 CLI 当前的一个限制。

¥Note: When fixing this issue with the Upgrade CLI, the generated SQL statements will keep appearing in the Upgrade CLI even after you have changed the column types in the underlying database. This is a currently a limitation in the Upgrade CLI.

标量列表（数组）由额外的表维护​

¥Scalar lists (arrays) are maintained with extra table

问题​

¥Problem

在 Prisma 1 中，你可以在模型上定义标量类型列表。在底层，这是通过一个额外的表来实现的，该表跟踪列表中的值。

¥In Prisma 1, you can define lists of scalar types on your models. Under the hood, this is implemented with an extra table that keeps track of the values in the list.

为了消除使用额外表的方法会带来隐藏的性能成本，Prisma ORM 2.x 及更高版本仅在你使用的数据库本身支持标量列表时才支持标量列表。目前只有 PostgreSQL 本身支持标量列表（数组）。

¥To remove the approach with the extra table which incurred hidden performance costs, Prisma ORM 2.x and later versions only support scalar lists only when they're natively supported by the database you use. At the moment, only PostgreSQL supports scalar lists (arrays) natively.

因此，使用 PostgreSQL，你可以继续在 Prisma ORM 2.x 及更高版本中使用标量列表，但你需要执行数据迁移，将额外表中的数据从 Prisma 1 传输到实际的 PostgreSQL 数组中。

¥With PostgreSQL, you therefore can keep using scalar lists in Prisma ORM 2.x and later versions, but you'll need to perform a data migration to transfer the data from the extra table from Prisma 1 into an actual PostgreSQL array.

示例​

¥Example

Prisma 1 数据模型​

¥Prisma 1 datamodel

type User {
  id: ID! @id
  coinflips: [Boolean!]! @scalarList(strategy: RELATION)
}

Prisma 1 生成的 SQL 迁移​

¥Prisma 1 generated SQL migration

CREATE TABLE "User" (
  id VARCHAR(25) PRIMARY KEY NOT NULL
);

CREATE TABLE "User_coinflips" (
    "nodeId" VARCHAR(25) REFERENCES "User"(id),
    position INTEGER,
    value BOOLEAN NOT NULL,
    CONSTRAINT "User_coinflips_pkey" PRIMARY KEY ("nodeId", position)
);
CREATE UNIQUE INDEX "User_coinflips_pkey" ON "User_coinflips"("nodeId" text_ops,position int4_ops);

Prisma ORM 2 内省结果​

¥Result of Prisma ORM 2 introspection

schema.prisma
model User {
  id             String           @id
  User_coinflips User_coinflips[]
}

model User_coinflips {
  nodeId   String
  position Int
  value    Boolean
  User     User    @relation(fields: [nodeId], references: [id])

  @@id([nodeId, position])
}

请注意，你现在可以生成 Prisma 客户端，并且可以通过额外的表访问标量列表中的数据。PostgreSQL 用户也可以将数据迁移到原生 PostgreSQL 数组中，并继续受益于用于标量列表的更灵活的 Prisma 客户端 API（请阅读下面的部分以获取更多信息）。

¥Note that you can now generate Prisma Client and you'll be able to access the data from the scalar lists through the extra table. PostgreSQL users can alternatively migrate the data into a native PostgreSQL array and continue to benefit from the slicker Prisma Client API for scalar lists (read the section below for more info).

Expand for sample Prisma Client API calls

要访问 coinflips 数据，你现在必须在查询中始终对其进行 include：

¥To access the coinflips data, you will now have to always include it in your queries:

const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    coinflips: {
      orderBy: { position: 'asc' },
    },
  },
})

注意：orderBy 对于保留列表的顺序很重要。

¥Note: The orderBy is important to retain the order of the list.

这是查询的结果：

¥This is the `result of the query:

{
  id: 1,
  name: 'Alice',
  coinflips: [
    { id: 1, position: 1000, value: false },
    { id: 2, position: 2000, value: true },
    { id: 3, position: 3000, value: false },
    { id: 4, position: 4000, value: true },
    { id: 5, position: 5000, value: true },
    { id: 6, position: 6000, value: false }
  ]
}

要仅访问列表中的布尔值，你可以在 user 上通过 map 覆盖 coinflips，如下所示：

¥To access just the boolean values from the list, you can map over the coinflips on user as follows:

const currentCoinflips = user!.coinflips.map((cf) => cf.value)

注意：上面的感叹号意味着你正在强制解包 user 值。这是必要的，因为从上一个查询返回的 user 可能是 null。

¥Note: The exclamation mark above means that you're force unwrapping the user value. This is necessary because the user returned from the previous query might be null.

这是调用 map 后 currentCoinflips 的值：

¥Here's the value of currentCoinflips after the call to map:

[false, true, false, true, true, false]

解决方法​

¥Workaround

以下解决方法仅适用于 PostgreSQL 用户！

¥The following workaround is only available for PostgreSQL users!

由于标量列表（即 arrays）可作为原生 PostgreSQL 功能使用，因此你可以在 Prisma 模式中继续使用 coinflips: Boolean[] 的相同表示法。

¥As scalar lists (i.e. arrays) are available as a native PostgreSQL feature, you can keep using the same notation of coinflips: Boolean[] in your Prisma schema.

但是，为此，你需要手动将基础数据从 User_coinflips 表迁移到 PostgreSQL 数组中。你可以按照以下方法执行此操作：

¥However, in order to do so you need to manually migrate the underlying data from the User_coinflips table into a PostgreSQL array. Here's how you can do that:

  1. 将新的 coinflips 列添加到 User 表中：

    ¥Add the new coinflips column to the User tables:

    ALTER TABLE "User" ADD COLUMN coinflips BOOLEAN[];

  2. 将数据从 "User_coinflips".value 迁移到 "User.coinflips"：

    ¥Migrate the data from "User_coinflips".value to "User.coinflips":

    UPDATE "User"
      SET coinflips = t.flips
    FROM (
      SELECT "nodeId", array_agg(VALUE ORDER BY position) AS flips
      FROM "User_coinflips"
      GROUP BY "nodeId"
    ) t
    where t."nodeId" = "User"."id";

  3. 要进行清理，你可以删除 User_coinflips 表：

    ¥To cleanup, you can delete the User_coinflips table:

    DROP TABLE "User_coinflips";

你现在可以检查你的数据库，coinflips 字段将在新的 Prisma 模式中表示为数组：

¥You can now introspect your database and the coinflips field will be represented as an array in your new Prisma schema:

schema.prisma
model User {
  id        String    @id
  coinflips Boolean[]
}

你可以像以前一样继续使用 Prisma Client：

¥You can keep using Prisma Client as before:

const user = await prisma.user.findUnique({
  where: { id: 1 },
})

这是 API 调用的结果：

¥This is the result from the API call:

{
  id: 1,
  name: 'Alice',
  coinflips: [ false, true, false, true, true, false ]
}