How to excute Raw SQL Query on NestJS framework using typeorm

Executing raw SQL queries in TypeORM is a straightforward and effective operation. You can accomplish this through several different methods. The following provides examples and step-by-step instructions.

Using EntityManager to Execute Raw SQL

The EntityManager class provides methods for executing SQL statements. The following demonstrates how to use it:

1. Obtaining the EntityManager instance - You can retrieve the current connection's EntityManager using the getConnection method.

typescript
import { getConnection } from 'typeorm';

const entityManager = getConnection().manager;

1. Executing Raw SQL Queries - Execute raw SQL queries using the query method.

typescript
// Execute a simple SELECT query
const rawData = await entityManager.query(`SELECT * FROM users WHERE id = $1`, [1]);

In this example, we use parameterized queries, where $1 is a placeholder for the first parameter, and the actual value is passed in an array. This helps prevent SQL injection attacks.

Using QueryRunner to Execute Raw SQL

The QueryRunner class can also be used to execute raw SQL, typically in transaction management. The following shows how to use it:

1. Creating the QueryRunner instance - Retrieve the QueryRunner from the connection.

typescript
import { getConnection } from 'typeorm';

const queryRunner = getConnection().createQueryRunner();

1. Executing Queries with QueryRunner

typescript
// First connect to the database
await queryRunner.connect();

try {
  // Then execute the query
  const rawData = await queryRunner.query(`SELECT * FROM users`);
} finally {
  // Release the connection
  await queryRunner.release();
}

Using Repository to Execute Raw SQL

Although the Repository is typically used for ORM operations, it also supports executing raw SQL.

1. Obtaining the Repository instance

typescript
import { getRepository } from 'typeorm';
import { User } from './entity/User';

const userRepository = getRepository(User);

1. Executing Raw SQL with Repository

typescript
const rawData = await userRepository.query(`SELECT * FROM users WHERE name = $1`, ['John']);

Important Considerations

• When executing raw SQL queries, be sure to consider the risk of SQL injection. In the above examples, I demonstrate how to use parameterized queries, which is an important way to prevent SQL injection.
• When using transactions, ensure proper management of connections and transaction lifecycles, including rolling back transactions on errors and finally releasing the connection.
• TypeORM supports multiple databases, and SQL may vary across different databases. Ensure your SQL queries are compatible with the database you are using.

These are the main ways to execute raw SQL queries with TypeORM. In practical applications, it is generally recommended to use TypeORM's methods as much as possible to leverage the ORM's advantages, reserving raw SQL for special cases.