PHP was my backend language of choice before I became interested in Node.js.  PHP has some wonderful ways to interact with SQL such as PDO (PHP Data Objects).  In addition, it is easy to pass literal strings as query strings to the SQL implementation of your choice using libraries such as MySQLi and MSSQL.  My favorite part of using PHP with literal strings is that the literal strings can span multiple lines, making it easy to write neat and tidy queries.

// Long and ugly query

$longQuery = "SELECT fname, lname, email FROM users WHERE id = 1234";

// The same query now less ugly 
// thanks to judicious placement of carriage returns

$tallQuery = "SELECT fname, lname, email
              FROM users
              WHERE id = 1234";

Obviously, this example is trivial, but if you have spent any time writing SQL queries I am sure you can imagine a scenario where your SQL query might be much longer.  If you have to put everything on the same line your query will eventually become a spaghetti string of unreadable code that requires endless amounts of scrolling left and right to read in its entirety.  Before you know it, you will be back at the doctor asking them to prescribe whatever drug it was they gave you to help cope with the trauma of miles long HTML snakes produced by MS FrontPage 2003.

By now you are probably wondering, what does this have to do with Node.js?  Well, unlike PHP, JavaScript cares about where you put your line breaks, and you can’t pass a query string like the $tallQuery example from above.  Instead, you need to join your strings with the infix operator, also known as the plus sign.

// Invalid query string

var invalidQuery = "SELECT fname, lname, email
                    FROM users
                    WHERE id = 1234"

// SyntaxError: Unexpected token ILLEGAL

// Valid query string using the infix operator, but ew!

var validQuery = "SELECT fname, lname, email " +
                 "FROM users " +
                 "WHERE id = 1234"

In the first example, I try to apply PHP practices to JavaScript and I am rewarded with a syntax error.  In the second example, I succeed in creating a valid multi-line query.  This is certainly more readable than a big long query, but I can’t help but feel that it sucks in my heart of hearts.  The infix operators are inconvenient to type, and now I have to worry about properly concatenating my strings, which is also inconvenient and less robust as I could forget one and cause the app to fail with another syntax error.

This small hiccup nearly ruined Node.js for me.  It just seemed so silly.  I wasn’t alone either, and a number of libraries were created to work around it.  One of my favorites is Squel, which allows you to build query strings by stringing together a series of functions prefixed the dot notation.  Check out the link to see what I mean.  Amusingly, when Squel was introduced on r/javascript back in 2013 it received some alarmed comments regarding how unsafe it is to pass query strings in the browser.  Node.js is server side.  You know that, I know that, but some people didn’t so now you can feel smug about something on your bus ride to work.

Anywho, Squel requires that you learn a somewhat esoteric API, as do solutions like node-sql by Brian Carlson, the sharp guy behind the node-postgres library.  I suppose that if you aren’t much of a SQL fan, you will prefer the OOP look and feel of Squel and node-sql.  But, if you are like me, you actually like SQL, and you don’t mind writing SQL queries one bit.  In fact, you might even feel that writing SQL queries is easier than writing code for other languages thanks to the declarative syntax of SQL.  You don’t define functions and assign values to variables, you SELECT things WHERE equality EXISTS.  It is easy to wrap your head around.

Enter ES6 template literals.  From MDN:

Template literals are string literals allowing embedded expressions. You can use multi-line strings and string interpolation features with them. They were called “template strings” in prior editions of the ES2015 specification.

How do you use a template literal?  It’s easy, just wrap your string with backticks, also known as the grave accent, also known as the angled dash under the squiggly (also known as the tilde), instead of single or double quotes.  Then your strings, ahem, literals are free to span as many lines that please you.

var spansManyLines = `This compiler doesn't own me!
                      I span as many lines as I please!
                      ...`

It’s so new that even my syntax highlighting plugin doesn’t support it yet.  I’m sure you can see where this is going.  Now we can have multiline strings without using the infix operator, which is a huge relief.  The folks on the steering committee are going in the right direction.

What if you need to pass a variable to your string?  Your first instinct might be to concatenate your string to a variable containing another literal.

// Concatenate a variable containing a string literal to an existing string.

var id = 1234

var validQuery = "SELECT fname, lname, email " +
                 "FROM users " +
                 "WHERE id = " + id

JavaScript will let you do this, but this is bad.  What if the variable contains something you didn’t expect?  For example, “;DROP TABLE users” (relevant xkcd).  That would ruin your day.  You could write a whole mess of code to sanitize your inputs.  This would also ruin your day as soon as you discovered that the effort wasn’t necessary.  Instead, you can take advantage of the built in parameterization features included in Node.js database drivers and take advantage of the ability to embed expressions in template literals using a library such as sql-template-strings.

var SQL = require('sql-template-strings')

var id = 1234

var query = (SQL
            `SELECT fname, lname, email
             FROM users
             WHERE id = ${id}`
            )

The text wrapped with ${} is the embedded expression.  Thanks to sql-template-strings, this is equivalent to passing a parameterized query in your favorite library.  This helps to protect you from nasty injection attacks like ‘1234;DROP TABLE users’.  I’ll leave the rest of the explanation for why you should use parameterized queries and how you should implement them (for Postgres, at least) to Brian Carlson (there’s that name again).

So there you have it, multiline query strings have a simple interface in Node.js assuming you are using an ES6 compliant version.  If you are familiar with JavaScript, but are just now learning Node.js, I recommend the learning tool known as learnyounode to help you get started.  Happy querying!

Be sure to take a look at JavaScript: The Good Parts.