How to output raw SQL with filled parameters in TypeORM?

Even though TypeORM provides more or less good ORM feeling, many times you have to use Query Builder instead of ORM mechanisms. It is important to check how the query will look like (for example for performance reason). So, you need to get somehow raw SQL from Query Builder, to run it manually against your database.

Although TypeORM provides getSql() method, it isn't handy for developers because it doesn't fill parameter placeholders. Let's pretend we have a simple TypeORM query builder code:

createQueryBuilder('user')
  .where('id = :id', { id: 1 })
  .andWhere('name = :name', { name: 'Chris' })
  .orderBy('id', 'DESC')
  .getSql(),

As the result of the getSql() function, you will get probably something like this:

SELECT
  users.id,
  users.name
FROM users
WHERE 
  id = ? AND
  name = ?
ORDER BY id DESC

You must admit it isn't very helpful because you need to replace manually each ? with related value. Here is a quick snippet, which will help you:

let [sql, params] = query.getQueryAndParameters();
params.forEach((value) => {
  if (typeof value === 'string') {
    sql = sql.replace('?', `"${value}"`);
  }
  if (typeof value === 'object') {
    if (Array.isArray(value)) {
      sql = sql.replace(
        '?',
        value.map((element) => (typeof element === 'string' ? `"${element}"` : element)).join(','),
      );
    } else {
      sql = sql.replace('?', value);
    }
  }
  if (['number', 'boolean'].includes(typeof value)) {
    sql = sql.replace('?', value.toString());
  }
}); 

console.log(sql);

query variable is SelectQueryBuilder<Entity> type. Your query will be outputted in a much clearer way. Now it can be directly used as SQL script in any RDB client:

SELECT
  user.id,
  user.name
FROM users
WHERE 
  id = 1 AND
  name = "Chris"
ORDER BY id DESC

If you're using Visual Studio Code as your IDE, you can try the snippet below:

"Print to console SQL query with filled params": {
  "prefix": "sqldump",
  "body": [
    "let [sql, params] = query.getQueryAndParameters();",
    "params.forEach((value) => {",
    "  if (typeof value === 'string') {",
    "    sql = sql.replace('?', `\"\\${value}\"`);",
    "  }",
    "  if (typeof value === 'object') {",
    "    if (Array.isArray(value)) {",
    "      sql = sql.replace(",
    "        '?',",
    "        value.map((element) => (typeof element === 'string' ? `\"\\${element}\"` : element)).join(','),",
    "      );",
    "    } else {",
    "      sql = sql.replace('?', value);",
    "    }",
    "  }",
    "  if (['number', 'boolean'].includes(typeof value)) {",
    "    sql = sql.replace('?', value.toString());",
    "  }",
    "});"
  ]
}

Now you can use the sqldump shortcut to print out the whole snippet. Quick and easy! :)

Edit: 08.11.2021
I've changed a little the snippet, and now it handles array type parameters as well.

Comments

[Sakal]

since the ? has been replace with $1, $2, ...etc

let [sql, params] = query.getQueryAndParameters();
params.forEach((value, i) => {
  const index = '$' + ( i + 1)
  if (typeof value === 'string') {
    sql = sql.replace(index, `"${value}"`);
  }
  if (typeof value === 'object') {
    if (Array.isArray(value)) {
      sql = sql.replace(
        index,
        value.map((element) => (typeof element === 'string' ? `"${element}"` : element)).join(','),
      );
    } else {
      sql = sql.replace(index, value);
    }
  }
  if (['number', 'boolean'].includes(typeof value)) {
    sql = sql.replace(index, value.toString());
  }
}); 

[charliemday]

Nice! This almost worked out-of-the-box for me for TypeORM PSQL printing but had some issues where ? should have been $ and PSQL takes single quotes instead of double quotes for values - have made minor updates to the code in this available gist here: gist.github.com/charliemday/b0668f...

Thanks!

[Krzysztof Szala]

I've changed a little the snippet, and now it handles array type parameters as well.