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 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.

1model Photo {
2  id       Int    @id @default(autoincrement())
3  imageUrl String
4}
5 
6model Article {
7  id         Int        @id @default(autoincrement())
8  title      String
9  content    String?
10  upvotes    Int        @default(0)
11  author     User       @relation(fields: [authorId], references: [id], onDelete: Restrict)
12  authorId   Int
13  categories Category[]
14}
15 
16enum CommentableType {
17  Photo
18  Article
19}
20 
21model Comment {
22  id Int @id @default(autoincrement())
23  content String
24 
25  commentableId   Int
26  commentableType CommentableType
27}

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.

1model Photo {
2  id       Int       @id @default(autoincrement())
3  imageUrl String
4 
5  comments Comment[] @relation("PhotoComment")
6}
7 
8model Article {
9  id         Int        @id @default(autoincrement())
10  title      String
11  content    String?
12  upvotes    Int        @default(0)
13  author     User       @relation(fields: [authorId], references: [id], onDelete: Restrict)
14  authorId   Int
15  categories Category[]
16 
17  comments   Comment[]  @relation("ArticleComment")
18}
19 
20enum CommentableType {
21  Photo
22  Article
23}
24 
25model Comment {
26  id Int @id @default(autoincrement())
27  content String
28 
29  photo   Photo?   @relation("PhotoComment", fields: [commentableId], references: [id], map: "photo_commentableId")
30  article Article? @relation("ArticleComment", fields: [commentableId], references: [id], map: "article_commentableId")
31 
32  commentableId   Int
33  commentableType CommentableType
34}

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.

1model Photo {
2  id       Int       @id @default(autoincrement())
3  imageUrl String
4  comments Comment[]
5}
6 
7model Article {
8  id         Int        @id @default(autoincrement())
9  title      String
10  content    String?
11  upvotes    Int        @default(0)
12  author     User       @relation(fields: [authorId], references: [id], onDelete: Restrict)
13  authorId   Int
14  categories Category[]
15  comments   Comment[]
16}
17 
18model Comment {
19  id Int @id @default(autoincrement())
20  content String
21 
22  photo   Photo? @relation(fields: [photoId], references: [id])
23  photoId Int?
24 
25  article   Article? @relation(fields: [articleId], references: [id])
26  articleId Int?
27}

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.

1npx prisma migrate dev --name add-comments-table --create-only

Thanks to adding the --create-only Prisma does not run the migration automatically and we have the chance to adjust it. If you want to know more about running migrations with Prisma, check out API with NestJS #115. Database migrations with Prisma

1-- CreateTable
2CREATE TABLE "Comment" (
3    "id" SERIAL NOT NULL,
4    "content" TEXT NOT NULL,
5    "photoId" INTEGER,
6    "articleId" INTEGER,
7 
8    CONSTRAINT "Comment_pkey" PRIMARY KEY ("id")
9);
10 
11-- AddForeignKey
12ALTER TABLE "Comment" ADD CONSTRAINT "Comment_photoId_fkey" FOREIGN KEY ("photoId") REFERENCES "Photo"("id") ON DELETE SET NULL ON UPDATE CASCADE;
13 
14-- AddForeignKey
15ALTER TABLE "Comment" ADD CONSTRAINT "Comment_articleId_fkey" FOREIGN KEY ("articleId") REFERENCES "Article"("id") ON DELETE SET NULL ON UPDATE CASCADE;
16 
17ALTER TABLE "Comment"
18ADD 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.

1import {
2  IsNotEmpty,
3  IsString,
4  registerDecorator,
5  ValidationArguments,
6} from 'class-validator';
7 
8const idKeys: (keyof CreateCommentDto)[] = ['photoId', 'articleId'];
9 
10export function ContainsValidForeignKeys() {
11  return function (object: Object, propertyName: string) {
12    registerDecorator({
13      name: 'containsValidForeignKeys',
14      target: object.constructor,
15      propertyName: propertyName,
16      options: {
17        message: `You need to provide exactly one of the following properties: ${idKeys.join(', ',)}`,
18      },
19      validator: {
20        validate(value: unknown, validationArguments: ValidationArguments) {
21          const comment = validationArguments.object as CreateCommentDto;
22 
23          if (value && !Number.isInteger(value)) {
24            return false;
25          }
26 
27          return (
28            !idKeys.every((key) => comment[key]) &&
29            idKeys.some((key) => comment[key])
30          );
31        },
32      },
33    });
34  };
35}
36 
37export class CreateCommentDto {
38  @IsString()
39  @IsNotEmpty()
40  content: string;
41 
42  @ContainsValidForeignKeys()
43  photoId?: number;
44 
45  @ContainsValidForeignKeys()
46  articleId?: number;
47}

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.

1import { BadRequestException, Injectable } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3import { CreateCommentDto } from './dto/create-comment.dto';
4import { Prisma } from '@prisma/client';
5import { PrismaError } from '../database/prisma-error.enum';
6 
7@Injectable()
8export class CommentsService {
9  constructor(private readonly prismaService: PrismaService) {}
10 
11  async create(comment: CreateCommentDto) {
12    try {
13      return await this.prismaService.comment.create({
14        data: {
15          content: comment.content,
16          articleId: comment.articleId,
17          photoId: comment.photoId,
18        },
19      });
20    } catch (error) {
21      if (
22        error instanceof Prisma.PrismaClientUnknownRequestError &&
23        error.message.includes('check_if_only_one_is_not_null')
24      ) {
25        throw new BadRequestException(
26          'You need to provide exactly one foreign key',
27        );
28      }
29      if (
30        error instanceof Prisma.PrismaClientKnownRequestError &&
31        error.code === PrismaError.ForeignKeyConstraintViolated
32      ) {
33        throw new BadRequestException(
34          'You need to provide a foreign key that matches a valid row',
35        );
36      }
37      throw error;
38    }
39  }
40 
41  // ...
42}

Prisma throws the PrismaClientKnownRequestError with the P2003 code when the provided foreign key is invalid. If you want to know more about handling constraints and errors with Prisma, check out API with NestJS #111. Constraints with PostgreSQL and Prisma

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.