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): Promise { 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): Promise { 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; 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 { ArticlesTable } from '../articles/articlesTable'; import { Kysely } from 'kysely'; import { UsersTable } from '../users/usersTable'; import { AddressesTable } from '../users/addressesTable'; import { CategoriesTable } from '../categories/categoriesTable'; import { CategoriesArticlesTable } from '../categories/categoriesArticlesTable'; interface Tables { articles: ArticlesTable; users: UsersTable; addresses: AddressesTable; categories: CategoriesTable; categories_articles: CategoriesArticlesTable; } export class Database extends Kysely {} 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_idLet’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, }); } } // .