Many-to-many relationships with Kysely and PostgreSQL

August 28, 2023

Implementing relationships across tables is a crucial aspect of working with SQL databases. So far, this series covers using Kysely to design simple relationships such as one-to-one and many-to-one. This article looks into many-to-many, which is a slightly more advanced relationship.

Check out this repository if you want to see the full code from this article.

The idea behind the many-to-many relationship#

We need to implement a many-to-many relationship if multiple records from one table relate to multiple records in another table. A very good example is a connection between categories and articles. A particular category can be related to various articles. On the other hand, a single article can be published under multiple categories. For example, the article you are reading falls both under the SQL and JavaScript categories.

So far, when working with Kysely, we implemented the one-to-one and many-to-one relationships. We used a simple column with a foreign key matching a row from the related table to do that.

The design becomes more complex when we want to connect a particular article to many categories. We shouldn’t put multiple IDs into the category_id column. To deal with this challenge, we need to create a joining table.

By creating the categories_articles, we can store the relationships between particular articles and categories.

Implementing the many-to-many relationship#

The first step to implementing the many-to-many relationship with Kysely is to create a new migration.

20230827204025_add_categories_table.ts#

import { Kysely } from "kysely"
 
export async function up(database: Kysely<unknown>): Promise<void> {
  await database.schema
    .createTable("categories")
    .addColumn("id", "serial", (column) => {
      return column.primaryKey()
    })
    .addColumn("name", "text", (column) => column.notNull())
    .execute()
  await database.schema
    .createTable("categories_articles")
    .addColumn("category_id", "integer", (column) => {
      return column.references("categories.id").notNull()
    })
    .addColumn("article_id", "integer", (column) => {
      return column.references("articles.id").notNull()
    })
    .addPrimaryKeyConstraint("primary_key", ["category_id", "article_id"])
    .execute()
}
export async function down(database: Kysely<unknown>): Promise<void> {
  await database.schema.dropTable("categories").execute()
  await database.schema.dropTable("categories_articles").execute()
}

While we could add the id column to our categories_articles table, it is unnecessary. Instead, we specify a composite primary key consisting of the category_id and article_id. This approach has more advantages than just saving disk space. Since PostgreSQL ensures a particular primary key is unique, we cannot assign an article to the category multiple times.

Besides adding a migration, we also need to create additional interfaces.

categoriesTable.ts#

import { Generated } from "kysely"
 
export interface CategoriesTable {
  id: Generated<number>
  name: string
}

categoriesArticlesTable.ts#

export interface CategoriesArticlesTable {
  category_id: number;
  article_id: number;
}

Once we have them, we can alter our Tables interface.

database.ts#

import { Kysely } from "kysely"
 
import { ArticlesTable } from "../articles/articlesTable"
import { CategoriesArticlesTable } from "../categories/categoriesArticlesTable"
import { CategoriesTable } from "../categories/categoriesTable"
import { AddressesTable } from "../users/addressesTable"
import { UsersTable } from "../users/usersTable"
 
interface Tables {
  articles: ArticlesTable
  users: UsersTable
  addresses: AddressesTable
  categories: CategoriesTable
  categories_articles: CategoriesArticlesTable
}
export class Database extends Kysely<Tables> {}

Connecting articles to categories#

An article can belong to multiple different categories. Therefore, we should be able to handle the following data format when creating a new article:

{
  "title": "My first article",
  "content": "Hello world!",
  "categoryIds": [1, 2]
}

The above categoryIds array indicates that we want to add two rows to the categories_articles table.

One way of inserting multiple rows into a particular table is with a SELECT query.

SELECT 1 as article_id, unnest(ARRAY[1,2]) AS category_id

Above, we use the unnest function built into PostgreSQL to expand an array to a set of rows. We can now combine it with the INSERT query to save the results of the SELECT into the database.

INSERT INTO categories_articles (
  article_id, category_id
)
  SELECT 1 as article_id, unnest(ARRAY[1,2]) AS category_id

Let’s use the above knowledge to create an article and connect it to multiple categories in the same query. First, let’s create a model for an article with the category ids.

articleWithCategoryIds.model.ts#

import { Article, ArticleModelData } from "./article.model"
 
export interface ArticleWithCategoryIdsModelData extends ArticleModelData {
  category_ids?: number[]
}
export class ArticleWithCategoryIds extends Article {
  categoryIds: number[]
  constructor(articleData: ArticleWithCategoryIdsModelData) {
    super(articleData)
    this.categoryIds = articleData.category_ids ?? []
  }
}

Now, we can add a new method to our repository that creates the article and connects it to categories with a single query.

articles.repository.ts#

import { Database } from "../database/database"
import { Injectable } from "@nestjs/common"
import { ArticleDto } from "./dto/article.dto"
import { sql } from "kysely"
import { ArticleWithCategoryIds } from "./articleWithCategoryIds.model"
@Injectable()
export class ArticlesRepository {
  constructor(private readonly database: Database) {}
  async createWithCategories(data: ArticleDto, authorId: number) {
    const databaseResponse = await this.database
      .with("created_article", (database) => {
        return database
          .insertInto("articles")
          .values({
            title: data.title,
            article_content: data.content,
            author_id: authorId,
          })
          .returningAll()
      })
      .with("created_relationships", (database) => {
        return database
          .insertInto("categories_articles")
          .columns(["article_id", "category_id"])
          .expression((expressionBuilder) => {
            return expressionBuilder
              .selectFrom("created_article")
              .select([
                "created_article.id as article_id",
                sql`unnest(${data.categoryIds}::int[])`.as("category_id"),
              ])
          })
      })
      .selectFrom("created_article")
      .select(["id", "title", "article_content", "author_id"])
      .executeTakeFirstOrThrow()
    return new ArticleWithCategoryIds({
      ...databaseResponse,
      category_ids: data.categoryIds,
    })
  } // ...
}

Fetching the category IDs of an article#

Whenever we fetch the details of a particular article, we can attach the IDs of the related categories. The first step would be to prepare an appropriate model.

articleWithDetails.model.ts#

import { Article, ArticleModelData } from "./article.model"
import { User } from "../users/user.model"
import { Type } from "class-transformer"
interface ArticleWithDetailsModelData extends ArticleModelData {
  user_id: number
  user_email: string
  user_name: string
  user_password: string
  address_id: number | null
  address_street: string | null
  address_city: string | null
  address_country: string | null
  category_ids: number[] | null
}
export class ArticleWithDetailsModel extends Article {
  @Type(() => User)
  author: User
  categoryIds: number[]
  constructor(articleData: ArticleWithDetailsModelData) {
    super(articleData)
    this.author = new User({
      id: articleData.user_id,
      email: articleData.user_email,
      name: articleData.user_name,
      password: articleData.user_password,
      address_city: articleData.address_city,
      address_country: articleData.address_country,
      address_street: articleData.address_street,
      address_id: articleData.address_id,
    })
    this.categoryIds = articleData.category_ids ?? []
  }
}

You can go a step further and include the details of each category.

Now, we can make a separate query to fetch the categories related to a specific article.

articles.repository.ts#

import { Database } from "../database/database"
import { Injectable } from "@nestjs/common"
import { ArticleWithDetailsModel } from "./articleWithDetails.model"
@Injectable()
export class ArticlesRepository {
  constructor(private readonly database: Database) {}
  async getWithDetails(id: number) {
    const articleResponse = await this.database
      .selectFrom("articles")
      .where("articles.id", "=", id)
      .innerJoin("users", "users.id", "articles.author_id")
      .leftJoin("addresses", "addresses.id", "users.address_id")
      .select([
        "articles.id as id",
        "articles.article_content as article_content",
        "articles.title as title",
        "articles.author_id as author_id",
        "users.id as user_id",
        "users.email as user_email",
        "users.name as user_name",
        "users.password as user_password",
        "addresses.id as address_id",
        "addresses.city as address_city",
        "addresses.street as address_street",
        "addresses.country as address_country",
      ])
      .executeTakeFirst()
    const categoryIdsResponse = await this.database
      .selectFrom("categories_articles")
      .where("article_id", "=", id)
      .selectAll()
      .execute()
    const categoryIds = categoryIdsResponse.map((response) => response.category_id)
    if (articleResponse) {
      return new ArticleWithDetailsModel({
        ...articleResponse,
        category_ids: categoryIds,
      })
    }
  } // ...
}

The above queries would benefit from wrapping them in a transaction. This is a broad topic that deserves a separate article.

Fetching all articles from a certain category#

Another feature that might be needed is getting a list of all articles from a particular category. To achieve it, we need to join the data from the articles table with categories_articles. First, we need to retrieve all article IDs from a specific category.

SELECT article_id
FROM categories_articles
WHERE category_id = 1

Since we now know the IDs of all articles, we can use the JOIN statement to match them with the rows from the articles table.

SELECT articles.id AS article_id, articles.title AS article_title, articles.article_content AS article_content, articles.author_id AS author_id
FROM categories_articles
JOIN articles ON articles.id=categories_articles.article_id
WHERE category_id = 1

Let’s create a new model suitable for the above data.

categoryWithArticles.model.ts#

import { Article, ArticleModelData } from "../articles/article.model"
import { Category, CategoryModelData } from "./category.model"
 
export interface CategoryWithArticlesModelData extends CategoryModelData {
  articles: ArticleModelData[]
}
class CategoryWithArticles extends Category {
  articles: Article[]
  constructor(categoryData: CategoryWithArticlesModelData) {
    super(categoryData)
    this.articles = categoryData.articles.map((articleData) => {
      return new Article(articleData)
    })
  }
}
export default CategoryWithArticles

Now, we can use all of the above knowledge to:

retrieve the data of a specific category,
match it with the articles,
fit the data into the new model.

categories.response.ts#

import { Database } from "../database/database"
import { Injectable } from "@nestjs/common"
import CategoryWithArticles from "./categoryWithArticles.model"
@Injectable()
export class CategoriesRepository {
  constructor(private readonly database: Database) {}
  async getWithArticles(categoryId: number) {
    const categoryResponse = await this.database
      .selectFrom("categories")
      .where("id", "=", categoryId)
      .selectAll()
      .executeTakeFirst()
    if (!categoryResponse) {
      return
    }
    const articlesResponse = await this.database
      .selectFrom("categories_articles")
      .innerJoin("articles", "articles.id", "categories_articles.article_id")
      .where("category_id", "=", categoryId)
      .select([
        "articles.id as id",
        "articles.title as title",
        "articles.article_content as article_content",
        "articles.author_id as author_id",
      ])
      .execute()
    return new CategoryWithArticles({
      ...categoryResponse,
      articles: articlesResponse,
    })
  } // ...
}

Summary#

In this article, we’ve explained the many-to-many relationship and implemented it in a project with Kysely and NestJS. When doing that, we used an example of articles and categories and learned how to manage a joining table and insert multiple records into the database with one query.

Some of the queries from this article could have been wrapped in a transaction, which deserves a separate article. Stay tuned!