Sign in
Log inSign up

Write multiline MySQL queries in JavaScript using <sql> tag

Gajus Kuizinas's photo
Gajus Kuizinas
·Dec 5, 2016

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.

1