Two Core Methods for Implementing LIKE Queries in TypeORM

Introduction

In database operations, fuzzy queries are a common requirement, especially when handling user input or search functionalities. TypeORM, as a powerful ORM framework for TypeScript and JavaScript, offers multiple ways to implement LIKE queries. However, many developers often encounter issues where queries return empty arrays, typically due to misunderstandings about parameter binding and wildcard usage. Based on a typical problem scenario, this article analyzes the root causes and systematically introduces two effective solutions.

Problem Analysis

In the original problem, the developer attempted to execute a LIKE query using TypeORM's QueryBuilder, but the code consistently returned an empty array. Error code examples include:

var data = await getRepository(User)
  .createQueryBuilder("user")
  .where("user.firstName = %:name%", { name: firstName })
  .getMany();

And:

var data = await getRepository(User)
  .createQueryBuilder("user")
  .where("user.firstName like %:name%", { name: firstName })
  .getMany();

The main issue with these codes is incorrectly placing wildcards % within the query string instead of as part of the parameter values. In TypeORM, the parameter binding mechanism for the where clause requires wildcards to be included directly in the parameter values, not in the query string. For example, %:name% is treated as a literal string rather than a dynamic parameter, causing the query to fail to match any records.

Solution 1: Using QueryBuilder with Parameterized Queries

According to the best answer (score 10.0), the correct implementation involves embedding wildcards in the parameter values to ensure the safety of parameterized queries. The core code is:

var data = await getRepository(User)
  .createQueryBuilder("user")
  .where("user.firstName like :name", { name: `%${firstName}%` })
  .getMany();

Key aspects of this method include:

• Using only :name as a placeholder in the query string, avoiding direct inclusion of wildcards.
• Including wildcards % as part of the parameter value, dynamically generated via template strings `%${firstName}%`.
• This ensures query flexibility and security, preventing SQL injection attacks.

From an underlying mechanism perspective, TypeORM binds the parameter value `%${firstName}%` to the :name placeholder, generating SQL statements like WHERE user.firstName LIKE '%John%'. This approach not only keeps the code concise but also fully utilizes TypeORM's query builder capabilities, supporting complex query scenarios.

Solution 2: Leveraging the Built-in Like Function

As a supplementary reference (score 7.9), TypeORM also provides a built-in Like function to simplify query operations. Example code:

import {Like} from "typeorm";

var data = await getRepository(User).find({
    firstName: Like(`%${firstName}%`)
});

Characteristics of this method include:

• Direct use of the find method, eliminating the need for complex QueryBuilder construction.
• The Like function automatically handles wildcard logic, making the code more readable.
• Suitable for simple fuzzy query scenarios, though it may be less flexible than QueryBuilder.

Note that the Like function is part of TypeORM's advanced find options, ideal for rapid development, but QueryBuilder might be more appropriate for multi-condition or complex join scenarios.

Comparison and Best Practices

Both methods have their advantages: the QueryBuilder approach offers greater flexibility and control, suitable for complex queries; while the Like function simplifies code and improves development efficiency. In practical projects, it is recommended to choose based on specific needs:

• For simple fuzzy queries, use the Like function to reduce code volume.
• For scenarios requiring dynamic conditions or multi-table joins, prioritize QueryBuilder.
• Always place wildcards within parameter values, avoiding direct use in query strings to ensure correctness and security.

Additionally, developers should pay attention to escaping special characters, such as when discussing HTML tags, where text like <br> needs escaping to prevent parsing as HTML elements. This highlights the importance of proper content handling in technical documentation.

Conclusion

Through this analysis, we have clarified the correct methods for executing LIKE fuzzy queries in TypeORM. The core lies in understanding the parameter binding mechanism, treating wildcards as part of parameter values rather than fixed content in query strings. Whether using QueryBuilder or the built-in Like function, both can effectively achieve fuzzy matching, enhancing an application's data retrieval capabilities. Mastering these techniques helps avoid common errors and write more robust and efficient database code.