API with NestJS #146. Polymorphic associations with PostgreSQL and Prisma

February 19, 2024

This entry is part 146 of 177 in the API with NestJS

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.

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

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.

Series Navigation<< API with NestJS #145. Securing applications with HelmetAPI with NestJS #147. The data types to store money with PostgreSQL and Prisma >>

2 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Eva

9 months ago

Wow, that’s awesome! I always validated such relations with large check constraint that looked for “type” column and by their value checked according columns is not null. Now with num_nonnulls it’s so much cleaner and I don’t need to store redundant calculated column!

teyalite

Hello, thank you for the article.
Regarding the validation, in my opinion you could you use @ValidateIf instead of a custom validation decorator.