API with NestJS #137. Recursive relationships with Prisma and PostgreSQL

December 11, 2023

This entry is part 137 of 180 in the API with NestJS

When we work with SQL databases, we usually create tables that relate to each other in some way. Managing those relationships is one of the most fundamental aspects of working with SQL. Across various types of SQL relationships, there is one that sticks out.

Sometimes, one of our tables points back to itself, creating a recursive relationship. This often occurs with hierarchical structures. In this article, we learn more about recursive relationships and how to create them with Prisma.

Recursive relationships are sometimes called self-referencing relationships

Introducing recursive relationships

In the previous parts of this series, we’ve designed a database that uses various types of relationships.

When defining it in the Prisma schema, we use the @relation attribute to define the relationships between our models.

schema.prisma

model User {

id Int @id @default(autoincrement())

email String @unique

name String

password String

articles Article[]

address Address? @relation(fields: [addressId], references: [id])

addressId Int? @unique

}

The only exception is when we define an implicit many-to-many relationship. If you want to learn more about defining regular relationships using Prisma, check out API with NestJS #33. Managing PostgreSQL relationships with Prisma

Let’s create a hierarchical structure where a particular article category can have nested categories like this:

Node.js
- Express
- NestJS
  - Integrating with Prisma
React
- React Hooks
- Testing React

To achieve this, we need to connect the category table to itself. When we want to create a recursive relationship, we also need to use the @relation parameter but we need to name our relationship.

schema.prisma

model Category {

id Int @id @default(autoincrement())

name String

articles Article[]

parentCategory Category? @relation("CategoriesHierarchy", fields: [parentCategoryId], references: [id])

nestedCategories Category[] @relation("CategoriesHierarchy")

parentCategoryId Int?

}

The above schema creates a one-to-many recursive relationship where:

a particular category can have no more than one parent category,
multiple categories can share the same parent.

Now, let’s generate a migration.

1	npx prisma migrate dev --name add-nested-categories

migration.sql

-- AlterTable

ALTER TABLE "Category" ADD COLUMN "parentCategoryId" INTEGER;

-- AddForeignKey

ALTER TABLE "Category" ADD CONSTRAINT "Category_parentCategoryId_fkey" FOREIGN KEY ("parentCategoryId") REFERENCES "Category"("id") ON DELETE SET NULL ON UPDATE CASCADE;

As we can see, recursive relationships don’t differ too much from regular relationships at first glance regarding the underlying SQL.

Defining the nested categories

One of the ways that we can let the user define the nested categories is by providing them when creating the category.

create-category.dto

import { IsString, IsNotEmpty, IsInt } from 'class-validator';

import { CanBeUndefined } from '../../utilities/can-be-undefined';

export class CreateCategoryDto {

@IsString()

@IsNotEmpty()

@IsInt({ each: true })

@CanBeUndefined()

nestedCategoryIds?: number[];

}

If you want to learn more about the @CanBeUndefined decorator, check out API with NestJS #114. Modifying data using PUT and PATCH methods with Prisma

Similarly, we can do that when updating an existing category.

update-category.dto

import { IsString, IsNotEmpty, IsInt } from 'class-validator';

import { CanBeUndefined } from '../../utilities/can-be-undefined';

export class UpdateCategoryDto {

@IsString()

@IsNotEmpty()

@CanBeUndefined()

name?: string;

@IsInt({ each: true })

@CanBeUndefined()

nestedCategoryIds?: number[];

}

When making the queries using Prisma, it is important to acknowledge that the user might provide an incorrect category ID in the nestedCategoryIds. When this happens, it violates the foreign key constraint. Fortunately, we can handle that. To do that, we first need to adjust our PrismaError enum.

prisma-enum.ts

export enum PrismaError {

RecordDoesNotExist = 'P2025',

UniqueConstraintViolated = 'P2002',

ForeignKeyConstraintViolated = 'P2003',

ConnectedRecordsNotFound = 'P2018',

}

We can now use the new property of our enum in the categories service.

categories.service.ts

import {

ConflictException,

Injectable,

NotFoundException,

} from '@nestjs/common';

import { PrismaService } from '../database/prisma.service';

import { CreateCategoryDto } from './dto/create-category.dto';

import { Prisma } from '@prisma/client';

import { PrismaError } from '../database/prisma-error.enum';

import { UpdateCategoryDto } from './dto/update-category.dto';

@Injectable()

export class CategoriesService {

constructor(private readonly prismaService: PrismaService) {}

async create(category: CreateCategoryDto) {

const nestedCategories =

category.nestedCategoryIds?.map((id) => ({

id,

})) || [];

try {

return await this.prismaService.category.create({

data: {

nestedCategories: {

connect: nestedCategories,

include: {

nestedCategories: true,

});

} catch (error) {

if (

error instanceof Prisma.PrismaClientKnownRequestError &&

error.code === PrismaError.ConnectedRecordsNotFound

) {

throw new ConflictException(

'Some of the provided category ids are not valid',

);

}

throw error;

}

async update(id: number, category: UpdateCategoryDto) {

try {

const nestedCategories =

category.nestedCategoryIds?.map((id) => ({

id,

})) || [];

return await this.prismaService.category.update({

data: {

nestedCategories: {

connect: nestedCategories,

include: {

nestedCategories: true,

where: {

id,

});

} catch (error) {

if (!(error instanceof Prisma.PrismaClientKnownRequestError)) {

throw error;

}

if (error.code === PrismaError.RecordDoesNotExist) {

throw new NotFoundException();

}

if (error.code === PrismaError.ConnectedRecordsNotFound) {

throw new ConflictException(

'Some of the provided category ids are not valid',

);

}

throw error;

}

// ...

}

Querying the nested categories

Connecting a category to its nested categories is relatively easy. However, fetching all of the related nested records might be tricky.

categories.service.ts

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

import { PrismaService } from '../database/prisma.service';

@Injectable()

export class CategoriesService {

constructor(private readonly prismaService: PrismaService) {}

async getById(id: number) {

const category = await this.prismaService.category.findUnique({

where: {

id,

include: {

articles: true,

nestedCategories: true,

});

if (!category) {

throw new NotFoundException();

}

return category;

}

// ...

}

The above approach using nestedCategories: true causes our API to respond with the nested categories.

The problem is that it only returns the first level of nested entities. With Prisma, we must state how deep we want to query explicitly.

categories.service.ts

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

import { PrismaService } from '../database/prisma.service';

@Injectable()

export class CategoriesService {

constructor(private readonly prismaService: PrismaService) {}

async getById(id: number) {

const category = await this.prismaService.category.findUnique({

where: {

id,

include: {

articles: true,

nestedCategories: {

include: {

nestedCategories: true,

});

if (!category) {

throw new NotFoundException();

}

return category;

}

// ...

}

PostgreSQL has recursive queries that could solve this problem, but Prisma does not support them yet. As a workaround, we could create a recursive function where we specify the maximum number of levels.

categories.service.ts

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

import { PrismaService } from '../database/prisma.service';

import { Prisma } from '@prisma/client';

@Injectable()

export class CategoriesService {

constructor(private readonly prismaService: PrismaService) {}

getAll() {

return this.prismaService.category.findMany();

}

private includeNestedCategories(

maximumLevel: number,

): boolean | Prisma.Category$nestedCategoriesArgs {

if (maximumLevel === 1) {

return true;

}

return {

include: {

nestedCategories: this.includeNestedCategories(maximumLevel - 1),

};

}

async getById(id: number) {

const category = await this.prismaService.category.findUnique({

where: {

id,

include: {

articles: true,

nestedCategories: this.includeNestedCategories(10),

});

if (!category) {

throw new NotFoundException();

}

return category;

}

// ...

}

Thanks to the includeNestedCategories method, we can fetch deeply nested categories up to a certain level.

Recursive query using raw SQL

If setting the maximum level of the query is not a good enough solution for you, you can use a raw recursive SQL.

WITH RECURSIVE category_hierarchy AS (

SELECT id, name, 0 as level -- Starting with level 0 for the root category

FROM "Category"

WHERE id = 1 -- Replace 1 with the id of the category you want to query

UNION ALL

SELECT

category.id,

category.name,

category_hierarchy.level + 1 -- Incrementing the level for each nested category

FROM "Category" category

JOIN category_hierarchy category_hierarchy

ON category."parentCategoryId" = category_hierarchy."id"

)

SELECT * FROM category_hierarchy;

With this approach, we fetch all deeply nested subcategories of a particular category.

Summary

In this article, we learned how to handle recursive relationships in PostgreSQL using Prisma. To do that, we created an example using categories and subcategories. An interesting part of the example was how to fetch categories along with all its deeply nested subcategories. We had to work around some of the limitations of Prisma to fetch deeply nested entities up to a certain level. If that’s not good enough, we were able to write a raw SQL query that fetches all entities without setting a maximum level. All of this gave us a good idea of how to handle various cases that include recursive relationships.

Series Navigation<< API with NestJS #136. Raw SQL queries with Prisma and PostgreSQL range typesAPI with NestJS #138. Filtering records with Prisma >>

1 Comment

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Alex

7 months ago

I did everything carefully as it is written in this article, however, when creating a new category in the database, nothing is created and error:
Foreign key constraint failed on the field:
categories_parentId_fkey (index)