What is QueryBuilder

QueryBuilder is one of the most powerful features of TypeORM – it allows you to build SQL queries using elegant and convenient syntax, execute them and get automatically transformed entities.

Simple example of QueryBuilder:

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

It builds the following SQL query:

SELECT
    user.id as userId,
    user.firstName as userFirstName,
    user.lastName as userLastName
FROM users user
WHERE user.id = 1

and returns you an instance of User:

User {
    id: 1,
    firstName: "Timber",
    lastName: "Saw"
}

Important note when using the QueryBuilder

When using the QueryBuilder, you need to provide unique parameters in your WHERE expressions. This will not work:

const result = await dataSource
    .createQueryBuilder('user')
    .leftJoinAndSelect('user.linkedSheep', 'linkedSheep')
    .leftJoinAndSelect('user.linkedCow', 'linkedCow')
    .where('user.linkedSheep = :id', { id: sheepId })
    .andWhere('user.linkedCow = :id', { id: cowId });

… but this will:

const result = await dataSource
    .createQueryBuilder('user')
    .leftJoinAndSelect('user.linkedSheep', 'linkedSheep')
    .leftJoinAndSelect('user.linkedCow', 'linkedCow')
    .where('user.linkedSheep = :sheepId', { sheepId })
    .andWhere('user.linkedCow = :cowId', { cowId });

Note that we uniquely named :sheepId and :cowId instead of using :id twice for different parameters.

How to create and use a QueryBuilder

There are several ways how you can create a Query Builder:

• Using DataSource:const user = await dataSource .createQueryBuilder() .select("user") .from(User, "user") .where("user.id = :id", { id: 1 }) .getOne()
• Using entity manager:const user = await dataSource.manager .createQueryBuilder(User, "user") .where("user.id = :id", { id: 1 }) .getOne()
• Using repository:const user = await dataSource .getRepository(User) .createQueryBuilder("user") .where("user.id = :id", { id: 1 }) .getOne()

There are 5 different QueryBuilder types available:

• SelectQueryBuilder – used to build and execute SELECT queries. Example:const user = await dataSource .createQueryBuilder() .select("user") .from(User, "user") .where("user.id = :id", { id: 1 }) .getOne()
• InsertQueryBuilder – used to build and execute INSERT queries. Example:await dataSource .createQueryBuilder() .insert() .into(User) .values([ { firstName: "Timber", lastName: "Saw" }, { firstName: "Phantom", lastName: "Lancer" }, ]) .execute()
• UpdateQueryBuilder – used to build and execute UPDATE queries. Example:await dataSource .createQueryBuilder() .update(User) .set({ firstName: "Timber", lastName: "Saw" }) .where("id = :id", { id: 1 }) .execute()
• DeleteQueryBuilder – used to build and execute DELETE queries. Example:await dataSource .createQueryBuilder() .delete() .from(User) .where("id = :id", { id: 1 }) .execute()
• RelationQueryBuilder – used to build and execute relation-specific operations [TBD]. Example:await dataSource .createQueryBuilder() .relation(User,"photos") .of(id) .loadMany();

You can switch between different types of query builder within any of them, once you do, you will get a new instance of query builder (unlike all other methods).