Many-to-many relationships with Drizzle ORM and PostgreSQL

Creating relationships across tables is a crucial aspect of working with SQL databases. Previously, this series focused on using the Drizzle ORM to create simple relationships, such as one-to-one and many-to-one relationships. In this article, we learn about many-to-many relationships, which are slightly more complex. Introducing many-to-many relationships A many-to-many relationship is necessary when multiple records in one table connect to multiple records in another table. A good example of this is the relationship between categories and articles. A single category can relate to various articles, and likewise, an article can belong to multiple categories. For instance, the article you are reading is listed under both SQL and JavaScript categories. Until now, with the Drizzle ORM, we have set up one-to-one and many-to-one relationships. We accomplished this by using a basic column containing a foreign key corresponding to a row in the related table. The design becomes more complex when linking a specific article to multiple categories. We can’t put multiple IDs in the category_id column. To address this, we need to create a joining table. Creating the categories_articles table allows us to store the connections between individual articles and categories. Many-to-many relationships with Drizzle ORM The first step to implementing a many-to-many relationship with the Drizzle ORM is to modify the database schema. database-schema.ts import { serial, text, integer, pgTable, primaryKey, } from 'drizzle-orm/pg-core'; export const articles = pgTable('articles', { id: serial('id').primaryKey(), title: text('title').notNull(), // ... }); export const categories = pgTable('categories', { id: serial('id').primaryKey(), name: text('title').notNull(), }); export const categoriesArticles = pgTable( 'categories_articles', { categoryId: integer('category_id') .notNull() .references(() => categories.id), articleId: integer('article_id') .notNull() .references(() => articles.id), }, (columns) => ({ pk: primaryKey({ columns: [columns.categoryId, columns.articleId] }), }), ); // ... export const databaseSchema = { articles, categories, categoriesArticles, // ... }; While adding an id column to our categories_articles table is an option, it is unnecessary. Instead, we can use a composite primary key made up of category_id and article_id. This approach offers several benefits beyond saving disk space. Because PostgreSQL enforces the uniqueness of primary keys, this method ensures that an article cannot be assigned to the same category more than once. Now, we can create a new migration based on the changes in the schema. If you want to learn more about migrations with Drizzle, check out API with NestJS #149. Introduction to the Drizzle ORM with PostgreSQL npx drizzle-kit generate --name add-categories-tableThe last step is to run the migration to modify our database and add the new tables. npx drizzle-kit migrate Connecting articles to categories An article can belong to several different categories. Therefore, when creating a new article, we should be able to handle the following data format:{ "title": "My first article", "content": "Hello world!", "categoryIds": [1, 2] }Let’s modify our DTO to support that. create-article.dto.ts import { IsString, IsNotEmpty, IsOptional, IsNumber } from 'class-validator'; export class CreateArticleDto { @IsString() @IsNotEmpty() @IsOptional() content?: string; @IsString() @IsNotEmpty() title: string; @IsOptional() @IsNumber({}, { each: true }) categoryIds?: number[]; }The categoryIds array with two elements means we need to insert two rows into the categories_articles table. We must create an article and insert the above rows in a single transaction to achieve that. If you want to learn more about transactions with Drizzle ORM, check out API with NestJS #153. SQL transactions with the Drizzle ORM articles.service.ts import { Injectable } from '@nestjs/common'; import { DrizzleService } from '../database/drizzle.service'; import { databaseSchema } from '../database/database-schema'; import { CreateArticleDto } from './dto/create-article.dto'; @Injectable() export class ArticlesService { constructor(private readonly drizzleService: DrizzleService) {} async createWithCategories(article: CreateArticleDto, authorId: number) { return this.drizzleService.db.transaction(async (transaction) => { const createdArticles = await transaction .insert(databaseSchema.articles) .values({ authorId, title: article.title, content: article.content, }) .returning(); const createdArticle = createdArticles.pop(); await transaction.insert(databaseSchema.categoriesArticles).values( article.categoryIds.map((categoryId) => ({ categoryId, articleId: createdArticle.id, })), ); return { ...createdArticle, categoryIds: article.categoryIds, }; }); } // ... } Fetching the categories of a particular article While we could perform join queries manually to fetch the categories of a particular article, the Drizzle ORM offers a more straightforward solution. However, we must provide the Drizzle ORM with details about our relationships. database-schema.ts import { serial, pgTable } from 'drizzle-orm/pg-core'; import { relations } from 'drizzle-orm'; export const users = pgTable('users', { id: serial('id').primaryKey(), // ... }); export const articles = pgTable('articles', { id: serial('id').primaryKey(), // ... }); export const categories = pgTable('categories', { id: serial('id').primaryKey(), // ... }); export const categoriesArticles = pgTable( 'categories_articles', // ... ); export const articlesRelations = relations(articles, ({ one, many }) => ({ author: one(users, { fields: [articles.authorId], references: [users.id], }), categoriesArticles: many(categoriesArticles), })); export const categoriesRelations = relations(categories, ({ many }) => ({ categoriesArticles: many(categoriesArticles), })); export const categoriesArticlesRelations = relations( categoriesArticles, ({ one }) => ({ category: one(categories, { fields: [categoriesArticles.categoryId], references: [categories.id], }), article: one(articles, { fields: [categoriesArticles.articleId], references: [articles.id], }), }), ); export const databaseSchema = { articles, articlesRelations, categories, categoriesArticles, categoriesArticlesRelations, categoriesRelations, // ... };Thanks to creating the articlesRelations, categoriesRelations, and categoriesArticlesRelations objects, Drizzle ORM now has all the information it needs to do the join queries for us. We can use this to fetch the data of a particular article with all of their categories. articles.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 ArticlesService { constructor(private readonly drizzleService: DrizzleService) {} async getById(articleId: number) { const article = await this.drizzleService.db.query.articles.findFirst({ with: { author: { with: { address: true, }, }, categoriesArticles: { with: { category: true, }, }, }, where: eq(databaseSchema.articles.id, articleId), }); if (!article) { throw new NotFoundException(); } const categories = article.categoriesArticles.map( ({ category }) => category, ); return { id: article.id, author: article.author, title: article.title, content: article.content, categories, }; } // ... }Since the data of a particular category is nested inside each object in the categoriesArticles array, we need to parse the data to get the array of categories. Fetching all articles from a particular category We can use a similar approach to fetch all articles of a particular category. 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: { categoriesArticles: { with: { article: true, }, }, }, where: eq(databaseSchema.categories.id, categoryId), }); if (!category) { throw new NotFoundException(); } const articles = category.categoriesArticles.map(({ article }) => article); return { id: category.id, name: category.name, articles, }; } // ... }Again, we need to transform the data to take into account that the properties of each article are deeply nested. Summary In this article, we discussed the many-to-many relationship and demonstrated how to implement it in a project using the Drizzle ORM and NestJS. Using articles and categories as an example, we learned how to handle a joining table with the built-in features of Drizzle. This allows us to efficiently manage many-to-many relationships in our projects that use the Drizzle ORM. The post API with NestJS #154. Many-to-many relationships with Drizzle ORM and PostgreSQL appeared first on Marcin Wanago Blog - JavaScript,