{"id":472,"date":"2020-10-04T11:22:08","date_gmt":"2020-10-04T18:22:08","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=472"},"modified":"2020-10-07T09:53:55","modified_gmt":"2020-10-07T16:53:55","slug":"promises-node-tedious-azure-sql-oh-my","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/promises-node-tedious-azure-sql-oh-my\/","title":{"rendered":"Promises, Node, Tedious, Azure SQL. Oh My!"},"content":{"rendered":"

\"Different<\/p>\n

If you are new to Node.js like I am, using\u00a0Tedious<\/a>\u00a0to access Azure SQL can be challenging at the beginning. My understanding is that Tedious, while being fully asynchronous, doesn’t support nor\u00a0Promises<\/a>\nnor the more modern\u00a0async\/await pattern<\/a>. Tedious, in fact, uses\u00a0events<\/em><\/a>\u00a0to execute asynchronous code and so a bit of work is needed to make it compatible with Promises.<\/p>\n

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\u00a0clear<\/em>\u00a0statement 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.<\/p>\n

Well, let’s shed some light then, and fix this hole in the shared knowledge book that is the internet!<\/p>\n

<\/a>Encapsulate usage of Tedious<\/h2>\n

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<\/p>\n

executeSQL = function(query, params) {\r\n  var result = null;\r\n\r\n  const conn = new Connection(...);\r\n  conn.on('connect', err => { ... invoke req ... });\r\n\r\n  const req = new Request(query, err => { return result });\r\n  req.addParameter(...params...);\r\n  req.on('rows', columns => { ... build result ... });\r\n  \r\n  conn.connect();  \r\n}<\/pre>\n
After the\u00a0connect()<\/code>\u00a0method has been successfully called, the\u00a0connect<\/em>\u00a0event will happen. From there, the created\u00a0Request<\/code>\u00a0can be executed. The request will generate a\u00a0row<\/code>\u00a0event that allows you to get the result coming in from Azure SQL, and process and store it into a variable of your choice.<\/p>\n
The challenge now is: how do we return the variable with the resultset to the caller?<\/p>\n
Writing something like<\/p>\n
queryResult = executeSQL(...)<\/pre>\n
will work but will not produce any result, as the content of\u00a0executeSQL<\/code>\u00a0function will be executed asynchronously. This means that, even if we would add a\u00a0return result;<\/code>\u00a0to the function body, just before it ends, the only thing that will be stored into our\u00a0queryResult<\/code>\u00a0variable is…nothing. If fact, the\u00a0return<\/code>\u00a0statement materially switches execution back to the caller, but at that time the\u00a0result<\/code>\u00a0variable 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.<\/p>\n
So what we need to do is to make sure that we start to use the variable\u00a0result<\/code>\u00a0and its contents only when they are available.<\/p>\n
That’s exactly what a Promise do. Taken from aforementioned and linked documentation:\u00a0“A promise is commonly defined as a proxy for a value that will eventually become available.”<\/em><\/p>\n
<\/a>Create the Promise Wrapper<\/h2>\n
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:<\/p>\n
const executeSQL = (query, params) => new Promise(\r\n  (resolve, reject) => { \r\n\r\n  ...function body...\r\n}<\/pre>\n
That’s easy right? There are two callbacks that we now have access to<\/p>\n