Many to Many relations with TypeORM and NestJS

A many-to-many relationship occurs when multiple records in the table are associated with multiple records in another table.

For example, the image above shows student and course table. Student can be part of multiple courses and course can have multiple students attending them. This problem is solved with many-to-many relation which creates a third table called student_course as shown in the image above. The student_course table contains student_it and course_id as foreign keys from student and course table.

In NestJS we would create such a relation following these steps:

Step 1:
Create student and course entities.

@Entity('student')
export class Student {
  @PrimaryGeneratedColumn({ type: 'int', name: 'id', unsigned: true })
  id: number;

  @Column('varchar', { name: 'student_name', length: 255, unique: true })
  studentName: string;

@Entity('course')
export class Course {
  @PrimaryGeneratedColumn({ type: 'int', name: 'id', unsigned: true })
  id: number;

  @Column('varchar', { name: 'course_name', length: 255, unique: true })
  courseName: string;

Step 2:

In student entity, we will add many to many relations.

@Entity('student')
export class Student {
  @PrimaryGeneratedColumn({ type: 'int', name: 'id', unsigned: true })
  id: number;

  @Column('varchar', { name: 'student_name', length: 255, unique: true })
  studentName: string;
  
  @ManyToMany(
  () => Course, 
  course => course.students, //optional
  {onDelete: 'NO ACTION', onUpdate: 'NO ACTION'})
  @JoinTable({
    name: 'student_course',
    joinColumn: {
      name: 'student_id',
      referencedColumnName: 'id',
    },
    inverseJoinColumn: {
      name: 'course_id',
      referencedColumnName: 'id',
    },
  })
  courses?: Course[];

With the code written above, we have created unidirectional many-to-many relations using TypeORM and NestJS. However, if we want bidirectional many-to-many relation we would need to write the following code in Course entity.

@Entity('course')
export class Course {
  @PrimaryGeneratedColumn({ type: 'int', name: 'id', unsigned: true })
  id: number;

  @Column('varchar', { name: 'course_name', length: 255, unique: true })
  courseName: string;
  
  @ManyToMany(
    () => Student,
    student => student.courses,
    {onDelete: 'NO ACTION', onUpdate: 'NO ACTION',},
  )
  students?: Student[];
}

You can see that in Course entity we don't have a @JoinTable decorator because we have already specified it in Student entity, and we need to do it only once.

The above-written solution for many to many relations is not that good however, we do not have any entities or repositories for student_course table which means that we can't access it directly. Only through relations of Student and Course entities or through raw queries. This is a problem especially if we want to delete some entries from student_course table.

This can be solved by creating a third entity called StudentCourse.

Step 3:

Creating StudentCourse entity.

@Entity('student_course')
export class StudentCourse {
  @PrimaryColumn({ name: 'student_id' })
  studentId: number;

  @PrimaryColumn({ name: 'course_id' })
  courseId: number;

  @ManyToOne(
    () => Student,
    student => student.course,
    {onDelete: 'NO ACTION', onUpdate: 'NO ACTION'}
  )
  @JoinColumn([{ name: 'student_id', referencedColumnName: 'id' }])
  students: Student[];

  @ManyToOne(
    () => Course,
    course => course.student,
    {onDelete: 'NO ACTION', onUpdate: 'NO ACTION'}
  )
  @JoinColumn([{ name: 'course_id', referencedColumnName: 'id' }])
  courses: Course[];
}

Now we have direct access to student_course table which means we can directly access entries inside that table and add/update/delete them.

From this point, you would continue as usual by creating modules, controllers and services. You wouldn't need to create a module for StudentCourse entity because there will never be an action which would result in doing anything over this entity without either grabbing Student or Course entity.

Before checking how to make Module, Controller and Service there is one last thing to check, and that is migrations. TypeORM will create this migration for you but you should always read it and check all the lines.

The migration for the code above would look something like this:

export class StudentCourseMigration implements MigrationInterface {
    name = 'StudentCourseMigration'

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`CREATE TABLE \`student\` (\`id\` int UNSIGNED NOT NULL AUTO_INCREMENT, \`student_name\` varchar(255) NOT NULL, UNIQUE INDEX \`IDX_xxxx}\` (\`student_name\`), PRIMARY KEY (\`id\`)) ENGINE=InnoDB`);
        await queryRunner.query(`CREATE TABLE \`course\` (\`id\` int UNSIGNED NOT NULL AUTO_INCREMENT, \`course_name\` varchar(255) NOT NULL, UNIQUE INDEX \`IDX_xxxx\` (\`course_name\`), PRIMARY KEY (\`id\`)) ENGINE=InnoDB`);
        await queryRunner.query(`CREATE TABLE \`student_course\` (\`student_id\` int NOT NULL, \`course_id\` int NOT NULL, PRIMARY KEY (\`student_id\`, \`course_id\`)) ENGINE=InnoDB`);
        await queryRunner.query(`ALTER TABLE \`student_course\` CHANGE \`student_id\` \`student_id\` int UNSIGNED NOT NULL`);
        await queryRunner.query(`ALTER TABLE \`student_course\` CHANGE \`course_id\` \`course_id\` int UNSIGNED NOT NULL`);
        await queryRunner.query(`CREATE INDEX \`IDX_xxxx\` ON \`student_course\` (\`student_id\`)`);
        await queryRunner.query(`CREATE INDEX \`IDX_xxxx\` ON \`student_course\` (\`course_id\`)`);
        await queryRunner.query(`ALTER TABLE \`student_course\` ADD CONSTRAINT \`FK_xxxx\` FOREIGN KEY (\`course_id\`) REFERENCES \`course\`(\`id\`) ON DELETE NO ACTION ON UPDATE NO ACTION`);
        await queryRunner.query(`ALTER TABLE \`student_course\` ADD CONSTRAINT \`FK_xxxx\` FOREIGN KEY (\`student_id\`) REFERENCES \`student\`(\`id\`) ON DELETE NO ACTION ON UPDATE NO ACTION`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE \`student_course\` DROP FOREIGN KEY \`FK_xxxx\``);
        await queryRunner.query(`ALTER TABLE \`student_course\` DROP FOREIGN KEY \`FK_xxxx\``);
        await queryRunner.query(`DROP INDEX \`IDX_xxxx\` ON \`student_course\``);
        await queryRunner.query(`DROP INDEX \`IDX_xxxx\` ON \`student_course\``);
        await queryRunner.query(`ALTER TABLE \`student_course\` CHANGE \`course_id\` \`course_id\` int NOT NULL`);
        await queryRunner.query(`ALTER TABLE \`student_course\` CHANGE \`student_id\` \`student_id\` int NOT NULL`);
        await queryRunner.query(`DROP TABLE \`student_course\``);
        await queryRunner.query(`DROP INDEX \`IDX_xxxx\` ON \`course\``);
        await queryRunner.query(`DROP INDEX \`IDX_xxxx\` ON \`student\``);
        await queryRunner.query(`DROP TABLE \`course\``);
        await queryRunner.query(`DROP TABLE \`student\``);
    }

}

Now let's get to a quick demo on how to create a simple module, controller and service for Student entity only, just because Course would basically be equal and there is no point in writing it.

Student Module:

@Module({
  imports: [
    TypeOrmModule.forFeature([Student]),
  ],
  providers: [StudentService],
  controllers: [StudentController],
})
export class StudentModule {}

Student Controller:

This will be an example with assign student to course method.

@Controller('/student')
export class StudentController {
  constructor(
    private readonly studentService: StudentService,
  ) {}

  @Post()
  async createStudentCourse(@Body() createStudentCourse: {studentId: number, courseId: number}) {
   await this.studentService.createStudentCorse(createStudentCourse);
  }

Student Service:

@Injectable()
export class StudentService {
  constructor(
    @InjectRepository(Student)
    private readonly studentRepository: Repository<Student>,
    @InjectRepository(StudentCourse)
    private readonly studentCourseRepository: Repository<StudentCourse>
  ) {}


  async createStudentCourse(createStudentCourse: {studentId: number, courseId: number}): Promise<void> {
    const student = await this.studentReposioty.findOne({where: {id: createStudentCourse.studentId}});
    if(!student){
    throw new NotFoundException()
    }
    /* You can check if course with given ID exists aswell here in the same way with CourseRepository */
    
    await this.studentCourseRepository.save(createStudentCourse)
    
    
  }

Optional:
You can create your custom repository class.
This is completely optional and you can do your work absolutely without this part but we will show how can we find this practice useful.

StudentRepository:

@EntityRepository(Student)
export class StudentRepository extends Repository<Student> {
  async getAllStudents(): Promise<Student[]> {
    const result = await this.find()
    return result;
  }

Now, imagine that you need to fetch some students based on multiple requirements. It would require us to have more complex queries than the ones shown in this article.

This too can be made without creating a custom repository. But let's imagine that you need those queries or methods somewhere else in the code. Would you just copy and paste them? That could work too, but the better practice is to use a custom repository and just call methods that you created earlier.

We could do something like this:

@Injectable()
export class CourseService {
  constructor(
    @InjectEntityManager()
    private readonly entityManager: EntityManager
  ) {}

  async exampleMethod(): Promise<void> {
    const students = entityManager.getCustomRepository(StudentRepository).getAllStudents()
  }

We haven't injected the course repository or created any meaningful methods here because the purpose of this CourseService is just to show how to inject StudentRepository methods from other parts of the code.

As you can see, we used the method from StudentRepository inside CourseService and this comes really useful as you are moving forward with your project and it becomes more and more complex.

We have covered how to create many to many relation with little addition of creating a custom repository which is completely optional and your code would work just fine without it.

Many to Many relations with TypeORM and NestJS

Written by:

Josip Brdar