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.