JavaScript has Template Literals (or Template Strings) built-in. This results in a string, after evaluating replacement expressions.
const name = 'Tim'
const job = 'Eng'
const str = `${name} works in ${job}`
// str === 'Tim works in Eng'
The expressions can be more than just a variable name.
const str = `1 + 1 = ${1 + 1}`
// str === '1 + 1 = 2'
A while back I started a new project, and decided not to use any ORM. Instead, I wanted to use templatized SQL files, with a minimal API to query against them.
At first I just read in the files and did some simple string replacements, but it didn't feel right, or safe. I was doing manual RegExp replacements that would possibly lead to SQL error or injections. And was simply too hackish.
This led me to dig into Template Literals. But, they are evaluated immediately. So as you define a template it will make the replacements and return a static string. Saving a file with replacement expressions still required some 'magic.'
It'd be convenient to have a static file read in, evaluated when needed, while keeping the niceties of Template Literals.
get-posts.sql
select *
from posts
where true
and user_id = ${userId}
and deleted is null
index.js
const fs = require('fs').promises
async function main() {
const templateQuery = await readFile('get-posts.sql', 'utf8')
const preparedQuery = templatized(templateQuery, {
userId: 123
})
/*
select *
from posts
where true
and user_id = 123
and deleted is null
*/
}
main()
Since Template Literals are evaluated when defined, we need to hack around this and generate new literals on-the-fly. And being able to pass a hash of values into the template gives a little extra complexity. Templates should access values like userId
instead of vars.userId
.
To achieve all this we can generate a function which will generate a new string each time it's called.
const templatized = (template, vars = {}) => {
const handler = new Function('vars', [
'const tagged = ( ' + Object.keys(vars).join(', ') + ' ) =>',
'`' + template + '`',
'return tagged(...Object.values(vars))'
].join('\n'))
return handler(vars)
}
This takes in the template string and an optional hash of values to use in the template. The template will still use any global variable in scope, and will also throw if variables are not available (that's normal in Template Literals).
The optional values are spread out as function arguments ('const tagged = ( ' + Object.keys(vars).join(', ') + ' ) =>',
), making them available in the template, without having to access them through vars.
.
In the examples above, the function that would be generated from templatized(templateQuery, { userId: 123 })
would be:
function(vars) {
const tagged = (userId) => `
select *
from posts
where true
and user_id = ${userId}
and deleted is null
`
return tagged(...Object.values(vars)) // return tagged(123)
}
A more mature version of this solution became dot-template, which is specific to flat files.
To achieve my goal of flat SQL files I created another library that uses dot-template
, specific to Postgres SQL queries, called pg-dot-template. SQL templates required some extra love, and features like redacting PII in logs.
That ultimately led me to create a third library, pg-dir, which, using the others, walks a directory of SQL template files and returns helper functions to query results. Using this library, you could run a query like the example above as const posts = await postsSql.getPosts({ id: 123 })