How to excute Raw SQL Query on NestJS framework using typeorm

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

You can use query builder to create the required query -

return await getRepository(users)
.createQueryBuilder("user")
.where("user.role = 'Admin'")
.select("user.id as userId")
.addSelect("user.fullName as fullName")
.addSelect("(select count(*) sendCnt from chat where senderId = user.id) as sendCnt")
.addSelect("(select count(*) recvCnt from chat where receiverId = user.id) as recvCnt")
.printSql()
.getRawMany();

You can use this:

import { getManager } from 'typeorm';

const entityManager = getManager();
return entityManager.query(`SELECT * FROM users`)

On your case:

return entityManager.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'`)

you can use typeorm - DataSource module for execute raw SQL queries

please refer below example

import { DataSource } from 'typeorm';

export class <class name> {
  constructor(
 @InjectDataSource() private dataSource: DataSource,
) {}

  async function_name () {
     this.dataSource.query(<QUERY>)
  }
}