How to write multiline MySQL queries in node.js using backticks?

user_13058778 0
Viewed 1617

I have a problem trying to write MySQL queries inside my node.js application. When writing a large query I need to split the code into various lines, so I need to use backticks to write a JavaScript string. The point is that inside my MySQL query I also need to use backticks as part of the query, like this:

SELECT `password` FROM `Users`

So the code for executing the query in node.js should look like this:

database.query(`
  SELECT `password` 
  FROM `Users`
`);

but the code doesn't work because the backticks of the query are interpreted as backticks of the string literal. I could concatenate several string literals using single or double quotes like this:

database.query( 
  'SELECT `password` ' +
  'FROM `Users`'
);

but I tried and it becomes a mess very quickly. I also tried to use a different approach using a special char as backticks replacement and replacing it with backticks with the replaceAll() function like this (inspired by MSSQL):

`SELECT [password]
FROM [Users]`.replaceAll('[', '`').replaceAll(']', '`');

So my question is: is there a way to write a multiline MySQL query without escaping the query backticks or without concatenating several single or double quotes string literals?

express node.js mysql sql javascript
3 Answers
user_40803060

I try to avoid backticks in queries. It is not ANSI SQL, it litters the code and in 99.9% of cases they are not needed. Even if you use some keywords as column names, in most cases it simply works, despite being colored differently in IDE.

If I have to use them, I escape them with backslash, which, for the rare case it is needed, is not too bad.

Lastly, if you really don't want to use string literals,

const sql = [
 "SELECT *",
 "FROM mytable",
 "WHERE x=0"
].join("\n");

still works just fine.

user_2163740

You could use a different character (that isn't used elsewhere in the query) and replace it with backticks afterwards. For example, #:

const query = `
SELECT #password#
FROM #Users#
`
  .replaceAll('#', '`')

Another option would be to import the query from a text file (not in JavaScript syntax), and import it somehow:

password-query.txt:

SELECT `password` 
FROM `Users`

const query = fs.readFileSync('./password-query.txt', 'utf-8');
// ...
database.query(query)
user_95152070
Related