[SOLVED] How can I write multiline MySQL queries in Node.js using backticks?

How can I write multiline MySQL queries in Node.js using backticks?

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 SQL Server):

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

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?

Solution

I prefer to just not use backticks in my MySQL queries. If you still need quotes around a table name, you can try setting ANSI_QUOTES to allow using " instaead : https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_ansi_quotes

If that doesn't work, you might prefer to just escape the backticks: Template literals with nested backticks(`) in ES6