How can I use raw SQL in NestJS instead of TypeOrm or Sequelize?

In NestJS, while TypeORM and Sequelize are two widely adopted ORM tools, there are scenarios where using raw SQL is necessary to perform specific database operations for performance optimization or to handle complex queries. To implement raw SQL in NestJS, you can adopt several different approaches.

Method 1: Using Database Drivers Directly

You can directly leverage the appropriate Node.js database driver based on your database type (e.g., PostgreSQL, MySQL, etc.). For instance, with PostgreSQL, you can utilize the pg library to execute raw SQL.

First, install pg:

bash
npm install pg

Then, create a database connection and execute queries within a service:

typescript
import { Injectable } from '@nestjs/common';
import { Pool } from 'pg';

@Injectable()
export class DatabaseService {
    private pool: Pool;

    constructor() {
        this.pool = new Pool({
            user: 'yourUsername',
            host: 'localhost',
            database: 'yourDatabase',
            password: 'yourPassword',
            port: 5432,
        });
    }

    async executeQuery(query: string) {
        const client = await this.pool.connect();
        try {
            const result = await client.query(query);
            return result.rows;
        } finally {
            client.release();
        }
    }
}

In this example, we define a DatabaseService that manages connections using pg.Pool. The executeQuery method executes the provided SQL query and returns the results.

Method 2: Using Third-Party Libraries

If you prefer not to manage low-level database connections and queries directly, you can employ query builder libraries like knex.js, which support both raw SQL and convenient methods for constructing queries.

First, install knex and a corresponding database client (e.g., pg):

bash
npm install knex pg

Configure and use knex:

typescript
import { Injectable } from '@nestjs/common';
import * as Knex from 'knex';

@Injectable()
export class DatabaseService {
    private knex: Knex;

    constructor() {
        this.knex = Knex({
            client: 'pg',
            connection: {
                host: '127.0.0.1',
                user: 'yourDatabaseUser',
                password: 'yourPassword',
                database: 'yourDatabaseName'
            }
        });
    }

    async executeQuery(query: string) {
        return await this.knex.raw(query);
    }
}

Here, DatabaseService uses Knex to execute raw SQL. The knex.raw() method enables direct execution of any SQL code.

Conclusion

Using raw SQL in NestJS is straightforward; you can select the appropriate methods and libraries based on your requirements. Directly using database drivers offers maximum control and performance, while libraries like knex.js provide additional convenience and security (such as SQL injection protection). The choice depends on your specific needs and project context.