How to model inheritance in Prisma

Inheritance is a design pattern commonly used in object-oriented programming which allows you to model entities which take on certain properties or functions from another entity. This can be useful for modeling a set of entities which are different variants of the same kind of thing. Each variant has its own particulars, but all the variants have certain properties in common.

Relational databases don't support inheritance per-se, but with some effort we can arrange things with Prisma to give a pretty good approximation.

We have a few cases in our data design at Basedash where we have models which share commonality between them. Take Bananas and Oranges. They are distinct models with their own particular properties, but they have common ground. We’d like to model each of them as a type of a Fruit. So Fruit is the “parent model” and Banana and Orange are “child models”. They inherit from Fruit. Our interest at Basedash has been an approach where we have a distinct table in our database for each of these models, and we set up our own “wiring” to keep them in order and maintain the parent-child arrangement. This is sometimes known as “Class Table Inheritance”, where you have one table per “class”.

To see what we mean, here's a very short article and diagrammed example: Class Table Inheritance

To see what we do not mean, here’s a common alternative: Single Table Inheritance

Concept: Polymorphism

One key aspect of inheritance is that there can be multiple subclasses under a superclass. So, say you have a Fruit parent class with Banana and an Orange child classes. A Fruit can be either a Banana or an Orange, it can take one of multiple forms. That is polymorphism.

Many ORMs support polymorphic associations. Here's a few examples:

• In GORM: Polymorphism Association

• In Rails Polymorphic Associations

Concept: Delegated types

Another key aspect of inheritance is that things which are common get passed down from the parent, and things that are particular are stored on the child. When there can be more than one type of child, the particulars for that type are passed down to the child. The parent class delegates its type to a child class.

So you have a Fruit, well what type of fruit? An Orange, say. The Fruit delegates its type to Orange.

The idea is that you can't have an Orange in isolation. The Orange record is paired with, and goes through life with, and should be inseparable from its corresponding Fruit record.

So, say Fruit has fields such as isRipe, and weight. And say Orange has a field particular to it called segmentCount. If you have an Orange, you can know how many segments it has, and you can also know if it is ripe by going to the Fruit record for that Orange.

Rails for instance has such a feature. Here's a quote from the Rails PR implementing delegated types explaining the concept:

With this approach, the "superclass" is a concrete class that is represented by its own table, where all the superclass attributes that are shared amongst all the "subclasses" are stored. And then each of the subclasses have their own individual tables for additional attributes that are particular to their implementation. This is similar to what's called multi-table inheritance in Django, but instead of actual inheritance, this approach uses delegation to form the hierarchy and share responsibilities.

Making a good approximation in Prisma

Unfortunately, Prisma is behind on these fronts, though it seems they are actively considering such things. Here's a prominent thread on the topic: Support for a Union type

The conversation from said thread seems to have spurred an official example of how to manually arrange things to achieve delegated types: - Comment announcing the example - Official delegated types Prisma example

This example felt good enough to us that we decided to try it out. And we decided to try it out first with our activity feed.

Data modeling for the Basedash activity feed

Basedash tracks such things such as edits to fields on records and deletions of records. We have an Edit model and a Deletion model. We wanted to "subclass" these models under an Activity model. So, to generate the activity feed, we would want to get all the Activity records and for each one, see what type it is (either Edit or Deletion) and then render each one with the combined fields from the child and the parent.

Here's the pattern we've gone with, which differs slightly from the Prisma delegated types example:

The schema file

// schema.prisma

enum ActivityType {
  edit
  deletion
}

model Activity {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now()) @db.Timestamptz(6)
  type      ActivityType

  user   User? @relation(fields: [userId], references: [id], onDelete: SetNull)
  userId Int?

  deletion Deletion?
  edit     Edit?
}

model Deletion {
  activity   Activity @relation(fields: [activityId], references: [id], onDelete: Cascade)
  activityId Int      @id

  table    Table?     @relation(fields: [tableId], references: [id], onDelete: SetNull)
  tableId  Int?

  recordId   Json
}

model Edit {
  activity   Activity @relation(fields: [activityId], references: [id], onDelete: Cascade)
  activityId Int      @id

  oldValue String
  newValue String

  attribute    Attribute?     @relation(fields: [attributeId], references: [id], onDelete: SetNull)
  attributeId  Int?

  recordId   Json
}

Things to note:

• We ensure a one-to-one pair between parent and child instance by having them share the same ID. The ID, in this case an auto-incrementing integer sequence, is part of the parent (Activity.id) and the child has a foreign key, (e.g. Edit.activityId) which points to it. This ensures that there can only be one single child record pointing to one single parent record.

• We store a type on the parent which is the name of one of the child models.

• The createdAt field is not present on the child models. Thanks to inheritance, we can get that information by looking at the parent.

Creating a child-parent pair

await prisma.$transaction(async (prisma) => {
  const activity = await prisma.activity.create({
    data: {
      type: 'edit',
      userId: user.id,
    },
  });
  edit = await prisma.edit.create({
    data: {
      activityId: activity.id,
      oldValue: oldValueString,
      newValue: newValueString,
    },
  });
});

Things to note:

• We make sure to always always create the parent-child pair in a single transaction. This ensures there will be exactly one child record for every parent record.

• The Prisma example uses UUIDs for the models, but at Basedash, we use consecutive integers. So we first create the parent, await its creation to get its id and then create the child with that very id. We leverage a preview feature of Prisma, Interactive Transactions to do this.

Querying the child-parent pairs

// The whole feed, with all types interspersed
const feed = await prisma.activity.findMany({
  include: {
    edit: true,
    deletion: true,
  },
});

// A feed of just one type
const deletions = await prisma.activity.findMany({
  where: {
    type: 'deletion'
  }
});

Note that we've gone with a simple findMany with an include directive for its simplicity and readability. The Prisma example does something a bit more sophisticated, which may be more performant: first executing a findMany on the parent and then mapping through those and querying for the child by id for each.

Conclusion: making do with what you’ve got

We’ve been quite happy with working with Prisma at Basedash, but this is a case where it seems a more mature ORM might be a better choice. Still, it’s nice that with a good example to follow and a bit more manual work we can have this inheritance functionality and put it to use in our codebase.