Recursive relationships with Prisma and PostgreSQL

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.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() name: string; @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: { name: category.name, 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: { name: category.name, 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,