Types of relationships with Postgres & TypeORM. TypeScript Express #8

January 21, 2019

This entry is part 8 of 15 in the TypeScript Express tutorial

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:

src/user/user.entity.ts

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

@Entity()

class User {

@PrimaryGeneratedColumn()

public id: string;

@Column()

public name: string;

@Column()

public email: string;

@Column()

public password: string;

}

export 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:

src/user/user.entity.ts

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

import Address from '../address/address.entity';

@Entity()

class User {

@PrimaryGeneratedColumn()

public id: string;

@Column()

public name: string;

@Column()

public email: string;

@Column()

public password: string;

@OneToOne(() => Address)

@JoinColumn()

public address: Address;

}

export default User;

src/address/address.entity.ts

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

@Entity()

class Address {

@PrimaryGeneratedColumn()

public id: string;

@Column()

public street: string;

@Column()

public city: string;

@Column()

public country: string;

}

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

src/user/user.entity.ts

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

import Address from '../address/address.entity';

@Entity()

class User {

@PrimaryGeneratedColumn()

public id: string;

@Column()

public name: string;

@Column()

public email: string;

@Column()

public password: string;

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

@JoinColumn()

public address: Address;

}

export default User;

src/address/address.entity.ts

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

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

@Entity()

class Address {

@PrimaryGeneratedColumn()

public id: string;

@Column()

public street: string;

@Column()

public city: string;

@Column()

public country: string;

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

public user: User;

}

export 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:

private getAllAddresses = async (request: express.Request, response: express.Response) => {

const addresses = await this.addressRepository.find({ relations: ['user'] });

response.send(addresses);

}

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:

src/user/user.entity.ts

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

import Address from '../address/address.entity';

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

@Entity()

class User {

@PrimaryGeneratedColumn()

public id: string;

@Column()

public name: string;

@Column()

public email: string;

@Column()

public password: string;

@OneToOne(() => Address, (address: Address) => address.user, {

cascade: true,

eager: true,

})

@JoinColumn()

public address: Address;

}

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

@OneToOne(() => Address, (address: Address) => address.user, {

cascade: true,

})

@JoinColumn()

public 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!

src/user/user.entity.ts

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

import Address from '../address/address.entity';

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

@Entity()

class User {

@PrimaryGeneratedColumn()

public id: string;

@Column()

public name: string;

@Column()

public email: string;

@Column()

public password: string;

@OneToOne(() => Address, (address: Address) => address.user, {

cascade: true,

eager: true,

})

@JoinColumn()

public address: Address;

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

public posts: Post[];

}

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

src/post/post.entity.ts

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

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

@Entity()

class Post {

@PrimaryGeneratedColumn()

public id?: number;

@Column()

public title: string;

@Column()

public content: string;

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

public author: User;

}

export 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!

private createPost = async (request: RequestWithUser, response: express.Response) => {

const postData: CreatePostDto = request.body;

const newPost = this.postRepository.create({

...postData,

author: request.user,

});

await this.postRepository.save(newPost);

response.send(newPost);

}

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.

src/post/post.entity.ts

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

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

import Category from "../category/category.entity";

@Entity()

class Post {

@PrimaryGeneratedColumn()

public id?: number;

@Column()

public title: string;

@Column()

public content: string;

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

public author: User;

@ManyToMany(() => Category)

@JoinTable()

categories: Category[];

}

export default Post;

src/category/category.entity.ts

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

@Entity()

class Category {

@PrimaryGeneratedColumn()

public id: string;

@Column()

public name: string;

}

export 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:

private getAllPosts = async (request: express.Request, response: express.Response) => {

const posts = await this.postRepository.find({ relations: ['categories'] });

response.send(posts);

}

private getPostById = async (request: express.Request, response: express.Response, next: express.NextFunction) => {

const id = request.params.id;

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

if (post) {

response.send(post);

} else {

next(new PostNotFoundException(id));

}

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.

src/post/post.entity.ts

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

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

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

@Entity()

class Post {

@PrimaryGeneratedColumn()

public id?: number;

@Column()

public title: string;

@Column()

public content: string;

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

public author: User;

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

@JoinTable()

public categories: Category[];

}

export default Post;

src/category/category.entity.ts

import Post from 'post/post.entity';

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

@Entity()

class Category {

@PrimaryGeneratedColumn()

public id: string;

@Column()

public name: string;

@ManyToMany(() => Post, (post: Post) => post.categories)

public posts: Post[];

}

export default Category;

With that approach, you can easily fetch categories with its posts.

private getAllCategories = async (request: express.Request, response: express.Response) => {

const categories = await this.categoryRepository.find({ relations: ['posts'] });

response.send(categories);

}

private getCategoryById = async (request: express.Request, response: express.Response, next: express.NextFunction) => {

const id = request.params.id;

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

if (category) {

response.send(category);

} else {

next(new CategoryNotFoundException(id));

}

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.

Series Navigation<< TypeScript Express tutorial #7. Relational databases with Postgres and TypeORMTypeScript Express tutorial #9. The basics of migrations using TypeORM and Postgres >>

6 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

James

5 years ago

You can also make the Many-To-Many relationship bidirectional

did not understand that… isn’t it bi-directional by default ? what is the difference between the two ?
thanks for these posts by the way

Hesham Shawky

Reply to James

No, it’s not, for example, you can get users address because the user has the address id but you can’t get users that live in specific address if it’s not an inverse relationship! because the address not having any information about users!

So We inverse the relation mean we put user id inside the address and put address id inside the user.

atas

4 years ago

I am having the following error when trying to run dev:
TSError: ⨯ Unable to compile TypeScript:
src/post/post.controller.ts(32,21): error TS2769: No overload matches this call.
Overload 1 of 3, ‘(entityLikeArray: DeepPartial[]): Post[]’, gave the following error.
Argument of type ‘{ author: any; id: number; content: string; title: string; categories: CategoryInPostDto[]; }’ is not assignable to parameter of type ‘DeepPartial[]’.
Object literal may only specify known properties, and ‘author’ does not exist in type ‘DeepPartial[]’.
Overload 2 of 3, ‘(entityLike: DeepPartial): Post’, gave the following error.
Argument of type ‘{ author: any; id: number; content: string; title: string; categories: CategoryInPostDto[]; }’ is not assignable to parameter of type ‘DeepPartial’.
Types of property ‘categories’ are incompatible.
Type ‘CategoryInPostDto[]’ is not assignable to type ‘DeepPartial[]’.
Type ‘CategoryInPostDto’ is not assignable to type ‘DeepPartial’.
Types of property ‘id’ are incompatible.
Type ‘number’ is not assignable to type ‘string’.

Any idea what is the problem ?

MLK

2 years ago

Reply to atas

Did you find a fix to this error? I’m facing the same issue

ArtK

fire!

Julian Otto

3 years ago

Thank you very much i really had trouble understanding the foreign key relationships, your example with users/posts really helped me! thanks man