Recursive relationships with Prisma and PostgreSQL

December 11, 2023

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
}

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[]
}

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