Skip to main content

表继承

概述​

¥Overview

表继承是一种软件设计模式，允许对实体之间的层次关系进行建模。在数据库级别使用表继承还可以在 JavaScript/TypeScript 应用中使用联合类型或跨多个模型共享一组通用属性。

¥Table inheritance is a software design pattern that allows the modeling of hierarchical relationships between entities. Using table inheritance on the database level can also enable the use of union types in your JavaScript/TypeScript application or share a set of common properties across multiple models.

本页介绍了两种表继承方法，并解释了如何将它们与 Prisma ORM 一起使用。

¥This page introduces two approaches to table inheritance and explains how to use them with Prisma ORM.

表继承的一个常见用例可能是当应用需要显示某种内容活动的提要时。在这种情况下，内容活动可以是视频或文章。作为一个例子，我们假设：

¥A common use case for table inheritance may be when an application needs to display a feed of some kind of content activities. A content activity in this case, could be a video or an article. As an example, let's assume that:

  • 内容活动始终有 id 和 url

    ¥a content activity always has an id and a url

  • 除了 id 和 url 之外，视频中还有 duration（建模为 Int）

    ¥in addition to id and a url, a video also has a duration (modeled as an Int)

  • 除了 id 和 url 之外，一篇文章还有一个 body（建模为 String）

    ¥in addition to id and a url, an article also a body (modeled as a String)

用例​

¥Use cases

联合类型​

¥Union types

联合类型是 TypeScript 中的一项便利功能，它允许开发者更灵活地使用数据模型中的类型。

¥Union types are a convenient feature in TypeScript that allows developers to work more flexibly with the types in their data model.

在 TypeScript 中，联合类型如下所示：

¥In TypeScript, union types look as follows:

type Activity = Video | Article

在 目前无法在 Prisma 模式中对联合类型进行建模 中，你可以通过使用表继承和一些附加类型定义将它们与 Prisma ORM 一起使用。

¥While it's currently not possible to model union types in the Prisma schema, you can use them with Prisma ORM by using table inheritance and some additional type definitions.

跨多个模型共享属性​

¥Sharing properties across multiple models

如果你有一个用例，其中多个模型应共享一组特定的属性，你也可以使用表继承对此进行建模。

¥If you have a use case where multiple models should share a particular set of properties, you can model this using table inheritance as well.

例如，如果上面的 Video 和 Article 模型都应具有共享的 title 属性，你也可以通过表继承来实现此目的。

¥For example, if both the Video and Article models from above should have a shared title property, you can achieve this with table inheritance as well.

示例​

¥Example

在简单的 Prisma 模式中，如下所示。请注意，我们还添加了 User 模型来说明它如何与关系一起使用：

¥In a simple Prisma schema, this would look as follows. Note that we're adding a User model as well to illustrate how this can work with relations:

schema.prisma
model Video {
  id       Int    @id
  url      String @unique
  duration Int

  user   User @relation(fields: [userId], references: [id])
  userId Int
}

model Article {
  id   Int    @id
  url  String @unique
  body String

  user   User @relation(fields: [userId], references: [id])
  userId Int
}

model User {
  id       Int       @id
  name     String
  videos   Video[]
  articles Article[]
}

让我们研究一下如何使用表继承对此进行建模。

¥Let's investigate how we can model this using table inheritance.

单表继承与多表继承​

¥Single-table vs multi-table inheritance

以下是表继承的两种主要方法的快速比较：

¥Here is a quick comparison of the two main approaches for table inheritance:

  • 单表继承（STI）：使用单个表将所有不同实体的数据存储在一个位置。在我们的示例中，将有一个包含 id、url 以及 duration 和 body 列的单个 Activity 表。它还使用 type 列来指示活动是视频还是文章。

    ¥Single-table inheritance (STI): Uses a single table to store data of all the different entities in one location. In our example, there'd be a single Activity table with the id, url as well as the duration and body column. It also uses a type column that indicates whether an activity is a video or an article.

  • 多表继承（MTI）：使用多个表分别存储不同实体的数据，并通过外键将它们链接起来。在我们的示例中，有一个包含 id、url 列的 Activity 表，一个包含 duration 和 Activity 外键的 Video 表，以及一个包含 body 和外键的 Article 表。还有一个 type 列充当区分器，指示活动是视频还是文章。请注意，多表继承有时也称为委托类型。

    ¥Multi-table inheritance (MTI): Uses multiple tables to store the data of the different entities separately and links them via foreign keys. In our example, there'd be an Activity table with the id, url column, a Video table with the duration and a foreign key to Activity as well as an Article table with the body and a foreign key. There is also a type column that acts as a discriminator and indicates whether an activity is a video or an article. Note that multi-table inheritance is also sometimes called delegated types.

你可以了解两种方法的权衡 below。

¥You can learn about the tradeoffs of both approaches below.

单表继承（STI）​

¥Single-table inheritance (STI)

数据模型​

¥Data model

使用 STI，上述场景可以建模如下：

¥Using STI, the above scenario can be modeled as follows:

model Activity {
  id       Int          @id // shared
  url      String       @unique // shared
  duration Int? // video-only
  body     String? // article-only
  type     ActivityType // discriminator

  owner   User @relation(fields: [ownerId], references: [id])
  ownerId Int
}

enum ActivityType {
  Video
  Article
}

model User {
  id         Int        @id @default(autoincrement())
  name       String?
  activities Activity[]
}

有几点需要注意：

¥A few things to note:

  • 特定于模型的属性 duration 和 body 必须标记为可选（即使用 ?）。这是因为 Activity 表中表示视频的记录不得具有 body 的值。相反，代表一篇文章的 Activity 记录永远不可能有 duration 集。

    ¥The model-specific properties duration and body must be marked as optional (i.e., with ?). That's because a record in the Activity table that represents a video must not have a value for body. Conversely, an Activity record representing an article can never have a duration set.

  • type 鉴别器列指示每个记录是代表视频还是文章项目。

    ¥The type discriminator column indicates whether each record represents a video or an article item.

Prisma 客户端 API​

¥Prisma Client API

由于 Prisma ORM 为数据模型生成类型和 API 的方式，你只能使用 Activity 类型和属于它的 CRUD 查询（create、update、delete...）。

¥Due to how Prisma ORM generates types and an API for the data model, there will only to be an Activity type and the CRUD queries that belong to it (create, update, delete, ...) available to you.

查询视频和文章​

¥Querying for videos and articles

你现在可以通过筛选 type 列来仅查询视频或文章。例如：

¥You can now query for only videos or articles by filtering on the type column. For example:

// Query all videos
const videos = await prisma.activity.findMany({
  where: { type: 'Video' },
})

// Query all articles
const articles = await prisma.activity.findMany({
  where: { type: 'Article' },
})

定义专用类型​

¥Defining dedicated types

当查询此类视频和文章时，TypeScript 仍然只能识别 Activity 类型。这可能很烦人，因为即使 videos 中的对象也会有（可选）body，而 articles 中的对象也会有（可选）duration 字段。

¥When querying for videos and articles like that, TypeScript will still only recognize an Activity type. That can be annoying because even the objects in videos will have (optional) body and the objects in articles will have (optional) duration fields.

如果你希望这些对象具有类型安全性，则需要为它们定义专用类型。例如，你可以通过使用生成的 Activity 类型和 TypeScript Omit 工具类型来从中删除属性来执行此操作：

¥If you want to have type safety for these objects, you need to define dedicated types for them. You can do this, for example, by using the generated Activity type and the TypeScript Omit utility type to remove properties from it:

import { Activity } from '@prisma/client'

type Video = Omit<Activity, 'body' | 'type'>
type Article = Omit<Activity, 'duration' | 'type'>

此外，创建将 Activity 类型的对象转换为 Video 和 Article 类型的映射函数会很有帮助：

¥In addition, it will be helpful to create mapping functions that convert an object of type Activity to the Video and Article types:

function activityToVideo(activity: Activity): Video {
  return {
    url: activity.url,
    duration: activity.duration ? activity.duration : -1,
    ownerId: activity.ownerId,
  } as Video
}

function activityToArticle(activity: Activity): Article {
  return {
    url: activity.url,
    body: activity.body ? activity.body : '',
    ownerId: activity.ownerId,
  } as Article
}

现在，你可以在查询后将 Activity 转换为更具体的类型（即 Article 或 Video）：

¥Now you can turn an Activity into a more specific type (i.e., Article or Video) after querying:

const videoActivities = await prisma.activity.findMany({
  where: { type: 'Video' },
})
const videos: Video[] = videoActivities.map(activityToVideo)

使用 Prisma 客户端扩展获得更方便的 API​

¥Using Prisma Client extension for a more convenient API

你可以使用 Prisma 客户端扩展 为数据库中的表结构创建更方便的 API。

¥You can use Prisma Client extensions to create a more convenient API for the table structures in your database.

多表继承（MTI）​

¥Multi-table inheritance (MTI)

数据模型​

¥Data model

使用 MTI，上述场景可以建模如下：

¥Using MTI, the above scenario can be modeled as follows:

model Activity {
  id   Int          @id @default(autoincrement())
  url  String // shared
  type ActivityType // discriminator

  video   Video? // model-specific 1-1 relation
  article Article? // model-specific 1-1 relation

  owner   User @relation(fields: [ownerId], references: [id])
  ownerId Int
}

model Video {
  id         Int      @id @default(autoincrement())
  duration   Int // video-only
  activityId Int      @unique
  activity   Activity @relation(fields: [activityId], references: [id])
}

model Article {
  id         Int      @id @default(autoincrement())
  body       String // article-only 
  activityId Int      @unique
  activity   Activity @relation(fields: [activityId], references: [id])
}

enum ActivityType {
  Video
  Article
}

model User {
  id         Int        @id @default(autoincrement())
  name       String?
  activities Activity[]
}

有几点需要注意：

¥A few things to note:

  • Activity 和 Video 以及 Activity 和 Article 之间需要 1-1 关系。此关系用于在需要时获取有关记录的特定信息。

    ¥A 1-1 relation is needed between Activity and Video as well as Activity and Article. This relationship is used to fetch the specific information about a record when needed.

  • 使用此方法可以要求特定于模型的属性 duration 和 body。

    ¥The model-specific properties duration and body can be made required with this approach.

  • type 鉴别器列指示每个记录是代表视频还是文章项目。

    ¥The type discriminator column indicates whether each record represents a video or an article item.

Prisma 客户端 API​

¥Prisma Client API

这次，你可以直接通过 PrismaClient 实例上的 video 和 article 属性查询视频和文章。

¥This time, you can query for videos and articles directly via the video and article properties on your PrismaClient instance.

查询视频和文章​

¥Querying for videos and articles

如果要访问共享属性，则需要使用 include 来获取与 Activity 的关系。

¥If you want to access the shared properties, you need to use include to fetch the relation to Activity.

// Query all videos
const videos = await prisma.video.findMany({
  include: { activity: true },
})

// Query all articles
const articles = await prisma.article.findMany({
  include: { activity: true },
})

根据你的需要，你还可以通过过滤 type 鉴别器列来进行相反的查询：

¥Depending on your needs, you may also query the other way around by filtering on the type discriminator column:

// Query all videos
const videoActivities = await prisma.activity.findMany({
  where: { type: 'Video' }
  include: { video: true }
})

定义专用类型​

¥Defining dedicated types

虽然与 STI 相比，在类型方面更方便一些，但生成的类型可能仍然无法满足你的所有需求。

¥While a bit more convenient in terms of types compared to STI, the generated typings likely still won't fit all your needs.

以下是如何通过将 Prisma ORM 生成的 Video 和 Article 类型与 Activity 类型相结合来定义 Video 和 Article 类型。这些组合创建了具有所需属性的新类型。请注意，我们还省略了 type 鉴别器列，因为特定类型不再需要它：

¥Here's how you can define Video and Article types by combining Prisma ORM's generated Video and Article types with the Activity type. These combinations create a new type with the desired properties. Note that we're also omitting the type discriminator column because that's not needed anymore on the specific types:

import {
  Video as VideoDB,
  Article as ArticleDB,
  Activity,
} from '@prisma/client'

type Video = Omit<VideoDB & Activity, 'type'>
type Article = Omit<ArticleDB & Activity, 'type'>

定义这些类型后，你可以定义映射函数，将从上述查询中收到的类型转换为所需的 Video 和 Article 类型。以下是 Video 类型的示例：

¥Once these types are defined, you can define mapping functions to convert the types you receive from the queries above into the desired Video and Article types. Here's the example for the Video type:

import { Prisma, Video as VideoDB, Activity } from '@prisma/client'

type Video = Omit<VideoDB & Activity, 'type'>

// Create `VideoWithActivity` typings for the objects returned above
const videoWithActivity = Prisma.validator<Prisma.VideoDefaultArgs>()({
  include: { activity: true },
})
type VideoWithActivity = Prisma.VideoGetPayload<typeof videoWithActivity>

// Map to `Video` type
function toVideo(a: VideoWithActivity): Video {
  return {
    id: a.id,
    url: a.activity.url,
    ownerId: a.activity.ownerId,
    duration: a.duration,
    activityId: a.activity.id,
  }
}

现在你可以获取上面查询返回的对象并使用 toVideo 转换它们：

¥Now you can take the objects returned by the queries above and transform them using toVideo:

const videoWithActivities = await prisma.video.findMany({
  include: { activity: true },
})
const videos: Video[] = videoWithActivities.map(toVideo)

使用 Prisma 客户端扩展获得更方便的 API​

¥Using Prisma Client extension for a more convenient API

你可以使用 Prisma 客户端扩展 为数据库中的表结构创建更方便的 API。

¥You can use Prisma Client extensions to create a more convenient API for the table structures in your database.

STI 和 MTI 之间的权衡​

¥Tradeoffs between STI and MTI

  • 数据模型：使用 MTI 可能会让数据模型感觉更干净。使用 STI，你最终可能会得到非常宽的行和大量包含 NULL 值的列。

    ¥Data model: The data model may feel more clean with MTI. With STI, you may end up with very wide rows and lots of columns that have NULL values in them.

  • 表现：MTI 可能会带来性能成本，因为你需要连接父表和子表才能访问与模型相关的所有属性。

    ¥Performance: MTI may come with a performance cost because you need to join the parent and child tables to access all properties relevant for a model.

  • 打字：借助 Prisma ORM，MTI 已经为你提供了特定模型（即上面示例中的 Article 和 Video）的正确类型，而你需要使用 STI 从头开始创建这些模型。

    ¥Typings: With Prisma ORM, MTI gives you proper typings for the specific models (i.e., Article and Video in the examples above) already, while you need to create these from scratch with STI.

  • ID/主键：对于 MTI，记录有两个可能不匹配的 ID（一个在父表上，另一个在子表上）。你需要在应用的业务逻辑中考虑这一点。

    ¥IDs / Primary keys: With MTI, records have two IDs (one on the parent and another on the child table) that may not match. You need to consider this in the business logic of your application.

第三方解决方案​

¥Third-party solutions

虽然 Prisma ORM 目前本身不支持联合类型或多态性，但你可以查看 禅斯塔克，它为 Prisma 模式添加了额外的功能层。阅读他们的 有关 Prisma ORM 中多态性的博客文章 以了解更多信息。

¥While Prisma ORM doesn't natively support union types or polymorphism at the moment, you can check out Zenstack which is adding an extra layer of features to the Prisma schema. Read their blog post about polymorphism in Prisma ORM to learn more.