TypeORM and Query Builder in TypeORM

Introduction

TypeORM is an ORM that can run in NodeJS, Browser, Cordova, PhoneGap, Ionic, React Native, NativeScript, Expo, and Electron platforms and can be used with TypeScript and JavaScript (ES5, ES6, ES7, ES8). Its goal is to always support the latest JavaScript features and provide additional features that help you to develop any kind of application that uses databases – from small applications with a few tables to large scale enterprise applications with multiple databases.

ORM is a type of tool that maps entities with database tables. ORM provides simplified development process by automating object-to-table and table-to-object conversion.

Overview

TypeORM is an Object Relational Mapper library running in node.js and written in TypeScript. TypeScript is an improvement to JavaScript with optional typing. TypeScript is a compiled language. It is not interpreted at run-time. The TypeScript compiler takes TypeScript files (.ts) and compiles them in to JavaScript files (.js).

TypeORM supports multiple databases like MySQL, PostgreSQL, MariaDB, SQLite, MS SQL Server, Oracle, SAP Hana and WebSQL. TypeORM is an easy-to-use ORM to scaffold new apps that connect to databases. TypeORM functionality is RDBMS-specific concepts.

Features of TypeORM

Automatically create database table schemes based on your models

Easily insert, update and delete object in the database

Create mapping (one-to-one, one-to-many and many-to-many) between tables

Provides simple CLI commands

Benefits of TypeORM

TypeORM is easy to use ORM framework with simple coding. It has the following benefits:

1. High quality and loosely-coupled applications
2. Scalable applications
3. Easily integrate with other modules
4. Perfectly fits any architecture from small to enterprise apps

In typeorm there are many features but in this blog I will write about how to use query builder of typeorm with typescript.

Query Builder

Query builder is used build complex SQL queries in an easy way. It is initialized from Connection method.

Connection

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

import {getConnection} from "typeorm";

const user = await getConnection()
.createQueryBuilder()
.from("user", "user")
.select("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();

Adding expression use getConnection (no model needed)

table user: id, name, age

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

getConnection().createQueryBuilder().from("user", "user").select("user") .where("user.id = :id", { id: 1 }) .getRawOne();

This query is equivalent to:

select * from user where user.id=1;

orderBy is used to sort the records based on the field

getConnection().createQueryBuilder().from("user", "user").orderBy("user.name");

This query is equivalent to:

select * from user order by user.name;

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

getConnection().createQueryBuilder().from("user", "user").groupBy("user.id")

This query is equivalent to:

select * from user group by user.id; 

limit: is used to limit the selection of rows.

getConnection().createQueryBuilder().from("user", "user").limit(5);

This query is equivalent to:

select * from user limit 5; 

offset is used to specify, how many rows to skip the result.

getConnection().createQueryBuilder().from("user", "user").offset(5);

This query is equivalent to:

select * from user offset 5;

joins: join clause is used to combine rows from two or more tables, based on a related column. The example below is two tables student with project  with 1-n relationship:

student:  id, name, email, phone

project : id, name, student_id

leftJoinAndSelect

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

This query is equivalent to:

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

innerJoinAndSelect

const student = await getConnection().createQueryBuilder().from("student", "student") 
.innerJoinAndSelect("project", "project", "project.student_id = student.id") .where("student.name = :name", { name: "student1" }) .getOne();

This query is equivalent to:

SELECT student.*, project.* FROM student student INNER JOIN project project 
ON project.student_id = student.id WHERE student.name = 'student1';

Insert

import {getConnection} from "typeorm"; 

await getConnection()
.createQueryBuilder()
.insert() 
.into('student') 
.values([ { name: "test", email: "test@gmail.com", phone: "09011112222"},
 { name: "test2", email: "test2@gmail.com", phone: "09011112222"}
]) 
.execute();

Update

import {getConnection} from "typeorm"; 

await getConnection().createQueryBuilder() 
.update('student') 
.set({ name: "test3", email: "test3@gmail.com"}) 
.where("id = :id", { id: 1 }) 
.execute(); 

Delete

import {getConnection} from "typeorm"; 

 await getConnection() 
.createQueryBuilder() 
.delete()
.from('student')
.where("id = :id", { id: 1 }) .execute();

Subqueries

Example 1: Use subQuery to select field name in table student the record has email contains the string test

Example 2: Use subQuery to select max id in table project match the field name containing the string test and select studentId, studentName, projectId

const student = await getConnection().createQueryBuilder().select("student.name", "name") 
.from((subQuery) => { return subQuery.select("student.name", "name") 
                     .from("student", "student").where("student.email like :email", { email: '%test%'}) },
                   "student") .getRawMany();

const student = await getConnection().createQueryBuilder()
.select(`student.id as studentId,student.name as studentName,project.id as projectId`)) 
.from("student", "student").leftJoin("project", "project", "project.student_id = student.id")
.where ("project.id = (select max(id) from project where name like '%test%' ")).getRawMany();

Adding expression use getRepository() with model

The example has the following User model:

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

@Entity('user') 
export class User { 
@PrimaryGeneratedColumn() 
id: number;
 
@Column('varchar', {name: 'name', nullable: true, length: 50}) 
name: string | null; 

@Column('int', {name: 'age', nullable: true}) 
name: number | null; 
}

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

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

This query is equivalent to:

select * from user where user.id=1;

orderBy is used to sort the records based on the field

getRepository(User).createQueryBuilder("user").orderBy("user.name");

This query is equivalent to:

select * from user order by user.name;

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

getRepository(User).createQueryBuilder("user") .groupBy("user.id")

This query is equivalent to:

select * from user group by user.id; 

limit: is used to limit the selection of rows.

getRepository(User).createQueryBuilder("user").limit(5);

This query is equivalent to:

select * from user limit 5; 

offset is used to specify, how many rows to skip the result

getRepository(User).createQueryBuilder("user") .offset(5);

This query is equivalent to:

select * from user offset 5;

joins: join clause is used to combine rows from two or more tables, based on a related column. Consider two entity:

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

@Entity('student')
export class Student { 
@PrimaryGeneratedColumn()
id: number; 

@Column('varchar', {name: 'name', nullable: true, length: 50})
name: string | null;

@Column('varchar', {name: 'email', nullable: true, length: 30})
email: string | null;

@Column('varchar', {name: 'phone', nullable: true, length: 11})
phone: string | null;

@OneToMany(()=> Project, project => project.student) 
projects: project[]; 
} 

import {Entity, PrimaryGeneratedColumn, Column, ManyToOne}

from "typeorm"; import {Student} from "./Student";

@Entity('project') 
export class Project {

@PrimaryGeneratedColumn() 
id: number;

@Column('varchar', {name: 'name', nullable: true, length: 50})
name: string | null;

@Column('bigint', {name: 'student_id', nullable: true, unsigned: true})
studentId: number | null;

@ManyToOne(() => Student, student => student.projects)
@JoinColumn([{name: 'student_id', referencedColumnName: 'id'}])
student : Student;
}

leftJoinAndSelect

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

This query is equivalent to:

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

innerJoinAndSelect

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

This query is equivalent to:

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

Insert

import {getConnection} from "typeorm"; 

await getRepository(Student)
.createQueryBuilder() 
.insert() 
.into(Student) 
.values([ { name: "test", email: "test@gmail.com", phone: "09011112222"}, 
{ name: "test2", email: "test2@gmail.com", phone: "09011112222"} ]) 
.execute();

Update

import {getConnection} from "typeorm"; 

await getRepository(Student)
.createQueryBuilder() 
.update(Student) 
.set({ name: "test3", email: "test3@gmail.com"}) 
.where("id = :id", { id: 1 }) .execute();

Delete

import {getConnection} from "typeorm"; 
await getRepository(Student).createQueryBuilder() 
.delete() 
.from(Student) 
.where("id = :id", { id: 1 }) 
.execute();

Conclusion

It is recommended to use query builder because it can build sql sentences from easy to complex

Syntax is similar to pure sql, so it is easy to code and read

It is also possible to use the query builder normally without the model declaration

In terms of performance, using query builder access time will be faster than using typeorm in api repository

References

https://typeorm.io/