TypeORM - Query Operations

Data manipulation is used to manage and view data. This section explains about how to access database queries like insert, update, select and delete queries using QueryBuilder. Let’s go through one by one in detail.

Build insert query

Let us create a Customer entity as follows −

Customer.ts

import {Entity, PrimaryGeneratedColumn, Column} from "typeorm"; 
@Entity() 
export class Customer {       

   @PrimaryGeneratedColumn() 
   id: number; 
   
   @Column() 
   name: string; 
   
   @Column() 
   age: number; 
}

Let’s add the following changes in index.ts as follows −

index.ts

import "reflect-metadata"; 
import {createConnection} from "typeorm"; 
import {Customer} from "./entity/Customer"; 
import {getConnection} from "typeorm"; 

createConnection().then(async connection => { 
   await getConnection().createQueryBuilder()   .insert() 
      .into(Customer)  
      .values([ { name: "Adam",age:11}, 
         { name: "David",age:12} ]) .execute(); 
}).catch(error => console.log(error));

Now, start your application using the below command −

npm start

Output

You could see the following output on your screen −

Now open your mysql server, table inserted with two fields as shown below −

Build update query

Last section, we have inserted two rows of data. Let’s check how update query works. Add the following changes in index.ts as follows −

import "reflect-metadata"; 
import {createConnection} from "typeorm"; 
import {Customer} from "./entity/Customer"; 
import {getConnection} from "typeorm";

createConnection().then(async connection => { 

await getConnection()         
   .createQueryBuilder() .update(Customer) 
   .set({ name: "Michael" }) .where("id = :id", { id: 1 }) .execute(); 
   console.log("data updated"); 
   
}).catch(error => console.log(error));

Now, start your application using the below command −

npm start

You could see the following output on your screen −

Mysql table is modified as shown below −

Build select query

select query is used to display the records from the table. Let’s add the following code in index.ts as follows −

index.ts

import "reflect-metadata"; 
import {createConnection} from "typeorm"; 
import {Customer} from "./entity/Customer"; 

createConnection().then(async connection => { 

   console.log("Display records from Customer table..."); 
   const cus = new Customer();

   console.log("Loading customers from the database..."); 
   const customers = await connection.manager.find(Customer); console.log("Loaded users: ", customers); 
}).catch(error => console.log(error));

You could see the following output on your screen −

where expression

Let us add where expression in the query to filter the customers. The sample code is as follows −

import "reflect-metadata"; 
import {createConnection} from "typeorm"; 
import {Customer} from "./entity/Customer"; 
import {getConnection} from "typeorm";

createConnection().then(async connection => { 
   const customer = await getConnection() .createQueryBuilder() .select("cus") 
   .from(Customer, "cus") .where("cus.id = :id", { id: 1 }) .getOne(); 
   
   console.log(customer); 
})
.catch(error => console.log(error));

The above program will return first id records. You could see the following output on your screen,

Similarly, you can try other expressions as well.

Build delete query

Last section, we have inserted, updated and select data. Let’s check how delete query works. Add the following changes in index.ts as follows −

import "reflect-metadata"; 
import {createConnection} from "typeorm"; 
import {Customer} from "./entity/Customer"; 
import {getConnection} from "typeorm"; 

createConnection().then(async connection => { 
   await getConnection() .createQueryBuilder() 
   .delete() 
   .from(Customer) 
   .where("id = :id", { id: 1 }) .execute();
console.log("data deleted"); }).catch(error => console.log(error));

You could see the following output on your screen −

And your mysql table is modified as follows −