Query builder in NestJS - Step-by-Step Execution

Concept Flow - Query builder

Start Query Builder

↓

Initialize Query Object

↓

Add Select Fields

↓

Add Where Conditions

↓

Add Joins (optional)

↓

Add Order/Limit (optional)

↓

Execute Query

↓

Return Results

This flow shows how a query builder in NestJS creates a query step-by-step, adding parts like select fields, conditions, joins, and finally runs the query to get results.

Execution Sample

NestJS

const query = this.repo.createQueryBuilder('user')
  .select(['user.id', 'user.name'])
  .where('user.age > :age', { age: 18 })
  .orderBy('user.name', 'ASC')
  .getMany();

Builds a query to select user id and name where age is over 18, orders by name ascending, and fetches the results.

Execution Table

Step	Action	Query State	Parameters	Output
1	Initialize QueryBuilder with alias 'user'	SELECT * FROM user	{}	QueryBuilder object created
2	Add select fields ['user.id', 'user.name']	SELECT user.id, user.name FROM user	{}	Select fields set
3	Add where condition 'user.age > :age' with {age:18}	SELECT user.id, user.name FROM user WHERE user.age > :age	{age:18}	Where condition added
4	Add order by 'user.name' ASC	SELECT user.id, user.name FROM user WHERE user.age > :age ORDER BY user.name ASC	{age:18}	Order by added
5	Execute getMany()	Final SQL executed	{age:18}	Returns array of user objects matching criteria
6	End	Query complete	{}	Results returned

💡 Query executed and results returned, no more steps.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
query	undefined	QueryBuilder with select fields	QueryBuilder with where condition	QueryBuilder with order by	Promise of results	Array of user objects

Key Moments - 2 Insights

Why do we use ':age' in the where condition instead of directly writing the number 18?

Using ':age' is a parameter placeholder that safely inserts the value 18, preventing SQL injection and allowing reuse. See execution_table step 3 where parameters {age:18} are passed separately.

What happens if we forget to call getMany() at the end?

Without getMany(), the query is not executed and no results are returned. The query builder only builds the query until getMany() triggers execution (see execution_table step 5).

Visual Quiz - 3 Questions

Look at the execution table, what is the query state after step 3?

A SELECT * FROM user

B SELECT user.id, user.name FROM user WHERE user.age > :age

C SELECT user.id, user.name FROM user ORDER BY user.name ASC

D Final SQL executed

💡 Hint

Check the 'Query State' column in row with Step 3.

At which step is the query actually executed to get results?

A Step 2

B Step 3

C Step 5

D Step 4

💡 Hint

Look for 'Execute getMany()' action in the execution_table.

If we change the where condition to 'user.age > :age' with {age:21}, how does the parameters column change at step 3?

A {age:21}

B {}

C {age:18}

D No change

💡 Hint

Parameters column shows the values passed for placeholders, see step 3.

Concept Snapshot

NestJS Query Builder:
- Start with createQueryBuilder(alias)
- Chain select(), where(), orderBy(), join() as needed
- Use parameter placeholders like ':param' with values
- Call getMany() or getOne() to execute
- Returns results as objects

Build queries step-by-step, then run to get data.

Full Transcript

This visual execution shows how NestJS Query Builder works step-by-step. First, it creates a query builder object with an alias. Then it adds select fields to choose which columns to fetch. Next, it adds a where condition using a parameter placeholder to safely insert values. After that, it adds an order by clause to sort results. Finally, calling getMany() executes the SQL query and returns matching user objects. Variables track the query state and parameters at each step. Key moments explain why parameters are used and the importance of calling getMany(). The quiz tests understanding of query state, execution step, and parameter changes.

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