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

NestJS SQL

This entry is part 146 of 158 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 and . 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 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 that points to a photo or an article.

schema.prisma

Besides the 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 that holds an enum.

While this approach works, it has a set of downsides. The 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

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 property with the in the table, we ensure that points to a valid photo. Above, we create a second foreign key constraint that ensures that 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 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 . Since we have it in our database, the foreign key constraint that matches the with the table would not complain. However, if we don’t have an article with an ID of , the constraint that ensures that the 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 is not practical and unmaintainable.

A better way to implement the polymorphic association

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

schema.prisma

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

Both the and 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.

Thanks to adding the 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

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

Adding validation

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

create-comment.dto.ts

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

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

comments.service.ts

Prisma throws the with the 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 >>
Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Eva
Eva
5 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
teyalite
5 months ago

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