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.
1import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
2
3@Entity()
4class Address {
5 @PrimaryGeneratedColumn()
6 public id: number;
7
8 @Column()
9 public street: string;
10
11 @Column()
12 public city: string;
13
14 @Column()
15 public country: string;
16}
17
18export 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.
1import { Column, Entity, JoinColumn, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
2import { Exclude } from 'class-transformer';
3import Address from './address.entity';
4
5@Entity()
6class User {
7 @PrimaryGeneratedColumn()
8 public id: number;
9
10 @Column({ unique: true })
11 public email: string;
12
13 @Column()
14 public name: string;
15
16 @Column()
17 @Exclude()
18 public password: string;
19
20 @OneToOne(() => Address)
21 @JoinColumn()
22 public address: Address;
23}
24
25export 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.
1CREATE TABLE user (
2 // ...
3 addressId integer REFERENCES address (id)
4)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.
1import { Column, Entity, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
2import User from './user.entity';
3
4@Entity()
5class Address {
6 @PrimaryGeneratedColumn()
7 public id: number;
8
9 @Column()
10 public street: string;
11
12 @Column()
13 public city: string;
14
15 @Column()
16 public country: string;
17
18 @OneToOne(() => User, (user: User) => user.address)
19 public user: User;
20}
21
22export 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.
1getAllAddressesWithUsers() {
2 return this.addressRepository.find({ relations: ['user'] });
3}If we want our related entities always to be included, we can make our relationship eager.
1@OneToOne(() => Address, {
2 eager: true
3})
4@JoinColumn()
5public 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.
1@OneToOne(() => Address, {
2 eager: true,
3 cascade: true
4})
5@JoinColumn()
6public 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.
1import { Column, Entity, JoinColumn, OneToMany, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
2import { Exclude } from 'class-transformer';
3import Address from './address.entity';
4import Post from '../posts/post.entity';
5
6@Entity()
7class User {
8 @PrimaryGeneratedColumn()
9 public id: number;
10
11 @Column({ unique: true })
12 public email: string;
13
14 @Column()
15 public name: string;
16
17 @Column()
18 @Exclude()
19 public password: string;
20
21 @OneToOne(() => Address, {
22 eager: true,
23 cascade: true
24 })
25 @JoinColumn()
26 public address: Address;
27
28 @OneToMany(() => Post, (post: Post) => post.author)
29 public posts: Post[];
30}
31
32export 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.
1import { Column, Entity, ManyToOne, PrimaryGeneratedColumn } from 'typeorm';
2import User from '../users/user.entity';
3
4@Entity()
5class Post {
6 @PrimaryGeneratedColumn()
7 public id: number;
8
9 @Column()
10 public title: string;
11
12 @Column()
13 public content: string;
14
15 @Column({ nullable: true })
16 public category?: string;
17
18 @ManyToOne(() => User, (author: User) => author.posts)
19 public author: User;
20}
21
22export 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.
1@Post()
2@UseGuards(JwtAuthenticationGuard)
3async createPost(@Body() post: CreatePostDto, @Req() req: RequestWithUser) {
4 return this.postsService.createPost(post, req.user);
5}1async createPost(post: CreatePostDto, user: User) {
2 const newPost = await this.postsRepository.create({
3 ...post,
4 author: user
5 });
6 await this.postsRepository.save(newPost);
7 return newPost;
8}If we want to return a list of the posts with the authors, we can now easily do so.
1getAllPosts() {
2 return this.postsRepository.find({ relations: ['author'] });
3}
4
5async getPostById(id: number) {
6 const post = await this.postsRepository.findOne(id, { relations: ['author'] });
7 if (post) {
8 return post;
9 }
10 throw new PostNotFoundException(id);
11}
12
13async updatePost(id: number, post: UpdatePostDto) {
14 await this.postsRepository.update(id, post);
15 const updatedPost = await this.postsRepository.findOne(id, { relations: ['author'] });
16 if (updatedPost) {
17 return updatedPost
18 }
19 throw new PostNotFoundException(id);
20}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.
1import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
2
3@Entity()
4class Category {
5 @PrimaryGeneratedColumn()
6 public id: number;
7
8 @Column()
9 public name: string;
10}
11
12export default Category;1import { Column, Entity, JoinTable, ManyToMany, ManyToOne, PrimaryGeneratedColumn } from 'typeorm';
2import User from '../users/user.entity';
3import Category from '../categories/category.entity';
4
5@Entity()
6class Post {
7 @PrimaryGeneratedColumn()
8 public id: number;
9
10 @Column()
11 public title: string;
12
13 @Column()
14 public content: string;
15
16 @Column({ nullable: true })
17 public category?: string;
18
19 @ManyToOne(() => User, (author: User) => author.posts)
20 public author: User;
21
22 @ManyToMany(() => Category)
23 @JoinTable()
24 public categories: Category[];
25}
26
27export 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.
1CREATE TABLE public.post_categories_category
2(
3 "postId" integer NOT NULL,
4 "categoryId" integer NOT NULL,
5 CONSTRAINT "PK_91306c0021c4901c1825ef097ce" PRIMARY KEY ("postId", "categoryId"),
6 CONSTRAINT "FK_93b566d522b73cb8bc46f7405bd" FOREIGN KEY ("postId")
7 REFERENCES public.post (id) MATCH SIMPLE
8 ON UPDATE NO ACTION
9 ON DELETE CASCADE,
10 CONSTRAINT "FK_a5e63f80ca58e7296d5864bd2d3" FOREIGN KEY ("categoryId")
11 REFERENCES public.category (id) MATCH SIMPLE
12 ON UPDATE NO ACTION
13 ON DELETE CASCADE
14)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.
1@ManyToMany(() => Category, (category: Category) => category.posts)
2@JoinTable()
3public categories: Category[];1@ManyToMany(() => Post, (post: Post) => post.categories)
2public posts: Post[];Thanks to doing the above, we can now easily fetch categories along with their posts.
1getAllCategories() {
2 return this.categoriesRepository.find({ relations: ['posts'] });
3}
4
5async getCategoryById(id: number) {
6 const category = await this.categoriesRepository.findOne(id, { relations: ['posts'] });
7 if (category) {
8 return category;
9 }
10 throw new CategoryNotFoundException(id);
11}
12
13async updateCategory(id: number, category: UpdateCategoryDto) {
14 await this.categoriesRepository.update(id, category);
15 const updatedCategory = await this.categoriesRepository.findOne(id, { relations: ['posts'] });
16 if (updatedCategory) {
17 return updatedCategory
18 }
19 throw new CategoryNotFoundException(id);
20}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.