Dev life is easy with node-mssql

#beginners #node #sql #azure

As mentioned already in a previous posts, I've just started learning Node in the last months. At first I had a rough start as the totally asynchronous nature of Node, and the many ways it can be leveraged, wasn't something I was used to. I battled a bit with that, learned a lot, and also figured out how to properly use Tedious to take advantage of Azure SQL in my projects.

But using Tedious is...~~tedious~~ verbose. Also the way it manages all asynchronous calls is quite different than the modern async/await pattern.

So I looked for something different and more modern and I found the node-mssql package that does exactly what I needed.

Taking advantage of Azure SQL is really a breeze now. Exposing a stored procedure as a REST endpoint is now as easy as writing something like (the stored procedure returns data a JSON):

const sql = require('mssql')

const AZURE_CONN_STRING = process.env["AzureSQLConnectionString"];

module.exports = async function (context, req) {    
    const pool = await sql.connect(AZURE_CONN_STRING);    

    const busData = await pool.request()
        .input("routeId", sql.Int, parseInt(req.query.rid))
        .input("geofenceId", sql.Int, parseInt(req.query.gid))
        .execute("web.GetMonitoredBusData");        

    context.res = {        
        body: JSON.parse(busData.recordset[0]["locationData"])
    };
}

The above code is literally all you need, for example, if you are using Azure Functions to host your node code.

I prefer to use the async/await pattern when I can as it is so easy to use, but if for some reason you cannot use it, our you prefer the old Promise approach, or even the Callback one, you'll be happy to know that they are supported too.

Definitely recommended.

Warning Note

Make sure that you import the mssql package:

npm install mssql

even if the official name is node-mssql. Unfortunately if you try to import "node-mssql" package you'll end up with something different and not really working :( Keep that in mind, I've lost a good hour just trying to figure out why my code was not working, and then I discovered I imported the wrong package :/

Photo by Jonathan Borba from Pexels