If you are new to NestJS or TypeORM, I’ve got you covered with some articles to get started with a Nest backend in Typescript and how to connect it to an SQL database.

In this article, we are going to look at some more advanced SQL concepts you can put in place in your NestJS application. We are going to start from a simple setup and add new relations and tables to it.

Architecture

Let’s have a high-level view of what we have now and want to achieve with our SQL schema. So far we only have a nest_users table that’s created via or users.entity.ts file.

classDiagram
    class nest_users {
        +id: uuid
        +name: varchar
        +created_at: timestamp
        +updated_at: timestamp
    }

To make it a bit more complex, we want to have two new entities:

• A Home for the users: A user can only have one home, but a home can have many users.
• A Job for the users: A user can have many jobs, and the same job can be done by many users.

Our users can’t afford to have multiple houses even while they are working multiple jobs! 😅

Any similarity with fictitious events or characters was purely coincidental, it’s only as an example.

Now here’s what it looks like (using pgadmin ERD tool) since we are using a postgres database. The ERD stands for Entity Relationship Diagram which display the tables and their relations nicely. The UI is a bit chunky, but it gets the job done.

See the links between the tables? That’s the relations we are going to create in our entities.

Implementation

In our existing NestJS application that’s setup with TypeORM, we are going to add two new entities.

Remember to generate and run the migrations after creating the entities.

Home with “One to Many”

Let’s have the Home entity wit a One To Many relation as follows:

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

@Entity('nest_homes')
export class Home {

  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  address: string;

  @OneToMany(() => User, (user) => user.home)
  inhabitants: User[];
}

Here we are adding a “one to many” relation between the Home and the User entities. This means that a Home can have many User entities, but a User can only have one Home.

So on the User side it looks like:

@Entity('nest_users')
export class User {
  @ManyToOne(() => Home, (home) => home.inhabitants, { onDelete: 'CASCADE' })
  home: Home;
}

As you can see both entities are linked with a mirrored OneToMany with ManyToOne relation. On the annotation we have three parameters:

• The first one is the type of the entity we are linking to.
• The second one is a function that returns the property of the linked entity.
• The third optional one is an object with the options,
  • here we are setting the onDelete to CASCADE which means that if a Home is deleted, all the User linked to it will be deleted too 😱.

In this case, we’re assuming everyone’s home when the home gets destroyed; they don’t survive it. 😵‍ Where’s the disclaimer again, I should have named my entities better, the scenario just got darker by the minute!

Job

with custom “Many to Many”

Let’s look at the Job entity, it’s a bit more complex as we have a Many-to-many relation between the User and the Job.

@Entity('nest_jobs')
export class Job {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @OneToMany(() => UserToJob, (userToJob) => userToJob.job)
  jobToUsers: UserToJob[];
}

Interestingly, here we have a One-to-many relation, but it’s not the same as the previous one. We have an intermediate table linking the two entities.

On the User entity we have something similar:

@Entity('nest_users')
export class User {
  @OneToMany(() => UserToJob, (userToJob) => userToJob.user)
  userToJobs: UserToJob[];
}

Both entities have a @OneToMany relation to the UserToJob entity, but with different properties.

Let’s have a look at that new UserToJob entity we have created:

@Entity('users_to_jobs')
export class UserToJob {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: string;

  @Column()
  jobId: number;

  @ManyToOne(() => User, (user) => user.userToJobs)
  @JoinColumn({ name: 'userId' })
  user: User;

  @ManyToOne(() => Job, (job) => job.jobToUsers)
  @JoinColumn({ name: 'jobId' })
  job: Job;
}

As you can see the UserToJob is linked to both the User and the Job entities.

Why not use the @ManyToMany relation directly?

• Because we want to have more control over the intermediate table, like adding more properties to it.

But now that I have talked about that ManyToMany relation, let’s have an example free of charge!

Role with “Many to Many”

Surprise a new requirement! That’s not part of the initial architecture diagram we had set to do! But who doesn’t like a bit of scope creep… 🙃 Let’s have the role in a many-to-many relation with the job, as you can have multiple roles for a job, and a role can be done by multiple jobs.

I try to make them generic while using term that would make some sense, I hope it’s not too much of a stretch. Let’s see what the entity would look like:

@Entity('nest_roles')
export class Role {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @ManyToMany(() => Job, job => job.roles)
  jobs: Job[];
}

Easy enough, it’s more straightforward than the UserToJob entity we had to create earlier. The complexity here is handled under the hood by TypeORM but the principle stays the same.

For the Job entity, we would need to add:

@Entity('nest_jobs')
export class Job {
  @ManyToMany(() => Role, role => role.jobs)
  @JoinTable()
  roles: Role[];
}

We have the @JoinTable() annotation that is used to determine who’s the “owner” of the relation, and there can be only one. (It can’t be on both entities) If you remember our UserToJob entity, the owner would be the user according to its name. It doesn’t change anything in the database, but you don’t need to have both a UserToJob and a JobToUser entity.

This annotation will let TypeORM know to create the intermediate table for us to store this relation.

And as you can see once the migration is run, we have a new nest_jobs_roles_nest_roles table that is created for us. Which is the intermediate table between the Job and the Role entities as [table name]_[annotated field]_[linked table name].

With the entity manager

Now that we have seen how to do a “Many to Many”, “One to Many” relations and so on, let’s have a quick example on how to retrieve these objects from the database.

Let’s have a simple database service with the User and Job repository

export class DatabaseService {
  private readonly logger = new Logger(DatabaseService.name);

  constructor(
    @InjectRepository(User) private readonly userRepository: Repository<User>,
    @InjectRepository(Job) private readonly jobRepository: Repository<Job>,
  ) {}

}

We could also use the EntityManager to get the jobs from the user’s repository, but having it both is more convenient. Here is the syntax in case you need to use the entity manager for more complex queries, it’s always available from the repository:

this.userRepository.manager.findOne(Job, {/* */})

Now let’s get the user, its home and its jobs:

const user = this.userRepository.findOne({
  where: { id },
  relations: ['home', 'userToJobs', 'userToJobs.job'],
});

Here we are using the relations property to get the home and the userToJobs with the job linked to it. Without that relations property, the home and userToJobs would be undefined, and without the userToJobs.job the job would be undefined in that entity as well.

For the job, with the roles, thanks the @ManyToMany relation, we can get the roles directly from the job entity:

const job = this.jobRepository.findOne({
  where: { id },
  relations: ['roles', 'jobToUsers', 'jobToUsers.user'],
});

No need to know the intermediate auto-generated table, you get the roles directly. And for the users from the job, it’s similar to getting the jobs from the user.

To manage the relation, you can use the entity manager functionality like save, remove, update, etc. Like a normal entity the change should propagate to the linked entities.