yzwxyw
Last Updated: October 19, 2018
·
746
· gajus

Write multiline MySQL queries in JavaScript using <sql> tag

In MySQL, if an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. The identifier quote character is the backtick (`).

Nonreserved keywords are permitted as identifiers without quoting. However, it is a healthy habit to quote all identifiers to prevent accidental collision with keywords, reserved words and names of built-in functions.

There is no problem writing single-line MySQL queries because you can use single or double quotes, e.g.

const = 'SELECT `p1`.`id`, `p1`.`name`, `t1`.`id` `tagId`, `t1`.`name` `tagName` FROM `page` `p1` INNER JOIN `page_tag` `pt1` ON `pt1`.`page_id` = `p1`.`id` GROUP BY `p1`.`id` HAVING COUNT(`pt1`.`id`) = 4';

However, writing a long query on a single line hurts the readability of the query. You cannot use template literals because the syntax clashes with the MySQL identifier quote character.

A proponent of the template literals syntax will argue that you can escape the backtick character, e.g.

const = `
SELECT
  \`p1\`.\`id\`,
  \`p1\`.\`name\`,
  \`t1\`.\`id\` \`tagId\`,
  \`t1\`.\`name\` \`tagName\`
FROM
  \`page\` \`p1\`
INNER JOIN
  \`page_tag\` \`pt1\`
ON
  \`pt1\`.\`page_id\` = \`p1\`.\`id\`
GROUP BY
  \`p1\`.\`id\`
HAVING
  COUNT(\`pt1\`.\`id\`) = 4
`;

However, this approach has several disadvantages:

  • It makes it hard to read the query.
  • You cannot copy back and forth the query between your IDE and an SQL client (as is often the workflow).

As a result, the established practise for writing MySQL queries that span multiple lines is to store them in a separate file. This is a good approach, esp. for very long queries. However, sometimes you want to have queries in-file, e.g. when prototyping or if you simply prefer to.

This is where the babel-plugin-transform-jsx-element-to-string-literal comes in. You can declare any query using JSX elements:

const = <sql>
SELECT
  `p1`.`id`,
  `p1`.`name`,
  `t1`.`id` `tagId`,
  `t1`.`name` `tagName`
FROM
  `page` `p1`
INNER JOIN
  `page_tag` `pt1`
ON
  `pt1`.`page_id` = `p1`.`id`
GROUP BY
  `p1`.`id`
HAVING
  COUNT(`pt1`.`id`) = 4
</sql>;

Note:
The above example is using {"tagNames": ["sql"]} configuration.

3 Responses
Add your response

30310

How to apply MySQL queries in JavaScript? Actually I am a new programmer of http://www.AOneEssays.net/ and I am just getting experience anyone can help me?

2 months ago ·
30497

This has been one of the greatest stuff to read here wish to see some more of these relevant here to see some more of the stuffs here like these wish to https://www.papersarena.com/ have been working here properly hope to see some more of these ideas.

about 2 months ago ·
31018

On the hills and far away. One million miles from L.A. We will find the beginning of something new. Take me anywhere, take me anywhere. Wherever you go with you. Just have you and the game: http://happy-wheelsgames.com happy wheels http://happy-wheelsgames.com/basketball-legends-game basketball legends

1 day ago ·