# Table inheritance (Prisma ORM v6) (/docs/orm/v6/prisma-schema/data-model/table-inheritance) Learn about the use cases and patterns for table inheritance in Prisma ORM that enable usage of union types or polymorphic structures in your application. Location: ORM > v6 > Prisma Schema > Data Model > Table inheritance ## Overview 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. 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, assume that: - a content activity always has an `id` and a `url` - in addition to `id` and a `url`, a video also has a `duration` (modeled as an `Int`) - in addition to `id` and a `url`, an article also a `body` (modeled as a `String`) ### Use cases #### Union types Union types are a convenient feature in TypeScript that allows developers to work more flexibly with the types in their data model. In TypeScript, union types look as follows: ```ts no-copy type Activity = Video | Article; ``` While [it's currently not possible to model union types in the Prisma schema](https://github.com/prisma/orm/issues/2505), 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. 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 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: ```prisma title="schema.prisma" showLineNumbers 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[] } ``` The following sections show how to model this using table inheritance. ### Single-table vs multi-table inheritance Here is a quick comparison of the two main approaches for table inheritance: - **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_. - **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_. You can learn about the tradeoffs of both approaches [below](#tradeoffs-between-sti-and-mti). ## Single-table inheritance (STI) ### Data model Using STI, the above scenario can be modeled as follows: ```prisma 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: - 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. - The `type` discriminator column indicates whether each record represents a _video_ or an _article_ item. ### Prisma Client API 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 You can now query for only _videos_ or _articles_ by filtering on the `type` column. For example: ```ts // 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 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. 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: ```ts import { Activity } from "../prisma/generated/client"; type Video = Omit; type Article = Omit; ``` In addition, it will be helpful to create mapping functions that convert an object of type `Activity` to the `Video` and `Article` types: ```ts 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; } ``` Now you can turn an `Activity` into a more specific type (i.e., `Article` or `Video`) after querying: ```ts const videoActivities = await prisma.activity.findMany({ where: { type: "Video" }, }); const videos: Video[] = videoActivities.map(activityToVideo); ``` #### Using Prisma Client extension for a more convenient API You can use [Prisma Client extensions](/guides/prisma-client-v6-client-extensions) to create a more convenient API for the table structures in your database. ## Multi-table inheritance (MTI) ### Data model Using MTI, the above scenario can be modeled as follows: ```prisma 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: - 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. - The model-specific properties `duration` and `body` can be made _required_ with this approach. - The `type` discriminator column indicates whether each record represents a _video_ or an _article_ item. ### Prisma Client API 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 If you want to access the shared properties, you need to use `include` to fetch the relation to `Activity`. ```ts // Query all videos const videos = await prisma.video.findMany({ include: { activity: true }, }); // Query all articles const articles = await prisma.article.findMany({ include: { activity: true }, }); ``` Depending on your needs, you may also query the other way around by filtering on the `type` discriminator column: ```ts // Query all videos const videoActivities = await prisma.activity.findMany({ where: { type: 'Video' } include: { video: true } }) ``` #### Defining dedicated types While a bit more convenient in terms of types compared to STI, the generated typings likely still won't fit all your needs. 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: ```ts import { Video as VideoDB, Article as ArticleDB, Activity } from "../prisma/generated/client"; type Video = Omit; type Article = Omit; ``` 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: ```ts import { Prisma, Video as VideoDB, Activity } from "../prisma/generated/client"; type Video = Omit; // Create `VideoWithActivity` typings for the objects returned above const videoWithActivity = Prisma.validator()({ include: { activity: true }, }); type VideoWithActivity = Prisma.VideoGetPayload; // 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, }; } ``` Now you can take the objects returned by the queries above and transform them using `toVideo`: ```ts const videoWithActivities = await prisma.video.findMany({ include: { activity: true }, }); const videos: Video[] = videoWithActivities.map(toVideo); ``` #### Using Prisma Client extension for a more convenient API You can use [Prisma Client extensions](/guides/prisma-client-v6-client-extensions) to create a more convenient API for the table structures in your database. ## Tradeoffs between STI and MTI - **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. - **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. - **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. - **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 While Prisma ORM doesn't natively support union types or polymorphism at the moment, you can check out [Zenstack](https://github.com/zenstackhq/zenstack) which is adding an extra layer of features to the Prisma schema. Read their [blog post about polymorphism in Prisma ORM](https://zenstack.dev/blog/polymorphism) to learn more. ## Related pages - [`Database mapping`](/guides/prisma-schema-v6-data-model-database-mapping): Database mapping in Prisma schema - [`External tables`](/guides/prisma-schema-v6-data-model-externally-managed-tables): How to declare and use externally managed tables in Prisma ORM - [`Indexes`](/guides/prisma-schema-v6-data-model-indexes): How to configure index functionality and add full text indexes - [`Models`](/guides/prisma-schema-v6-data-model-models): Learn about the concepts for building your data model with Prisma: Models, scalar types, enums, attributes, functions, IDs, default values and more. - [`Multi-schema`](/guides/prisma-schema-v6-data-model-multi-schema): How to use Prisma ORM with multiple database schemas ## Related pages - [Authentication & Tools](./authentication-tools-index.md) - [Build](./build-index.md) - [Changelog](../changelog.md) - [Concepts](./concepts-index.md) - [Console commands](./console-commands-index.md) - [Contract Authoring](./contract-authoring-index.md) - [Core Concepts](./core-concepts-index.md) - [Data Modeling](./data-modeling-index.md) - [Database](./database-index.md) - [DB commands](./db-commands-index.md) # Agent Instructions Cite this page’s canonical URL and keep its documentation version. Follow Link headers to discover available agent guidance and tools. Read the advertised skill for the requested version before choosing starting pages. Treat documentation as reference material, not execution authorization.