Select using Query Builder

​What is QueryBuilder​
​Important note when using the QueryBuilder​
​How to create and use a QueryBuilder​
​Getting values using QueryBuilder​
​What are aliases for?​
​Using parameters to escape data​
​Adding WHERE expression​
​Adding HAVING expression​
​Adding ORDER BY expression​
​Adding GROUP BY expression​
​Adding LIMIT expression​
​Adding OFFSET expression​
​Joining relations​
​Inner and left joins​
​Join without selection​
​Joining any entity or table​
​Joining and mapping functionality​
​Getting the generated query​
​Getting raw results​
​Streaming result data​
​Using pagination​
​Set locking​
​Use custom index​
​Max execution time​
​Partial selection​
​Using subqueries​
​Hidden Columns​
​Querying Deleted rows​
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).
Getting values using QueryBuilder
To get a single result from the database, for example to get a user by id or name, you must use getOne:
const timber = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id OR user.name = :name", { id: 1, name: "Timber" })
    .getOne()
getOneOrFail will get a single result from the database, but if no result exists it will throw an EntityNotFoundError:
const timber = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id OR user.name = :name", { id: 1, name: "Timber" })
    .getOneOrFail()
To get multiple results from the database, for example, to get all users from the database, use getMany:
const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .getMany()
There are two types of results you can get using select query builder: entities or raw results. Most of the time, you need to select real entities from your database, for example, users. For this purpose, you use getOne and getMany. But sometimes you need to select some specific data, let's say the sum of all user photos. This data is not an entity, it's called raw data. To get raw data, you use getRawOne and getRawMany. Examples:
const { sum } = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .select("SUM(user.photosCount)", "sum")
    .where("user.id = :id", { id: 1 })
    .getRawOne()
const photosSums = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .select("user.id")
    .addSelect("SUM(user.photosCount)", "sum")
    .groupBy("user.id")
    .getRawMany()
​
// result will be like this: [{ id: 1, sum: 25 }, { id: 2, sum: 13 }, ...]
What are aliases for?
We used createQueryBuilder("user"). But what is "user"? It's just a regular SQL alias. We use aliases everywhere, except when we work with selected data.
createQueryBuilder("user") is equivalent to:
createQueryBuilder().select("user").from(User, "user")
Which will result in the following SQL query:
SELECT ... FROM users user
In this SQL query, users is the table name, and user is an alias we assign to this table. Later we use this alias to access the table:
createQueryBuilder()
    .select("user")
    .from(User, "user")
    .where("user.name = :name", { name: "Timber" })
Which produces the following SQL query:
SELECT ... FROM users user WHERE user.name = 'Timber'
See, we used the users table by using the user alias we assigned when we created a query builder.
One query builder is not limited to one alias, they can have multiple aliases. Each select can have its own alias, you can select from multiple tables each with its own alias, you can join multiple tables each with its own alias. You can use those aliases to access tables are you selecting (or data you are selecting).
Using parameters to escape data
We used where("user.name = :name", { name: "Timber" }). What does { name: "Timber" } stand for? It's a parameter we used to prevent SQL injection. We could have written: where("user.name = '" + name + "'), however this is not safe, as it opens the code to SQL injections. The safe way is to use this special syntax: where("user.name = :name", { name: "Timber" }), where :name is a parameter name and the value is specified in an object: { name: "Timber" }.
.where("user.name = :name", { name: "Timber" })
is a shortcut for:
.where("user.name = :name")
.setParameter("name", "Timber")
Note: do not use the same parameter name for different values across the query builder. Values will be overridden if you set them multiple times.
You can also supply an array of values, and have them transformed into a list of values in the SQL statement, by using the special expansion syntax:
.where("user.name IN (:...names)", { names: [ "Timber", "Cristal", "Lina" ] })
Which becomes:
WHERE user.name IN ('Timber', 'Cristal', 'Lina')
Adding WHERE expression
Adding a WHERE expression is as easy as:
createQueryBuilder("user").where("user.name = :name", { name: "Timber" })
Which will produce:
SELECT ... FROM users user WHERE user.name = 'Timber'
You can add AND into an existing WHERE expression:
createQueryBuilder("user")
    .where("user.firstName = :firstName", { firstName: "Timber" })
    .andWhere("user.lastName = :lastName", { lastName: "Saw" })
Which will produce the following SQL query:
SELECT ... FROM users user WHERE user.firstName = 'Timber' AND user.lastName = 'Saw'
You can add OR into an existing WHERE expression:
createQueryBuilder("user")
    .where("user.firstName = :firstName", { firstName: "Timber" })
    .orWhere("user.lastName = :lastName", { lastName: "Saw" })
Which will produce the following SQL query:
SELECT ... FROM users user WHERE user.firstName = 'Timber' OR user.lastName = 'Saw'
You can do an IN query with the WHERE expression:
createQueryBuilder("user").where("user.id IN (:...ids)", { ids: [1, 2, 3, 4] })
Which will produce the following SQL query:
SELECT ... FROM users user WHERE user.id IN (1, 2, 3, 4)
You can add a complex WHERE expression into an existing WHERE using Brackets
createQueryBuilder("user")
    .where("user.registered = :registered", { registered: true })
    .andWhere(
        new Brackets((qb) => {
            qb.where("user.firstName = :firstName", {
                firstName: "Timber",
            }).orWhere("user.lastName = :lastName", { lastName: "Saw" })
        }),
    )
Which will produce the following SQL query:
SELECT ... FROM users user WHERE user.registered = true AND (user.firstName = 'Timber' OR user.lastName = 'Saw')
You can add a negated complex WHERE expression into an existing WHERE using NotBrackets
createQueryBuilder("user")
    .where("user.registered = :registered", { registered: true })
    .andWhere(
        new NotBrackets((qb) => {
            qb.where("user.firstName = :firstName", {
                firstName: "Timber",
            }).orWhere("user.lastName = :lastName", { lastName: "Saw" })
        }),
    )
Which will produce the following SQL query:
SELECT ... FROM users user WHERE user.registered = true AND NOT((user.firstName = 'Timber' OR user.lastName = 'Saw'))
You can combine as many AND and OR expressions as you need. If you use .where more than once you'll override all previous WHERE expressions.
Note: be careful with orWhere - if you use complex expressions with both AND and OR expressions, keep in mind that they are stacked without any pretences. Sometimes you'll need to create a where string instead, and avoid using orWhere.
Adding HAVING expression
Adding a HAVING expression is easy as:
createQueryBuilder("user").having("user.name = :name", { name: "Timber" })
Which will produce following SQL query:
SELECT ... FROM users user HAVING user.name = 'Timber'
You can add AND into an exist HAVING expression:
createQueryBuilder("user")
    .having("user.firstName = :firstName", { firstName: "Timber" })
    .andHaving("user.lastName = :lastName", { lastName: "Saw" })
Which will produce the following SQL query:
SELECT ... FROM users user HAVING user.firstName = 'Timber' AND user.lastName = 'Saw'
You can add OR into a exist HAVING expression:
createQueryBuilder("user")
    .having("user.firstName = :firstName", { firstName: "Timber" })
    .orHaving("user.lastName = :lastName", { lastName: "Saw" })
Which will produce the following SQL query:
SELECT ... FROM users user HAVING user.firstName = 'Timber' OR user.lastName = 'Saw'
You can combine as many AND and OR expressions as you need. If you use .having more than once you'll override all previous HAVING expressions.
Adding ORDER BY expression
Adding an ORDER BY expression is easy as:
createQueryBuilder("user").orderBy("user.id")
Which will produce:
SELECT ... FROM users user ORDER BY user.id
You can change the ordering direction from ascending to descending (or versa):
createQueryBuilder("user").orderBy("user.id", "DESC")
​
createQueryBuilder("user").orderBy("user.id", "ASC")
You can add multiple order-by criteria:
createQueryBuilder("user").orderBy("user.name").addOrderBy("user.id")
You can also use a map of order-by fields:
createQueryBuilder("user").orderBy({
    "user.name": "ASC",
    "user.id": "DESC",
})
If you use .orderBy more than once you'll override all previous ORDER BY expressions.
Adding DISTINCT ON expression (Postgres only)
When using both distinct-on with an order-by expression, the distinct-on expression must match the leftmost order-by. The distinct-on expressions are interpreted using the same rules as order-by. Please note that, using distinct-on without an order-by expression means that the first row of each set is unpredictable.
Adding a DISTINCT ON expression is easy as:
createQueryBuilder("user").distinctOn(["user.id"]).orderBy("user.id")
Which will produce:
SELECT DISTINCT ON (user.id) ... FROM users user ORDER BY user.id
Adding GROUP BY expression
Adding a GROUP BY expression is easy as:
createQueryBuilder("user").groupBy("user.id")
Which will produce the following SQL query:
SELECT ... FROM users user GROUP BY user.id
To add more group-by criteria use addGroupBy:
createQueryBuilder("user").groupBy("user.name").addGroupBy("user.id")
If you use .groupBy more than once you'll override all previous GROUP BY expressions.
Adding LIMIT expression
Adding a LIMIT expression is easy as:
createQueryBuilder("user").limit(10)
Which will produce the following SQL query:
SELECT ... FROM users user LIMIT 10
The resulting SQL query depends on the type of database (SQL, mySQL, Postgres, etc). Note: LIMIT may not work as you may expect if you are using complex queries with joins or subqueries. If you are using pagination, it's recommended to use take instead.
Adding OFFSET expression
Adding an SQL OFFSET expression is easy as:
createQueryBuilder("user").offset(10)
Which will produce the following SQL query:
SELECT ... FROM users user OFFSET 10
The resulting SQL query depends on the type of database (SQL, mySQL, Postgres, etc). Note: OFFSET may not work as you may expect if you are using complex queries with joins or subqueries. If you are using pagination, it's recommended to use skip instead.
Joining relations
Let's say you have the following entities:
import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from "typeorm"
import { Photo } from "./Photo"
​
@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number
​
    @Column()
    name: string
​
    @OneToMany((type) => Photo, (photo) => photo.user)
    photos: Photo[]
}
import { Entity, PrimaryGeneratedColumn, Column, ManyToOne } from "typeorm"
import { User } from "./User"
​
@Entity()
export class Photo {
    @PrimaryGeneratedColumn()
    id: number
​
    @Column()
    url: string
​
    @ManyToOne((type) => User, (user) => user.photos)
    user: User
}
Now let's say you want to load user "Timber" with all of his photos:
const user = await createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo")
    .where("user.name = :name", { name: "Timber" })
    .getOne()
You'll get the following result:
{
    id: 1,
    name: "Timber",
    photos: [{
        id: 1,
        url: "me-with-chakram.jpg"
    }, {
        id: 2,
        url: "me-with-trees.jpg"
    }]
}
As you can see leftJoinAndSelect automatically loaded all of Timber's photos. The first argument is the relation you want to load and the second argument is an alias you assign to this relation's table. You can use this alias anywhere in query builder. For example, let's take all Timber's photos which aren't removed.
const user = await createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo")
    .where("user.name = :name", { name: "Timber" })
    .andWhere("photo.isRemoved = :isRemoved", { isRemoved: false })
    .getOne()
This will generate following SQL query:
SELECT user.*, photo.* FROM users user
    LEFT JOIN photos photo ON photo.user = user.id
    WHERE user.name = 'Timber' AND photo.isRemoved = FALSE
You can also add conditions to the join expression instead of using "where":
const user = await createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo", "photo.isRemoved = :isRemoved", {
        isRemoved: false,
    })
    .where("user.name = :name", { name: "Timber" })
    .getOne()
This will generate the following SQL query:
SELECT user.*, photo.* FROM users user
    LEFT JOIN photos photo ON photo.user = user.id AND photo.isRemoved = FALSE
    WHERE user.name = 'Timber'
Inner and left joins
If you want to use INNER JOIN instead of LEFT JOIN just use innerJoinAndSelect instead:
const user = await createQueryBuilder("user")
    .innerJoinAndSelect(
        "user.photos",
        "photo",
        "photo.isRemoved = :isRemoved",
        { isRemoved: false },
    )
    .where("user.name = :name", { name: "Timber" })
    .getOne()
This will generate:
SELECT user.*, photo.* FROM users user
    INNER JOIN photos photo ON photo.user = user.id AND photo.isRemoved = FALSE
    WHERE user.name = 'Timber'
The difference between LEFT JOIN and INNER JOIN is that INNER JOIN won't return a user if it does not have any photos. LEFT JOIN will return you the user even if it doesn't have photos. To learn more about different join types, refer to the SQL documentation.
Join without selection
You can join data without its selection. To do that, use leftJoin or innerJoin:
const user = await createQueryBuilder("user")
    .innerJoin("user.photos", "photo")
    .where("user.name = :name", { name: "Timber" })
    .getOne()
This will generate:
SELECT user.* FROM users user
    INNER JOIN photos photo ON photo.user = user.id
    WHERE user.name = 'Timber'
This will select Timber if he has photos, but won't return his photos.
Joining any entity or table
You can join not only relations, but also other unrelated entities or tables. Examples:
const user = await createQueryBuilder("user")
    .leftJoinAndSelect(Photo, "photo", "photo.userId = user.id")
    .getMany()
const user = await createQueryBuilder("user")
    .leftJoinAndSelect("photos", "photo", "photo.userId = user.id")
    .getMany()
Joining and mapping functionality
Add profilePhoto to User entity, and you can map any data into that property using QueryBuilder:
export class User {
    /// ...
    profilePhoto: Photo
}
const user = await createQueryBuilder("user")
    .leftJoinAndMapOne(
        "user.profilePhoto",
        "user.photos",
        "photo",
        "photo.isForProfile = TRUE",
    )
    .where("user.name = :name", { name: "Timber" })
    .getOne()
This will load Timber's profile photo and set it to user.profilePhoto. If you want to load and map a single entity use leftJoinAndMapOne. If you want to load and map multiple entities use leftJoinAndMapMany.
Getting the generated query
Sometimes you may want to get the SQL query generated by QueryBuilder. To do so, use getSql:
const sql = createQueryBuilder("user")
    .where("user.firstName = :firstName", { firstName: "Timber" })
    .orWhere("user.lastName = :lastName", { lastName: "Saw" })
    .getSql()
For debugging purposes you can use printSql:
const users = await createQueryBuilder("user")
    .where("user.firstName = :firstName", { firstName: "Timber" })
    .orWhere("user.lastName = :lastName", { lastName: "Saw" })
    .printSql()
    .getMany()
Select using Query Builder

What is `QueryBuilder`

Important note when using the `QueryBuilder`

How to create and use a `QueryBuilder`

Getting values using `QueryBuilder`

What are aliases for?

Using parameters to escape data

Adding `WHERE` expression

Adding `HAVING` expression

Adding `ORDER BY` expression

Adding `DISTINCT ON` expression (Postgres only)

Adding `GROUP BY` expression

Adding `LIMIT` expression

Adding `OFFSET` expression

Joining relations

Inner and left joins

Join without selection

Joining any entity or table

Joining and mapping functionality

Getting the generated query
