How to execute raw query with parameters in Typeorm

Executing raw queries in TypeORM is a straightforward and efficient operation, especially when you need to perform specific database operations or when the ORM's built-in methods fall short. To safely execute raw queries—particularly when they involve parameters from external input—we must leverage parameterized queries to prevent SQL injection attacks.

Here is a specific example demonstrating how to execute raw queries with parameters in TypeORM:

First, ensure you have created a DataSource instance and successfully connected to your database. The following is a simple parameterized query example, assuming we want to retrieve user information from the user table based on a specific ID:

typescript
import { DataSource } from 'typeorm';

async function findUserById(dataSource: DataSource, userId: number) {
    const query = 'SELECT * FROM user WHERE id = $1'; // Using $1 as a parameter placeholder
    const result = await dataSource.query(query, [userId]); // Passing userId as a parameter
    return result;
}

In this example, $1 serves as the parameter placeholder (note that different databases may use different placeholders, such as ? for MySQL), and [userId] is an array containing all parameters in the order they appear in the SQL query. When you call dataSource.query, TypeORM automatically replaces the parameters into the query placeholders and executes the query securely, mitigating SQL injection risks.

The benefits of this approach are clear:

1. Security: Parameterized queries effectively prevent SQL injection attacks.
2. Flexibility: Complex SQL queries can be reused by simply passing different parameters.
3. Performance: The database can optimize execution plans since the SQL structure remains consistent across multiple calls, with only parameters varying.