How to output raw SQL with filled parameters in TypeORM?
#javascript#typescript#typeorm#sql
Krzysztof Szala

Krzysztof Szala @avantar

About: PHP and TypeScript developer, currently working at DAZN. Don't want to miss my next article? Just click 👇

Location:
Silesia
Joined:
May 30, 2020

How to output raw SQL with filled parameters in TypeORM?

Publish Date: Apr 19 '21
15 3

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(),
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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());",
    "  }",
    "});"
  ]
}
Enter fullscreen mode Exit fullscreen mode

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 3 total

  • Krzysztof Szala
    Krzysztof SzalaNov 8, 2021

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

  • charliemday
    charliemdayMar 17, 2022

    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!

  • Sakal
    SakalJul 21, 2022

    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());
  }
});
    Enter fullscreen mode Exit fullscreen mode
Add comment