DEV Community

Cover image for How to output raw SQL with filled parameters in TypeORM?
Krzysztof Szala
Krzysztof Szala

Posted on • Updated on

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(),
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.

Top comments (3)

Collapse
 
sakalso profile image
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());
  }
}); 
Enter fullscreen mode Exit fullscreen mode
Collapse
 
charliemday profile image
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!

Collapse
 
avantar profile image
Krzysztof Szala

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