Self-paced

Explore our extensive collection of courses designed to help you master various subjects and skills. Whether you're a beginner or an advanced learner, there's something here for everyone.

Bootcamp

Learn live

Join us for our free workshops, webinars, and other events to learn more about our programs and get started on your journey to becoming a developer.

Upcoming live events

Learning library

For all the self-taught geeks out there, here is our content library with most of the learning materials we have produced throughout the years.

It makes sense to start learning by reading and watching videos about fundamentals and how things work.

Search from all Lessons


LoginGet Started
← Back to Lessons
  • node

  • typescript

  • express

Edit on Github

Queries with Express and TypeORM

Basic options
  • Basic options
  • Advanced options

Basic options

All repository and manager find methods accept special options you can use to query data you need without using QueryBuilder:

  • select - indicates which properties of the main object must be selected
1userRepository.find({ select: ["firstName", "lastName"] });
  • relations - relations needs to be loaded with the main entity. Sub-relations can also be loaded (shorthand for join and leftJoinAndSelect)
1userRepository.find({ relations: ["profile", "photos", "videos"] }); 2userRepository.find({ relations: ["profile", "photos", "videos", "videos.video_attributes"] });
  • join - join needs to be performed for the entity. Extended version of "relations".
1userRepository.find({ 2 join: { 3 alias: "user", 4 leftJoinAndSelect: { 5 profile: "user.profile", 6 photo: "user.photos", 7 video: "user.videos" 8 } 9 } 10});
  • where - simple conditions by which entity should be queried.
1userRepository.find({ where: { firstName: "Timber", lastName: "Saw" } });

Querying a column from an embedded entity should be done with respect to the hierarchy in which it was defined. Example:

1userRepository.find({ where: { name: { first: "Timber", last: "Saw" } } });

Querying with OR operator:

1userRepository.find({ 2 where: [ 3 { firstName: "Timber", lastName: "Saw" }, 4 { firstName: "Stan", lastName: "Lee" } 5 ] 6});

will execute the following query:

1SELECT * FROM "user" WHERE ("firstName" = 'Timber' AND "lastName" = 'Saw') OR ("firstName" = 'Stan' AND "lastName" = 'Lee')
  • order - selection order.
1userRepository.find({ 2 order: { 3 name: "ASC", 4 id: "DESC" 5 } 6});
  • withDeleted - include entities which have been soft deleted with softDelete or softRemove, e.g. have their @DeleteDateColumn column set. By default, soft deleted entities are not included.
1userRepository.find({ 2 withDeleted: true 3});

find methods which return multiple entities (find, findAndCount, findByIds) also accept the following options:

  • skip - offset (paginated) from where entities should be taken.
1userRepository.find({ 2 skip: 5 3});
  • take - limit (paginated) - max number of entities that should be taken.
1userRepository.find({ 2 take: 10 3});
  • If you are using typeORM with MSSQL, and want to use take or limit, you need to use order as well or you will receive the following error: 'Invalid usage of the option NEXT in the FETCH statement.'
1userRepository.find({ 2 order: { 3 columnName: 'ASC' 4 }, 5 skip: 0, 6 take: 10 7})
  • cache - Enables or disables query result caching.
1userRepository.find({ 2 cache: true 3})
  • lock - Enables locking mechanism for query. Can be used only in findOne method. lock is an object which can be defined as:
1{ mode: "optimistic", version: number|Date }

or

1{ mode: "pessimistic_read"|"pessimistic_write"|"dirty_read"|"pessimistic_partial_write"|"pessimistic_write_or_fail"|"for_no_key_update" }

for example:

1userRepository.findOne(1, { 2 lock: { mode: "optimistic", version: 1 } 3})

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.

1| | pessimistic_read | pessimistic_write | dirty_read | pessimistic_partial_write | pessimistic_write_or_fail | for_no_key_update | 2| --------------- | -------------------- | ----------------------- | ------------- | --------------------------- | --------------------------- | ------------------- | 3| MySQL | LOCK IN SHARE MODE | FOR UPDATE | (nothing) | FOR UPDATE SKIP LOCKED | FOR UPDATE NOWAIT | | 4| Postgres | FOR SHARE | FOR UPDATE | (nothing) | FOR UPDATE SKIP LOCKED | FOR UPDATE NOWAIT | FOR NO KEY UPDATE | 5| Oracle | FOR UPDATE | FOR UPDATE | (nothing) | | | | 6| SQL Server | WITH (HOLDLOCK, ROWLOCK) | WITH (UPDLOCK, ROWLOCK) | WITH (NOLOCK) | | | | 7| AuroraDataApi | LOCK IN SHARE MODE | FOR UPDATE | (nothing) | | | | 8

Complete example of find options:

1userRepository.find({ 2 select: ["firstName", "lastName"], 3 relations: ["profile", "photos", "videos"], 4 where: { 5 firstName: "Timber", 6 lastName: "Saw" 7 }, 8 order: { 9 name: "ASC", 10 id: "DESC" 11 }, 12 skip: 5, 13 take: 10, 14 cache: true 15});

Advanced options

TypeORM provides a lot of built-in operators that can be used to create more complex comparisons:

  • Not
1import {Not} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: Not("About #1") 5})

will execute the following query:

1SELECT * FROM "post" WHERE "title" != 'About #1'
  • LessThan
1import {LessThan} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 likes: LessThan(10) 5});

will execute the following query:

1SELECT * FROM "post" WHERE "likes" < 10
  • LessThanOrEqual
1import {LessThanOrEqual} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 likes: LessThanOrEqual(10) 5});

will execute the following query:

1SELECT * FROM "post" WHERE "likes" <= 10
  • MoreThan
1import {MoreThan} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 likes: MoreThan(10) 5});

will execute following query:

1SELECT * FROM "post" WHERE "likes" > 10
  • MoreThanOrEqual
1import {MoreThanOrEqual} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 likes: MoreThanOrEqual(10) 5});

will execute the following query:

1SELECT * FROM "post" WHERE "likes" >= 10
  • Equal
1import {Equal} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: Equal("About #2") 5});

will execute the following query:

1SELECT * FROM "post" WHERE "title" = 'About #2'
  • Like
1import {Like} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: Like("%out #%") 5});

will execute the following query:

1SELECT * FROM "post" WHERE "title" LIKE '%out #%'
  • ILike
1import {ILike} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: ILike("%out #%") 5});

will execute the following query:

1SELECT * FROM "post" WHERE "title" ILIKE '%out #%'
  • Between
1import {Between} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 likes: Between(1, 10) 5});

will execute the following query:

1SELECT * FROM "post" WHERE "likes" BETWEEN 1 AND 10
  • In
1import {In} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: In(["About #2", "About #3"]) 5});

will execute the following query:

1SELECT * FROM "post" WHERE "title" IN ('About #2','About #3')
  • Any
1import {Any} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: Any(["About #2", "About #3"]) 5});

will execute the following query (Postgres notation):

1SELECT * FROM "post" WHERE "title" = ANY(['About #2','About #3'])
  • IsNull
1import {IsNull} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: IsNull() 5});

will execute the following query:

1SELECT * FROM "post" WHERE "title" IS NULL
  • Raw
1import {Raw} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 likes: Raw("dislikes - 4") 5});

will execute the following query:

1SELECT * FROM "post" WHERE "likes" = "dislikes" - 4

In the simplest case, a raw query is inserted immediately after the equal symbol. But you can also completely rewrite the comparison logic using the function.

1import {Raw} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 currentDate: Raw(alias =>`${alias} > NOW()`) 5});

will execute the following query:

1SELECT * FROM "post" WHERE "currentDate" > NOW()

If you need to provide user input, you should not include the user input directly in your query as this may create a SQL injection vulnerability. Instead, you can use the second argument of the Raw function to provide a list of parameters to bind to the query.

1import {Raw} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 currentDate: Raw(alias =>`${alias} > ':date'`, { date: "2020-10-06" }) 5});

will execute the following query:

1SELECT * FROM "post" WHERE "currentDate" > '2020-10-06'

If you need to provide user input that is an array, you can bind them as a list of values in the SQL statement by using the special expression syntax:

1import {Raw} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: Raw(alias =>`${alias} IN (:...titles)`, { titles: ["Go To Statement Considered Harmful", "Structured Programming"] }) 5});

will execute the following query:

1SELECT * FROM "post" WHERE "titles" IN ('Go To Statement Considered Harmful', 'Structured Programming')

Combining Advanced Options

Also you can combine these operators with Not operator:

1import {Not, MoreThan, Equal} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 likes: Not(MoreThan(10)), 5 title: Not(Equal("About #2")) 6});

will execute the following query:

1SELECT * FROM "post" WHERE NOT("likes" > 10) AND NOT("title" = 'About #2')