Insert using Query Builder
You can create INSERT queries using QueryBuilder. Examples: This is the most efficient way in terms of performance to insert rows into your database. You can also perform bulk insertions this way. Raw SQL support In some cases when you need to execute SQL queries you need to use function style value: This syntax doesn’t escape your values, you need to handle escape on your own. Update values ON CONFLICT If the values you are trying to insert conflict due to existing data the orUpdate function can be used to update specific values on the conflicted target.
Debugging: sql()
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() .getRepository(Customer) .createQueryBuilder(“customer”) .where(“customer.id = :id”, { id: 1 }) // .getQuery() //GIVES THIS RESULT // “SELECT `customer`.`id` AS `customer_id`, `customer`.`firstName` AS `customer_firstName`, `customer`.`lastName` AS `customer_lastName`, `customer`.`deletedAt` AS `customer_deletedAt` FROM `customer` `customer` WHERE ( `customer`.`id` = :id ) AND ( `customer`.`deletedAt` IS NULL )” //.getQueryAndParameters() //GIVES THIS RESULT // [ // “SELECT `customer`.`id` AS `customer_id`, `customer`.`firstName` AS `customer_firstName`, `customer`.`lastName` AS `customer_lastName`, `customer`.`deletedAt` AS `customer_deletedAt` FROM `customer` `customer` WHERE ( `customer`.`id` = ? ) AND ( `customer`.`deletedAt` IS NULL )”, // [ // 1 // ] // ]
CTE in TypeOrm
QueryBuilder instances support common table expressions , if minimal supported version of your database supports them. Common table expressions aren’t supported for Oracle yet.
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: Using a standard find or query, you will not receive the rows which have a value in that row. However, if you do the following: You will get all the rows, including the ones which are Soft-deleted.
QueryBuilder Delete & Soft-Delete
You can create DELETE queries using QueryBuilder. Examples: This is the most efficient way in terms of performance to delete entities from your database. Soft-Delete Applying Soft Delete to QueryBuilder Examples: Restore-Soft-Delete Alternatively, You can recover the soft deleted rows by using the restore() method: Examples:
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: Using a standard find or query, you will not receive the password property for the model. However, if you do the following: You will get the property password in your query.
Locking in TypeOrm
Locking in TypeORM involves controlling access to database rows to prevent concurrency issues when multiple transactions or processes try to modify the same data simultaneously. There are different lock modes that determine how a database row is locked, and TypeORM provides methods to apply these lock modes when using the QueryBuilder. Here’s an in-depth explanation of the locking mechanisms and how to use them with TypeORM: 1. Pessimistic Read Locking (pessimistic_read): 2. Pessimistic Write Locking (pessimistic_write): 3. Dirty Read Locking (dirty_read): 4. Optimistic Locking (optimistic): 5. Setting onLocked Behavior: Now, let’s illustrate how to use locking in TypeORM’s QueryBuilder using examples: Pessimistic Read Locking: const users = await dataSource.getRepository(User).createQueryBuilder(“user”).setLock(“pessimistic_read”).getMany(); Pessimistic Write Locking: const users = await dataSource.getRepository(User).createQueryBuilder(“user”).setLock(“pessimistic_write”).getMany(); Dirty Read Locking (MySQL with NOLOCK): const users = await dataSource.getRepository(User).createQueryBuilder(“user”).setLock(“dirty_read”).getMany(); Optimistic Locking (with Version Column): const users = await dataSource.getRepository(User).createQueryBuilder(“user”).setLock(“optimistic”, existingUser.version) // existingUser is the loaded entity.getMany(); Setting onLocked Behavior (PostgreSQL and MySQL 8+): const users = await dataSource.getRepository(User).createQueryBuilder(“user”).setLock(“pessimistic_write”).setOnLocked(“nowait”).getMany(); Note: The supported lock modes and onLocked behaviors depend on the database you’re using. Be sure to refer to the compatibility matrix in the original code snippet for details. Locking mechanisms are crucial for maintaining data integrity and handling concurrency scenarios. Choose the appropriate locking mode based on your application’s requirements and database compatibility. If you have a scenario where data is fetched using a read operation with a lock, and on another connected call data is written using a write operation, the behavior depends on the type of locking used and the timing of the operations. Let’s break down the possibilities: In all cases, locking mechanisms are designed to manage concurrent access to data and prevent issues like dirty writes and lost updates. The behavior you describe—where the fetched row is edited after the fetch—is one of the key scenarios that these locking mechanisms aim to address. The specific outcome will depend on the locking strategy you choose and how your application is designed to handle concurrency and conflicts. It’s important to carefully consider the locking strategy based on your application’s requirements and the potential for concurrent updates. Additionally, handling concurrency conflicts is a crucial part of implementing a robust application that deals with concurrent access to shared data.
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. This will give you the first 10 users with their photos. This will give you all except the first 10 users with their photos. You can combine those methods: 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.
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:
Partial selection
If you want to select only some entity properties, you can use the following syntax:
