TypeORM - Query Builder

Connection

Consider a simple example of how to use QueryBuilder using connection method.

import {getConnection} from "typeorm"; 

const user = await getConnection() .createQueryBuilder() 
.select("user") 
.from(User, "user") 
.where("user.id = :id", { id: 1 }) .getOne();

Entity manager

Let’s create a query builder using entity manager as follows −

import {getManager} from "typeorm"; 

const user = await getManager() .createQueryBuilder(User, "user") .where("user.id = :id", { id: 1 })    .getOne();

Repository

We can use repository to create query builder. It is described below,

import {getRepository} from "typeorm"; 

const user = await getRepository(User) .createQueryBuilder("user") .where("user.id = :id", { id: 1 }) .getOne();

Aliases

Aliases are same as SQL alias. We create alias for Student table using QueryBuilder as described below −

import {getConnection} from "typeorm"; 

const user = await getConnection() .createQueryBuilder() 
.select("stud") 
.from(Student, "stud")

This query is equivalent to,

select * from students as stud

Parameters

Parameters are used as placeholders for the dynamic values in the query. In many cases, the query to find different entity object will be same except values. For example, the query to find different student is same except the Student ID data. In this case, we can use parameter for Student ID and then change the parameter to get the different student objects.

Another important use of parameter is to prevent SQL injection. It is one of the important security breach in the modern web application. By using parameter in the query, we can survive the SQL injection attacks.

For example

"student.id = :id", { id: 1 }

Here,

:id - parameter name.

{ id: 1 } - value of the parameter

Adding expression

This section explains about how to use expressions.

where

where is used to filter the records if the condition is matched.

createQueryBuilder("student") .where("student.id = :id", { id: 1 })

This query is equivalent to,

select * from students student where student.id=1;

We can also use AND, OR, NOT, IN conditions inside.

having

Simple having expression is defined below −

createQueryBuilder("student") .having("student.id = :id", { id: 1 })

This query is equivalent to,

select * from students student having student.id=1;

orderBy

orderby is used to sort the records based on the field.

createQueryBuilder("student") .orderBy("student.name")

This query is equivalent to,

select * from students student order by student.name;

groupBy

It is used to group the records based on the specified column.

createQueryBuilder("student") .groupBy("student.id")

This query is equivalent to,

select * from students student group by student.id;

limit

It is used to limit the selection of rows. Below, example shows how to use limit in query builder,

createQueryBuilder("student") .limit(5)

This query is equivalent to,

select * from students student limit 5;

offset

Offset is used to specify, how many rows to skip the result. It is defined below −

createQueryBuilder("student") .offset(5)

This query is equivalent to,

select * from students student offset 5;

joins

join clause is used to combine rows from two or more tables, based on a related column. Consider the two entities −

Student.ts

import {Entity, PrimaryGeneratedColumn, Column, OneToMany} from "typeorm"; 
import {Project} from "./Project"; 

@Entity() 
export class User {
   
   @PrimaryGeneratedColumn() 
   id: number; 
   
   @Column() 
   name: string; 
   
   @OneToMany(type => Project, project => project.student) projects: project[]; 
}

Project.ts

import {Entity, PrimaryGeneratedColumn, Column, ManyToOne} from "typeorm"; 
import {Student} from "./Student"; 

@Entity() 
export class Project { 

   @PrimaryGeneratedColumn() 
   id: number; 
   
   @Column() 
   title: string; 
   
   @ManyToOne(type => Student, student => student.projects) student: Student; 
}

Let us perform simple left join using the below query −

const student = await createQueryBuilder("student") .leftJoinAndSelect("student.projects", "project") 
.where("student.name = :name", { name: "Student1" }) 
.getOne();

This query is equivalent to,

SELECT student.*, project.* FROM students student 
   LEFT JOIN projects project ON project.student = student.id 
   WHERE student.name = 'Student1'

Similarly, we can try inner join as well.

Join without selection

We can join data without using select. Let us try this example using Inner join as follows −

const student = await createQueryBuilder("student") .innerJoin("student.projects", "project") 
   .where("student.name = :name", { name: "student1" }) 
   .getOne();

The above query is equivalent to −

SELECT student.* FROM students student 
   INNER JOIN projects project ON project.student = student.id 
   WHERE student.name = 'Student1';

Pagination

If you have more data in your application, you need pagination, page slider or scrolling functionalities.

For example, if you want to show first five students projects in your application,

const students = await getRepository(Student) .createQueryBuilder("student") .leftJoinAndSelect("student.projects", "project") 
   .take(5) 
   .getMany();

subqueries

It is called query within another query or nested query. We use subqueries in FROM, WHERE and JOIN expressions.

Simple example is shown below −

const projects = await connection .createQueryBuilder() .select("project.id", "id")
.addSelect(subQuery => { 
   return subQuery 
      .select("student.name", "name") .from(Student, "student") 
      .limit(1); 
}, "name")
.from(Project, "project") .getMany();

Hidden field

If any of your column field is marked as {select: false} then that column is considered as hidden column. Consider the below entity −

import {Entity, PrimaryGeneratedColumn, Column} from "typeorm"; 

@Entity() 
export class Student {

   @PrimaryGeneratedColumn() 
   id: number; 
   
   @Column() 
   name: string; 
   
   @Column({select: false}) 
   address: string; 
}

Here,

address field is marked as hidden. We can use addSelect method to retrieve the information from the column. It is defined below,

const student = await connection.getRepository(Student) .createQueryBuilder() .select("student.id", "student")    .addSelect("student.address") .getMany();

getSql()

This method is used to get the generated SQL query by query builder. It is defined below −

const sql = createQueryBuilder("student") .where("student.name = :name", { name: "Student1" })  .orWhere("student.age = :age", { age: 14 }) 
.getSql();

Previous Page Print Page