DEV Community

loading...
Cover image for How to output raw SQL with filled parameters in TypeORM?

How to output raw SQL with filled parameters in TypeORM?

avantar profile image Krzysztof Szala Updated on ・2 min read

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}"`); 
  } else { 
    sql = sql.replace(`?`, value); 
  } 
}); 

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(); \nparams.forEach((value) => { \n  if (typeof value === 'string') { \n    sql = sql.replace('?', `\"\\${value}\"`); \n  } else { \n    sql = sql.replace('?', value); \n  } \n}); \n\nconsole.log(sql);\n"
  ]
}
Enter fullscreen mode Exit fullscreen mode

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

Discussion (0)

pic
Editor guide