felixfbecker / node-sql-template-strings

ES6 tagged template strings for prepared SQL statements 📋
ISC License
609 stars 40 forks source link

MSSQL workaround (sample code in comments) #121

Open steve-krisjanovs opened 4 years ago

steve-krisjanovs commented 4 years ago

Great module by the way!

I used it quite extensively against PGSQL and now I want to expand my app to support MSSQL as well.

MSSQL has it's own es6 templating engine in their node driver but it's very different from this module's implementation which isn't compatible with mssql due to how parameters are handled (Microsoft's way templates the query and executes it against the database in one pass - ugh). I didn't want that in order for my app to work across both mssql and pgsql with minimal refactoring, so I implemented the following code that accepts a query string templated by this module, transforms it into a parameterized mssql query, then executes it :)

Code example below ("cmd" is an query templated by node-sql-template-strings, and "client" is a request object from a mssql pool or transaction):

const padding_length = 4;

//transform the query e.g. "select * from table where id = $1" >> "select * from table where id = @param0001"
//the loop below will handle queries with as many as 1000 parameters (i.e. param0001..param1000)
var newquery = cmd.text;
for (var i = 1; i <= 1000; i++) {
    var findthis = `${i}`;
    var replacewith = `@param${(i).toString().padStart(padding_length, "0")}`;
    newquery = newquery.replace(findthis, replacewith); //replace first occurance
}

//added this for debugging
client.debug_newquery = newquery; 
client.debug_cmd = cmd;

//build the parameters for the call....
for (var i = 0; i < cmd.values.length; i++) {
    var val = cmd.values[i];
    client.input(`param${(i + 1).toString().padStart(padding_length, "0")}`, val);
}

//execute query
res = await client.query(newquery);