Writing Conditional SQL Statements Using Nested Tagged Template Literals In Node.js
The other week, Scott Rippey - one of our senior Node developers - was chatting about nested template literals in ES6. Template literals are one of the most exciting features of ES6, in my opinion; but, the ability to nest them seemed, at first glance, to be more confusing than helpful. That said, I couldn't get the thought out of my mind - I kept mulling it over, thinking about possible use-cases. And, one that finally occurred to me was that it might be helpful for writing conditional SQL statements in Node.js.
ASIDE: Exploring ES6, by Dr. Axel Rauschmayer, has an entire chapter on Template Literals, including a section on using nested template literals to perform text templating.
Say what you will about ColdFusion as a programming language, its optional tag-based syntax makes writing conditional SQL statements easier than I've seen in any other language. Take the following for example:
<cfargument name="userID" type="numeric" required="false" default="0" />
<cfargument name="teamID" type="numeric" required="false" default="0" />
<cfargument name="ensureActiveMembership" type="boolean" required="false" default="false" />
<cfquery name="users">
SELECT
u.id,
u.name,
u.email
FROM
user u
<cfif ( userID && teamID )>
INNER JOIN
team_membership tm
ON
(
tm.userID = #userID#
AND
tm.teamID = #teamID#
<cfif ensureActiveMembership>
AND
tm.endedAt IS NULL
</cfif>
)
</cfif>
<cfif ( userID && ! teamID )>
WHERE
userID = #userID#
</cfif>
</cfquery>
The fact that you can integrate the ColdFusion control-flow statements seamlessly within the SQL syntax makes queries like this a joy to write and to read while keeping the overall intent of the query clear and co-located. Sure, you could probably do the same thing with some abstraction layer or ORM (Object-Relational Mapper); but for me, I always prefer to see the SQL as I find that it makes the query easier to reason about.
Node.js doesn't have an optional tag-based sytax. But, it does have those powerful template literals. And, as Scott Rippey pointed out, those template literals can be nested. As such, I wanted to see if I could use nested template literals, in Node.js, to try and reproduce the kind of conditional, yet highly readable SQL structuring that I can achieve in ColdFusion.
In JavaScript, there are two kinds of template literals: the basic template literal and the tagged template literal. The basic template literal performs the value interpolation for you; the tagged template literal allows you to explicitly perform the interpolation through the use of a tagging function. This tagging function provides an array of string parts and an array of values and leaves it up to you to figure out how to zip the two sets together.
I wanted to explore the use of tagged template literals for SQL statement construction because an embedded value, in a template literal, is always embedded. Which means that if I'm going to use "truthy" / "falsey" values to conditionally include nested literals, I'll quickly end up with "falsey" values in my resultant SQL.
Consider the following interpolation syntax:
` ${ false && ` nested template ` } `
When JavaScript evaluates this nested template literal, it will end up embedding "false" in the result. If we're in the business of constructing conditional SQL statements, what we really want is "false" - and its associated template literal - to be omitted entirely. To do this, we can use a tagged template literal that examines the interpolation values and omits any that are non-string values:
includeStrings` ${ false && ` nested template ` } `
In this case, includeStrings() would be a normal JavaScript function that becomes responsible for zipping the parts of the template literal together. And, by using this tagging function, we can examine the part that is "false" and omit it from the result.
In my exploration of nested template literals, I created a tagging function called "embed." This function examines the value parts and includes only those that are String values that contain at least one non-space character. By doing this, I can create simple conditionally-nested template literals without having to worry about the falsey values showing up in the resulting SQL statements.
In the following code, notice that the SQL statement, in the getUsers() function, is conditionally constructed based on the function arguments.
// Require the core node modules.
var chalk = require( "chalk" );
// Try calling the "database method" with various combinations of inputs. The key here
// is to notice that falsey values, such as "0", neither include the associated
// conditional SQL statements; and that the falsey value itself doesn't get included
// in the result (since the "embed" tag will ignore values that aren't strings).
header( "getUsers()", "Excluding all conditional statements." );
format( getUsers() );
header( "getUsers( 0 )", "Excluding all conditionals, but using a non-empty falsey." );
format( getUsers( 0 ) );
header( "getUsers( 4 )", "Including the user constraint." );
format( getUsers( 4 ) );
header( "getUsers( 4, 16, false )", "Including the user and team constraint, but not an active constraint." );
format( getUsers( 4, 16, false ) );
header( "getUsers( 4, 16, true )", "Including all conditional, nested statements." );
format( getUsers( 4, 16, true ) );
// ----------------------------------------------------------------------------------- //
// ----------------------------------------------------------------------------------- //
// I get the given users, filtered by the optional userID and teamID.
// --
// NOTE: Obviously, this function doesn't actually perform any query - for the demo,
// it is just constructing the SQL statement that would be used.
function getUsers( userID, teamID, ensureActiveMembership ) {
var statement = embed`
SELECT
u.id,
u.name,
u.email
FROM
user u
${ userID && teamID && embed`
INNER JOIN
team_membership tm
ON
(
tm.userID = :userID
AND
tm.teamID = :teamID
${ ensureActiveMembership && embed`
AND
tm.endedAt IS NULL
`}
)
`}
${ userID && ! teamID && embed`
WHERE
userID = :userID
`}
;
`;
return( statement );
}
// ----------------------------------------------------------------------------------- //
// ----------------------------------------------------------------------------------- //
// I construct the SQL statement by zipping the given template strings and values
// together. Only string values with non-space characters will be included in the
// result (so that falseys don't get included).
// --
// CAUTION: This function is intended to be used with a TAGGED TEMPLATE literal.
function embed() {
var strings = arguments[ 0 ];
var values = Array.prototype.slice.call( arguments, 1 );
var results = [];
for ( var i = 0 ; i < strings.length ; i++ ) {
// Make sure the string part isn't just spaces and tabs.
if ( isEmbeddable( strings[ i ] ) ) {
results.push( strings[ i ] );
}
// Make sure the value is a String and more than just spaces and tabs.
// --
// NOTE: There may not be a value at this index; but, if we go beyond the bounds
// of the value collection, the result will just be "undefined", which will be
// omitted anyway by the isEmbeddable() check.
if ( isEmbeddable( values[ i ] ) ) {
results.push( values[ i ] );
}
}
return( results.join( "" ) );
}
// I determine if the given value is a String that contains at least one non-space
// character (that is worth embedding).
function isEmbeddable( input ) {
var isString = ( String( input ) === input );
var hasContent = /\S/.test( input );
return( isString && hasContent );
}
// ----------------------------------------------------------------------------------- //
// ----------------------------------------------------------------------------------- //
// I output the logging header.
function header( label, note ) {
console.log( chalk.cyan.bold( label ) );
console.log( chalk.cyan.bold( "-".repeat( label.length ) ) );
console.log( chalk.dim( "Note:", note ) );
}
// I output the logging body.
function format( content ) {
var formattedContent = content
.replace( /^\t\t/gm, " " )
.replace( /\t/g, " " )
.replace( /(^[ \t]+)[\r\n]\s+/gm, "$1" )
;
console.log( formattedContent );
}
As you can see, I'm using the embed() tagging function to interpolate each template literal, even the nested ones. In some cases, I don't necessarily need it, such as with the inner-most template literals; but, I like keeping the tagging function in the code for the consistency and flexibility. In the end, I really feel like this reads almost as nicely as the ColdFusion code. And, when we run this file through Node.js, we get the following terminal output:
As you can see, the SQL statements came out exactly as we intended thanks to the tagging function and the nested template literals.
One of the biggest drawbacks for me, when it comes to Node.js, is the fact that SQL is so hard to write. Especially when compared to the ease with which I have become accustomed-to thanks to ColdFusion. But, by using nested template literals and tagging functions, I think that we can make writing conditional SQL statements in Node.js almost as easy as it is in ColdFusion. Of course, the color-coding leaves something to be desired. But, I'm calling this a victory regardless.
Want to use code from this post? Check out the license.
Reader Comments
Ben, I went down the same exact path as you! Conditional blocks are a huge missing feature, so I implemented the same "tag function" that omits falsey values. I was bummed that you have to "tag" the nested templates too, but that's not too big a deal.
I found that there's 2 more things missing too: `unindent` is a pretty critical feature, and `repeating` things is pretty cumbersome.
I'm really bummed that these aren't better supported via template strings!
So I implemented these features myself, and the results aren't bad.
https://gist.github.com/scottrippey/d712f72066d2dc7e17a6bc74d3fa0f21
@Scott,
Ha ha, great minds think alike :D I had not even thought about repeating, but that definitely takes you into a more robust templating arena. As far as "unindenting", you know how maniacal I am about my white-space. Don't think for a second that having the SQL statements *not* line up isn't driving me crazy!! But, it is what it is. At least, in most cases, we're generating things like SQL and HTML where white-space is much less of an issue.