It’s not uncommon for a single table to be related to multiple similar tables. A good example is a comment the user can write under a photo, an article, or an audio file.
A possible approach would be to create a separate table for each type of comment, such as PhotoComment or ArticleComment. Unfortunately, this would duplicate much of our code since a comment under a photo would work the same as a comment under an article.
Polymorphic associations
Alternatively, we can implement a polymorphic association. It’s a design pattern that allows a single table to be associated with one of various different tables. This way, we create only a single Comment table. The crucial thing is that a single comment can be related to either a photo or an article, but not both.
An incorrect way to design a polymorphic association
Let’s say that we have a database with articles and photos.
1import { serial, text, pgTable } from 'drizzle-orm/pg-core';
2
3export const photos = pgTable('photos', {
4 id: serial('id').primaryKey(),
5 imageUrl: text('image_url').notNull(),
6});
7
8export const articles = pgTable('articles', {
9 id: serial('id').primaryKey(),
10 title: text('title').notNull(),
11 content: text('content').notNull(),
12});
13
14export const databaseSchema = {
15 articles,
16 photos,
17};The most straightforward way to implement a polymorphic association is through a single column, such as targetId, pointing to either a photo or an article.
1import { serial, text, pgTable, integer } from 'drizzle-orm/pg-core';
2
3export const photos = pgTable('photos', {
4 id: serial('id').primaryKey(),
5 imageUrl: text('photo_url').notNull(),
6});
7
8export const articles = pgTable('articles', {
9 id: serial('id').primaryKey(),
10 title: text('title').notNull(),
11 content: text('content').notNull(),
12});
13
14export const comments = pgTable('comments', {
15 id: serial('id').primaryKey(),
16 content: text('content').notNull(),
17 targetId: integer('target_id'),
18});
19
20export const databaseSchema = {
21 articles,
22 photos,
23 comments,
24};While this could work, it has multiple downsides. PostgreSQL treats the targetId as a regular number and can’t guarantee that it points to a valid photo or article. Because of that, we would have to manually ensure the database’s integrity. For example, when we delete a photo, we must remember to delete all related comments.
A better way solution
Let’s create a schema with separate articleId and photoId columns.
1import { serial, text, pgTable, integer } from 'drizzle-orm/pg-core';
2import { relations } from 'drizzle-orm';
3
4export const photos = pgTable('photos', {
5 id: serial('id').primaryKey(),
6 imageUrl: text('photo_url').notNull(),
7});
8
9export const articles = pgTable('articles', {
10 id: serial('id').primaryKey(),
11 title: text('title').notNull(),
12 content: text('content').notNull(),
13});
14
15export const comments = pgTable('comments', {
16 id: serial('id').primaryKey(),
17 content: text('content').notNull(),
18 photoId: integer('photo_id').references(() => photos.id),
19 articleId: integer('article_id').references(() => articles.id),
20});
21
22export const commentsRelations = relations(comments, ({ one }) => ({
23 photo: one(photos, {
24 fields: [comments.photoId],
25 references: [photos.id],
26 }),
27 article: one(articles, {
28 fields: [comments.articleId],
29 references: [articles.id],
30 }),
31}));
32
33export const databaseSchema = {
34 articles,
35 photos,
36 comments,
37 commentsRelations,
38};If you want to know more about designing relationships with the Drizzle ORM, check out API with NestJS #154. Many-to-many relationships with Drizzle ORM and PostgreSQL
Now, Drizzle ORM will create two separate foreign key constraints, each based on a separate column. Let’s examine the SQL migration it generates.
1npx drizzle-kit generate --name create-comments-table1CREATE TABLE IF NOT EXISTS "comments" (
2 "id" serial PRIMARY KEY NOT NULL,
3 "content" text NOT NULL,
4 "photo_id" integer,
5 "article_id" integer
6);
7
8--> statement-breakpoint
9DO $$ BEGIN
10 ALTER TABLE "comments" ADD CONSTRAINT "comments_photo_id_photos_id_fk" FOREIGN KEY ("photo_id") REFERENCES "public"."photos"("id") ON DELETE no action ON UPDATE no action;
11EXCEPTION
12 WHEN duplicate_object THEN null;
13END $$;
14
15--> statement-breakpoint
16DO $$ BEGIN
17 ALTER TABLE "comments" ADD CONSTRAINT "comments_article_id_articles_id_fk" FOREIGN KEY ("article_id") REFERENCES "public"."articles"("id") ON DELETE no action ON UPDATE no action;
18EXCEPTION
19 WHEN duplicate_object THEN null;
20END $$;
21
22ALTER TABLE "comments"
23ADD CONSTRAINT check_if_only_one_is_not_null CHECK (num_nonnulls("photo_id", "article_id") = 1);It’s crucial to notice that both the articleId and photoId columns are nullable. Because of that, we could have a comment that is not related to either an article or a photo.
Adding the check constraint
We can fix the above issue with a check constraint.
If you want to know more about constraints with the Drizzle ORM, check out API with NestJS #152. SQL constraints with the Drizzle ORM
Since the Drizzle ORM does not support check constraints out of the box, we must adjust the default migration generated by the Drizzle Kit.
1CREATE TABLE IF NOT EXISTS "comments" (
2 "id" serial PRIMARY KEY NOT NULL,
3 "content" text NOT NULL,
4 "photo_id" integer,
5 "article_id" integer
6);
7
8--> statement-breakpoint
9DO $$ BEGIN
10 ALTER TABLE "comments" ADD CONSTRAINT "comments_photo_id_photos_id_fk" FOREIGN KEY ("photo_id") REFERENCES "public"."photos"("id") ON DELETE no action ON UPDATE no action;
11EXCEPTION
12 WHEN duplicate_object THEN null;
13END $$;
14
15--> statement-breakpoint
16DO $$ BEGIN
17 ALTER TABLE "comments" ADD CONSTRAINT "comments_article_id_articles_id_fk" FOREIGN KEY ("article_id") REFERENCES "public"."articles"("id") ON DELETE no action ON UPDATE no action;
18EXCEPTION
19 WHEN duplicate_object THEN null;
20END $$;
21
22ALTER TABLE "comments"
23ADD CONSTRAINT check_if_only_one_is_not_null CHECK (num_nonnulls("photo_id", "article_id") = 1);Thanks to adding the check_if_only_one_is_not_null constraint at the bottom of our migration, PostgreSQL now ensures that precisely one of the photoId and articleId columns does not contain a null value. To achieve that, we use the num_nonnulls function built into PostgreSQL.
Validation
We must ensure the user provides exactly one of the articleId and photoId properties. To do that, we can create a custom decorator for the class-validator library.
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 const includedIdKeys = idKeys.filter((key) => comment[key]);
28 return includedIdKeys.length === 1;
29 },
30 },
31 });
32 };
33}
34
35export class CreateCommentDto {
36 @IsString()
37 @IsNotEmpty()
38 content: string;
39
40 @ContainsValidForeignKeys()
41 photoId?: number;
42
43 @ContainsValidForeignKeys()
44 articleId?: number;
45}Our ContainsValidForeignKeys decorator checks if the value is an integer and ensures that the user provides precisely one of the articleId and photoId properties.
Alternatively, when the user creates the comment, we can detect if the check_if_only_one_is_not_null constraint is violated.
1import { BadRequestException, Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { CreateCommentDto } from './dto/create-comment.dto';
5import { isDatabaseError } from '../database/databse-error';
6import { PostgresErrorCode } from '../database/postgres-error-code.enum';
7
8@Injectable()
9export class CommentsService {
10 constructor(private readonly drizzleService: DrizzleService) {}
11
12 async create(comment: CreateCommentDto) {
13 try {
14 const createdComments = await this.drizzleService.db
15 .insert(databaseSchema.comments)
16 .values(comment)
17 .returning();
18
19 return createdComments.pop();
20 } catch (error) {
21 if (!isDatabaseError(error)) {
22 throw error;
23 }
24
25 if (error.code === PostgresErrorCode.ForeignKeyViolation) {
26 throw new BadRequestException('Provide a valid foreign key');
27 }
28
29 if (error.code === PostgresErrorCode.CheckViolation) {
30 throw new BadRequestException('Provide exactly one foreign key');
31 }
32 throw error;
33 }
34 }
35
36 // ...
37}Thanks to the above solution, we can create comments associated with photos or articles but not both. Also, if the user tries to make a comment that is not related to either articles or photos, they will see an appropriate error message as well.
Summary
In this article, we implemented a polymorphic association using the Drizzle ORM and PostgreSQL while maintaining the integrity of our database.
With polymorphic associations, we have a lot of flexibility, which allows us to simplify the schema of our database by reducing the number of SQL tables we need. However, they aren’t perfect for every situation. Some might say that having a single table that handles multiple different relationships makes the structure less clear, making it tougher to debug and maintain – especially for developers unfamiliar with this pattern. Though polymorphic associations can make our database schema more flexible, we must use them carefully since implementing them requires a thorough understanding of this pattern.