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 bees 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?

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 bees 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?

• javascript
• mysql
• sql
• node.js
• express

• 1 developer.mozilla/en-US/docs/Web/JavaScript/Reference/… TLDR: escape them, don't use them, or don't use template literals. – Kevin B Commented Sep 21, 2020 at 22:10
• @KevinB Escaping them manually every time would get very tedious. As OP says: a way to write a multiline MySQL query without escaping the query backticks – CertainPerformance Commented Sep 21, 2020 at 22:14
• Tip: You only need to put quotes around entity terms that conflict with MySQL reserved keywords. Avoid using columns like this and the problem goes away. If on rare occasions you have a collision, use backslash to add literal backticks. Let this be a reminder to not collide with reserved keywords. – tadman Commented Sep 21, 2020 at 22:15
• you have to escape them or make an abstraction layer db.find('User', {id: 1}) or User.find({id: 1}) which handles preparing the query based upon whats passed, also its not like you change a query much after writing it, imo your mangling it with non standard [] wrapping and replaceAll it looks worse than a few \'s – Lawrence Cherone Commented Sep 21, 2020 at 22:35
• Not a direct answer, but knex can hide this type of issue from your code. – Matt Commented Sep 21, 2020 at 23:47

3 Answers 3

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./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

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.

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)