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. 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 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 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; } } // ... } Prisma throw