Thursday, January 12, 2017

Node.js 7.x async/await example with express and mssql

Async/await has finally found its home in node 7.x and it’s just great, we’ve been waiting for ages. Not only it works great, with Visual Studio Code you can write and debug async/await like any other code. Just make sure you have runtimeArgs in your launch.json as a temporary fix until it’s not required somewhere in future:

{
    // Use IntelliSense to learn about possible Node.js debug attributes.
    // Hover to view descriptions of existing attributes.
    // For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387
    "version": "0.2.0",
    "configurations": [
        {
            "type": "node",
            "request": "launch",
            "name": "Launch Program",
            "program": "${workspaceRoot}/app.js",
            "cwd": "${workspaceRoot}",
            "runtimeArgs": [
                "--harmony"
            ]
        },
        {
            "type": "node",
            "request": "attach",
            "name": "Attach to Process",
            "port": 5858
        }
    ]
}

Async/await could possibly sound like a ephemeral alien until you realize that a lot of good code has already been written with Promises async/await is a sugar over. In an example below I read the data from a simple Sql Server table using node’s mssql module:

CREATE TABLE [dbo].[Parent](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [ParentName] [nvarchar](150) NOT NULL,
 CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)
) ON [PRIMARY]

I also need a connection string somewhere

// settings.js
module.exports.connectionString = 'server=.\\sql2012;database=databasename;user id=user;password=password';

Note that since tedious doesn't support integrated auth at the moment, you are stuck with sql's username/pwd authentication. The mssql on the other hand uses tedious internally.

Here goes an example of the "old-style" code, written with promises  
   var conn = new sql.Connection(settings.connectionString);
   conn.connect()
     .then( () => {

         var request = new sql.Request(conn);

         request.query('select * from Parent')
              .then( recordset => {

                  recordset.forEach( r => {
     // do something with single record
                  });

    conn.close();
              })
              .catch( err => {
                  console.log( err );
              });
     })
     .catch( err => {
         console.log(err);
     });  
(note that you could possibly avoid nesting thens by just returing the request.query promise so that the next, chained then would refer to it.) However, with async/await the same becomes
try {
 var conn = new sql.Connection(settings.connectionString);
 await conn.connect();
 
 var request = new sql.Request(conn);
 var recordset = await request.query('select * from Parent') 
 
 recordset.forEach( r => {
  // do something with single record
 });
 
 conn.close();
}
catch ( err ) {
 console.log( err );
}
Pretty impressive, if you ask me. If you don't mind occasional awaits, the code is clean, no .thens, no .catches. Remember that express middlewares can be async too
var app = express();

app.get('/', async (req, res) => {

      // code that awaits

});
All this means the callback hell is hopefully gone forever.