- What is
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
- Partial selection
- Using subqueries
- Hidden Columns
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 connection
.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"
}
There are several ways how you can create a Query Builder
:
-
Using connection:
import {getConnection} from "typeorm"; const user = await getConnection() .createQueryBuilder() .select() .from(User, "user") .where("user.id = :id", { id: 1 }) .getOne();
-
Using entity manager:
import {getManager} from "typeorm"; const user = await getManager() .createQueryBuilder(User, "user") .where("user.id = :id", { id: 1 }) .getOne();
-
Using repository:
import {getRepository} from "typeorm"; const user = await getRepository(User) .createQueryBuilder("user") .where("user.id = :id", { id: 1 }) .getOne();
There are 5 different QueryBuilder
types available:
-
SelectQueryBuilder
- used to build and executeSELECT
queries. Example:import {getConnection} from "typeorm"; const user = await getConnection() .createQueryBuilder() .select() .from(User, "user") .where("user.id = :id", { id: 1 }) .getOne();
-
InsertQueryBuilder
- used to build and executeINSERT
queries. Example:import {getConnection} from "typeorm"; await getConnection() .createQueryBuilder() .insert() .into(User) .values([ { firstName: "Timber", lastName: "Saw" }, { firstName: "Phantom", lastName: "Lancer" } ]) .execute();
-
UpdateQueryBuilder
- used to build and executeUPDATE
queries. Example:import {getConnection} from "typeorm"; await getConnection() .createQueryBuilder() .update(User) .set({ firstName: "Timber", lastName: "Saw" }) .where("id = :id", { id: 1 }) .execute();
-
DeleteQueryBuilder
- used to build and executeDELETE
queries. Example:import {getConnection} from "typeorm"; await getConnection() .createQueryBuilder() .delete() .from(User) .where("id = :id", { id: 1 }) .execute();
-
RelationQueryBuilder
- used to build and execute relation-specific operations [TBD].
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).
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 getRepository(User)
.createQueryBuilder("user")
.where("user.id = :id OR user.name = :name", { id: 1, name: "Timber" })
.getOne();
To get multiple results from the database,
for example, to get all users from the database, use getMany
:
const users = await 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 getRepository(User)
.createQueryBuilder("user")
.select("SUM(user.photosCount)", "sum")
.where("user.id = :id", { id: 1 })
.getRawOne();
const photosSums = await getRepository(User)
.createQueryBuilder("user")
.select("user.id")
.addSelect("SUM(user.photosCount)", "sum")
.where("user.id = :id", { id: 1 })
.getRawMany();
// result will be like this: [{ id: 1, sum: 25 }, { id: 2, sum: 13 }, ...]
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()
.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()
.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).
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")
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 exist 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 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 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 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 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 ORDER BY
expressions.
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 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.
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'
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.
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.
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();
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
.
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();
This query will return users and print the used sql statement to the console.
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 a entity, it's called raw data.
To get raw data, you use getRawOne
and getRawMany
.
Examples:
const { sum } = await getRepository(User)
.createQueryBuilder("user")
.select("SUM(user.photosCount)", "sum")
.where("user.id = :id", { id: 1 })
.getRawOne();
const photosSums = await getRepository(User)
.createQueryBuilder("user")
.select("user.id")
.addSelect("SUM(user.photosCount)", "sum")
.where("user.id = :id", { id: 1 })
.getRawMany();
// result will be like this: [{ id: 1, sum: 25 }, { id: 2, sum: 13 }, ...]
You can use stream
which returns you a stream.
Streaming returns you raw data and you must handle entity transformation manually:
const stream = await getRepository(User)
.createQueryBuilder("user")
.where("user.id = :id", { id: 1 })
.stream();
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 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 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 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.
QueryBuilder supports both optimistic and pessimistic locking. To use pessimistic read locking use the following method:
const users = await getRepository(User)
.createQueryBuilder("user")
.setLock("pessimistic_read")
.getMany();
To use pessimistic write locking use the following method:
const users = await getRepository(User)
.createQueryBuilder("user")
.setLock("pessimistic_write")
.getMany();
To use optimistic locking use the following method:
const users = await getRepository(User)
.createQueryBuilder("user")
.setLock("optimistic", existUser.version)
.getMany();
Optimistic locking works in conjunction with both @Version
and @UpdatedDate
decorators.
If you want to select only some entity properties, you can use the following syntax:
const users = await getRepository(User)
.createQueryBuilder("user")
.select([
"user.id",
"user.name"
])
.getMany();
This will only select the id
and name
of User
.
You can easily create subqueries. Subqueries are supported in FROM
, WHERE
and JOIN
expressions.
Example:
const qb = await 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 connection.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 connection.getRepository(User)
.createQueryBuilder("user")
.select("user.name")
.where("user.registered = :registered", { registered: true });
const posts = await connection.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 connection.getRepository(User)
.createQueryBuilder("user")
.select("user.name", "name")
.where("user.registered = :registered", { registered: true });
const posts = await connection
.createQueryBuilder()
.select("user.name", "name")
.from("(" + userQb.getQuery() + ")", "user")
.setParameters(userQb.getParameters())
.getRawMany();
or using more a elegant syntax:
const posts = await connection
.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 connection
.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
If the model you are querying has a column with a select: false
column, you must use the addSelect
function in order to retreive 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 recieve the password
property for the model. However, if you do the following:
const users = await connection.getRepository(User)
.createQueryBuilder()
.select("user.id", "id")
.addSelect("user.password")
.getMany();
You will get the property password
in your query.