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.
1ALTER TABLE articles
2ADD COLUMN author_id int REFERENCES users(id) NOT NULLERROR: 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.
1ADMIN_EMAIL=admin@admin.com
2ADMIN_PASSWORD=strongPasswordNow we can add the seed file to our migrations.
1import { config } from 'dotenv';
2import * as bcrypt from 'bcrypt';
3import { Database } from '../database/database';
4import { ConfigService } from '@nestjs/config';
5import { EnvironmentVariables } from '../types/environmentVariables';
6import { Migration } from 'kysely';
7
8config();
9
10const configService = new ConfigService<EnvironmentVariables>();
11
12export const up: Migration['up'] = async (database) => {
13 const email = configService.get('ADMIN_EMAIL');
14 const password = configService.get('ADMIN_PASSWORD');
15 const hashedPassword = await bcrypt.hash(password, 10);
16
17 await database
18 .insertInto('users')
19 .values({
20 email,
21 password: hashedPassword,
22 name: 'Admin',
23 })
24 .execute();
25};
26
27export const down: Migration['up'] = async (database) => {
28 const email = configService.get('ADMIN_EMAIL');
29
30 await database.deleteFrom('users').where('email', '=', email).execute();
31};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.
1import { Kysely, Migration } from 'kysely';
2import { config } from 'dotenv';
3import { ConfigService } from '@nestjs/config';
4import { EnvironmentVariables } from '../types/environmentVariables';
5
6config();
7
8const configService = new ConfigService<EnvironmentVariables>();
9
10export const up: Migration['up'] = async (database) => {
11 const email = configService.get('ADMIN_EMAIL');
12
13 const adminDatabaseResponse = await database
14 .selectFrom('users')
15 .where('email', '=', email)
16 .selectAll()
17 .executeTakeFirstOrThrow();
18
19 const adminId = adminDatabaseResponse.id;
20
21 await database.schema
22 .alterTable('articles')
23 .addColumn('author_id', 'integer', (column) => {
24 return column.references('users.id');
25 })
26 .execute();
27
28 await database
29 .updateTable('articles')
30 .set({
31 author_id: adminId,
32 })
33 .execute();
34
35 await database.schema
36 .alterTable('articles')
37 .alterColumn('author_id', (column) => {
38 return column.setNotNull();
39 })
40 .execute();
41};
42
43export async function down(database: Kysely<unknown>): Promise<void> {
44 await database.schema
45 .alterTable('articles')
46 .dropColumn('author_id')
47 .execute();
48}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:
1await database.schema
2 .alterTable('users')
3 .addColumn('address_id', 'integer', (column) => {
4 return column.unique().references('addresses.id');
5 })
6 .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:
1await database.schema
2 .alterTable('articles')
3 .addColumn('author_id', 'integer', (column) => {
4 return column.references('users.id');
5 })
6 .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.
1import { Generated } from 'kysely';
2
3export interface ArticlesTable {
4 id: Generated<number>;
5 title: string;
6 article_content: string;
7 author_id: number;
8}Let’s also add the author’s id to the article’s model.
1interface ArticleModelData {
2 id: number;
3 title: string;
4 article_content: string;
5 author_id: number;
6}
7
8export class Article {
9 id: number;
10 title: string;
11 content: string;
12 authorId: number;
13 constructor({ id, title, article_content, author_id }: ArticleModelData) {
14 this.id = id;
15 this.title = title;
16 this.content = article_content;
17 this.authorId = author_id;
18 }
19}We must also handle the author_id column when inserting the article into our database.
1import { Database } from '../database/database';
2import { Article } from './article.model';
3import { Injectable } from '@nestjs/common';
4import ArticleDto from './dto/article.dto';
5
6@Injectable()
7export class ArticlesRepository {
8 constructor(private readonly database: Database) {}
9
10 async create(data: ArticleDto, authorId: number) {
11 const databaseResponse = await this.database
12 .insertInto('articles')
13 .values({
14 title: data.title,
15 article_content: data.content,
16 author_id: authorId,
17 })
18 .returningAll()
19 .executeTakeFirstOrThrow();
20
21 return new Article(databaseResponse);
22 }
23
24 // ...
25}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
1import { Controller, Post, Body, UseGuards, Req } from '@nestjs/common';
2import ArticleDto from './dto/article.dto';
3import { ArticlesService } from './articles.service';
4import JwtAuthenticationGuard from '../authentication/jwt-authentication.guard';
5import { RequestWithUser } from '../authentication/requestWithUser.interface';
6
7@Controller('articles')
8export class ArticlesController {
9 constructor(private readonly articlesService: ArticlesService) {}
10
11 @Post()
12 @UseGuards(JwtAuthenticationGuard)
13 create(@Body() data: ArticleDto, @Req() request: RequestWithUser) {
14 return this.articlesService.create(data, request.user.id);
15 }
16
17 // ...
18}Fetching articles of a particular user
We can query the articles written by a particular author using the where function.
1import { Database } from '../database/database';
2import { Article } from './article.model';
3import { Injectable } from '@nestjs/common';
4
5@Injectable()
6export class ArticlesRepository {
7 constructor(private readonly database: Database) {}
8
9 async getByAuthorId(authorId: number) {
10 const databaseResponse = await this.database
11 .selectFrom('articles')
12 .where('author_id', '=', authorId)
13 .selectAll()
14 .execute();
15
16 return databaseResponse.map((articleData) => new Article(articleData));
17 }
18
19 // ...
20}Let’s use a different method from our repository based on whether the author’s id is provided.
1import { Injectable } from '@nestjs/common';
2import { ArticlesRepository } from './articles.repository';
3
4@Injectable()
5export class ArticlesService {
6 constructor(private readonly articlesRepository: ArticlesRepository) {}
7
8 getAll(authorId?: number) {
9 if (authorId) {
10 return this.articlesRepository.getByAuthorId(authorId);
11 }
12 return this.articlesRepository.getAll();
13 }
14
15 // ...
16}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.
1import { Transform } from 'class-transformer';
2import { IsNumber, IsOptional, Min } from 'class-validator';
3
4export class GetArticlesByAuthorQuery {
5 @IsNumber()
6 @Min(1)
7 @IsOptional()
8 @Transform(({ value }) => Number(value))
9 authorId?: number;
10}We can now use the above class in our controller.
1import { Controller, Get, Query } from '@nestjs/common';
2import { ArticlesService } from './articles.service';
3import { GetArticlesByAuthorQuery } from './getArticlesByAuthorQuery';
4
5@Controller('articles')
6export class ArticlesController {
7 constructor(private readonly articlesService: ArticlesService) {}
8
9 @Get()
10 getAll(@Query() { authorId }: GetArticlesByAuthorQuery) {
11 return this.articlesService.getAll(authorId);
12 }
13
14 // ...
15}Combining the data from both tables
It is common to want to combine the data from more than one table. Let’s create a model containing detailed information about the article and its author.
1import { Article, ArticleModelData } from './article.model';
2import { User } from '../users/user.model';
3
4interface ArticleWithAuthorModelData extends ArticleModelData {
5 user_id: number;
6 user_email: string;
7 user_name: string;
8 user_password: string;
9 address_id: number | null;
10 address_street: string | null;
11 address_city: string | null;
12 address_country: string | null;
13}
14export class ArticleWithAuthor extends Article {
15 author: User;
16 constructor(articleData: ArticleWithAuthorModelData) {
17 super(articleData);
18 this.author = new User({
19 id: articleData.user_id,
20 email: articleData.user_email,
21 name: articleData.user_name,
22 password: articleData.user_password,
23 address_city: articleData.address_city,
24 address_country: articleData.address_country,
25 address_street: articleData.address_street,
26 address_id: articleData.address_id,
27 });
28 }
29}We need to perform a join to fetch the author’s data together with the article.
1SELECT
2 articles.id AS id, articles.title AS title, articles.article_content AS article_content, articles.author_id as author_id,
3 users.id AS user_id, users.email AS user_email, users.name AS user_name, users.password AS user_password
4 FROM articles
5JOIN users ON articles.author_id = users.id
6WHERE articles.id=$1The default type of join is the inner join. It returns records that have matching values in both tables. Since every article requires an author, it works as expected.
In the previous article, we implemented the outer join when fetching the user together with the address since the address is optional. Outer joins preserve the rows that don’t have matching values in both tables.
We must perform two joins to query the article, author, and possible address.
1import { Database } from '../database/database';
2import { Injectable } from '@nestjs/common';
3import { ArticleWithAuthorModel } from './articleWithAuthor.model';
4
5@Injectable()
6export class ArticlesRepository {
7 constructor(private readonly database: Database) {}
8
9 async getWithAuthor(id: number) {
10 const databaseResponse = 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 if (databaseResponse) {
32 return new ArticleWithAuthorModel(databaseResponse);
33 }
34 }
35
36 // ...
37}Summary
In this article, we’ve explained the one-to-many relationship in SQL and implemented it using Kysely and NestJS. When doing that, we had to make a SQL query that used more than one join. We also learned how to write a migration that adds a new non-nullable column and how to avoid errors when running it on an existing database. There is still more to cover regarding relationships with PostgreSQL and Kysely, so stay tuned!