How to show generated SQL / raw SQL in TypeORM queryBuilder

In TypeORM, when building queries using QueryBuilder, it is sometimes necessary to view the final generated SQL statement to ensure it meets our expectations or for debugging purposes. Here are several methods to view or print the SQL statements generated in TypeORM's QueryBuilder:

Method 1: Using getQuery and getParameters

The getQuery method returns the string of the SQL statement to be executed, while getParameters returns an object containing all parameters used in the SQL statement. This method does not execute the SQL statement.

javascript
import { getConnection } from "typeorm";

const userRepository = getConnection().getRepository(User);

const queryBuilder = userRepository.createQueryBuilder("user")
    .select("user.name")
    .where("user.id = :id", { id: 1 });

const query = queryBuilder.getQuery();
const parameters = queryBuilder.getParameters();

console.log(query);  // Output SQL statement
console.log(parameters);  // Output { id: 1 }

Method 2: Using printSql

The printSql method is a chained call that can be used directly when building queries. It prints the generated SQL statement to the console. This method also does not execute the SQL statement.

javascript
import { getConnection } from "typeorm";

const userRepository = getConnection().getRepository(User);

userRepository.createQueryBuilder("user")
    .select("user.name")
    .where("user.id = :id", { id: 1 })
    .printSql()
    .getMany();

Method 3: Listening to the query event

If you want to globally monitor all SQL statements executed through TypeORM, you can add a logger option when creating the connection to listen for the query event.

javascript
import { createConnection } from "typeorm";

createConnection({
    type: "mysql",
    host: "localhost",
    username: "test",
    password: "test",
    database: "test",
    entities: [
        //...entities here
    ],
    logger: "advanced-console",
    logging: "all"  // This will print all SQL statements, including queries
}).then(connection => {
    //...use connection
}).catch(error => console.log(error));

Use Case Example

Suppose we are developing an e-commerce application and want to check if a query for retrieving the user's last order details is correct. In this case, we can use any of the above methods to print and inspect the SQL statement, ensuring it correctly joins the user and order tables and properly filters the data.

By using these methods, we can ensure the transparency and correctness of our application when executing database queries, while helping us quickly identify and resolve potential query errors or performance issues.