Back to Tutorials
8 min read

Polymorphic associations with PostgreSQL and Prisma

Often, we might have a situation where a single entity, such as a comment, needs to be associated with more than one type of table. For example, the user might…

February 19, 2024

Often, we might have a situation where a single entity, such as a comment, needs to be associated with more than one type of table. For example, the user might be able to comment on articles, photos, and more. One solution would be to create a separate table for each type of comment, such as ArticleComment and PhotoComment. This could lead to duplicating a lot of logic since a comment for an article or a photo would contain the same columns, such as the author and the content.

An alternative would be to implement polymorphic association. It is a design pattern where a table can be associated with multiple different tables. Instead of creating various tables, we create just one Comment table. The crucial aspect is that a particular comment can be associated either with an article or a photo, not both.

Open out this repository if you want to check out the full code from this article.

Various ways to implement the polymorphic association#

The most straightforward way of implementing a polymorphic association is through a table with a single property called commentableId that points to a photo or an article.

schema.prisma#
model Photo {
  id       Int    @id @default(autoincrement())
  imageUrl String
}
 
model Article {
  id         Int        @id @default(autoincrement())
  title      String
  content    String?
  upvotes    Int        @default(0)
  author     User       @relation(fields: [authorId], references: [id], onDelete: Restrict)
  authorId   Int
  categories Category[]
}
 
enum CommentableType {
  Photo
  Article
}
 
model Comment {
  id Int @id @default(autoincrement())
  content String
 
  commentableId   Int
  commentableType CommentableType
}

Besides the commentableId property, we also need a way to determine if the comment is related to an article or a photo. To do that, we can add the commentableType that holds an enum.

While this approach works, it has a set of downsides. The commentableId is just a number, and PostgreSQL does not guarantee that it points to a valid article or a number. Even if we would add a comment to an existing article, we would have to ensure our database’s integrity manually. For example, if we ever delete the article, we must remember to delete all related comments.

A common mistake with the foreign keys#

Looking through Stack Overflow and GitHub, we can see people suggesting the creation of two foreign key constraints based on the same column.

schema.prisma#
model Photo {
  id       Int       @id @default(autoincrement())
  imageUrl String
 
  comments Comment[] @relation("PhotoComment")
}
 
model Article {
  id         Int        @id @default(autoincrement())
  title      String
  content    String?
  upvotes    Int        @default(0)
  author     User       @relation(fields: [authorId], references: [id], onDelete: Restrict)
  authorId   Int
  categories Category[]
 
  comments   Comment[]  @relation("ArticleComment")
}
 
enum CommentableType {
  Photo
  Article
}
 
model Comment {
  id Int @id @default(autoincrement())
  content String
 
  photo   Photo?   @relation("PhotoComment", fields: [commentableId], references: [id], map: "photo_commentableId")
  article Article? @relation("ArticleComment", fields: [commentableId], references: [id], map: "article_commentableId")
 
  commentableId   Int
  commentableType CommentableType
}

The role of the foreign key constraint is to ensure that the value in one table matches the value in another table. By creating a foreign key constraint that matches the commentableId property with the id in the Photo table, we ensure that commentableId points to a valid photo. Above, we create a second foreign key constraint that ensures that commentableId matches a valid article.

While this might look correct on the surface, it creates a big issue. By creating two foreign key constraints, we ensure that the commentableId property points to both a valid photo and an article.

For example, we might want to comment on a valid photo with an ID of 10. Since we have it in our database, the foreign key constraint that matches the commentableId with the Photo table would not complain. However, if we don’t have an article with an ID of 10, the constraint that ensures that the commentableId property matches a valid article would cause a foreign key constraint violation.

Because of the above problem, the approach with two foreign key constraints based on a single commentableId is not practical and unmaintainable.

A better way to implement the polymorphic association#

Instead, let’s create the Comment model with separate articleId and photoId.

schema.prisma#
model Photo {
  id       Int       @id @default(autoincrement())
  imageUrl String
  comments Comment[]
}
 
model Article {
  id         Int        @id @default(autoincrement())
  title      String
  content    String?
  upvotes    Int        @default(0)
  author     User       @relation(fields: [authorId], references: [id], onDelete: Restrict)
  authorId   Int
  categories Category[]
  comments   Comment[]
}
 
model Comment {
  id Int @id @default(autoincrement())
  content String
 
  photo   Photo? @relation(fields: [photoId], references: [id])
  photoId Int?
 
  article   Article? @relation(fields: [articleId], references: [id])
  articleId Int?
}

Now, Prisma creates two foreign key constraints, each based on a separate column. There is one important catch with this approach, though.

Both the photoId and articleId properties are nullable. It means that we could have a comment that is not associated with either an article or a photo. We can fix that by adding a check constraint. Since Prisma does not support them directly, we must adjust the default migration.

npx prisma migrate dev --name add-comments-table --create-only
migration.sql#
-- CreateTable
CREATE TABLE "Comment" (
    "id" SERIAL NOT NULL,
    "content" TEXT NOT NULL,
    "photoId" INTEGER,
    "articleId" INTEGER,
 
    CONSTRAINT "Comment_pkey" PRIMARY KEY ("id")
);
 
-- AddForeignKey
ALTER TABLE "Comment" ADD CONSTRAINT "Comment_photoId_fkey" FOREIGN KEY ("photoId") REFERENCES "Photo"("id") ON DELETE SET NULL ON UPDATE CASCADE;
 
-- AddForeignKey
ALTER TABLE "Comment" ADD CONSTRAINT "Comment_articleId_fkey" FOREIGN KEY ("articleId") REFERENCES "Article"("id") ON DELETE SET NULL ON UPDATE CASCADE;
 
ALTER TABLE "Comment"
ADD CONSTRAINT check_if_only_one_is_not_null CHECK (num_nonnulls("photoId", "articleId") = 1);

Above, we add the constraint called check_if_only_one_is_not_null. It uses the num_nonnulls function built into PostgreSQL to ensure that exactly one of the photoId and articleId does not contain a null value.

Adding validation#

We need to ensure that the users provide precisely one of the photoId and articleId properties. One way of doing that would be through the class-validator library and a custom decorator.

create-comment.dto.ts#
import { IsNotEmpty, IsString, registerDecorator, ValidationArguments } from "class-validator"
const idKeys: (keyof CreateCommentDto)[] = ["photoId", "articleId"]
export function ContainsValidForeignKeys() {
  return function (object: Object, propertyName: string) {
    registerDecorator({
      name: "containsValidForeignKeys",
      target: object.constructor,
      propertyName: propertyName,
      options: {
        message: `You need to provide exactly one of the following properties: ${idKeys.join(", ")}`,
      },
      validator: {
        validate(value: unknown, validationArguments: ValidationArguments) {
          const comment = validationArguments.object as CreateCommentDto
          if (value && !Number.isInteger(value)) {
            return false
          }
          return !idKeys.every((key) => comment[key]) && idKeys.some((key) => comment[key])
        },
      },
    })
  }
}
export class CreateCommentDto {
  @IsString()
  @IsNotEmpty()
  content: string
  @ContainsValidForeignKeys()
  photoId?: number
  @ContainsValidForeignKeys()
  articleId?: number
}

In our ContainsValidForeignKeys, we check if the provided value is an integer and ensure that the user provided exactly one of the photoId and articleId properties.

Another way would be to detect if the check_if_only_one_is_not_null constraint was violated when creating the comment.

comments.service.ts#
import { BadRequestException, Injectable } from "@nestjs/common"
import { PrismaService } from "../database/prisma.service"
import { CreateCommentDto } from "./dto/create-comment.dto"
import { Prisma } from "@prisma/client"
import { PrismaError } from "../database/prisma-error.enum"
@Injectable()
export class CommentsService {
  constructor(private readonly prismaService: PrismaService) {}
  async create(comment: CreateCommentDto) {
    try {
      return await this.prismaService.comment.create({
        data: {
          content: comment.content,
          articleId: comment.articleId,
          photoId: comment.photoId,
        },
      })
    } catch (error) {
      if (
        error instanceof Prisma.PrismaClientUnknownRequestError &&
        error.message.includes("check_if_only_one_is_not_null")
      ) {
        throw new BadRequestException("You need to provide exactly one foreign key")
      }
      if (
        error instanceof Prisma.PrismaClientKnownRequestError &&
        error.code === PrismaError.ForeignKeyConstraintViolated
      ) {
        throw new BadRequestException("You need to provide a foreign key that matches a valid row")
      }
      throw error
    }
  } // ...
}

Thanks to all of the above, we can create comments associated with photos or articles. If we try to create a comment related to both or neither of them, the user will see an error that clearly explains the issue.

Summary#

In this article, we’ve used Prisma and PostgreSQL to design a polymorphic association that is easy to maintain and ensures the integrity of our database.

The polymorphic associations offer a lot of flexibility and can reduce the complexity of our schema by allowing us to create fewer SQL tables. However, polymorphic associations are not ideal for every case. Some would argue that using just one table to manage multiple relationships is less explicit. Therefore, debugging and maintaining it might be more challenging, especially for new developers unfamiliar with this design.

While polymorphic associations can enhance the flexibility and simplicity of your database schema, they require careful consideration and understanding to implement effectively and maintain over time.

Polymorphic associations with PostgreSQL and Prisma | NestJS.io