API with NestJS #7. Creating relationships with Postgres and TypeORM

June 22, 2020

When we build an application, we create many entities. They often somehow relate to each other, and defining such relationships is an essential part of designing a database. In this article, we go through what is a relationship in the context of a Postgres database and how do we work with them using TypeORM and NestJS.

The relational databases have been around for quite some time and work great with structured data. They do so by organizing the data into tables and linking them to each other. When running various SQL queries, we can join the tables and extract meaningful information. There are a few different types of relationships, and today we go through them with the use of examples.

We’ve also gone through it in the TypeScript Express series. The below article acts as a recap of what we can get from there. This time we also look more into the SQL queries that TypeORM generates

You can find all of the code from this series in this repository.

One-to-one

With the one-to-one relationship, the first table has just one matching row in the second table, and vice versa.

The most straightforward example would be adding an address entity.

users/address.entity.ts

import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';

@Entity()

class Address {

@PrimaryGeneratedColumn()

public id: number;

@Column()

public street: string;

@Column()

public city: string;

@Column()

public country: string;

}

export default Address;

Let’s assume that one address can be linked to just one user. Also, a user can’t have more than one address.

To implement the above, we need a one-to-one relationship. When using TypeORM, we can create it effortlessly with the use of decorators.

users/user.entity.ts

import { Column, Entity, JoinColumn, OneToOne, PrimaryGeneratedColumn } from 'typeorm';

import { Exclude } from 'class-transformer';

import Address from './address.entity';

@Entity()

class User {

@PrimaryGeneratedColumn()

public id: number;

@Column({ unique: true })

public email: string;

@Column()

public name: string;

@Column()

@Exclude()

public password: string;

@OneToOne(() => Address)

@JoinColumn()

public address: Address;

}

export default User;

Above, we use the @OneToOne() decorator. Its argument is a function that returns the class of the entity that we want to make a relationship with.

The second decorator, the @JoinColumn(), indicates that the User entity owns the relationship. It means that the rows of the User table contain the addressId column that can keep the id of an address. We use it only on one side of the relationship.

We can look into pgAdmin to inspect what TypeORM does to create the desired relationship.

Above, we can see that the addressId is a regular integer column. It has a constraint put onto it that indicates that any value we place into the addressId column needs to match some id in the address table.

The above can be simplified without the CONSTRAINT keyword.

CREATE TABLE user (

// ...

addressId integer REFERENCES address (id)

)

Both ON UPDATE NO ACTION and ON DELETE NO ACTION are a default behavior. They indicate that Postgres will raise an error if we attempt to delete or change the id of an address that is currently in use.

The MATCH SIMPLE refers to a situation when we use more than one column as the foreign key. It means that we allow some of them to be null.

Inverse relationship

Currently, our relationship is unidirectional. It means that only one side of the relationship has information about the other side. We could change that by creating an inverse relationship. By doing so, we make the relationship between the User and the Address bidirectional.

To create the inverse relationship, we need to use the @OneToOne and provide a property that holds the other side of the relationship.

users/address.entity.ts

import { Column, Entity, OneToOne, PrimaryGeneratedColumn } from 'typeorm';

import User from './user.entity';

@Entity()

class Address {

@PrimaryGeneratedColumn()

public id: number;

@Column()

public street: string;

@Column()

public city: string;

@Column()

public country: string;

@OneToOne(() => User, (user: User) => user.address)

public user: User;

}

export default Address;

The crucial thing is that the inverse relationship is a bit of an abstract concept, and it does not create any additional columns in the database.

Storing the information about both sides of the relationship can come in handy. We can easily relate to both sides, for example, to fetch the addresses with users.

getAllAddressesWithUsers() {

return this.addressRepository.find({ relations: ['user'] });

}

If we want our related entities always to be included, we can make our relationship eager.

@OneToOne(() => Address, {

eager: true

})

@JoinColumn()

public address: Address;

Now, every time we fetch users, we also get their addresses. Only one side of the relationship can be eager.

Saving the related entities

Right now, we need to save users and addresses separately and this might not be the most convenient way. Instead, we can turn on the cascade option. Thanks to that, we can save an address while saving a user.

@OneToOne(() => Address, {

eager: true,

cascade: true

})

@JoinColumn()

public address: Address;

One-to-many and many-to-one

The one-to-many and many-to-one is a relationship where a row from the first table can be linked to multiple rows of the second table. Rows from the second table can be linked to just one row of the first table.

The above is a very fitting relationship to implement to posts and users that we’ve defined in the previous parts of this series. Let’s assume that a user can create multiple posts, but a post has just one author.

users/user.entity.ts

import { Column, Entity, JoinColumn, OneToMany, OneToOne, PrimaryGeneratedColumn } from 'typeorm';

import { Exclude } from 'class-transformer';

import Address from './address.entity';

import Post from '../posts/post.entity';

@Entity()

class User {

@PrimaryGeneratedColumn()

public id: number;

@Column({ unique: true })

public email: string;

@Column()

public name: string;

@Column()

@Exclude()

public password: string;

@OneToOne(() => Address, {

eager: true,

cascade: true

})

@JoinColumn()

public address: Address;

@OneToMany(() => Post, (post: Post) => post.author)

public posts: Post[];

}

export default User;

Thanks to using the @OneToMany() decorator, one user can be linked to many posts. We also need to define the other side of this relationship.

import { Column, Entity, ManyToOne, PrimaryGeneratedColumn } from 'typeorm';

import User from '../users/user.entity';

@Entity()

class Post {

@PrimaryGeneratedColumn()

public id: number;

@Column()

public title: string;

@Column()

public content: string;

@Column({ nullable: true })

public category?: string;

@ManyToOne(() => User, (author: User) => author.posts)

public author: User;

}

export default Post;

Thanks to the @ManyToOne() decorator, many posts can be related to one user.

We implemented the authentication in the third part of this series. When a post is created in our API, we have access to the data about the authenticated user. We need to use it to determine the author of the post.

@Post()

@UseGuards(JwtAuthenticationGuard)

async createPost(@Body() post: CreatePostDto, @Req() req: RequestWithUser) {

return this.postsService.createPost(post, req.user);

}

async createPost(post: CreatePostDto, user: User) {

const newPost = await this.postsRepository.create({

...post,

author: user

});

await this.postsRepository.save(newPost);

return newPost;

}

If we want to return a list of the posts with the authors, we can now easily do so.

getAllPosts() {

return this.postsRepository.find({ relations: ['author'] });

}

async getPostById(id: number) {

const post = await this.postsRepository.findOne(id, { relations: ['author'] });

if (post) {

return post;

}

throw new PostNotFoundException(id);

}

async updatePost(id: number, post: UpdatePostDto) {

await this.postsRepository.update(id, post);

const updatedPost = await this.postsRepository.findOne(id, { relations: ['author'] });

if (updatedPost) {

return updatedPost

}

throw new PostNotFoundException(id);

}

If we look into the database, we can see that the side of the relationship that uses ManyToOne() decorator stores the foreign key.

This means that the post stores the id of the author and not the other way around.

Many-to-many

Previously, we added a property called category to our posts. Let’s elaborate on that more.

We would like to be able to define categories reusable across posts. We also want a single post to be able to belong to multiple categories.

The above is a many-to-many relationship. It happens when a row from the first table can link to multiple rows from the second table and the other way around.

categories/category.entity.ts

import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';

@Entity()

class Category {

@PrimaryGeneratedColumn()

public id: number;

@Column()

public name: string;

}

export default Category;

posts/post.entity.ts

import { Column, Entity, JoinTable, ManyToMany, ManyToOne, PrimaryGeneratedColumn } from 'typeorm';

import User from '../users/user.entity';

import Category from '../categories/category.entity';

@Entity()

class Post {

@PrimaryGeneratedColumn()

public id: number;

@Column()

public title: string;

@Column()

public content: string;

@Column({ nullable: true })

public category?: string;

@ManyToOne(() => User, (author: User) => author.posts)

public author: User;

@ManyToMany(() => Category)

@JoinTable()

public categories: Category[];

}

export default Post;

When we use the @ManyToMany() and @JoinTable() decorators, TypeORM set ups an additional table. This way, neither the Post nor Category table stores the data about the relationship.

CREATE TABLE public.post_categories_category

(

"postId" integer NOT NULL,

"categoryId" integer NOT NULL,

CONSTRAINT "PK_91306c0021c4901c1825ef097ce" PRIMARY KEY ("postId", "categoryId"),

CONSTRAINT "FK_93b566d522b73cb8bc46f7405bd" FOREIGN KEY ("postId")

REFERENCES public.post (id) MATCH SIMPLE

ON UPDATE NO ACTION

ON DELETE CASCADE,

CONSTRAINT "FK_a5e63f80ca58e7296d5864bd2d3" FOREIGN KEY ("categoryId")

REFERENCES public.category (id) MATCH SIMPLE

ON UPDATE NO ACTION

ON DELETE CASCADE

)

Above, we can see that our new post_categories_category table uses a primary key that consists of the postId and categoryId combined.

We can also make the many-to-many relationship bidirectional. Remember to use the JoinTable decorator only on one side of the relationship, though.

@ManyToMany(() => Category, (category: Category) => category.posts)

@JoinTable()

public categories: Category[];

1 2	@ManyToMany(() => Post, (post: Post) => post.categories) public posts: Post[];

Thanks to doing the above, we can now easily fetch categories along with their posts.

getAllCategories() {

return this.categoriesRepository.find({ relations: ['posts'] });

}

async getCategoryById(id: number) {

const category = await this.categoriesRepository.findOne(id, { relations: ['posts'] });

if (category) {

return category;

}

throw new CategoryNotFoundException(id);

}

async updateCategory(id: number, category: UpdateCategoryDto) {

await this.categoriesRepository.update(id, category);

const updatedCategory = await this.categoriesRepository.findOne(id, { relations: ['posts'] });

if (updatedCategory) {

return updatedCategory

}

throw new CategoryNotFoundException(id);

}

Summary

This time we’ve covered creating relationships while using NestJS with Postgres and TypeORM. It included one-to-one, one-to-many, and many-to-many. We supplied them with various options, such as cascade and eager. We’ve also looked into SQL queries that TypeORM creates, to understand better how it works.

Series Navigation<< API with NestJS #6. Looking into dependency injection and modulesAPI with NestJS #8. Writing unit tests >>

12 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Duc Lux

1 year ago

TYSM for this helpfull, Can you write article about Grapql, Typeorm, nestjs

babou

1 year ago

When I create a user with address in the body, it works. (I enabled the cascade parameter)
But when trying to update a user (which has not address yet) (PUT) with address in the body, it didn’t work.

I can’t figure out why.

Ignacio Ruiz

1 year ago

can i post an address with a user inside? like
{
“street”: “test”,
“user”: {
“name”: “John”
}
}

Goutham

1 year ago

How to save many-to-many relationships

Tamerlan

1 year ago

Reply to Goutham

Here’s the way I did it:
Get a list of category ids from user. (from request body, or createPostDto)
Once you have that then, you need to find all categories with the list of ids

const categories = await this.categoriesService.getCategoriesByIds(

post.categories,

);

Then you link the post with categories

const newPost = await this.postRepository.create({

title: post.title,

content: post.content,

author: user,

categories: categories,

});

await this.postRepository.save(newPost);

Stephanie

10 months ago

Reply to Tamerlan

How does your getCategoriesByIds look ?

Solomon

4 months ago

Reply to Stephanie

Mine looks like this

async getCategoriesByIds(categories: string[]): Promise<Category[]> {

const returnedCategories = await this.categoriesRepository.find({

where: { id: In(categories) }

// order: { createDate: "ASC" }

})

if (returnedCategories.length == categories.length) {

return returnedCategories

}

throw new NotFoundException(<code>Some of the categories you supplied were not found ${categories}</code>)

}

Last edited 4 months ago by Solomon

Mike

1 year ago

What if I want not to update the category, but add another one? Which method should I use?

Constantin

1 year ago

Hi
Can you help me please?
I hav an entity:

@Entity()

export class Stock {

@PrimaryGeneratedColumn()

id: number

@ManyToOne(() => Product, (product: Product) => product.stocks)

product: Product

@ManyToOne(() => Branch, (branch: Branch) => branch.availability)

branch: Branch

@Column()

quantity: number

}

Also a DTO:

export class CreateStockDto {

product: number

branch: number

quantity: number

}

And a service when i’m try to update the record if it exists or create if not:

async changeQuantityWhenSupplied(

queryRunner: QueryRunner,

transaction: CreateStockDto

) {

const { product, branch } = transaction

const record = await queryRunner.manager.findOne(Stock, { product, branch }) // TS2769

if (record) {

await queryRunner.manager.update(Stock, record.id, {

quantity: () => <code>quantity + ${transaction.quantity}

})

} else {

await queryRunner.manager.save(Stock, transaction) TS2769

}

It doesn’t work because of error:

TS2769: No overload matches this call. Overload 2 of 3, '(entityClass: EntityTarget<Stock>, options?: FindOneOptions<Stock>): Promise<Stock>', gave the following error. Argument of type '{ product: number; branch: number; }' is not assignable to parameter of type 'FindOneOptions<Stock>'. Object literal may only specify known properties, and 'product' does not exist in type 'FindOneOptions<Stock>'.

TS2769: No overload matches this call. The last overload gave the following error. Argument of type 'CreateStockDto' is not assignable to parameter of type 'DeepPartial<Stock>'. Types of property 'product' are incompatible. Type 'number' is not assignable to type 'Product | DeepPartial<Product>'. EntityManager.d.ts(137, 5): The last overload is declared here.

I really don’t understand this error…