Query builder in NestJS - Deep Dive

Overview - Query builder

What is it?

A query builder is a tool that helps you create database queries using code instead of writing raw SQL. It lets you build queries step-by-step with simple commands, making it easier to read and write. In NestJS, query builders are often used with TypeORM or other database libraries to interact with databases safely and efficiently.

Why it matters

Without a query builder, developers must write raw SQL queries, which can be error-prone, hard to read, and vulnerable to security issues like SQL injection. Query builders solve these problems by providing a clear, programmatic way to build queries that are easier to maintain and safer to run. This improves developer productivity and application security.

Where it fits

Before learning query builders, you should understand basic database concepts and how to write simple SQL queries. After mastering query builders, you can learn advanced database topics like transactions, migrations, and performance optimization in NestJS applications.

Mental Model

Core Idea

A query builder is like a recipe book that guides you step-by-step to prepare a database query without writing raw SQL.

Think of it like...

Imagine ordering a custom sandwich at a deli. Instead of writing a recipe yourself, you tell the server each ingredient you want, and they build it for you. Similarly, a query builder lets you specify parts of a query one by one, and it assembles the final SQL behind the scenes.

┌───────────────┐
│ Start Query   │
└──────┬────────┘
       │
┌──────▼───────┐
│ Select Table │
└──────┬───────┘
       │
┌──────▼───────┐
│ Add Filters  │
└──────┬───────┘
       │
┌──────▼───────┐
│ Add Sorting  │
└──────┬───────┘
       │
┌──────▼───────┐
│ Execute SQL  │
└──────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Basic Database Queries

Concept: Learn what a database query is and how it retrieves data.

A database query is a question you ask a database to get specific information. For example, 'Show me all users' is a simple query. In SQL, this looks like: SELECT * FROM users; This command asks the database to return all records from the users table.

Result

You get a list of all users stored in the database.

Understanding what a query does is essential before learning how to build it programmatically.

2

Foundation

Introduction to Raw SQL Queries

Concept: See how SQL commands are written and executed.

SQL is a language used to communicate with databases. You write commands like SELECT, WHERE, and ORDER BY to get and organize data. For example: SELECT name FROM users WHERE age > 18 ORDER BY name ASC; This gets names of users older than 18, sorted alphabetically.

Result

The database returns a sorted list of user names over 18.

Knowing raw SQL helps you understand what the query builder is creating behind the scenes.

3

Intermediate

Using Query Builder Basics in NestJS

🤔Before reading on: Do you think query builders generate SQL strings directly or use another method? Commit to your answer.

Concept: Learn how to start building queries using NestJS with TypeORM's query builder.

In NestJS, you can use TypeORM's query builder to create queries step-by-step. For example: const users = await dataSource .getRepository(User) .createQueryBuilder('user') .where('user.age > :age', { age: 18 }) .orderBy('user.name', 'ASC') .getMany(); This builds and runs a query to get users older than 18, sorted by name.

Result

You get an array of User objects matching the conditions.

Understanding that query builders build SQL internally helps you trust and use them effectively.

4

Intermediate

Adding Conditions and Joins

🤔Before reading on: Can query builders handle complex queries with multiple tables? Yes or no? Commit to your answer.

Concept: Learn how to add filters and join related tables using query builders.

Query builders let you add WHERE conditions and JOINs easily. For example, to get users with their posts: const usersWithPosts = await dataSource .getRepository(User) .createQueryBuilder('user') .leftJoinAndSelect('user.posts', 'post') .where('post.isPublished = :published', { published: true }) .getMany(); This fetches users who have published posts.

Result

You get users with their published posts included.

Knowing how to join tables with query builders unlocks powerful data retrieval patterns.

5

Intermediate

Parameter Binding for Security

🤔Before reading on: Do you think query builders automatically protect against SQL injection? Commit to your answer.

Concept: Learn how query builders use parameters to prevent security risks.

Query builders use parameter binding to safely insert user input into queries. Instead of concatenating strings, you write: .where('user.name = :name', { name: userInput }) This ensures the input is treated as data, not code, preventing SQL injection attacks.

Result

Your queries are safe from malicious input.

Understanding parameter binding is key to writing secure database code.

6

Advanced

Building Dynamic Queries with Conditions

🤔Before reading on: Can query builders build queries that change based on user input? Yes or no? Commit to your answer.

Concept: Learn how to add conditions dynamically to queries based on runtime data.

You can build queries that add filters only if certain conditions exist. For example: const query = dataSource.getRepository(User).createQueryBuilder('user'); if (ageFilter) { query.andWhere('user.age > :age', { age: ageFilter }); } if (nameFilter) { query.andWhere('user.name LIKE :name', { name: `%${nameFilter}%` }); } const users = await query.getMany(); This builds a query with filters only if the filters are provided.

Result

You get users filtered by the conditions that exist at runtime.

Knowing how to build queries dynamically makes your app flexible and efficient.

7

Expert

Query Builder Internals and Performance

🤔Before reading on: Do you think query builders always produce the most optimized SQL? Commit to your answer.

Concept: Understand how query builders generate SQL and their impact on performance.

Query builders translate method calls into SQL strings. They parse conditions, joins, and parameters to build the final query. However, sometimes they generate more complex SQL than hand-written queries, which can affect performance. Knowing this helps you review generated SQL and optimize when needed by using raw queries or indexes.

Result

You understand when to trust query builders and when to optimize manually.

Understanding query builder internals helps you balance convenience with performance in production.

Under the Hood

Query builders work by collecting query parts like SELECT fields, WHERE conditions, JOINs, and ORDER BY clauses as method calls. Internally, they store these parts in objects or arrays. When you execute the query, the builder assembles these parts into a valid SQL string with parameters safely inserted. Then it sends this SQL to the database driver to run.

Why designed this way?

Query builders were designed to make database queries easier, safer, and less error-prone. Writing raw SQL can lead to syntax errors and security risks. By using a builder pattern, developers can construct queries step-by-step with code completion and type safety. This design balances flexibility with safety and readability.

┌───────────────┐
│ Query Builder │
├───────────────┤
│ Collect Parts │
│ - SELECT      │
│ - WHERE       │
│ - JOIN        │
│ - ORDER BY    │
├───────────────┤
│ Assemble SQL  │
├───────────────┤
│ Bind Params   │
├───────────────┤
│ Execute Query │
└───────┬───────┘
        │
   ┌────▼─────┐
   │ Database │
   └──────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does using a query builder guarantee your queries are always fast? Commit to yes or no.

Common Belief: Query builders always produce the fastest and most efficient SQL queries.

Reality: Query builders generate correct SQL but not always the most optimized. Sometimes manual tuning or raw SQL is needed for performance.

Why it matters: Believing this can lead to slow applications if developers never review or optimize generated queries.

Quick: Do query builders eliminate all risks of SQL injection automatically? Commit to yes or no.

Common Belief: Using a query builder means you don't have to worry about SQL injection at all.

Reality: Query builders protect against injection only if you use parameter binding properly. Concatenating strings manually still risks injection.

Why it matters: Misusing query builders can create false security and open vulnerabilities.

Quick: Can query builders only build simple queries? Commit to yes or no.

Common Belief: Query builders are only for simple queries and can't handle complex joins or subqueries.

Reality: Modern query builders support complex queries, including joins, subqueries, and unions.

Why it matters: Underestimating query builders limits their use and leads to unnecessary raw SQL.

Quick: Do query builders always produce the same SQL for the same method calls? Commit to yes or no.

Common Belief: The SQL generated by query builders is always consistent and predictable.

Reality: SQL generation can vary based on database type, version, or builder implementation details.

Why it matters: Assuming consistency can cause bugs when switching databases or upgrading libraries.

Expert Zone

1

Query builders often cache parts of queries internally, so reusing the same builder instance can cause unexpected results if not reset.

2

Some query builders support advanced features like query hints or locking mechanisms that are rarely documented but critical for high-concurrency systems.

3

The order of method calls in a query builder can affect the generated SQL and its performance, so understanding the builder's internal order is important.

When NOT to use

Query builders are not ideal when you need highly optimized, database-specific SQL or complex queries that the builder cannot express easily. In such cases, writing raw SQL or using stored procedures is better.

Production Patterns

In production NestJS apps, query builders are used for dynamic filtering, pagination, and joining related data safely. Developers often combine query builders with caching layers and logging to monitor query performance and errors.

Connections

Fluent Interface Pattern

Query builders implement the fluent interface pattern by chaining method calls.

Understanding fluent interfaces helps you write and read query builder code more naturally and predictably.

SQL Injection

Query builders help prevent SQL injection by using parameter binding.

Knowing how injection works clarifies why query builders improve security when used correctly.

Recipe Writing

Building a query step-by-step is like writing a recipe with clear instructions.

This connection shows how breaking complex tasks into steps improves clarity and reduces errors.

Common Pitfalls

#1 Concatenating user input directly into query strings.

Wrong approach:

queryBuilder.where("user.name = '" + userInput + "'");

Correct approach:

queryBuilder.where('user.name = :name', { name: userInput });

Root cause:

Misunderstanding how to safely insert variables leads to security vulnerabilities.

#2 Reusing the same query builder instance without resetting.

Wrong approach:

const qb = repo.createQueryBuilder('user');
qb.where('user.age > 18');
const result1 = await qb.getMany();
qb.where('user.name = :name', { name: 'Alice' });
const result2 = await qb.getMany();

Correct approach:

const qb1 = repo.createQueryBuilder('user').where('user.age > 18');
const result1 = await qb1.getMany();
const qb2 = repo.createQueryBuilder('user').where('user.name = :name', { name: 'Alice' });
const result2 = await qb2.getMany();

Root cause:

Not realizing query builders keep state causes unexpected query results.

#3 Assuming query builder methods execute queries immediately.

Wrong approach:

const users = repo.createQueryBuilder('user').where('user.active = true');
console.log(users); // expecting data here

Correct approach:

const users = await repo.createQueryBuilder('user').where('user.active = true').getMany();
console.log(users);

Root cause:

Confusing query construction with execution leads to bugs and empty results.

Key Takeaways

Query builders let you build database queries step-by-step in code, making queries easier to write, read, and maintain.

They protect against SQL injection by using parameter binding instead of string concatenation.

Understanding how query builders assemble SQL helps you trust their safety and know when to optimize manually.

Query builders support complex queries with joins and dynamic conditions, making them powerful tools in NestJS applications.

Misusing query builders or misunderstanding their behavior can cause security risks, bugs, or performance issues.