# Select using Query Builder

## What is a QueryBuilder?[​](#what-is-a-querybuilder "Direct link to What is a 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`[​](#important-note-when-using-the-querybuilder "Direct link to 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

    .getRepository(User)

    .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

    .getRepository(User)

    .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?[​](#how-to-create-and-use-a-querybuilder "Direct link to 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`[​](#getting-values-using-querybuilder "Direct link to 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 }, ...]
```

## Getting a count[​](#getting-a-count "Direct link to Getting a count")

You can get the count on the number of rows a query will return by using `getCount()`. This will return the count as a number rather than an Entity result.

```
const count = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .where("user.name = :name", { name: "Timber" })

    .getCount()
```

Which produces the following SQL query:

```
SELECT count(*) FROM users user WHERE user.name = 'Timber'
```

## What are aliases for?[​](#what-are-aliases-for "Direct link to 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 you are selecting (or data you are selecting).

## Using parameters to escape data[​](#using-parameters-to-escape-data "Direct link to 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", "Crystal", "Lina" ] })
```

Which becomes:

```
WHERE user.name IN ('Timber', 'Crystal', 'Lina')
```

## Adding `WHERE` expression[​](#adding-where-expression "Direct link to 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-having-expression "Direct link to 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-order-by-expression "Direct link to 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)[​](#adding-distinct-on-expression-postgres-only "Direct link to 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-group-by-expression "Direct link to 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-limit-expression "Direct link to 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-offset-expression "Direct link to 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[​](#joining-relations "Direct link to 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 dataSource

    .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 dataSource

    .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 dataSource

    .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[​](#inner-and-left-joins "Direct link to Inner and left joins")

If you want to use `INNER JOIN` instead of `LEFT JOIN` just use `innerJoinAndSelect` instead:

```
const user = await dataSource

    .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](https://msdn.microsoft.com/en-us/library/zt8wzxy4.aspx).

## Join without selection[​](#join-without-selection "Direct link to Join without selection")

You can join data without its selection. To do that, use `leftJoin` or `innerJoin`:

```
const user = await dataSource

    .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[​](#joining-any-entity-or-table "Direct link to Joining any entity or table")

You can join not only relations, but also other unrelated entities or tables. Examples:

```
const user = await dataSource

    .createQueryBuilder("user")

    .leftJoinAndSelect(Photo, "photo", "photo.userId = user.id")

    .getMany()
```

```
const user = await dataSource

    .createQueryBuilder("user")

    .leftJoinAndSelect("photos", "photo", "photo.userId = user.id")

    .getMany()
```

## Joining and mapping functionality[​](#joining-and-mapping-functionality "Direct link to 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 dataSource

    .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[​](#getting-the-generated-query "Direct link to Getting the generated query")

Sometimes you may want to get the SQL query generated by `QueryBuilder`. To do so, use `getSql`:

```
const sql = dataSource

    .createQueryBuilder("user")

    .where("user.firstName = :firstName", { firstName: "Timber" })

    .orWhere("user.lastName = :lastName", { lastName: "Saw" })

    .getSql()
```

For debugging purposes you can inspect the generated SQL using `getSql()` or `getQuery()`:

```
const qb = dataSource

    .createQueryBuilder("user")

    .where("user.firstName = :firstName", { firstName: "Timber" })

    .orWhere("user.lastName = :lastName", { lastName: "Saw" })



console.log(qb.getSql())

// SELECT ... WHERE "user"."firstName" = $1 OR "user"."lastName" = $2



const users = await qb.getMany()
```

`getSql()` returns the SQL with parameter placeholders. `getQuery()` returns the same SQL string. To also see the parameter values, use `getQueryAndParameters()` which returns a `[sql, parameters]` tuple.

All executed queries are also automatically logged through the configured logger when you enable query logging in your DataSource options:

```
new DataSource({

    // ...

    logging: ["query"],

})
```

## Getting raw results[​](#getting-raw-results "Direct link to Getting raw results")

There are two types of results you can get using select query builder: **entities** and **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`. However, sometimes you need to select specific data, like the *sum of all user photos*. Such 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 }, ...]
```

## Streaming result data[​](#streaming-result-data "Direct link to Streaming result data")

You can use `stream` which returns you a stream. Streaming returns you raw data, and you must handle entity transformation manually:

```
const stream = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

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

    .stream()
```

## Using pagination[​](#using-pagination "Direct link to Using pagination")

Most of the time when you develop an application, you need pagination functionality. This is used if you have pagination, page slider, or infinite scroll components in your application.

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .leftJoinAndSelect("user.photos", "photo")

    .take(10)

    .getMany()
```

This will give you the first 10 users with their photos.

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .leftJoinAndSelect("user.photos", "photo")

    .skip(10)

    .getMany()
```

This will give you all except the first 10 users with their photos. You can combine those methods:

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .leftJoinAndSelect("user.photos", "photo")

    .skip(5)

    .take(10)

    .getMany()
```

This will skip the first 5 users and take 10 users after them.

`take` and `skip` may look like we are using `limit` and `offset`, but they aren't. `limit` and `offset` may not work as you expect once you have more complicated queries with joins or subqueries. Using `take` and `skip` will prevent those issues.

## Set locking[​](#set-locking "Direct link to Set locking")

QueryBuilder supports both optimistic and pessimistic locking.

### Lock modes[​](#lock-modes "Direct link to Lock modes")

Support of lock modes, and SQL statements they translate to, are listed in the table below (blank cell denotes unsupported). When specified lock mode is not supported, a `LockNotSupportedOnGivenDriverError` error will be thrown.

|                       | pessimistic\_read                            | pessimistic\_write        | dirty\_read     | for\_no\_key\_update | for\_key\_share |
| --------------------- | -------------------------------------------- | ------------------------- | --------------- | -------------------- | --------------- |
| MySQL, MariaDB        | `FOR SHARE` (MySQL 8+), `LOCK IN SHARE MODE` | `FOR UPDATE`              | (nothing)       |                      |                 |
| Oracle                | `FOR UPDATE`                                 | `FOR UPDATE`              | (nothing)       |                      |                 |
| Postgres, CockroachDB | `FOR SHARE`                                  | `FOR UPDATE`              | (nothing)       | `FOR NO KEY UPDATE`  | `FOR KEY SHARE` |
| SAP HANA              | `FOR SHARE LOCK`                             | `FOR UPDATE`              | (nothing)       |                      |                 |
| SQL Server            | `WITH (HOLDLOCK, ROWLOCK)`                   | `WITH (UPDLOCK, ROWLOCK)` | `WITH (NOLOCK)` |                      |                 |

To use pessimistic read locking use the following method:

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .setLock("pessimistic_read")

    .getMany()
```

To use pessimistic write locking use the following method:

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .setLock("pessimistic_write")

    .getMany()
```

To use dirty read locking use the following method:

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .setLock("dirty_read")

    .getMany()
```

To use optimistic locking use the following method:

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .setLock("optimistic", existUser.version)

    .getMany()
```

Optimistic locking works in conjunction with both `@Version` and `@UpdatedDate` decorators.

### Lock tables[​](#lock-tables "Direct link to Lock tables")

You can also lock tables using the following method:

```
const users = await dataSource

    .getRepository(Post)

    .createQueryBuilder("post")

    .leftJoin("post.author", "user")

    .setLock("pessimistic_write", undefined, ["post"])

    .getMany()
```

If the Lock Tables argument is provided, only the table that is locked in the `FOR UPDATE OF` clause is specified. Lock tables are supported in PostgreSQL / CockroachDB only.

### setOnLocked[​](#setonlocked "Direct link to setOnLocked")

Allows you to control what happens when a row is locked. By default, the database will wait for the lock. You can control that behavior by using `setOnLocked`

To not wait:

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .setLock("pessimistic_write")

    .setOnLocked("nowait")

    .getMany()
```

To skip the row:

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .setLock("pessimistic_write")

    .setOnLocked("skip_locked")

    .getMany()
```

Database support for `setOnLocked` based on [lock mode](#lock-modes):

* MySQL < 8, Maria DB: `pessimistic_write`
* MySQL 8+: `pessimistic_read`, `pessimistic_write`
* Oracle: `pessimistic_write`
* Postgres, Cockroach: `pessimistic_read`, `pessimistic_write`, `for_no_key_update`, `for_key_share`
* SAP HANA: `pessimistic_read`, `pessimistic_write`

## Use custom index[​](#use-custom-index "Direct link to Use custom index")

You can provide a certain index for database server to use in some cases. This feature is only supported in MySQL.

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .useIndex("my_index") // name of index

    .getMany()
```

## Max execution time[​](#max-execution-time "Direct link to Max execution time")

We can drop slow query to avoid crashing the server.

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .maxExecutionTime(1000) // milliseconds.

    .getMany()
```

## Partial selection[​](#partial-selection "Direct link to Partial selection")

If you want to select only some entity properties, you can use the following syntax:

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .select(["user.id", "user.name"])

    .getMany()
```

This will only select the `id` and `name` of `User`.

## Using subqueries[​](#using-subqueries "Direct link to Using subqueries")

You can easily create subqueries. Subqueries are supported in `FROM`, `WHERE` and `JOIN` expressions. Example:

```
const qb = await dataSource.getRepository(Post).createQueryBuilder("post")



const posts = qb

    .where(

        "post.title IN " +

            qb

                .subQuery()

                .select("user.name")

                .from(User, "user")

                .where("user.registered = :registered")

                .getQuery(),

    )

    .setParameter("registered", true)

    .getMany()
```

A more elegant way to do the same:

```
const posts = await dataSource

    .getRepository(Post)

    .createQueryBuilder("post")

    .where((qb) => {

        const subQuery = qb

            .subQuery()

            .select("user.name")

            .from(User, "user")

            .where("user.registered = :registered")

            .getQuery()

        return "post.title IN " + subQuery

    })

    .setParameter("registered", true)

    .getMany()
```

Alternatively, you can create a separate query builder and use its generated SQL:

```
const userQb = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .select("user.name")

    .where("user.registered = :registered", { registered: true })



const posts = await dataSource

    .getRepository(Post)

    .createQueryBuilder("post")

    .where("post.title IN (" + userQb.getQuery() + ")")

    .setParameters(userQb.getParameters())

    .getMany()
```

You can create subqueries in `FROM` like this:

```
const userQb = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .select("user.name", "name")

    .where("user.registered = :registered", { registered: true })



const posts = await dataSource

    .createQueryBuilder()

    .select("user.name", "name")

    .from("(" + userQb.getQuery() + ")", "user")

    .setParameters(userQb.getParameters())

    .getRawMany()
```

or using a more elegant syntax:

```
const posts = await dataSource

    .createQueryBuilder()

    .select("user.name", "name")

    .from((subQuery) => {

        return subQuery

            .select("user.name", "name")

            .from(User, "user")

            .where("user.registered = :registered", { registered: true })

    }, "user")

    .getRawMany()
```

If you want to add a subselect as a "second from" use `addFrom`.

You can use subselects in `SELECT` statements as well:

```
const posts = await dataSource

    .createQueryBuilder()

    .select("post.id", "id")

    .addSelect((subQuery) => {

        return subQuery.select("user.name", "name").from(User, "user").limit(1)

    }, "name")

    .from(Post, "post")

    .getRawMany()
```

## Hidden Columns[​](#hidden-columns "Direct link to Hidden Columns")

If the model you are querying has a column with a `select: false` column, you must use the `addSelect` function in order to retrieve the information from the column.

Let's say you have the following entity:

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



@Entity()

export class User {

    @PrimaryGeneratedColumn()

    id: number



    @Column()

    name: string



    @Column({ select: false })

    password: string

}
```

Using a standard `find` or query, you will not receive the `password` property for the model. However, if you do the following:

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder()

    .select("user.id", "id")

    .addSelect("user.password")

    .getMany()
```

You will get the property `password` in your query.

## Querying Deleted rows[​](#querying-deleted-rows "Direct link to Querying Deleted rows")

If the model you are querying has a column with the attribute `@DeleteDateColumn` set, the query builder will automatically query rows which are 'soft deleted'.

Let's say you have the following entity:

```
import {

    Entity,

    PrimaryGeneratedColumn,

    Column,

    DeleteDateColumn,

} from "typeorm"



@Entity()

export class User {

    @PrimaryGeneratedColumn()

    id: number



    @Column()

    name: string



    @DeleteDateColumn()

    deletedAt?: Date

}
```

Using a standard `find` or query, you will not receive the rows which have a value in that column. However, if you do the following:

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder()

    .select("user.id", "id")

    .withDeleted()

    .getMany()
```

You will get all the rows, including the ones which are deleted.

## Common table expressions[​](#common-table-expressions "Direct link to Common table expressions")

`QueryBuilder` instances support [common table expressions](https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression) , if minimal supported version of your database supports them. Common table expressions aren't supported for Oracle yet.

```
const users = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .select("user.id", "id")

    .addCommonTableExpression(

        `

      SELECT "userId" FROM "post"

    `,

        "post_users_ids",

    )

    .where(`user.id IN (SELECT "userId" FROM 'post_users_ids')`)

    .getMany()
```

Result values of `InsertQueryBuilder` or `UpdateQueryBuilder` can be used in Postgres:

```
const insertQueryBuilder = dataSource

    .getRepository(User)

    .createQueryBuilder()

    .insert({

        name: "John Smith",

    })

    .returning(["id"])



const users = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

    .addCommonTableExpression(insertQueryBuilder, "insert_results")

    .where(`user.id IN (SELECT "id" FROM 'insert_results')`)

    .getMany()
```

## Time Travel Queries[​](#time-travel-queries "Direct link to Time Travel Queries")

[Time Travel Queries](https://www.cockroachlabs.com/blog/time-travel-queries-select-witty_subtitle-the_future/) currently supported only in `CockroachDB` database.

```
const repository = dataSource.getRepository(Account)



// create a new account

const account = new Account()

account.name = "John Smith"

account.balance = 100

await repository.save(account)



// imagine we update the account balance 1 hour after creation

account.balance = 200

await repository.save(account)



// outputs { name: "John Smith", balance: "200" }

console.log(account)



// load account state on 1 hour back

account = await repository

    .createQueryBuilder("account")

    .timeTravelQuery(`'-1h'`)

    .getOneOrFail()



// outputs { name: "John Smith", balance: "100" }

console.log(account)
```

By default `timeTravelQuery()` uses `follower_read_timestamp()` function if no arguments passed. For another supported timestamp arguments and additional information please refer to [CockroachDB](https://www.cockroachlabs.com/docs/stable/as-of-system-time.html) docs.

## Debugging[​](#debugging "Direct link to Debugging")

You can get the generated SQL from the query builder by calling `getQuery()` or `getQueryAndParameters()`.

If you just want the query you can use `getQuery()`

```
const sql = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

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

    .getQuery()
```

Which results in:

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

Or if you want the query and the parameters you can get an array back using `getQueryAndParameters()`

```
const queryAndParams = await dataSource

    .getRepository(User)

    .createQueryBuilder("user")

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

    .getQueryAndParameters()
```

Which results in:

```
;[

    "SELECT `user`.`id` as `userId`, `user`.`firstName` as `userFirstName`, `user`.`lastName` as `userLastName` FROM `users` `user` WHERE `user`.`id` = ?",

    [1],

]
```