Advanced Queries with TypeORM QueryBuilder

TypeORM is one of the most preferred ORM libraries in modern Node.js projects. With complex data retrieval needs, creating advanced and dynamic queries becomes much easier with the TypeORM QueryBuilder tool. Particularly in projects with multiple table relationships and flexible filtering options, using QueryBuilder offers significant advantages in terms of performance and readability.

Basic and Advanced Usages with QueryBuilder

QueryBuilder allows you to write both simple queries and complex SQL queries involving multiple tables in a way that is more readable and maintainable in TypeScript or JavaScript. For example, you can filter and sort the relationship between users and their posts and optionally select custom fields.

Simple Example: Fetching User List

const users = await dataSource
  .getRepository(User)
  .createQueryBuilder("user")
  .where("user.isActive = :isActive", { isActive: true })
  .orderBy("user.createdAt", "DESC")
  .getMany();

Using Join and Group By

You can easily perform grouping and join operations with QueryBuilder to fetch the number of posts per user:

const postCounts = await dataSource
  .getRepository(User)
  .createQueryBuilder("user")
  .leftJoinAndSelect("user.posts", "post")
  .select(["user.id", "user.username"])
  .addSelect("COUNT(post.id)", "postCount")
  .groupBy("user.id")
  .getRawMany();

Parameterized and Dynamic Queries

You can add dynamic filters with QueryBuilder. This provides great flexibility especially in API development.

const minAge = 18;
const country = "Turkey";

const results = await dataSource
  .getRepository(User)
  .createQueryBuilder("user")
  .where("user.age > :minAge", { minAge })
  .andWhere("user.country = :country", { country })
  .getMany();

Conclusion: Powerful Queries with TypeORM QueryBuilder

Writing advanced queries with TypeORM QueryBuilder makes your code more readable and more protected against security risks such as SQL injection. Especially in large and complex projects, it is possible to write high-performance and flexible queries thanks to QueryBuilder. With the API it provides, you can quickly create dynamic complex SQL and develop easy-to-maintain projects.