How to add a raw PostgreSQL function to a query builder join in TypeORM?

Using the Query Builder in TypeORM to incorporate raw PostgreSQL functions enables developers to directly leverage database-native capabilities for complex query operations, providing significant flexibility and power. To utilize raw PostgreSQL functions within the TypeORM Query Builder, we can employ the raw method. The following example demonstrates how to integrate the PostgreSQL LOWER function into a query, which converts text data to lowercase.

Example

Assume we have an entity named User with fields firstName and lastName. Now, we want to search for users based on the lowercase firstName. We can implement this as follows:

typescript
import { getConnection } from "typeorm";

// Create QueryBuilder
const userRepository = getConnection().getRepository(User);
const users = await userRepository
  .createQueryBuilder("user")
  .where("LOWER(user.firstName) = LOWER(:firstName)", { firstName: 'alice' })
  .getMany();

console.log(users);

In this example, the LOWER function ensures case-insensitive comparison. LOWER(user.firstName) converts each value of the firstName field in the database to lowercase and compares it with the lowercase input parameter 'alice'.

Expanded Example: Using More Complex Functions

When working with more complex PostgreSQL functions or expressions, you can directly insert raw SQL statements using the raw method. For instance, to filter users based on their creation date using the PostgreSQL DATE_PART function to extract the year:

typescript
import { getConnection } from "typeorm";

const userRepository = getConnection().getRepository(User);
const users = await userRepository
  .createQueryBuilder("user")
  .where("DATE_PART('year', user.createdAt) = :year", { year: 2021 })
  .getMany();

console.log(users);

Important Considerations

When using raw SQL or specific functions, it is crucial to be aware of SQL injection risks. Although TypeORM's parameter replacement feature offers some security, validating and sanitizing all user input data when constructing complex SQL statements remains essential.

Through these examples, it becomes evident that leveraging the Query Builder in TypeORM with raw PostgreSQL functions is straightforward and effectively harnesses database-native capabilities to optimize and simplify data queries.