How to excute Raw SQL Query on NestJS framework using typeorm

Question

I am going to execute such a long query on the NestJS framework using Typeform. Please let me know how to execute this query.

select user.id, user.fullName, (select count(*) sendCnt from chat where senderId = user.id), (select count(*) recvCnt from chat where receiverId = user.id) from users user where user.role = 'Admin'

Answer 1

If you're using TypeORM you can inject the connection using @InjectConnection() and use query to run a raw query, as shown in the TypeORM docs

const rawData = await connection.query(`SELECT * FROM USERS`);

---

Assuming you're using @nestjs/typeorm, this.connection can be obtained through the @InjectConnection() decorator in the constructor

@Injectable()
export class FooService {
  constructor(@InjectConnection() private readonly connection: Connection) {}

  async doSomeQuery() {
    return this.connection.query('SELECT * FROM USERS;');
  }
}

---

As of Nest v9 (and somewhere in @nestjs/typeorm@8, rather late in the version) TypeORM v0.3.0 is used and @InjectConnection() is deprecated. @InjectDataSource() should be used instead