API with NestJS #170. Polymorphic associations with PostgreSQL and Drizzle ORM

October 14, 2024

This entry is part 170 of 174 in the API with NestJS

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.

database-schema.ts

import { serial, text, pgTable } from 'drizzle-orm/pg-core';

export const photos = pgTable('photos', {

id: serial('id').primaryKey(),

imageUrl: text('image_url').notNull(),

});

export const articles = pgTable('articles', {

id: serial('id').primaryKey(),

title: text('title').notNull(),

content: text('content').notNull(),

});

export const databaseSchema = {

articles,

photos,

};

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.

database-schema.ts

import { serial, text, pgTable, integer } from 'drizzle-orm/pg-core';

export const photos = pgTable('photos', {

id: serial('id').primaryKey(),

imageUrl: text('photo_url').notNull(),

});

export const articles = pgTable('articles', {

id: serial('id').primaryKey(),

title: text('title').notNull(),

content: text('content').notNull(),

});

export const comments = pgTable('comments', {

id: serial('id').primaryKey(),

content: text('content').notNull(),

targetId: integer('target_id'),

});

export const databaseSchema = {

articles,

photos,

comments,

};

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.

database-schema.ts

import { serial, text, pgTable, integer } from 'drizzle-orm/pg-core';

import { relations } from 'drizzle-orm';

export const photos = pgTable('photos', {

id: serial('id').primaryKey(),

imageUrl: text('photo_url').notNull(),

});

export const articles = pgTable('articles', {

id: serial('id').primaryKey(),

title: text('title').notNull(),

content: text('content').notNull(),

});

export const comments = pgTable('comments', {

id: serial('id').primaryKey(),

content: text('content').notNull(),

photoId: integer('photo_id').references(() => photos.id),

articleId: integer('article_id').references(() => articles.id),

});

export const commentsRelations = relations(comments, ({ one }) => ({

photo: one(photos, {

fields: [comments.photoId],

references: [photos.id],

}),

article: one(articles, {

fields: [comments.articleId],

references: [articles.id],

}),

}));

export const databaseSchema = {

articles,

photos,

comments,

commentsRelations,

};

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.

1	npx drizzle-kit generate --name create-comments-table

0001_create-comments-table.sql

CREATE TABLE IF NOT EXISTS "comments" (

"id" serial PRIMARY KEY NOT NULL,

"content" text NOT NULL,

"photo_id" integer,

"article_id" integer

);

--> statement-breakpoint

DO $$ BEGIN

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;

EXCEPTION

WHEN duplicate_object THEN null;

END $$;

--> statement-breakpoint

DO $$ BEGIN

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;

EXCEPTION

WHEN duplicate_object THEN null;

END $$;

ALTER TABLE "comments"

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

0001_create-comments-table.sql

CREATE TABLE IF NOT EXISTS "comments" (

"id" serial PRIMARY KEY NOT NULL,

"content" text NOT NULL,

"photo_id" integer,

"article_id" integer

);

--> statement-breakpoint

DO $$ BEGIN

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;

EXCEPTION

WHEN duplicate_object THEN null;

END $$;

--> statement-breakpoint

DO $$ BEGIN

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;

EXCEPTION

WHEN duplicate_object THEN null;

END $$;

ALTER TABLE "comments"

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

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;

}

const includedIdKeys = idKeys.filter((key) => comment[key]);

return includedIdKeys.length === 1;

});

};

}

export class CreateCommentDto {

@IsString()

@IsNotEmpty()

content: string;

@ContainsValidForeignKeys()

photoId?: number;

@ContainsValidForeignKeys()

articleId?: number;

}

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.

comments.service.ts

import { BadRequestException, Injectable } from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { CreateCommentDto } from './dto/create-comment.dto';

import { isDatabaseError } from '../database/databse-error';

import { PostgresErrorCode } from '../database/postgres-error-code.enum';

@Injectable()

export class CommentsService {

constructor(private readonly drizzleService: DrizzleService) {}

async create(comment: CreateCommentDto) {

try {

const createdComments = await this.drizzleService.db

.insert(databaseSchema.comments)

.values(comment)

.returning();

return createdComments.pop();

} catch (error) {

if (!isDatabaseError(error)) {

throw error;

}

if (error.code === PostgresErrorCode.ForeignKeyViolation) {

throw new BadRequestException('Provide a valid foreign key');

}

if (error.code === PostgresErrorCode.CheckViolation) {

throw new BadRequestException('Provide exactly one foreign key');

}

throw error;

}

// ...

}

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.

Series Navigation<< API with NestJS #169. Unique IDs with UUIDs using Drizzle ORM and PostgreSQLAPI with NestJS #171. Recursive relationships with Drizzle ORM and PostgreSQL >>