Polymorphism in Prisma Schema - Best practices?

Question

This is more a design question than a coding question. Suppose the following schema:

// schema.prisma
// Solution 1

model Entity {
  id    Int          @id @default(autoincrement())
  attrs EntityAttr[] 
}

model EntityAttr {
  id       Int         @id @default(autoincrement())
  value    Json        // or String, doesnt matter much here
                       // the point is I need to attach info on the
                       // join table of this relation
  attr     Attr        @relation(fields: [attrId], references: [id])
  entity   Entity      @relation(fields: [entityId], references: [id])

  entityId Int
  attrId   Int

  @@unique([entityId, attrId])
}

model Attr {
  id       Int          @id @default(autoincrement())
  entities EntityAttr[]   
}

// Solution 2
model Entity {
  id          Int          @id @default(autoincrement())
  dateAttrs   DateAttr[]
  recordAttrs RecordAttr[]
  // ... this pattern could continue for more Attr-like models
}

model DateAttr {
  id     Int       @id @default(autoincrement())
  name   String
  entity Entity    @relation(fields: [entityId], references: [id])
  value  DateTime  // Stronger typing in generated code
}

model RecordAttr {
  // ... define another Entity @relation(...)
  name   String
  value  String
  // ...
}

// ... and so on

Please note that the schema might not be 100% complete or accurate. It is mainly to get the point across.

Solution 1 has its merits where redundancy and the number of tables in the database is reduced significantly (depending on the number of Attrs). Its downfall comes as confusing queries*, possible case-specific type casting and no code-completion for the value field for each Attr-like model.

* by confusing, I mean that the option for simplified m-n queries in prisma is functionally disabled when using a custom join table (e.g. EntityAttr)

Solution 2 has its merits where the generated code results in more strongly typed code generation for the value field, however it falls in the number of generated tables (I don't actually know if more tables is a good thing or a bad thing, all I think is that if you have similar values, they ought to be in the same table).

What would you do in my shoes?

Answer 1

I was looking pretty long for an appropriate answer and found it here. I'm not sure if it could be applied to your question, but this is question about prisma and polymorphism, so I think this code snippet might be useful for developers:

model Photo {
  id Int @id @default(autoincrement())

  likes Like[] @relation("PhotoLike")
}

model Video {
  id Int @id @default(autoincrement())

  likes Like[] @relation("VideoLike")
}

enum LikableType {
  Photo
  Video
}

model Like {
  id Int @id @default(autoincrement())

  Photo Photo? @relation("PhotoLike", fields: [likableId], references: [id], map: "photo_likableId")
  Video Video? @relation("VideoLike", fields: [likableId], references: [id], map: "video_likableId")

  likableId   Int
  likableType LikableType
}

Resuling relations in dbdocs:

Answer 2

Sometimes the use case can't be generalized to abstract and have a typing's.

if you control them and has a limited attribute sure you can create each attribute as a separate table each has it is own schema.

Some Times more freedom is needed or the blocks are dynamic.

Use Case: Build A Block Document Editor Like 'notion.so' and you want to let the user create custom blocks or configure them.

you can do it like :

model Document {
    id     String  @id
    blocks Block[]
}

model Block {
    id           String    @id
    value        Json
    index        Int
    customConfig Json?
    document     Document? @relation(fields: [documentID], references: [id])
    documentID   String?
    blockType    BlockType @relation(fields: [blockTypeID], references: [id])
    blockTypeID  String
}

model BlockType {
    id     String  @id
    name   String
    config Json
    blocks Block[]
}

where config and custom config can contains html,custom css classes, link attribute color or anything.

using type script you can create block.types.ts and add different let say templates for the config's .

I hope that I was useful to you, To sum it, it depends on the requirements :>)

Answer 3

It is not a Prisma limitation, every ORM that relays on foreign key constraints for a relationship is not able to handle the "morph to" in a direct way. The best way to implement this kind of relationship is by using generalization and linking the relation to the general form of objects instead of the concrete types:

model Obj{
  id      String  @default(cuid())
  objType ObjType
  user  User?
  publisher  Publisher?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  subscribers   Subscription[]
  @@id([id,objType])
}

enum ObjType{
  Publisher
  User
}
model User {
  id       String @id
  objType  ObjType @default(User)
  obj Obj? @relation(fields: [id,objType],references: [id,objType],map:"UserObj")
  email     String   @unique
  phone     String   @unique
  otp   String
  refreshToken  String
  password  String
  // posts     Post[]
  roles      Role[]
  subscriptions Subscription[] @relation("subscriber")

  @@unique([id,objType])
}

model Publisher {
  id       String @id
  objType  ObjType @default(User)
  obj Obj? @relation(fields: [id,objType],references: [id,objType],map:"PublisherObj")
  name     String
  @@unique([id,objType])
}

//subscription
model Subscription {
  subscriber  User @relation("subscriber",fields: [subscriberId], references: [id])
  subscriberId  String
  subscribed Obj @relation(fields: [subscribedId,subscribedType],references: [id,objType])
  subscribedId   String
  subscribedType ObjType
  duration Int
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  @@id([subscriberId,subscribedId,subscribedType])
}

Answer 4

The recent v2 alpha release of ZenStack built on top of Prisma made a natural support for it:

model User {
    id Int @id @default(autoincrement())
    contents Content[]
}
    
model Content {
    id Int @id @default(autoincrement())
    published Boolean @default(false)
    owner User @relation(fields: [ownerId], references: [id])
    ownerId Int
    contentType String
    
    @@delegate(contentType)
}

model Post extends Content {
    title String
}

model Video extends Content {
    name String
    duration Int
}

See more details on: https://zenstack.dev/docs/next/guides/polymorphism

If you are interested on the implementation detail, check out the below blog post: https://zenstack.dev/blog/polymorphism