Back to Tutorials
6 min read

Recursive relationships with Drizzle ORM and PostgreSQL

Managing relationships between the tables in our database is one of the fundamental aspects of using SQL. When a particular table points back to itself, it…

October 21, 2024

Managing relationships between the tables in our database is one of the fundamental aspects of using SQL. When a particular table points back to itself, it creates a recursive relationship. In this article, we learn what the recursive relationships are and how to work with them using the Drizzle ORM and PostgreSQL.

We sometimes refer to the recursive relationships as the self-referencing relationships.

Recursive relationships#

In the previous parts of this series, we’ve created a database with articles, users, and categories.

Above, we have a many-to-one relationship between users and articles where a particular user can be an author of multiple articles. Still, an article can have just one author.

Also, we have a many-to-many relationship where a single category can relate to various articles, and an article can belong to multiple categories.

Adding a recursive relationship#

The recursive relationships often occur when we deal with hierarchical structures. A good example is working with nested categories, such as:

  • React
    • Testing React
    • Redux
  • Node.js
    • NestJS
      • Integrating with the Drizzle ORM
    • Express.js

To achieve the above in our database, we must create a recursive relationship where a particular category can point to its parent.

database-schema.ts#
import { relations } from "drizzle-orm"
import { AnyPgColumn, integer, pgTable, serial, text } from "drizzle-orm/pg-core"
 
export const categories = pgTable("categories", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  parentCategoryId: integer("parent_category_id").references((): AnyPgColumn => categories.id),
})
export const databaseSchema = {
  categories, // ...
}

We use the AnyPgColumn type to avoid the following error:
categories implicitly has type any because it does not have a type annotation and is referenced directly or indirectly in its own initializer.

Defining the parent#

Now, we can modify our DTO to allow the users to define the parent category.

database-schema.ts#
import { IsString, IsNotEmpty, IsNumber, IsOptional } from "class-validator"
export class CategoryDto {
  @IsString()
  @IsNotEmpty()
  name: string
  @IsNumber()
  @IsOptional()
  parentCategoryId?: number
}

We also have to modify our service so that it can insert the parent category ID into our database.

categories.service.ts#
import { Injectable } from "@nestjs/common"
import { CategoryDto } from "./dto/category.dto"
import { DrizzleService } from "../database/drizzle.service"
import { databaseSchema } from "../database/database-schema"
@Injectable()
export class CategoriesService {
  constructor(private readonly drizzleService: DrizzleService) {}
  async create(data: CategoryDto) {
    const createdCategories = await this.drizzleService.db
      .insert(databaseSchema.categories)
      .values({
        name: data.name,
        parentCategoryId: data.parentCategoryId,
      })
      .returning()
    return createdCategories.pop()
  } // ...
}

At some point, we might want to fetch the details of a parent or children. Fortunately, the Drizzle ORM’s relational query API allows us to do that.

Fetching the parent#

To easily fetch the parent of a given entity in the recursive relationship, we need to define the relationship in our database schema.

database-schema.ts#
import { relations } from "drizzle-orm"
import { AnyPgColumn, integer, pgTable, serial, text } from "drizzle-orm/pg-core"
 
export const categories = pgTable("categories", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  parentCategoryId: integer("parent_category_id").references((): AnyPgColumn => categories.id),
})
export const categoriesRelations = relations(categories, ({ one }) => ({
  parentCategory: one(categories, {
    fields: [categories.parentCategoryId],
    references: [categories.id],
  }),
}))
export const databaseSchema = {
  categories,
  categoriesRelations,
}

Thanks to defining the categoriesRelations, we can now use the relational query API built into the Drizzle ORM.

categories.service.ts#
import { Injectable, NotFoundException } from "@nestjs/common"
import { DrizzleService } from "../database/drizzle.service"
import { databaseSchema } from "../database/database-schema"
import { eq } from "drizzle-orm"
@Injectable()
export class CategoriesService {
  constructor(private readonly drizzleService: DrizzleService) {}
  async getById(categoryId: number) {
    const category = await this.drizzleService.db.query.categories.findFirst({
      with: {
        parentCategory: true,
      },
      where: eq(databaseSchema.categories.id, categoryId),
    })
    if (!category) {
      throw new NotFoundException()
    }
    return category
  } // ...
}

Fetching the children#

Besides fetching the parent of a given entity, we might want to fetch all of their children. To do that, we need to adjust the definition of our relationship in our database schema.

database-schema.ts#
import { relations } from "drizzle-orm"
import { AnyPgColumn, integer, pgTable, serial, text } from "drizzle-orm/pg-core"
 
export const categories = pgTable("categories", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  parentCategoryId: integer("parent_category_id").references((): AnyPgColumn => categories.id),
})
export const categoriesRelations = relations(categories, ({ one, many }) => ({
  parentCategory: one(categories, {
    fields: [categories.parentCategoryId],
    references: [categories.id],
    relationName: "nested_categories",
  }),
  nestedCategories: many(categories, {
    relationName: "nested_categories",
  }),
}))
export const databaseSchema = {
  categories,
  categoriesRelations,
}

Above, we add the relationName to avoid the following error: There are multiple relations between “categories” and “categories”. Please specify relation name

Thanks to the above, we can use the nestedCategories in our service.

categories.service.ts#
import { Injectable, NotFoundException } from "@nestjs/common"
import { DrizzleService } from "../database/drizzle.service"
import { databaseSchema } from "../database/database-schema"
import { eq } from "drizzle-orm"
@Injectable()
export class CategoriesService {
  constructor(private readonly drizzleService: DrizzleService) {}
  async getById(categoryId: number) {
    const category = await this.drizzleService.db.query.categories.findFirst({
      with: {
        parentCategory: true,
        nestedCategories: true,
      },
      where: eq(databaseSchema.categories.id, categoryId),
    })
    if (!category) {
      throw new NotFoundException()
    }
    return category
  } // ...
}

Fetching the deeply nested entities#

It’s crucial to notice that in the above example, we’re only fetching one level of entities, so we can’t see the nested categories of our nested categories.

PostgreSQL has support for fully recursive queries that would solve this problem, but the Drizzle ORM does not support that yet. If we need that, we can write a raw SQL query.

WITH RECURSIVE category_hierarchy AS (
    SELECT id, name, 0 as level -- Starting with level 0 for the root category
    FROM categories
    WHERE id = 2 -- Replace 1 with the id of the category you want to query
    UNION ALL
 
    SELECT
        category.id,
        category.name,
        category_hierarchy.level + 1 -- We increment the level for each nested category
    FROM categories category
    JOIN category_hierarchy category_hierarchy
  ON category.parent_category_id = category_hierarchy.id
)
SELECT * FROM category_hierarchy;

Summary#

In this article, we explored the recursive relationships with PostgreSQL and the Drizzle ORM. While doing that, we used an example of categories and subcategories. We used the Drizzle ORM to fetch one level of the nested entities and leveraged raw SQL to fetch the deeply nested entities. Thanks to all of the above, we learned how the recursive relationships work, what the limitations of the Drizzle ORM are, and how to overcome them if necessary.

Recursive relationships with Drizzle ORM and PostgreSQL | NestJS.io