Query builder in NestJS - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

What is the output of this NestJS QueryBuilder code?

Consider this code snippet using TypeORM's QueryBuilder in a NestJS service. What will be the output of the query execution?

NestJS

const users = await this.userRepository.createQueryBuilder('user')
  .where('user.age > :age', { age: 18 })
  .andWhere('user.isActive = :active', { active: true })
  .getMany();

return users.length;

A Number of all users regardless of age or active status

B Number of users older than 18 and active

C Number of users older than 18 regardless of active status

D Number of users active regardless of age

💡 Hint

Look at the where and andWhere conditions carefully.

The query filters users with age > 18 and isActive true, so the result length is the count of users matching both conditions.

📝 Syntax

intermediate

Which option correctly adds a LEFT JOIN in NestJS QueryBuilder?

You want to join the 'profile' relation of 'user' entity using a LEFT JOIN. Which code snippet is correct?

A createQueryBuilder('user').joinAndSelect('user.profile', 'profile')

B createQueryBuilder('user').join('user.profile', 'profile')

C createQueryBuilder('user').leftJoin('profile', 'user.profile')

D createQueryBuilder('user').leftJoinAndSelect('user.profile', 'profile')

💡 Hint

LEFT JOIN with selection requires a specific method.

leftJoinAndSelect performs a LEFT JOIN on the relation and selects its fields. join() is an INNER JOIN by default, and joinAndSelect() performs an INNER JOIN with selection.

🔧 Debug

advanced

Why does this QueryBuilder code throw an error?

This code throws a runtime error. What is the cause?

NestJS

const result = await this.userRepository.createQueryBuilder('user')
  .where('user.name = :name')
  .setParameters({ name: 'Alice' })
  .getOne();

A No error; code runs and returns user named Alice

B setParameters is called after where without parameters, causing error

C Missing parameter value in where clause causes error

D Using setParameters instead of passing parameters in where causes error

💡 Hint

Check how parameters are passed and when setParameters is called.

Calling where with a parameter placeholder and then setParameters before getOne is valid and works correctly.

❓ state_output

advanced

What is the value of 'count' after running this QueryBuilder code?

Given this code, what will be the value of 'count'?

NestJS

const count = await this.userRepository.createQueryBuilder('user')
  .where('user.isActive = :active', { active: true })
  .getCount();

A Total number of all users regardless of active status

B Number of users returned by getMany()

C Total number of active users in the database

D Number of users with isActive false

💡 Hint

getCount() returns the count of records matching the query.

getCount() returns the number of users where isActive is true, matching the where condition.

🧠 Conceptual

expert

Which option best describes how QueryBuilder handles SQL injection prevention?

How does NestJS QueryBuilder (TypeORM) protect against SQL injection when using parameters?

A It automatically escapes parameters passed via placeholders like :param

B It requires manual escaping of all parameters before passing

C It disables parameter binding by default for performance

D It does not protect; raw SQL must be sanitized manually

💡 Hint

Think about how parameters are passed safely in QueryBuilder.

QueryBuilder uses parameter binding with placeholders and automatically escapes values to prevent SQL injection.

Practice

1. What is the main purpose of using a QueryBuilder in NestJS with TypeORM?

easy

A. To build database queries safely and readably without raw SQL

B. To write raw SQL queries directly in the code

C. To create frontend UI components

D. To manage user authentication

Solution

1. Step 1: Understand QueryBuilder role

   QueryBuilder helps create database queries in a safe and readable way, avoiding raw SQL.

2. Step 2: Identify incorrect options

   Options B, C, and D do not describe QueryBuilder's purpose correctly.

3. Final Answer:

   To build database queries safely and readably without raw SQL -> Option A

4. Quick Check:

   QueryBuilder purpose = safe, readable queries [OK]

Hint: QueryBuilder avoids raw SQL for safer queries [OK]

Common Mistakes:

• Confusing QueryBuilder with raw SQL usage
• Thinking QueryBuilder manages UI or auth
• Assuming QueryBuilder is for frontend

2. Which of the following is the correct way to start a QueryBuilder for an entity named User in NestJS with TypeORM?

easy

A. this.userRepository.startQuery('user')

B. this.userRepository.queryBuilder('user')

C. this.userRepository.buildQuery('user')

D. this.userRepository.createQueryBuilder('user')

Solution

1. Step 1: Recall QueryBuilder method

   The correct method to start a QueryBuilder is createQueryBuilder.

2. Step 2: Match method with options

   Only this.userRepository.createQueryBuilder('user') uses createQueryBuilder correctly.

3. Final Answer:

   this.userRepository.createQueryBuilder('user') -> Option D

4. Quick Check:

   Start QueryBuilder = createQueryBuilder() [OK]

Hint: Use createQueryBuilder() to start queries [OK]

Common Mistakes:

• Using incorrect method names like queryBuilder or buildQuery
• Forgetting to call createQueryBuilder on repository
• Passing wrong arguments to createQueryBuilder

3. Given the following code snippet, what will be the output of users?

const users = await this.userRepository
  .createQueryBuilder('user')
  .where('user.age > :age', { age: 18 })
  .getMany();

medium

A. A single user with age exactly 18

B. An array of users with age greater than 18

C. An array of all users regardless of age

D. An error because of wrong parameter syntax

Solution

1. Step 1: Analyze the where condition

   The query filters users where age > 18, so only users older than 18 are selected.

2. Step 2: Understand getMany() result

   getMany() returns an array of matching users, not a single user or error.

3. Final Answer:

   An array of users with age greater than 18 -> Option B

4. Quick Check:

   Where age > 18 + getMany() = filtered array [OK]

Hint: getMany() returns all matching records as array [OK]

Common Mistakes:

• Confusing getMany() with getOne()
• Misreading the where condition syntax
• Expecting all users without filter

4. Identify the error in this QueryBuilder code snippet:

const user = await this.userRepository
  .createQueryBuilder('user')
  .where('user.name = :name', { username: 'Alice' })
  .getOne();

medium

A. The parameter name in where clause does not match the object key

B. Missing await keyword before createQueryBuilder

C. Using getOne() instead of getMany() causes error

D. The alias 'user' is not allowed in QueryBuilder

Solution

1. Step 1: Check parameter binding

   The where clause uses :name but the object key is username, causing mismatch.

2. Step 2: Understand effect of mismatch

   This mismatch means the parameter :name is undefined, causing query error or no results.

3. Final Answer:

   The parameter name in where clause does not match the object key -> Option A

4. Quick Check:

   Parameter keys must match placeholders [OK]

Hint: Match parameter keys exactly with placeholders [OK]

Common Mistakes:

• Using different names for parameters and placeholders
• Forgetting to await async calls
• Misunderstanding getOne() usage

5. You want to fetch users who are either older than 30 or have the role 'admin'. Which QueryBuilder code correctly implements this condition?

hard

A. this.userRepository.createQueryBuilder('user')
.where('user.age > 30 OR user.role = admin')
.getMany();

B. this.userRepository.createQueryBuilder('user')
.where('user.age > :age AND user.role = :role', { age: 30, role: 'admin' })
.getMany();

C. this.userRepository.createQueryBuilder('user')
.where('user.age > :age', { age: 30 })
.orWhere('user.role = :role', { role: 'admin' })
.getMany();

D. this.userRepository.createQueryBuilder('user')
.where('user.age > :age OR user.role = :role', { age: 30 })
.getMany();

Solution

1. Step 1: Understand the OR condition

   We want users older than 30 OR with role 'admin', so we use where and orWhere.

2. Step 2: Check parameter usage

   this.userRepository.createQueryBuilder('user') .where('user.age > :age', { age: 30 }) .orWhere('user.role = :role', { role: 'admin' }) .getMany(); correctly uses where with age and orWhere with role, both with parameters.

3. Step 3: Identify errors in other options

   this.userRepository.createQueryBuilder('user') .where('user.age > :age AND user.role = :role', { age: 30, role: 'admin' }) .getMany(); uses AND instead of OR. this.userRepository.createQueryBuilder('user') .where('user.age > 30 OR user.role = admin') .getMany(); lacks parameter binding. this.userRepository.createQueryBuilder('user') .where('user.age > :age OR user.role = :role', { age: 30 }) .getMany(); misses role parameter.

4. Final Answer:

   this.userRepository.createQueryBuilder('user') .where('user.age > :age', { age: 30 }) .orWhere('user.role = :role', { role: 'admin' }) .getMany(); -> Option C

5. Quick Check:

   OR condition = where + orWhere with params [OK]

Hint: Use where() then orWhere() for OR conditions [OK]

Common Mistakes:

• Using AND instead of OR in where clause
• Not binding parameters properly
• Combining conditions in one where without orWhere