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.
1import { Kysely } from 'kysely';
2
3export async function up(database: Kysely<unknown>): Promise<void> {
4 await database.schema
5 .createTable('categories')
6 .addColumn('id', 'serial', (column) => {
7 return column.primaryKey();
8 })
9 .addColumn('name', 'text', (column) => column.notNull())
10 .execute();
11
12 await database.schema
13 .createTable('categories_articles')
14 .addColumn('category_id', 'integer', (column) => {
15 return column.references('categories.id').notNull();
16 })
17 .addColumn('article_id', 'integer', (column) => {
18 return column.references('articles.id').notNull();
19 })
20 .addPrimaryKeyConstraint('primary_key', ['category_id', 'article_id'])
21 .execute();
22}
23
24export async function down(database: Kysely<unknown>): Promise<void> {
25 await database.schema.dropTable('categories').execute();
26 await database.schema.dropTable('categories_articles').execute();
27}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.
1import { Generated } from 'kysely';
2
3export interface CategoriesTable {
4 id: Generated<number>;
5 name: string;
6}1export interface CategoriesArticlesTable {
2 category_id: number;
3 article_id: number;
4}Once we have them, we can alter our Tables interface.
1import { ArticlesTable } from '../articles/articlesTable';
2import { Kysely } from 'kysely';
3import { UsersTable } from '../users/usersTable';
4import { AddressesTable } from '../users/addressesTable';
5import { CategoriesTable } from '../categories/categoriesTable';
6import { CategoriesArticlesTable } from '../categories/categoriesArticlesTable';
7
8interface Tables {
9 articles: ArticlesTable;
10 users: UsersTable;
11 addresses: AddressesTable;
12 categories: CategoriesTable;
13 categories_articles: CategoriesArticlesTable;
14}
15
16export 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:
1{
2 "title": "My first article",
3 "content": "Hello world!",
4 "categoryIds": [1, 2]
5}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.
1SELECT 1 as article_id, unnest(ARRAY[1,2]) AS category_idAbove, 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.
1INSERT INTO categories_articles (
2 article_id, category_id
3)
4 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.
1import { Article, ArticleModelData } from './article.model';
2
3export interface ArticleWithCategoryIdsModelData extends ArticleModelData {
4 category_ids?: number[];
5}
6
7export class ArticleWithCategoryIds extends Article {
8 categoryIds: number[];
9 constructor(articleData: ArticleWithCategoryIdsModelData) {
10 super(articleData);
11 this.categoryIds = articleData.category_ids ?? [];
12 }
13}Now, we can add a new method to our repository that creates the article and connects it to categories with a single query.
1import { Database } from '../database/database';
2import { Injectable } from '@nestjs/common';
3import { ArticleDto } from './dto/article.dto';
4import { sql } from 'kysely';
5import { ArticleWithCategoryIds } from './articleWithCategoryIds.model';
6
7@Injectable()
8export class ArticlesRepository {
9 constructor(private readonly database: Database) {}
10
11 async createWithCategories(data: ArticleDto, authorId: number) {
12 const databaseResponse = await this.database
13 .with('created_article', (database) => {
14 return database
15 .insertInto('articles')
16 .values({
17 title: data.title,
18 article_content: data.content,
19 author_id: authorId,
20 })
21 .returningAll();
22 })
23 .with('created_relationships', (database) => {
24 return database
25 .insertInto('categories_articles')
26 .columns(['article_id', 'category_id'])
27 .expression((expressionBuilder) => {
28 return expressionBuilder
29 .selectFrom('created_article')
30 .select([
31 'created_article.id as article_id',
32 sql`unnest(${data.categoryIds}::int[])`.as('category_id'),
33 ]);
34 });
35 })
36 .selectFrom('created_article')
37 .select(['id', 'title', 'article_content', 'author_id'])
38 .executeTakeFirstOrThrow();
39
40 return new ArticleWithCategoryIds({
41 ...databaseResponse,
42 category_ids: data.categoryIds,
43 });
44 }
45
46 // ...
47}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.
1import { Article, ArticleModelData } from './article.model';
2import { User } from '../users/user.model';
3import { Type } from 'class-transformer';
4
5interface ArticleWithDetailsModelData extends ArticleModelData {
6 user_id: number;
7 user_email: string;
8 user_name: string;
9 user_password: string;
10 address_id: number | null;
11 address_street: string | null;
12 address_city: string | null;
13 address_country: string | null;
14 category_ids: number[] | null;
15}
16export class ArticleWithDetailsModel extends Article {
17 @Type(() => User)
18 author: User;
19 categoryIds: number[];
20 constructor(articleData: ArticleWithDetailsModelData) {
21 super(articleData);
22 this.author = new User({
23 id: articleData.user_id,
24 email: articleData.user_email,
25 name: articleData.user_name,
26 password: articleData.user_password,
27 address_city: articleData.address_city,
28 address_country: articleData.address_country,
29 address_street: articleData.address_street,
30 address_id: articleData.address_id,
31 });
32 this.categoryIds = articleData.category_ids ?? [];
33 }
34}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.
1import { Database } from '../database/database';
2import { Injectable } from '@nestjs/common';
3import { ArticleWithDetailsModel } from './articleWithDetails.model';
4
5@Injectable()
6export class ArticlesRepository {
7 constructor(private readonly database: Database) {}
8
9 async getWithDetails(id: number) {
10 const articleResponse = await this.database
11 .selectFrom('articles')
12 .where('articles.id', '=', id)
13 .innerJoin('users', 'users.id', 'articles.author_id')
14 .leftJoin('addresses', 'addresses.id', 'users.address_id')
15 .select([
16 'articles.id as id',
17 'articles.article_content as article_content',
18 'articles.title as title',
19 'articles.author_id as author_id',
20 'users.id as user_id',
21 'users.email as user_email',
22 'users.name as user_name',
23 'users.password as user_password',
24 'addresses.id as address_id',
25 'addresses.city as address_city',
26 'addresses.street as address_street',
27 'addresses.country as address_country',
28 ])
29 .executeTakeFirst();
30
31 const categoryIdsResponse = await this.database
32 .selectFrom('categories_articles')
33 .where('article_id', '=', id)
34 .selectAll()
35 .execute();
36
37 const categoryIds = categoryIdsResponse.map(
38 (response) => response.category_id,
39 );
40
41 if (articleResponse) {
42 return new ArticleWithDetailsModel({
43 ...articleResponse,
44 category_ids: categoryIds,
45 });
46 }
47 }
48
49 // ...
50}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.
1SELECT article_id
2FROM categories_articles
3WHERE category_id = 1Since we now know the IDs of all articles, we can use the JOIN statement to match them with the rows from the articles table.
1SELECT articles.id AS article_id, articles.title AS article_title, articles.article_content AS article_content, articles.author_id AS author_id
2FROM categories_articles
3JOIN articles ON articles.id=categories_articles.article_id
4WHERE category_id = 1Let’s create a new model suitable for the above data.
1import { Category, CategoryModelData } from './category.model';
2import { Article, ArticleModelData } from '../articles/article.model';
3
4export interface CategoryWithArticlesModelData extends CategoryModelData {
5 articles: ArticleModelData[];
6}
7class CategoryWithArticles extends Category {
8 articles: Article[];
9 constructor(categoryData: CategoryWithArticlesModelData) {
10 super(categoryData);
11 this.articles = categoryData.articles.map((articleData) => {
12 return new Article(articleData);
13 });
14 }
15}
16
17export 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.
1import { Database } from '../database/database';
2import { Injectable } from '@nestjs/common';
3import CategoryWithArticles from './categoryWithArticles.model';
4
5@Injectable()
6export class CategoriesRepository {
7 constructor(private readonly database: Database) {}
8
9 async getWithArticles(categoryId: number) {
10 const categoryResponse = await this.database
11 .selectFrom('categories')
12 .where('id', '=', categoryId)
13 .selectAll()
14 .executeTakeFirst();
15
16 if (!categoryResponse) {
17 return;
18 }
19
20 const articlesResponse = await this.database
21 .selectFrom('categories_articles')
22 .innerJoin('articles', 'articles.id', 'categories_articles.article_id')
23 .where('category_id', '=', categoryId)
24 .select([
25 'articles.id as id',
26 'articles.title as title',
27 'articles.article_content as article_content',
28 'articles.author_id as author_id',
29 ])
30 .execute();
31
32 return new CategoryWithArticles({
33 ...categoryResponse,
34 articles: articlesResponse,
35 });
36 }
37
38 // ...
39}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!