Why Query builder in NestJS? - Purpose & Use Cases

The Big Idea

What if you could write database queries as easily as writing normal code, without worrying about syntax errors or security?

The Scenario

Imagine writing raw SQL queries by hand every time you need to fetch or update data in your NestJS app. You have to carefully build strings with table names, conditions, and joins.

The Problem

Manually writing SQL strings is slow and error-prone. One small typo can break your query. It's hard to read and maintain, especially as queries grow complex. You also risk SQL injection if not careful.

The Solution

A query builder lets you create database queries using easy-to-read code instead of raw strings. It automatically handles syntax, escaping, and complex joins, making your code safer and clearer.

Before vs After

✗ Before

const query = `SELECT * FROM users WHERE age > ${age}`;

✓ After

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

What It Enables

It enables building complex, safe, and dynamic database queries with simple, readable code that integrates smoothly into your NestJS app.

Real Life Example

When building a user search feature with filters like age, location, and status, a query builder helps combine these conditions dynamically without messy string concatenation.

Key Takeaways

Manual SQL strings are hard to write and maintain.

Query builders simplify query creation with readable code.

They improve safety, reduce bugs, and handle complexity easily.

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