Today we continue using Postgres with Express and Typescript. Relationships are an essential part of working with Postgres, and therefore we cover it today. To handle it we use TypeORM. The code for the tutorial is in the express-typescript repository in the postgres branch. Feel free to give it a star.
TypeScript Express Postgres Relationships
When we create a database, we use tables for different entities. They are often related to each other, and Postgres can handle many types of relationships. It helps you handle related entities easily. Let’s create the entity of a user:
1import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
2
3@Entity()
4class User {
5 @PrimaryGeneratedColumn()
6 public id: string;
7
8 @Column()
9 public name: string;
10
11 @Column()
12 public email: string;
13
14 @Column()
15 public password: string;
16}
17
18export default User;The id column is a primary key (PK) because it uniquely identifies each row of the table. You can also create foreign keys that uniquely identify a row of another table. By using foreign keys you can form relationships.
One-To-One
The One-To-One is a relationship where the row of a table A may be linked to just one row of a table B and vice versa. Let’s expand on our example from above:
1import { Column, Entity, JoinColumn, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
2import Address from '../address/address.entity';
3
4@Entity()
5class User {
6 @PrimaryGeneratedColumn()
7 public id: string;
8
9 @Column()
10 public name: string;
11
12 @Column()
13 public email: string;
14
15 @Column()
16 public password: string;
17
18 @OneToOne(() => Address)
19 @JoinColumn()
20 public address: Address;
21}
22
23export default User;1import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
2
3@Entity()
4class Address {
5 @PrimaryGeneratedColumn()
6 public id: string;
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;Here we use a new decorator called OneToOne. With its help, we can easily create a one-to-one relationship between two rows. It takes an argument which is a function returning the class of the entity with which we make our relationship with.
Inverse relationship
The other decorator called JoinColumn indicates that this side of the relationship owns it. Thanks to that, it contains the column with a foreign key. Right now our relationship is unidirectional. It means only the user has the id of the address and not the other way around. The address does not know anything about the user. We can effortlessly change that by adding an inverse relationship. By that, we make the relationship between the User and the Address bidirectional.
1import { Column, Entity, JoinColumn, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
2import Address from '../address/address.entity';
3
4@Entity()
5class User {
6 @PrimaryGeneratedColumn()
7 public id: string;
8
9 @Column()
10 public name: string;
11
12 @Column()
13 public email: string;
14
15 @Column()
16 public password: string;
17
18 @OneToOne(() => Address, (address: Address) => address.user)
19 @JoinColumn()
20 public address: Address;
21}
22
23export default User;1import { Column, Entity, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
2import User from '../user/user.entity';
3
4@Entity()
5class Address {
6 @PrimaryGeneratedColumn()
7 public id: string;
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;When creating the inverse side of the relationship, the OneToOne receives an additional argument, which is a function that returns the property that holds the reverse side of the relationship.
Please notice that we only use the JoinColumn decorator only on one side of the relationship, making it the owning side. When you look into the tables in the database, only the side that owns the relationship stores the id of the row in the other table.
There is an advantage of having a bidirectional relationship. It is the fact that you can easily relate to the other side of the relationship, even if the table that you are processing currently does not own it. A good example is fetching a list of all addresses. Without having an additional inverse relationship you wouldn’t have an easy way to connect addresses to users. If you have it, you can use the find function with the relations option to append additional data:
1private getAllAddresses = async (request: express.Request, response: express.Response) => {
2 const addresses = await this.addressRepository.find({ relations: ['user'] });
3 response.send(addresses);
4}Thanks to the code above, when you fetch addresses, the data about the user is attached, which might prove to be useful in many situations.
You can also achieve a similar effect by making the relationship eager. You can do it by passing an additional option to your relationship. By doing that, you make the relationship be joined to the table automatically. Let’s do it in the User entity:
1import { Column, Entity, JoinColumn, OneToMany, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
2import Address from '../address/address.entity';
3import Post from '../post/post.entity';
4
5@Entity()
6class User {
7 @PrimaryGeneratedColumn()
8 public id: string;
9
10 @Column()
11 public name: string;
12
13 @Column()
14 public email: string;
15
16 @Column()
17 public password: string;
18
19 @OneToOne(() => Address, (address: Address) => address.user, {
20 cascade: true,
21 eager: true,
22 })
23 @JoinColumn()
24 public address: Address;
25}
26
27export default User;Now, when you access the data of a user, his address is added automatically.
Please note that only one side of a relationship might be eager.
Automatically saving related objects
We can still make one improvement. Right now we need to save the User and Address rows separately. With the cascade option, we can save the User object containing nested address data. By that, we let TypeORM handle saving rows in two distinct tables.
1@OneToOne(() => Address, (address: Address) => address.user, {
2 cascade: true,
3})
4@JoinColumn()
5public address: Address;Thanks to our configuration, rows both in the User and the Address table were created. You can see it in the pgAdmin console:
One-To-Many and Many-To-One
The Ony-To-Many and Many-To-One is a relationship where a row from table A may be linked to multiple rows of table B, but a row from table B may be connected to just one row of table A.
An example of that is when a user can create multiple posts, but a post has just one author. Let’s implement it!
1import { Column, Entity, JoinColumn, OneToMany, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
2import Address from '../address/address.entity';
3import Post from '../post/post.entity';
4
5@Entity()
6class User {
7 @PrimaryGeneratedColumn()
8 public id: string;
9
10 @Column()
11 public name: string;
12
13 @Column()
14 public email: string;
15
16 @Column()
17 public password: string;
18
19 @OneToOne(() => Address, (address: Address) => address.user, {
20 cascade: true,
21 eager: true,
22 })
23 @JoinColumn()
24 public address: Address;
25
26 @OneToMany(() => Post, (post: Post) => post.author)
27 public posts: Post[];
28}
29
30export default User;In the User entity, we use the OneToMany decorator in a similar manner to the OneToOne decorator. Thanks to it, one user can be linked to many posts.
1import { Column, Entity, JoinColumn, ManyToOne, PrimaryGeneratedColumn } from 'typeorm';
2import User from '../user/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 @ManyToOne(() => User, (author: User) => author.posts)
16 public author: User;
17}
18
19export default Post;In the Post entity, we use the ManyToOne decorator. Using it here means that many posts may be related to one user. Let’s try it out in action!
1private createPost = async (request: RequestWithUser, response: express.Response) => {
2 const postData: CreatePostDto = request.body;
3 const newPost = this.postRepository.create({
4 ...postData,
5 author: request.user,
6 });
7 await this.postRepository.save(newPost);
8 response.send(newPost);
9}The side of the relationship that uses ManyToOne stores the foreign key, as you can see on the example above. OneToMany can’t exist without ManyToOne. You may want the data of the author when fetching a post, or data about posts when fetching an author. To fetch it, you can use the find function with the relations option or make the relationship eager.
Many-To-Many
The Many-To-Many relationship is where the row from table A can link to multiple rows of table B and vice versa.
The example of it is when a post can be in multiple categories and category can contain numerous posts.
1import { Column, Entity, ManyToOne, PrimaryGeneratedColumn, ManyToMany, JoinTable } from 'typeorm';
2import User from '../user/user.entity';
3import Category from "../category/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 @ManyToOne(() => User, (author: User) => author.posts)
17 public author: User;
18
19 @ManyToMany(() => Category)
20 @JoinTable()
21 categories: Category[];
22}
23
24export default Post;1import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
2
3@Entity()
4class Category {
5 @PrimaryGeneratedColumn()
6 public id: string;
7
8 @Column()
9 public name: string;
10}
11
12export default Category;Here we use an additional JoinTable decorator because when we create a Many-To-Many relationship, we set up an extra table so that neither the Post nor Category table store the data about the relationship.
After creating a few categories using the CategoryController, we can send posts with categories.
To fetch posts with the data about the categories we can use the find function with the relations option:
1private getAllPosts = async (request: express.Request, response: express.Response) => {
2 const posts = await this.postRepository.find({ relations: ['categories'] });
3 response.send(posts);
4}
5
6private getPostById = async (request: express.Request, response: express.Response, next: express.NextFunction) => {
7 const id = request.params.id;
8 const post = await this.postRepository.findOne(id, { relations: ['categories'] });
9 if (post) {
10 response.send(post);
11 } else {
12 next(new PostNotFoundException(id));
13 }
14}You can also make the Many-To-Many relationship bidirectional, but remember to use the JoinTable decorator once, using it only on one side of the relationship.
1import { Column, Entity, JoinTable, ManyToMany, ManyToOne, PrimaryGeneratedColumn } from 'typeorm';
2import Category from '../category/category.entity';
3import User from '../user/user.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 @ManyToOne(() => User, (author: User) => author.posts)
17 public author: User;
18
19 @ManyToMany(() => Category, (category: Category) => category.posts)
20 @JoinTable()
21 public categories: Category[];
22}
23
24export default Post;1import Post from 'post/post.entity';
2import { Column, Entity, ManyToMany, PrimaryGeneratedColumn } from 'typeorm';
3
4@Entity()
5class Category {
6 @PrimaryGeneratedColumn()
7 public id: string;
8
9 @Column()
10 public name: string;
11
12 @ManyToMany(() => Post, (post: Post) => post.categories)
13 public posts: Post[];
14}
15
16export default Category;With that approach, you can easily fetch categories with its posts.
1private getAllCategories = async (request: express.Request, response: express.Response) => {
2 const categories = await this.categoryRepository.find({ relations: ['posts'] });
3 response.send(categories);
4}
5
6private getCategoryById = async (request: express.Request, response: express.Response, next: express.NextFunction) => {
7 const id = request.params.id;
8 const category = await this.categoryRepository.findOne(id, { relations: ['posts'] });
9 if (category) {
10 response.send(category);
11 } else {
12 next(new CategoryNotFoundException(id));
13 }
14}Summary
In this article, we covered creating relationships in Postgres with TypeORM, including One-To-One, One-To-Many with Many-To-One and Many-To-Many relationships. Aside from that, we also used some additional options like like the cascade and the eager relationship.