How do I cause n + 1 problems with NestJs TypeOrm?

Question

I was studying TypeOrm and I'm trying to create an N+1 problem, but it's not happening properly. Company and employee have a 1:N relationship.

Could you tell me why N + 1 is not causing any problems? I've tried setting up Lazy and setting up Eager, but I've been doing left join continuously so that n + 1 doesn't cause problems.

entity

@Entity('COMPANY')
export class Company extends TimeStamped {
    @PrimaryGeneratedColumn('increment')
    companyId: number;

    @Column({ type: 'varchar' })
    companyName: string;

    @OneToMany(() => Employee, (employee) => employee.company, {
        onDelete: 'CASCADE'
    })
    employee: Employee[];
}

@Entity('EMPLOYEE')
export class Employee extends TimeStamped {
    @PrimaryGeneratedColumn('increment')
    employeeId: number;

    @Column({ type: 'varchar' })
    employeeName: string;

    @ManyToOne(() => Company, (company) => company.employee)
    @JoinColumn([{ name: 'companyId', referencedColumnName: 'companyId' }])
    company: Company;
}

crud

@Injectable()
export class CompanyService {
    constructor(
        @InjectRepository(Company)
        private readonly companyRepository: Repository<Company>
    ) {}

    getAllCompany() {
        return this.companyRepository.find({ relations: ['employee'] });
    }

    getCompany(companyId: number) {
        return this.companyRepository.findOne(companyId, {
            relations: ['employee']
        });
    }

    setCompany(setComanyDto: SetCompanyDto) {
        return this.companyRepository.save(setComanyDto);
    }
}


@Injectable()
export class EmployeeService {
    constructor(
        @InjectRepository(Employee)
        private readonly employeeRepository: Repository<Employee>,

        @InjectRepository(Company)
        private readonly companyRepository: Repository<Company>
    ) {}

    getAllEmployee() {
        return this.employeeRepository.find({
            relations: ['company']
        });
    }

    getEmployee(employeeId: number) {
        return this.employeeRepository.findOne(employeeId, {
            relations: ['company']
        });
    }

    async setEmployee(setEmployeeDto: SetEmployeeDto) {
        const employee: Employee = new Employee();
        employee.employeeName = setEmployeeDto.employeeName;
        employee.company = await this.companyRepository.findOne(
            setEmployeeDto.companyId
        );

        return this.employeeRepository.save(employee);
    }
}

Eranga Heshan · Accepted Answer · 2021-10-15T11:58:33.347

I believe you have a good idea about what N+1 problem is. You can check this question if you need to understand it more clearly.

If you use eager loading, you will not see the N+1 problem anyway since it joins the related entity and return both entities in one query.

If you specify relations as you've done below, again you will not see the N+1 problem since it creates a join query and returns all in 1 single query.

this.companyRepository.find({ relations: ['employee'] });

To create the N+1 problem,

Update your Company entity like below:

@Entity('COMPANY')
export class Company extends TimeStamped {
  @PrimaryGeneratedColumn('increment')
  companyId: number;

  @Column({ type: 'varchar' })
  companyName: string;

  @OneToMany(() => Employee, (employee) => employee.company, {
    onDelete: 'CASCADE',
    lazy: true
  })
  employee: Promise<Employee[]>
}

In your CompanyService, create a new function to simulate the N+1 problem like below:

@Injectable()
export class CompanyService {
  async createNPlus1Problem() {
    // Query all companies (let's say you have N number of companies)
    // SELECT * FROM "COMPANY";
    const companies = this.companyRepository.find();

    // The following `for` loop, loops through all N number of 
    // companies to get the employee data of each
    for(company of companies) {
      // Query employees of each company
      // SELECT * FROM "EMPLOYEE" WHERE "companyId"=?;
      const employees = await company.employee;
    }
  }
}

So in the above example, you have 1 query to get the company data. And N queries to get the employee data. Hence the N+1 problem.

Hope this clarifies your problem. Cheers !!!

oh my god. Thank you so much Heshan. I'm sorry, but may I ask you one more question? When I solved the N+1 problem in TypeOrm, I used queryBuilder to solve it. Is this a good way? — momo, Aug 30 '21 at 18:23
I would usually prefer using `find` method to do simple queries and use `QueryBuilder` to do more complex ones. For example, `eager` loading will happen with `find` while it will not be for `QueryBuilder` (you will have to write the join operation). But in theory, both should be fine and safe to use. By safe I mean both method should avoid SQL injections etc. — Eranga Heshan, Aug 31 '21 at 01:21
Shouldn't it be `employee: Promise` if we specify `lazy: true`? As per https://orkhan.gitbook.io/typeorm/docs/eager-and-lazy-relations#lazy-relations — Andrii Abramov, Oct 15 '21 at 10:04
@AndriiAbramov I updated the answer. I believe it would have still worked without the promise since it is just the type. But you're absolutely correct, it should be a promise. Thank you for pointing it out. — Eranga Heshan, Oct 15 '21 at 12:00

score 0 · Answer 2 · answered Sep 15 '22 at 05:41

0

You can try to use this library https://github.com/Adrinalin4ik/Nestjs-Graphql-Tools it allows to overcome n+1 with the simple decorator. And it has minimum deps.

answered Sep 15 '22 at 05:41

Алексей Панфилкин

1

score 0 · Answer 3 · answered Sep 15 '22 at 07:03

You can use leftJoinAndSelect method with query builder. https://orkhan.gitbook.io/typeorm/docs/select-query-builder#joining-relations

    const user = await createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo")
    .where("user.name = :name", { name: "Timber" })
    .andWhere("photo.isRemoved = :isRemoved", { isRemoved: false })
    .getOne()

    SELECT user.*, photo.* FROM users user
    LEFT JOIN photos photo ON photo.user = user.id AND photo.isRemoved = FALSE
    WHERE user.name = 'Timber'

How do I cause n + 1 problems with NestJs TypeOrm?

3 Answers3