Query builder in NestJS - Mini Project: Build & Apply

Building a Simple Query with NestJS Query Builder

📖 Scenario: You are creating a small NestJS service to fetch users from a database. You want to build a query that filters users by their age.

🎯 Goal: Build a NestJS query builder that selects users from a users table and filters those whose age is greater than a given threshold.

📋 What You'll Learn

Create a variable with the initial query builder for the users table

Add a variable for the age threshold filter

Use the query builder to filter users with age greater than the threshold

Complete the query builder chain with a call to getMany() to fetch the results

💡 Why This Matters

🌍 Real World

Filtering database records dynamically is common in web apps to show users only relevant data.

💼 Career

Understanding query builders is essential for backend developers working with databases and NestJS.

1

Create the initial query builder

Create a variable called queryBuilder that uses this.userRepository.createQueryBuilder('user') to start building a query for the users table.

NestJS

# Your code here
// Create the initial query builder for users

Hint

Use this.userRepository.createQueryBuilder('user') to start the query.

2

Add an age threshold variable

Create a variable called ageThreshold and set it to 30 to use as the minimum age filter.

NestJS

const queryBuilder = this.userRepository.createQueryBuilder('user');
// Create an age threshold variable
# Your code here

Hint

Set ageThreshold to 30.

3

Add the age filter to the query builder

Use queryBuilder.where('user.age > :age', { age: ageThreshold }) to filter users older than ageThreshold.

NestJS

const queryBuilder = this.userRepository.createQueryBuilder('user');
const ageThreshold = 30;
// Add a where clause to filter users by age
# Your code here

Hint

Use where with a parameter :age and pass { age: ageThreshold }.

4

Complete the query with getMany()

Add const users = await queryBuilder.getMany() to execute the query and get the list of users older than ageThreshold.

NestJS

const queryBuilder = this.userRepository.createQueryBuilder('user');
const ageThreshold = 30;
queryBuilder.where('user.age > :age', { age: ageThreshold });
// Execute the query to get users
# Your code here

Hint

Use await queryBuilder.getMany() to fetch the results.

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