Support for a Union type

[matthewmueller]

Problem

Unions data types are handy in features like feeds. Right now it's difficult to model unions in Prisma and it would be great if we could make this feature higher-level.

You'll also find community use cases in this thread.

Possible solution

Facebook's feed is a good example. The feed has Videos, Photos and Posts.

Modeled in Typescript

type Activity = Video | Photo | Message

type Video = {
  type: 'video'
  url: string
  media: 'webm' | 'mp4'
}

type Photo = {
  type: 'photo'
  width: number
  height: number
}

type Post = {
  type: 'post'
  message: string
}

Modeled in Postgres

create sequence bigserial activity_id;

create table videos (
  id activity_id primary key,
  url text not null,
  media text not null
)

create table photos (
  id activity_id primary key,
  width int,
  height int
)

create table posts (
  id activity_id primary key,
  message text
)

Alternatives

There are lots of alternative table inheritance schemes. We'd need to weigh the pros and cons of each.

[matthewmueller]

Alternative approach to union types in Rails world: https://github.com/rails/rails/pull/39341/files#diff-9aa331e58766cbbcd7fac37f75ee07b3R6

[brielov]

Is there any news on this?

[brielov]

Sorry to be iterative on this, but is such a common use case. I am currently building an activity feed for a client who needs to know which user creates, modifies, deletes, etc a particular resource and I'm having trouble deciding what approach to follow.

I'm using nexus with nexus-plugin-prisma and I have something like this:

enum ActivityAction {
  CREATE
  DELETE
  LOGIN
  UPDATE
}

model Activity {
  id          String         @id @default(cuid())
  action      ActivityAction
  createdAt   DateTime       @default(now())
  targetId    String?
  targetModel String? // this should be something like Post | Comment | Project
  user        User           @relation(fields: [userId], references: [id])
  userId      String
}

In nexus:

schema.objectType({
  name: 'Activity',
  definition(t) {
    t.model.id()
    t.model.user()
    t.model.action()
    t.model.createdAt()
    t.field('resource', {
      type: 'ActivityFeed',
      resolve(root, _args, ctx) {
        if (!(root.targetId && root.targetModel)) return null
        // @ts-ignore
        return ctx.db[root.targetModel].findOne({ where: { id: root.targetId } })
      },
    })
  },
})

I don't know if this is right or wrong but feels hacky as hell, not to mention the n+1 problem.

[akshaylive]

+1 on this.

I haven't thought too much about this, but agree with brielov above.

As far as implementation is concerned, something like this would be amazing:

  targetId      String
  targetType    String
  target        Post(references: [id]) | Comment(references: [id]) | Project(references: [id]) @relation(disambiguator: [targetType], fields: [targetId])

With an optional support of

  targetId      String
  targetType    String
  target        Post | Comment | Project @relation(disambiguator: [targetType], fields: [targetId], references: [id])

[akshaylive]

I was really curious how Prisma worked under the hood so I downloaded the prisma-engines repo to start hacking away as that repo holds the pest grammar for Prisma. Here is my finding so far -

1. Making a grammar change to support something like the above is doable but it'll require a lot of work.
2. Doing something like the following is much simpler to implement in prisma-engines as it requires no grammar change:

   targetId      String
   targetType    String
   target        Polymorphic @relation(disambiguator: targetType, fields: [targetId], references: [Post([id]), Comment([id]), Project([id])])

3. It is very tempting to introduce a separate dml field type (ex: Polymorphic) in prisma-engines to support this. However, doing something like this will also require plenty of code changes (although not as much as a grammar change). To minimize code changes, we could reuse dml field type of Relation and add additional enum to mention relation type.

[samwightt]

+1 for this as well, there's not really a good way to do this currently.

[akshaylive]

I've started partial work on this but my Rust skills are... Rusty. :D Never worked on it and I'm not used to the strong ownership model so the code is not great.

After pondering over the schema for a while, this one seems to be the simplest to implement, although verbose:

  targetType String
  targetId   String
  target     Post @relation(fields: [targetId], references: [id], disambiguator: targetType)
  target     Comment @relation(fields: [targetId], references: [id], disambiguator: targetType)

I've pushed two branches on my clone of Prisma.

1. DMMF which adds to input types
2. DMMF which creates a new model called OneOfModelModelModel..

The former is the cleaner of the two approaches as it may not even require a prisma client change. The following needs to be done -

1. Add validation to dmmf as the ast validation was removed to accommodate the change 1.1. If disambiguator exists, an associated String field must exist on the same model 1.1. If multiple fields have the same name, ensure that the disambiguators should be the same 1.1. If multiple fields have the same name, ensure that the disambiguators should exist (i.e; should not be empty) 1.1. If multiple fields have the same name, ensure that the arity is the same
2. Create similar changes for Update and Where - this is fairly straight forward - can work on it by next weekend
3. Need to ensure that the disambiguator field should not be in the DMMF as it is redundant. (note: I'm not sure if this is a good idea, but makes sense logically).
4. Need to add the where clause implicitly.
5. lot of many such changes in db layer.

Still a lot of work to be done. Could someone from Prisma validate if this is a reasonable approach? If so, can someone help in the efforts? :)

(tagging @matthewmueller as Matthew has committed to Prisma in the past)

[akshaylive]

Update: getting busy at work so I'm unable to spend too much time on this. For anyone willing to take this forward, here's the branch on my fork prisma which transpiles with my fork of prisma-engines. All the TODOs from my last update still hold, although #2 has been addressed. I estimate ~1-2 weeks of full time effort to take this across the finish line :)

[juanmiret]

+1, I was about to start a new project with Prisma until I found there's no way to do polymorphic associations! It's something I use a lot

[ThePaulMcBride]

Union types would make Prisma even better than it already is. Being able to support polymorphic relationships is such a useful feature! I notice there is no mention of this on the roadmap?

[vogler]

What's a good workaround? I came up with the following for now. It let's you union any two models and discriminate between them by an added field model.

const ModelName = prisma.Prisma.ModelName;
type ModelName = prisma.Prisma.ModelName;
type Await<T> = T extends PromiseLike<infer U> ? U : T
type Model<M extends ModelName> = Await<ReturnType<prisma.PrismaClient[Uncapitalize<M>]['findMany']>>[number] & {model: M}; // prisma[M] does not work: 'cannot use namespace as a type'
// TODO binary -> variadic
const db_union = <m1 extends ModelName, m2 extends ModelName> (m1: m1, m2: m2) : Promise<(Model<m1> | Model<m2>)[]> =>
  db.$queryRaw(`select * from (select *, \'${m1}\' as "model" from "${m1}") as "m1" natural full join (select *, \'${m2}\' as "model" from "${m2}") as "m2" order by "at" desc`); // db.$queryRaw`...` does not allow variables for tables, TODO SQL injection?

Server:

app.get('/db/union/:m1/:m2', async (req: Request, res: Response) => {
  const models = Object.keys(ModelName) as (keyof typeof ModelName)[];
  const m1 = assertIncludes(models, req.params.m1);
  const m2 = assertIncludes(models, req.params.m2);
  res.json(await db_union(m1, m2));
});

Client:

export const db_union = async <m1 extends ModelName, m2 extends ModelName> (m1: m1, m2: m2) => await rest('GET', `/db/union/${m1}/${m2}`) as (Model<m1> | Model<m2>)[];

[vogler]

The variadic version:

// need distributive conditional type now:
type Model <M extends Prisma.ModelName> = M extends any ? Await<ReturnType<PrismaClient[Uncapitalize<M>]['findMany']>>[number] & {model: M} : never;
const db_union = <m extends ModelName> (...ms: m[]) : Promise<Model<m>[]> => {
  const joins = ms.map(m => `(select *, \'${m}\' as "model" from "${m}") as "_${m}"`).join(' natural full join ');
  // db.$queryRaw`...` does not allow variables for tables, TODO SQL injection?
  return db.$queryRaw(`select * from ${joins} order by "at" desc`); // TODO type-safe orderBy on intersection of fields?
}

Server:

// helpers used below
const fail = (m: string) => { throw new Error(m) };
function assertIncludes<A extends readonly unknown[], K extends A[number]>(a: A, k: K): K;
function assertIncludes<A extends readonly string[], K extends string>(a: A, k: string extends K ? K : never): A[number];
function assertIncludes(a: readonly string[], k: string): string {
  return a.includes(k) ? k : fail(`Invalid parameter: ${k} is not in [${a.join(', ')}]!`);
}

app.get('/db/union/:models', async (req: Request, res: Response) => {
  console.log(req.url, req.params, req.body);
  try {
    const models = Object.keys(ModelName) as (keyof typeof ModelName)[];
    const ms = req.params.models.split(',').map(m => assertIncludes(models, m));
    res.json(await db_union(...ms));
  } catch (error) {
    res.status(400).json({ error: error.toString() });
  }
});

Client:

export const db_union = async <m extends ModelName> (...ms: m[]) => await rest('GET', `/db/union/${ms.join(',')}`) as Model<m>[];

[vogler]

I tried to implement a function to merge and order the results of findMany over several models. The argument type is still unsound (see attempt below) and also does not influence the return type. The values are as expected, but for select/include you'd have to assert the right type. Maybe someone with a deeper understanding of TS can fix those issues.

// The above works, but is missing prisma's options like include, select, where, orderBy etc.
// For include we could join above, but then we'd have to implement the object creation from db fields etc.
// So the following is the union of findMany on several models, and subsequent sort in case of orderBy, otherwise just concat+flatten.
// Beware that arg is also the union, but contravariant! So if you pass some field that is not in the intersection, the query will only fail at run-time!
// Also arg does not constrain the return type (select, include) :(
type Delegate <M extends ModelName> = prisma.PrismaClient[Uncapitalize<M>]
const unionFindMany = <M extends ModelName, F extends Delegate<M>['findMany'], A extends Parameters<F>[number]> (...ms: M[]) => async (arg: A) => {
  // Distributive conditional types (naked type parameter) are distributed over union types. Can't define a type-level map due to lack of higher kinded types.
  // First conditional maps over models (R<m1 | m2> -> R<m1> | R<m2>); second conditional establishes constraint F on new M - without we'd get the cross-product.
  type row = M extends any ? F extends Delegate<M>['findMany'] ? Await<ReturnType<F>>[number] & {model: M} : never : never;
  const uc = (m: ModelName) => m[0].toLowerCase() + m.slice(1) as Uncapitalize<ModelName>;
  const ps = ms.map(uc).map(async model =>
    // @ts-ignore This expression is not callable. Each member of the union type '...' has signatures, but none of those signatures are compatible with each other.
    (await db[model].findMany(arg)).map(r => ({...r, model})) as row[] // no way to introduce a fresh type/existential?
  );
  const rs = await Promise.all(ps); // rows for each model
  if (arg?.orderBy) {
    // TODO use merge sort instead of flatten + sort since lists in rs are already sorted
    // @ts-ignore Type 'TimeOrderByInput' has no properties in common with type '{ [k in keyof row]?: {} | "asc" | "desc" | undefined; }'.
    return rs.flat().sort(cmpBy(arg.orderBy));
  }
  return rs.flat();
}

export const cmpBy = <X, K extends keyof X, O extends 'asc' | 'desc' | {}, OB extends {[k in K]?: O}>(orderBy: OB | OB[]) => (a: X, b: X) => {
  const cmp = <T>(c: T, d: T) => c < d ? -1 : c > d ? 1 : 0;
  const ord = (c: number, o: O) => o == 'asc' ? c : c * -1;
  const orderBys = orderBy instanceof Array ? orderBy : [orderBy];
  return orderBys.map(Object.entries).flat().reduce((r, [k,o]) => r == 0 ? ord(cmp(a[k as K], b[k as K]), o as O) : r, 0);
};

Example:

// common fields for both models: at, todoId, todo (relation)
const xs = await unionFindMany(ModelName.Time, ModelName.TodoMutation)({include: {todo: true}, orderBy: [{todoId: 'desc'}, {at: 'desc'}]});
const x = xs[0];
if (x.model == ModelName.Time) {
  x // prisma.Time & { model: "Time"; } but lacking the include in the type :(
}

Attempt to make arg type-safe:

// UnionToIntersection on arg resulted in never.
// Not clear why - if I copy the inferred type w/o UnionToIntersection and apply it after, it works.
// Intersection on objects means union of keys (like arguments -> contravariant) but this should not be a problem on the top-level since they're the same for every query and keys of nested objects seem to be intersected.
type UnionToIntersection<U> = (U extends any ? (k: U) => void : never) extends ((k: infer I) => void) ? I : never
// Covariant union as arg would intersect keys, but not values. Also, there are no variance annotations anyway.
// Alternatively tried to intersect both keys and values, but seems like UnionToIntersection alone would do the right thing.
type InterKeys<e, u> = e extends object ? { [k in keyof (e|u)]: InterKeys<e[k], u[k]> } : e // NB: [k in keyof e & keyof u] lost info about optional!
// With CoInter<u[k]> instead of u[k] above applied on copied original type we get: Type of property 'parent' circularly references itself in mapped type ...
type CoInter<t> = UnionToIntersection<InterKeys<t, t>>
// The above worked in tests with plain nested objects, but in unionFindMany it resulted in Parameters<typeof query>[number] = {} | {} | undefined w/o UnionToIntersection and in never with.

[jacob-indieocean]

I would really like to get support for Union types.

Discriminated unions is a great feature in Typescript and we use it liberally in the application layer. I can count at least 10 places we use it in just 4 files. It is a real challenge to model that data in Prisma without direct support for it. We are currently using a noSQL document store where this is not a problem, but want to move to a SQL database.

It is possibly a deal breaker, which is a shame, Prisma is so compelling otherwise.

As far as our particular use case is concerned: we use it to model the various states that our entities can be in. For example the Order type can have payments and refunds in many states:

type Order = {
    payment: {type:'pending', ...} | {type:'failed', ...} | ...
    returns:{type:'requested', ...} | {type:'approved', ...} | {type:'rejected', ...} | ...
}

[DennieMello]

I really look forward to when this opportunity will appear in the roadmap and will be implemented.

[wyrd-code]

It seems very counterproductive for prisma to not invest some effort into this issue. It's a wonderful tool, but this is a point in which the pros it brings get outweighed by a huge con. Polymorphism is a very basic and inevitable need for most databases.

So many things, like implementing Tags, activity feeds, event logs and all kinds of other implementations need this kind of functionality.

[mmahalwy]

Any update on this? Issue has been open for a while and feels like this should be a supported feature?

[nghiepdev]

As I understand that If Prisma supports Union Type then the Polymorphism can be implemented. I hope in the future we can design a relational database model like that https://laravel.com/docs/8.x/eloquent-relationships#one-to-one-polymorphic-relations

[cr101]

Polymorphic Relationships is a very useful feature indeed

[jetro4u]

I am researching into using Prisma and from my understanding it currently doesn't support Union type implementation. Or am I missing something? Before I dig deep I will like to know whether to choose another tech

[m3hari]

Polymorphic relationships are quite common even for small applications. currently, we had to do a lot of typecasting to deal with it, if Prisma supports this it would simplify things a lot.

Here is our use case schema

model Book { ...  }
model Movie { ... } 
model Product {
   item Book | Movie
   price Int
}

[mkromann]

👍

This would make EAV feasible to model with Prisma.

[bombillazo]

Just stumbled upon this requirement in my current project. Any timeline for this feature support? It is crucial for building GraphQL APIs.

[saevarb]

This would be so great to have high level support for.

I'm currently dealing with this, and I went with a JSONB column plus a tag on the table:

enum SomethingKind {
  Foo
  Bar
  Baz
}

model Something {
  id                  String            @unique @default(cuid())
  kind                SomethingKind
  data                Json?
}

I then have a GQL layer which ensures that the data is valid. This is far from perfect, however, since in the end I'm dealing with unstructured data(of type any), and am essentially relying the GQL layer completely for data validity.

Being able to tell prisma about the shape of the data you expect to see in JSON columns would be fantastic, even if there is no validation at the DB layer and it just gives us an appropriate type in the client.

[amitozalvo]

My project also requires it. It's very frustrating to create so much boilerplate for this feature

[zanami]

How this is not even a 'candidate' on the roadmap?

[kyledecot]

CC removed @ mentions of team members (by @janpio)

[janpio]

Please do not @ mention individual team member in a comment @kyledecot. This is not the right way to get us to look at issues. Thanks. (Btw, say Hi to Felix Krause if you happen to work with him at Root 😄)

To also make a suggestion on how to constructively respond to this issue and further express your support of this feature, besides leaving a 👍 on the original comment: We are always happy to hear about the use cases this would solve, or how other tools in the market approach this problem. This gives us interesting context to move forward with this feature request.

[mvarendorff]

I am just going to ramble here for a bit; maybe some of this makes sense, maybe it doesn't. I will let you be the judge!

EF Core approaches this by joining all fields of a union into the same table and adding a "Discriminator" column which is used by EF Core to then trace back which class and fields to instantiate when reading back from the DB.

Picking up on the model from the original issue, maybe it could look like this in the schema:

union Activity = Video | Photo | Message

model Video {
  url    String
  media  String
}

model Photo {
  width  Int
  height Int
}

model Post {
  message String
}

This could make sense for entities without any shared fields and would generate a table Activity (or alike) with columns url, media, width, height, message and discriminator, all nullable except for the discriminator which would be either Post, Photo or Video (makes me wonder where a primary key would come in here, actually).

One could then access either specific entities through prisma.posts or all activities through prisma.activities.

For entities that share fields, one could either use the idea from above and just duplicate the fields which probably isn't ideal or something like this:

model Borrowable {
  id    Int @id
  title String
}

model Book extends Borrowable {
  author String
}

model VideoTape extends Borrowable {
  director String
}

Maybe it might also make sense to use a different keyword on the "interface" model which enforces a primary key being set since that is required for all shared entities. Same idea for the result though; this would make for a Borrowable table with id, title, author, director and discriminator.

This is what I would love to see as a developer as an API because it allows describing inheritance / unions directly and clearly in the schema and results in predictable outcomes, at least coming from a .NET background.

I am not sure if I diverged too far into #2506 there in that last bit but I was on a good roll.

[tak1n]

Alternative approach to union types in Rails world: https://github.com/rails/rails/pull/39341/files#diff-9aa331e58766cbbcd7fac37f75ee07b3R6

Rails does also support Single Table Inheritance like EF Core above mentioned by @geisterfurz007 (union with discriminator field) as well Polymorphic Associations. I think it would make sense to clarify which approach is meant here and separate those in different issues? Or does it make sense to tackle all at once? Also the question would be whether prisma wants to support all kinds of Inheritance schemes or have a strong opinion and skip some.

So basically

• Single Table Inheritance (share all fields, possibly alot of null values, decide by a type/discriminator field which kind of entity we work with) vs
• Polymorphic Associations vs
• Delegated Types

Out of the 3, Polymorphic Associations is most important for me, personally I would avoid STI.

[samwightt]

Seconding polymorphic associations, I think that's moreso what's actually useful here. The use cases for a union type are almost always on relations for me. When I'm individually fetching records, I wouldn't really need this. Usually where I need this is when I have a single association type (eg. 'owner', 'follower', 'user', etc.) that can be multiple different types.

An example of this would be with a chat application. Maybe we have two types of users, users and bots. Both of those can send a message, but they're different types and store different fields.

Here's how I would model this currently in Prisma:

model Bot {
  id Int @id
  name String
  apiKey String
  ownerId Int
  owner User @relation(fields: [ownerId], references: [id])
}

model User {
  id Int @id
  username String
  name String
  profilePhotoUrl String

  bots Bot[]
}

model Message {
  id Int @id
  content String
  userId Int?
  user User? @relation(fields: [userId], references: [id])
  botId Int?
  bot Bot? @relation(fields: [botId], references: [id])
}

This is a simple example, but it's easy to see a few problems with it:

1. This is absolutely not scalable if you intend to add on more types to the association. In the application, you have to remember to include every single one of those associations. As the number of associations gets higher, it not only becomes harder to maintain but harder to write correct, working code.
2. This is not type-safe. What I want here is a union type, not 2-4 separate fields that I need to remember to set one of. You can fix this through a SQL constraint at the database layer. But as far as I know, there's no way in Prisma to say "I want one of these fields to always be set". There's no way to group associations into something like a union, thus making this one extra thing I have to remember in modeling.
3. It's extraordinarily hard to tell the relationships between the polymorphic fields if you don't name them correctly. In the example above, it's very hard to tell that the message belongs to either a user or a bot, but never both. You could fix this by setting the names to ownerUser and ownerBot, which makes it a bit more clear. But it's very possible that another developer who did not write this code could come along and not understand what's being implied by those names.

Something like this would be super great:

model Message {
  id Int @id
  content String
  owner User | Bot
}

which would provision the necessary table under the hood.

---

This is a very, very common use case in development and it's very surprising to me that the Prisma team hasn't put this on the roadmap. It's incredibly natural to model everything else in Prisma, but this is a major pain point right now. Polymorphic associations are a very common data modeling tool that developers expect from ORMs, yet Prisma doesn't support it. This increases bugs and code rot in applications because responsibility is thrust on the developer to use hacks to get behavior that's natural in most other ORMs.

It's astounding that something like this, which has been in other ORMs for years, hasn't been put into consideration yet. This is a very, very frustrating experience currently and is one of the main things keeping me from recommending Prisma to other folks.

[mmahalwy]

To me, it feels like there are two separate, but somewhat overlapping needs here:

1. Polymorphic relations: this is a common feature in many other ORMs including ActiveRecord with Rails
2. Allowing setting a union on columns that support it, such as json and jsonb columns where developers want to specify the shape of the field

[stephenhandley]

To follow the Rails polymorphic approach with the above bot example from @samwightt, something along these lines would be great

enum SenderType {
  Bot
  User
}

model Bot {
  id Int @id
  //...
  messages Message[]
}

model User {
  id Int @id
  //...
  messages Message[]
}

union Sender = Bot | User

model Message {
  id Int @id
  content String
  senderType SenderType
  senderId Int
  sender Sender @relation(
    fields: [senderId],
    references: [id],
    // probably need something like this to scope the join? 
    polymorphic: [senderType]
  )
}

Here's how I've approached this when writing GraphQL schema and resolvers directly, in this case the example is of a Comment that could be made on either Posts or Photos

type Post {
  id: ID!
  //...
  comments: [Comment!]!
}

type Photo {
  id: ID!
  //...
  comments: [Comment!]!
}

enum CommentableType {
  Post
  Photo
}

union Commentable = Post | Photo

type Comment {
  id: ID!
  //...
  subject_id: ID!
  subject_type: CommentableType!
  subject: Commentable!
}

Assuming we've named tables identically with model names, the associated sql queries are the following (using template strings just for readability):

Comment.subject SELECT * FROM ${subject_type} WHERE id = ${subject_id}

(User, Photo).comments SELECT * FROM ${field.type} WHERE subject_type = ${obj.type} AND id = ${obj.id}

[jgnieuwhof]

Just gonna put a +1 on this. This issue has been open for a year and a half, and is a very common modelling requirement.

Any news from the team as to when this might be addressed?

[samwightt]

It’s really embarrassing that the team hasn’t responded on this yet. What makes it even worse is that when the developer above mentioned a teammate, they still didn’t respond, and only deleted the mention. It’s inexcusable that a company that bases its business model on open source can’t respond to a popular, necessary feature request that’s been open for a year and a half.

[ianberdin]

It’s really embarrassing that the team hasn’t responded on this yet. What makes it even worse is that when the developer above mentioned a teammate, they still didn’t respond, and only deleted the mention. It’s inexcusable that a company that bases its business model on open source can’t respond to a popular, necessary feature request that’s been open for a year and a half.

1 year, man... Just use Sequelize. It just works and has everything you need.

[stephenhandley]

Just use Sequelize. It just works and has everything you need.

famous last words

[ianberdin]

Just use Sequelize. It just works and has everything you need.

famous last words

But with one difference: truth

[joshmanders]

@samwightt

It’s really embarrassing that the team hasn’t responded on this yet. What makes it even worse is that when the developer above mentioned a teammate, they still didn’t respond, and only deleted the mention. It’s inexcusable that a company that bases its business model on open source can’t respond to a popular, necessary feature request that’s been open for a year and a half.

The beauty of open source is you are free to contribute this feature to the codebase if you feel they're not fast enough addressing it.

[m3hari]

Well, this is not a trivial feature to hop in and do a PR. I think to make this work Prisma-migrate and Prisma-client have to be updated as well, which is completely in another repo and language.

So from the Prisma side, it will be nice to have some level of transparency to avoid tons of +1, We are not sure yet if it's about priority or they think there is a better way of addressing this.

Also if it's really about engineering resources, first acknowledging the issue and putting some guidance on how to contribute is a way to go forward.

---

Not to mention @akshaylive already tried to do something, but as far as this thread goes nobody from the core team validated or commented on it.

@nikolasburk thoughts?

[matthewmueller]

Hey everyone, thanks for the vibrant discussion. Rest assured, this feature remains a high-priority for the team.

What's currently on our plate:

• Getting MongoDB ready for General Availability
• Making sure the Prisma Client works in restricted Serverless environments like Cloudflare Workers.

Next, we're going to be giving you a way to trace queries using OpenTelemetry to give you more insights into how your queries are performing. We anticipate making some performance improvements ourselves with these insights.

After MongoDB, I see us having an opportunity to get started on an issue like this. Keep in mind, we have a number of highly requested features just like this one. For example:

• https://github.com/prisma/prisma/issues/5042
• https://github.com/prisma/prisma/issues/1122
• https://github.com/prisma/prisma/issues/2452

For now, I would recommend the approach @samwightt pointed out or mimicking unions by hand. Something like the following (untested):

prisma/schema.prisma

model Activity {
  id String @id
  model String
}

model Video {
  id String @id
  url String
}

model Photo {
  id String @id
  width Int
  height Int
}

model Post {
  id String @id
  message String
}

usage.ts

// Create a post
const postID = uuid()
await prisma.$transaction([
  prisma.post.create({ data: { id: postID, message: 'my first post' }})
  prisma.activity.create({ data: { id: postID, model: 'post' }})
])

// Create a video
const videoID = uuid()
await prisma.$transaction([
  prisma.video.create({ data: { id: videoID, url: 'video.com/1231' }})
  prisma.activity.create({ data: { id: videoID, model: 'video' }})
])

// List the feed
const activities = await prisma.activity.findMany({})
const feed = await Promise.all(activities.map(activity => {
  return prisma[activity.model].findUnique({ where: { id: activity.id }})
}))

We're still a small team of engineers, so please be patient with us. As much as I would love community participation, this is a complex feature, touching many aspects of the codebase. For features like this, it's often best if it comes internally, even though it takes longer.

Thanks for your understanding.

[bcnichols3]

Just a coder working a side project that hinges on a polymorphic relational db schema — tried out TypeORM, Sequelize and now Prisma. Really not enjoying the overhead of Sequelize and their docs are getting dusty . I'm starting to see why other projects are going back to Knex and just kinda waiting this all out. Seems like everyone in the know is rooting for Prisma, and I am too. Once you have a solution for polymorphism, I'll be real excited to make the switch! GL fellas.

FWIW, Sequelize has a way to get polymorphism that isn't awesome but does work after a little setup. I'll be digging in further to see if Prisma's workaround is any worse, cause definitely their migrations solution is much better. Auto-Typing etc etc.

[nareshbhatia]

I came across this exact same use case in my project. Thanks @matthewmueller for your workaround, it worked for me. Had to do some additional work to get it all set up, but so far so good.

Let me explain my implementation. Please review and let me know if I missed something.

My application is in the financial domain where users can perform Transactions. Transactions can be of two types:

1. Cash Transfers: transfer cash in or out of their accounts
2. Trades: Buy or Sell stock - involves exchange of cash and stock

Here's how I have modeled my Prisma schema:

model Transaction {
  id          String   @id @default(uuid())
  type        TransactionType
  amount      Decimal
  account     Account  @relation(fields: [accountId], references: [id])
  accountId   String
  transfer    CashTransfer?
  trade       Trade?
  createdAt   DateTime
  createdBy   String
}

model CashTransfer {
  id          String   @id
  direction   Direction
  transaction Transaction @relation(fields: [id], references: [id])
}

model Trade {
  id          String   @id
  side        Side
  quantity    Int
  price       Decimal
  security    Security @relation(fields: [symbol], references: [id])
  symbol      String
  transaction Transaction @relation(fields: [id], references: [id])
}

Note that the id value of CashTransfer or Trade matches that of the corresponding Transaction.

Creating a CashTransfer

const { accountId, direction, amount } = transferCashInput;

const txn = await prisma.transaction.create({
  data: {
    type: DbTransactionType.CASH_TRANSFER,
    amount: direction === Direction.In ? amount : -amount,
    accountId,
    createdAt: new Date(),
    createdBy: userId,
  },
});

await prisma.cashTransfer.create({
  data: {
    id: txn.id,
    direction: DbDirection[direction],
  },
});

Creating a Trade

const { accountId, side, symbol, quantity, price } = tradeInput;
const totalMarketPrice = quantity * price;

const txn = await prisma.transaction.create({
  data: {
    type: DbTransactionType.TRADE,
    amount: side === Side.Buy ? -totalMarketPrice : totalMarketPrice,
    accountId,
    createdAt: new Date(),
    createdBy: userId,
  },
});

await prisma.trade.create({
  data: {
    id: txn.id,
    side: DbSide[side],
    quantity,
    price,
    symbol,
  },
});

Reading a mixed list of transactions in an account

async getAccountTransactions(accountId: string) {
  const transactions = await prisma.transaction.findMany({
    where: {
      accountId,
    },
    include: {
      transfer: true,
      trade: true,
    },
  });

  return transactions.map((t) => {
    const { transfer, trade, ...rest } = t;
    switch (t.type) {
      case DbTransactionType.CASH_TRANSFER:
        return {
          ...rest,
          direction: t.transfer!.direction,
        };
      case DbTransactionType.TRADE:
        return {
          ...rest,
          side: t.trade!.side,
          quantity: t.trade!.quantity,
          price: t.trade!.price,
          symbol: t.trade!.symbol,
        };
    }
  });
}

What do you think?

[cr101]

@nareshbhatia Does that mean that for every record created in the Transaction table either the transfer or trade field will be null?

[bombillazo]

@nareshbhatia Does that mean that for every record created in the Transaction table either the transfer or trade field will be null?

Probably so, that's the "con" of this approach to polymorphism, you'll have a new Foreign Key for each new "subtype" of the main table and have null relations, making constraints harder to enforce.

[cr101]

@nareshbhatia This potential solution might help until Polymorphic Relationships are implemented into Prisma

[nareshbhatia]

@nareshbhatia Does that mean that for every record created in the Transaction table either the transfer or trade field will be null?

Yes, that's correct. That's the only way I could fetch the related records using include. Was not able to do this using @matthewmueller 's solution.

I will look at the potential solution referenced by @cr101.

[ruheni]

Hi friends,

We created a Prisma example showcasing how you can work with delegated types from Matt's suggestion – this is experimental.

https://github.com/prisma/prisma-examples/tree/latest/data-modeling/delegated-types

Happy to answer any questions you may have, and your feedback is welcome 🙂

[brielov]

@ruheni but aren't you executing one query per activity entry?

  /**
   * Entire feed
   */
  const content = await prisma.activity.findMany()

  const feed = await Promise.all(content.map((contentItem) => {
    // @ts-ignore
    return prisma[contentItem.type].findUnique({ where: { id: contentItem.id } });
  }))

Suppose content has 100 items, you're calling findUnique 100 times + the findMany. It doesn't seem ideal nor scalable.

[Xetera]

From what I know, Prisma takes care of this N+1 problem automatically so I imagine the 100 db requests are consolidated into a single query. This is still not a scalable option for me though, I feel like copy pasting schemas is still a much easier approach