Prepared Statements and Tagged templates

[benjamingr]

It would be really cool if you supported tagged templates for prepared statements instead of @param syntax. It's more modern and easier to work with in newer versions of node.

[patriksimek]

Could you please post an example or link? Not sure what tagged template is.

[benjamingr]

ES2015 introduced a new kind of string literals:

var world = "World!";
console.log(`Hello ${world}`); // logs Hello World!

So far, not very interesting - the interesting part is that it also introduced tagged templates that desugar to function calls:

function tag() { console.log(arguments); }
var world = "World!";
tag`Hello ${world}.`; // logs [["Hello","."], "World!"]

This desugars to a function call - meaning you can create parameterized queries this way:

var ps = new sql.PreparedStatement();
ps.input('param', sql.Int);
ps.prepare('select @param as value').then(function() {
    return ps.execute({param: 12345});
});

Can become:

sql`select ${12345} as value`.then(res => ...);

Where the sql tagged template desugars to concat the string with @p1 to @pn and then place those parameters inside and execute it. Hopefully there can also be a cache of prepared statements to save some of the work between calls.

Here is a super basic example of how something like this might look like:

function sql(template, ...args) {
    let str = template.join("?");
    const ps = new sql.PreparedStatement();
    for(const arg of args) { 
      ps.add(getTypeFromVal(arg), arg); ; // not sure what the syntax with ? is with node-mssql
    }
    return ps.execute(); 
}

[patriksimek]

Thanks for explanation, this feature was discussed here some time ago. Maybe it's time to make it happen :)

[patriksimek]

Just released a new version with experimental support for tagged templates. More examples in docs.

sql.query`select ${1337} as num`.then(recordset => console.dir(recordset))

Prepared statements are complicated in situations when null is passed as a value to initial template - I have no chance to guess the data type so it might end with corrupted data on input. Suggestions are welcome.

[benjamingr]

First of all awesome news ^_^ I'll check it out at work and provide more concrete feedback.

nulls - Yeah, types are really hard when you can't infer them and there is no static type-system.

The options are to:

• throw an error on an initial null and allow an optional type in the tagged template sql.query(sql.Int)SELECT ...` as another form.
• Run it as a regular query and not a prepared statement, this breaks if there are other parameters except for the null and might be unsafe anyway for following invocations.
• Defer the query for a tick and hope that the query gets invoked again with a non-null value.

Honestly, I'm fine with tagged templates working for 90% of usages cleanly and just directing users to the regular syntax for the other 10% - it's a convenience feature.

[patriksimek]

What about defining types instead of values?

sql.ps`insert into mytable (id, text, bin) values (${sql.Int}, ${sql.VarChar}, ${sql.VarBinary})`.then(function(ps) {
    ps.execute(1337, 'sometext', new Buffer(10)).then(function(recordset) {
        ps.unprepare()
    }).catch(function(err) {
        // ... error checks
    });
}).catch(function(err) {
    // ... error checks
});

[benjamingr]

That could work for making the statement, but it would not give you a "safe easy default" where using a prepared statement is the easiest way to run your code and even people who are not very informed don't run into SQL Injection

[dhensby]

this was implemented some time ago