How to organize a long SQL statement written in Javascript/Node

Question

I've written a microservice that is doing external calls to a SQL database. I have a js file that is solely dedicated to these awful looking queries that are formed as a string..

let data_example = 'SELECT * \
WHERE BLAH = BLAH AND \
....';

It's barely human readable and looks awful. What's the best way to store/organize or approach a long SQL query string that needs to be stored and called in Node?

Answer 1

You have a couple of options.

1. You could store them in a file you read at program startup, so you're authoring them in a tool that understands SQL, perhaps can even connect to your DB to auto-complete things for you, can help you format, do syntax highlighting, etc.

2. You can use ES2015 ("ES6") template strings (also called "template literals"):

   let data_example = `
       SELECT *
       WHERE BLAH = BLAH AND ...
   `;
   

   Template strings can span lines (note that newlines, and whitespace at the beginning of subsequent lines, are part of the string).

   Just be sure you don't use the features of template strings to fill in parameters through (hidden) string concatenation, because That Would Be Wrong™. :-) That is, just like you wouldn't do this:

   // We know NOT to do this
   example = "WHERE SomeColumn = '" + userInputValue + "'";
   

   don't do the same thing using the hidden string concatenation of template strings:

   // We also know NOT to do this
   example = `WHERE SomeColumn = ${userInputValue};`
   

   ....which is how you would do that string concatenation using a template string. If you're in the habit of using template strings, it would be really easy to do that by mistake, and have an SQL Injection vector.

There are probably use cases for both options.