Designing relationships is one of the crucial aspects of working with SQL databases. In this article, we continue using Kysely with NestJS and implement many-to-one relationships. Check out this repository if you want to see the full code from this article. Introducing the many-to-one relationship When implementing the many-to-one relationship, a row from the first table is connected to multiple rows in the second table. What’s essential, a row from the second table can connect to just one row from the first table. An example is an article with a single author, while the user can be an author of many articles. A way to implement it is to save the author’s id in the articles table as a foreign key. A foreign key is a value that matches a column from a different table. Whenever we create a foreign key in our database, PostgreSQL defines the foreign key constraint to ensure the consistency of our data. Thanks to that, it prevents us from having an author_id value that refers to a user that does not exist. We can’t: create an article and provide the author_id that points to a user that cannot be found in the users table, update an existing article and change the author_id to match a user that does not exist, delete a user with an id used in the author_id column we would have to delete the article first or change its author alternatively, we could use the CASCADE option to force PostgreSQL to delete all articles the deleted user is an author of Defining the many-to-one relationship with Kysely In one of the previous parts of this series, we learned how to write SQL migrations when using Kysely. This time, we want to add the author_id column that is not nullable. Unfortunately, we might already have some articles in our database, and adding a new non-nullable column without a default value would cause an error.ALTER TABLE articles ADD COLUMN author_id int REFERENCES users(id) NOT NULL ERROR: column “author_id” of relation “articles” contains null values To solve the above problem, we can provide a default value for the author_id column. To do that, we need to have a default user. Let’s add a seed file to our migrations directory. Creating seed files is a way to populate our database with initial data. Adding the seed file First, let’s add the email and password of the admin to the environment variables. .env ADMIN_EMAIL=admin@admin.com ADMIN_PASSWORD=strongPasswordNow we can add the seed file to our migrations. 20230817223154_insert_admin.ts import { config } from 'dotenv'; import * as bcrypt from 'bcrypt'; import { Database } from '../database/database'; import { ConfigService } from '@nestjs/config'; import { EnvironmentVariables } from '../types/environmentVariables'; import { Migration } from 'kysely'; config(); const configService = new ConfigService(); export const up: Migration['up'] = async (database) => { const email = configService.get('ADMIN_EMAIL'); const password = configService.get('ADMIN_PASSWORD'); const hashedPassword = await bcrypt.hash(password, 10); await database .insertInto('users') .values({ email, password: hashedPassword, name: 'Admin', }) .execute(); }; export const down: Migration['up'] = async (database) => { const email = configService.get('ADMIN_EMAIL'); await database.deleteFrom('users').where('email', '=', email).execute(); }; Creating the migration When writing the migration file that adds the author_id column, we can implement the following approach: get the id of the admin, add the author_id column as nullable, set the value in the author_id column for articles that don’t have it, make the author_id column non-nullable. 20230817230950_add_author_id.ts import { Kysely, Migration } from 'kysely'; import { config } from 'dotenv'; import { ConfigService } from '@nestjs/config'; import { EnvironmentVariables } from '../types/environmentVariables'; config(); const configService = new ConfigService(); export const up: Migration['up'] = async (database) => { const email = configService.get('ADMIN_EMAIL'); const adminDatabaseResponse = await database .selectFrom('users') .where('email', '=', email) .selectAll() .executeTakeFirstOrThrow(); const adminId = adminDatabaseResponse.id; await database.schema .alterTable('articles') .addColumn('author_id', 'integer', (column) => { return column.references('users.id'); }) .execute(); await database .updateTable('articles') .set({ author_id: adminId, }) .execute(); await database.schema .alterTable('articles') .alterColumn('author_id', (column) => { return column.setNotNull(); }) .execute(); }; export async function down(database: Kysely): Promise { await database.schema .alterTable('articles') .dropColumn('author_id') .execute(); } Many-to-one vs one-to-one In the previous part of this series, we’ve covered the one-to-one relationship. When writing the migration, we’ve run the following query:await database.schema .alterTable('users') .addColumn('address_id', 'integer', (column) => { return column.unique().references('addresses.id'); }) .execute();Adding the unique constraint ensures that a particular address belongs to only one user. On the contrary, when adding the author_id column, we ran the query without adding the unique constraint:await database.schema .alterTable('articles') .addColumn('author_id', 'integer', (column) => { return column.references('users.id'); }) .execute();Thanks to the above approach, multiple articles can share the same author. Creating an article with the author The next thing we need to do is to modify the TypeScript definition of our articles table. articlesTable.ts import { Generated } from 'kysely'; export interface ArticlesTable { id: Generated; title: string; article_content: string; author_id: number; }Let’s also add the author’s id to the article’s model. articles.model.ts interface ArticleModelData { id: number; title: string; article_content: string; author_id: number; } export class Article { id: number; title: string; content: string; authorId: number; constructor({ id, title, article_content, author_id }: ArticleModelData) { this.id = id; this.title = title; this.content = article_content; this.authorId = author_id; } }We must also handle the author_id column when inserting the article into our database. articles.service.ts import { Database } from '../database/database'; import { Article } from './article.model'; import { Injectable } from '@nestjs/common'; import ArticleDto from './dto/article.dto'; @Injectable() export class ArticlesRepository { constructor(private readonly database: Database) {} async create(data: ArticleDto, authorId: number) { const databaseResponse = await this.database .insertInto('articles') .values({ title: data.title, article_content: data.content, author_id: authorId, }) .returningAll() .executeTakeFirstOrThrow(); return new Article(databaseResponse); } // ... }When figuring out who is the author of the new article, we don’t expect the information to be provided directly through the body of the POST request. Instead, we get this information by decoding the JWT token. If you want to read more about JWT tokens and authentication, check out API with NestJS #3. Authenticating users with bcrypt, Passport, JWT, and cookies articles.controller.ts import { Controller, Post, Body, UseGuards, Req } from '@nestjs/common'; import ArticleDto from './dto/article.dto'; import { ArticlesService } from './articles.service'; import JwtAuthenticationGuard from '../authentication/jwt-authentication.guard'; import { RequestWithUser } from '../authentication/requestWithUser.interface'; @Controller('articles') export class ArticlesController { constructor(private readonly articlesService: ArticlesService) {} @Post() @UseGuards(JwtAuthenticationGuard) create(@Body() data: ArticleDto, @Req() request: RequestWithUser) { return this.articlesService.create(data, request.user.id); } // ... } Fetching articles of a particular user We can query the articles written by a particular author using the where function. articles.repository.ts import { Database } from '../database/database'; import { Article } from './article.model'; import { Injectable } from '@nestjs/common'; @Injectable() export class ArticlesRepository { constructor(private readonly database: Database) {} async getByAuthorId(authorId: number) { const databaseResponse = await this.database .selectFrom('articles') .where('author_id', '=', authorId) .selectAll() .execute(); return databaseResponse.map((articleData) => new Article(articleData)); } // ... }Let’s use a different method from our repository based on whether the author’s id is provided. articles.service.ts import { Injectable } from '@nestjs/common'; import { ArticlesRepository } from './articles.repository'; @Injectable() export class ArticlesService { constructor(private readonly articlesRepository: ArticlesRepository) {} getAll(authorId?: number) { if (authorId) { return this.articlesRepository.getByAuthorId(authorId); } return this.articlesRepository.getAll(); } // ... }A good way to use the above feature through our REST API is with a query parameter. Let’s define a class that validates if it is provided using the correct format. getArticlesByAuthorQuery.service.ts import { Transform } from 'class-transformer'; import { IsNumber, IsOptional, Min } from 'class-validator'; export class GetArticlesByAut