If you are new to Node.js like I am, using Tedious to access Azure SQL can be challenging at the beginning. My understanding is that Tedious, while being fully asynchronous, doesn't support nor Promises nor the more modern async/await pattern. Tedious, in fact, uses events to execute asynchronous code and so a bit of work is needed to make it compatible with Promises.

At the end of the day is just a few lines of code, but the process of discovering those two lines can be quite long and sometime frustrating. There is no clear statement anywhere that shows how to properly do that. I think that's due to the fact that once you get the grasp of Promises it became absolutely obvious....but if you haven't got to that point, you're left in the dark.

Well, let's shed some light then, and fix this hole in the shared knowledge book that is the internet!

Encapsulate usage of Tedious

To avoid writing the same code again and again, you probably want to encapsulate the business logic that executes a SQL command into a function. Something - in theory - like the following

executeSQL = function(query, params) {
  var result = null;

  const conn = new Connection(...);
  conn.on('connect', err => { ... invoke req ... });

  const req = new Request(query, err => { return result });
  req.addParameter(...params...);
  req.on('rows', columns => { ... build result ... });

  conn.connect();  
}

After the connect() method has been successfully called, the connect event will happen. From there, the created Request can be executed. The request will generate a row event that allows you to get the result coming in from Azure SQL, and process and store it into a variable of your choice.

The challenge now is: how do we return the variable with the resultset to the caller?

Writing something like

queryResult = executeSQL(...)

will work but will not produce any result, as the content of executeSQL function will be executed asynchronously. This means that, even if we would add a return result; to the function body, just before it ends, the only thing that will be stored into our queryResult variable is...nothing. If fact, the return statement materially switches execution back to the caller, but at that time the result variable will still be empty as it is very unlikely that Azure SQL would have returned results so quickly. Azure SQL can easily return data in milliseconds, but in this case we're taking about microseconds or less, and just the network roundtrip is more than that.

So what we need to do is to make sure that we start to use the variable result and its contents only when they are available.

That's exactly what a Promise do. Taken from aforementioned and linked documentation: "A promise is commonly defined as a proxy for a value that will eventually become available."

Create the Promise Wrapper

With that clear in our mind, it now become obvious that we need to wrap our reusable method in a Promise, instead of a classic function. The code will then look like the following:

const executeSQL = (query, params) => new Promise(
  (resolve, reject) => { 

  ...function body...
}

That's easy right? There are two callbacks that we now have access to

• resolve: to do something when the everything worked as expected and result is ready to be processed by the caller
• reject: when something didn't work and result is not available.

Given that we know that the Request object also supports a callback to allow us to execute some code once the resultset has been fully sent by Azure SQL, the original code will now look like this:

const req = new Request(query, (err) => {
        if (err) {
            reject(err);
        } else {
            resolve(result);
        }       
    });    

We know we can safely use the result variable here, which was populated inside the row event handler, as Tedious doc assure us that: "The callback is called when the request has completed, either successfully or with an error. If an error occurs during execution of the statement(s), then err will describe the error."

Use the Promise, Luke!

Now that we have our Promise, let's use it.

Somewhere in our code we want to call the executeSQL function and get the result into a variable of our choice. Instead of writing something like,

const result = executeSQL(query, params);

we need to do something like this instead:

executeSQL(query, params)
  .then(ok => {
     ...process result here...
        })
  .catch(err => {
     ...handle errors here...
        });

In the then code block you can use the result, now available in the ok variable.

That's, done! All will now work smoothly and nicely.

Conclusion

I really hope this article helps to clearly resolve this challenge once and for all. I would have loved to have such article right away, it would have saved hours and hours in my life. Now I have a quite good understanding on Promises, and - yeah - everything is trivial now, but reaching this point has been a painful voyage. Coding should be joy, not pain! Frustration is ok and also helpful sometimes, but I felt that here was way too much. Well, the table is turned now, this article is here to bring joy back to the game!

Until next time, have fun.

Wait! I Want the Source Code!

Uh yeah, of course, source code! It's here: Todo Backend Implementation with Azure Functions, Node and Azure SQL and more specifically the code described in the article is here.

As an exercise I took the chance to implement a fully working back-end API for the Todo MVC app, following the Todo Backend API specifications. Love how everything amazingly works well together like a couple of LEGO bricks.

Photo by Castorly Stock from Pexels