API with NestJS #121. Many-to-one relationships with PostgreSQL and Kysely

August 21, 2023

This entry is part 121 of 166 in the API with NestJS

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.

1 2	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

1 2	ADMIN_EMAIL=admin@admin.com ADMIN_PASSWORD=strongPassword

Now 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<EnvironmentVariables>();

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<EnvironmentVariables>();

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<unknown>): Promise<void> {

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<number>;

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 GetArticlesByAuthorQuery {

@IsNumber()

@Min(1)

@IsOptional()

@Transform(({ value }) => Number(value))

authorId?: number;

}

We can now use the above class in our controller.

articles.controller.ts

import { Controller, Get, Query } from '@nestjs/common';

import { ArticlesService } from './articles.service';

import { GetArticlesByAuthorQuery } from './getArticlesByAuthorQuery';

@Controller('articles')

export class ArticlesController {

constructor(private readonly articlesService: ArticlesService) {}

@Get()

getAll(@Query() { authorId }: GetArticlesByAuthorQuery) {

return this.articlesService.getAll(authorId);

}

// ...

}

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.

articleWithAuthor.model.ts

import { Article, ArticleModelData } from './article.model';

import { User } from '../users/user.model';

interface ArticleWithAuthorModelData 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;

}

export class ArticleWithAuthor extends Article {

author: User;

constructor(articleData: ArticleWithAuthorModelData) {

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,

});

}

We need to perform a join to fetch the author’s data together with the article.

SELECT

articles.id AS id, articles.title AS title, articles.article_content AS article_content, 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

FROM articles

JOIN users ON articles.author_id = users.id

WHERE articles.id=$1

The 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.

articles.service.ts

import { Database } from '../database/database';

import { Injectable } from '@nestjs/common';

import { ArticleWithAuthorModel } from './articleWithAuthor.model';

@Injectable()

export class ArticlesRepository {

constructor(private readonly database: Database) {}

async getWithAuthor(id: number) {

const databaseResponse = 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();

if (databaseResponse) {

return new ArticleWithAuthorModel(databaseResponse);

}

// ...

}

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!

Series Navigation<< API with NestJS #120. One-to-one relationships with the Kysely query builderAPI with NestJS #122. Many-to-many relationships with Kysely and PostgreSQL >>