I’m quite new to Node so I thought it could be helpful for anyone in my situation to share what I have learned. Here we go!<\/p>\n
Recipe for KISS<\/h2>\n
I really wanted to follow the KISS principle, so I decided to avoid any additional library and just go with Tedious, the most widely used Node package for SQL Server \/ Azure SQL. I also wanted to everything in the most javascript-friendly way possible, which means manipulating objects and JSON instead of SQL statements or anything that would remind me that behind the scene I have a (post-)relational database. From this point of view, NoSQL databases usually offer a great user experience as everything is already JSON, and so there is basically nothing special to do to manipulate data. You send JSON, you get JSON, as simple as that<\/em>. With Azure SQL is possible too and, on top of that, you’ll get all the additional goodness that Azure SQL offers.<\/p>\n First of all, you need to create an Azure Function and this can be easily done using the Azure Function CLI. Make sure you have it installed and than simply run<\/p>\n in an empty folder that you’ll use as Azure Function working folder. Then add a new function<\/p>\n Everything is now already ready to be used, and you can start Azure Function runtime locally just by running<\/p>\n Azure Function will start to listen on localhost<\/em>, and you can already start to use the created REST endpoint by issuing a GET request, for example, at<\/p>\n using your a REST client like cUrl or Insomnia<\/a> (or your favorite one). The REST endpoint doesn’t do much at the moment, and you still need to connect it to Azure SQL. To do that you have to install the Tedious package (make sure you are\u00a0in<\/em> the created Azure Function folder, where you can see also the file host.json<\/em>)<\/p>\n and you’re good to go. You can now open your favorite editor (Visual Studio or Visual Studio Code in my case) and start to create a REST endpoint that will handle HTTP request to implement CRUD operation for our sample Customer endpoint. I won’t enter into detail of how to use Tedious as it is pretty simple, and also it has quite good documentation on its website<\/a>; it will be enough to say that you just have to follow to usual pattern:<\/p>\n Now, there is only one problem here. If you try to use Tedious with the default Azure Function template, which look like this:<\/p>\n it won’t work. Even worse, it will work without any error, but you’ll never get a result<\/strong>.<\/p>\n The reason, as per my (limited, as I’m pretty new to this) understanding, is that Tedious doesn’t support the async\/await pattern which is instead used by default by Azure Functions. The solution is to remove the Azure Functions JavaScript developer guide: context.done method<\/a><\/p>\n Here’s the pseudo-code needed to make it work (GitHub repo with fully working code is also available, just keep reading)<\/p>\n Once you know that, is pretty easy.<\/p>\n As written at the beginning, as a developer I feel much more comfortable manipulating a JSON object than tables with columns and rows, so I’d love to be able to get JSON from Azure SQL and send back JSON to the database. Thanks to Azure SQL native JSON support, I can do that. For example, to return a JSON array of all customers that I should return as result of an HTTP GET request I can just do this:<\/p>\n and the result would be something like:<\/p>\n This is great for receiving<\/strong> JSON data from Azure SQL! But what about sending<\/strong> a JSON so that it will be then stored into an existing table? Easy as before. Let’s say this is the JSON you’ll be sending to Azure SQL:<\/p>\n then this is the T-SQL code that you can use to turn JSON into a table that it can be easily inserted into existing structures:<\/p>\n That’s it!<\/p>\n This way we can use the most convenient or correct structure for each language and tool we are using. JSON for Javascript and Tables for SQL.<\/em><\/p>\n If you want, of course, you could also store JSON also as-is<\/strong>, without the need to turn it into a table, but that will be less efficient. Depending what you want and need to do, choose what’s the best for you. In case you decide to store JSON as-is, remember that you can also create indexes on JSON documents<\/a>. If you are unsure which strategy would be the most suitable for your scenario – JSON or proper tables – this article can surely help you decide: JSON in your Azure SQL Database? Let\u2019s benchmark some options!<\/a><\/p>\n Using Node with Azure SQL has never been easier thanks to JSON acting as a trait d’union<\/em> between the two different worlds. It finally seems that the days of impedance mismatch<\/em> are finally gone. So it’s now time to fully take advantage of two truly amazing technologies like Node and Azure SQL.<\/p>\n With Azure SQL you can have Row-Level Security, Change Tracking, Encryption, Columnstore, Lock-Free tables, and much more<\/a>….all usable via JSON integration.<\/p>\n Give it a test drive yourself, by deploying on Azure the code available here:<\/p>\n![\"Image](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/08\/node-sql-json-1024x620.png\")
<\/p>\nAzure Function and Tedious<\/h2>\n
func init --worker-runtime node --language javascript\r\n<\/code><\/pre>\nfunc new --template HttpTrigger --name customer\r\n<\/code><\/pre>\nfunc start\r\n<\/code><\/pre>\nhttp:\/\/localhost:7071\/api\/customer\r\n<\/code><\/pre>\nnpm install tedious \r\n<\/code><\/pre>\n\n
Connection<\/code> object<\/li>\nRequest<\/code> object<\/li>\nEhy, it doesn’t work!<\/h2>\n
module.exports = async function (context, req)\r\n{\r\n \/*\r\n * Business Logic here\r\n *\/\r\n responseMessage = { customerId: 123, name: \"Davide\" }\r\n\r\n context.res = { \r\n body: responseMessage\r\n };\r\n}\r\n<\/code><\/pre>\nasync<\/code> keyword from the function definition and then make sure the HTTP response is properly sent back by setting it in the context<\/code> object, provided by Azure Function runtime. Once the HTTP response is ready to be sent, a call to context.done()<\/code> is needed to inform Azure Function that work is done. Everything is explained here:<\/p>\ndefine Connection object\r\ndefine Request object\r\n if Request fails \r\n set HTTP status to 500\r\n once Request is done\r\n put Request result into context.res\r\n call context.done()\r\nopen Connection\r\n if Connection fails \r\n set HTTP status to 500\r\n call context.done()\r\n else\r\n execute Request\r\n<\/code><\/pre>\nOk, enough with columns and rows<\/h2>\n
SELECT \r\n [CustomerID] AS 'Id', \r\n [CustomerName] AS 'Name'\r\nFROM \r\n [Sales].[Customers] \r\nFOR JSON PATH\r\n<\/code><\/pre>\n[\r\n {\r\n \"Id\": 832,\r\n \"Name\": \"Aakriti Byrraju\"\r\n },\r\n {\r\n \"Id\": 836,\r\n \"Name\": \"Abel Spirlea\"\r\n },\r\n {...}\r\n]\r\n<\/code><\/pre>\n{\r\n \"CustomerName\": \"John Doe\",\r\n \"PhoneNumber\": \"123-234-5678\",\r\n \"FaxNumber\": \"123-234-5678\",\r\n \"WebsiteURL\": \"http:\/\/www.something.com\",\r\n \"Delivery\": {\r\n \"AddressLine1\": \"One Microsoft Way\",\r\n \"PostalCode\": 98052\r\n }\r\n}\r\n<\/code><\/pre>\nSELECT \r\n * \r\nFROM\r\n OPENJSON(@Json) WITH\r\n ( \r\n [CustomerName] NVARCHAR(100), \r\n [PhoneNumber] NVARCHAR(20), \r\n [FaxNumber] NVARCHAR(20), \r\n [WebsiteURL] NVARCHAR(256),\r\n [DeliveryAddressLine1] NVARCHAR(60) '$.Delivery.AddressLine1',\r\n [DeliveryAddressLine2] NVARCHAR(60) '$.Delivery.AddressLine2',\r\n [DeliveryPostalCode] NVARCHAR(10) '$.Delivery.PostalCode' \r\n )\r\n<\/code><\/pre>\nConclusion<\/h2>\n