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.
Top comments (3)
since the
?has been replace with$1,$2, ...etcNice! 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!
I've changed a little the snippet, and now it handles array type parameters as well.