Guide to Using Raw SQL with TypeORM

TypeORM is a powerful ORM library frequently preferred in modern Node.js applications. Although its repository and entity-based structure is commonly used in database operations, in some advanced queries or for performance reasons, it may be necessary to use Raw SQL. In this article, we will discuss in detail the use of Raw SQL with TypeORM and explain it with real-world examples.

How Do Raw SQL Queries Work with TypeORM?

In addition to repository and entity-based queries, TypeORM allows you to send SQL queries directly to the database. Especially in scenarios such as complex joins or bulk updates/calculations, raw query methods offer significant advantages. With the query and queryRunner functions provided by TypeORM, it is possible to use Raw SQL safely and effectively.

Using the Query Method

You can easily execute raw SQL queries over an existing database connection using the query function. Below is an example:

// A Raw SQL query fetching the user list
const users = await dataSource.query(
  "SELECT * FROM user WHERE isActive = ?", [true]
);
console.log(users);

Here, the ? parameter is used as a placeholder, and the corresponding values are sent as an array. This method reduces the risk of SQL Injection.

Advanced Raw SQL with QueryRunner

For more advanced operations (e.g., running multiple Raw SQL queries within a transaction), you can use the QueryRunner object:

// Raw SQL example with transaction management
const queryRunner = dataSource.createQueryRunner();
await queryRunner.connect();
await queryRunner.startTransaction();
try {
  await queryRunner.query(
    "UPDATE user SET lastLogin = NOW() WHERE id = ?",
    [userId]
  );
  await queryRunner.commitTransaction();
} catch (error) {
  await queryRunner.rollbackTransaction();
  throw error;
} finally {
  await queryRunner.release();
}

This structure is ideal for safe and flexible raw SQL usage, and it also provides error management and rollback support.

Conclusion and Tips

Using Raw SQL with TypeORM is very suitable for situations that require flexibility and maximum control in projects. However, unnecessary use should be avoided and parameterized queries should be preferred for security reasons. When TypeORM's own query builder and repository features are not sufficient, it is possible to write concise and performant code with raw SQL. This guide on using Raw SQL with TypeORM will add flexibility to your applications.