Javascript / Typescript (and thus Node) are, among Full-Stack and Back-End developers, one of the most common choices to create scalable, elegant, high-performance, REST API to be used by the most diverse clients.

Azure Functions are another pretty popular solution that developers use to create scalable solution without having to deal with all the infrastructural woes, as it just allow you to code you own function, deploy it and….done! No IIS or Apache to configure and monitor, no headaches to setup, configure and maintain a load-balanced cluster….just the sheer joy of coding!

As a developer I'd love to be able to use Node and Azure Functions with the database of my choice which, as you can already guess, it Azure SQL. I have already explained extensively (here and here, for example) why I think Azure SQL is just the perfect database for the cloud, so I decided to try to create a REST endpoint just using Azure Functions, Azure SQL and Node.

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!

Recipe for KISS

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 do 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.

With Azure SQL is possible too and, on top of that, you'll get all the additional goodness that Azure SQL offers.

Azure Function and Tedious

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

func init --worker-runtime node --language javascript

in an empty folder that you'll use as Azure Function working folder. Then add a new function

func new --template HttpTrigger --name customer

Everything is now ready to be used, and you can start Azure Function runtime locally just by running

func start

Azure Function will start to listen on localhost, and you can already start to use the created REST endpoint by issuing a GET request, for example, at

http://localhost:7071/api/customer

using your a REST client like cUrl or Insomnia (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 in the create Azure Function folder, where you can see also the file host.json)

npm install tedious    

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; it will be enough to say that you just have to follow to usual pattern:

• Connect to Azure SQL via the Connection object
• Execute a command, via the Request object
• Consume the resultset

Hey, it doesn't work!

Now, there is only one problem here. If you try to use Tedious with the default Azure Function template, which look like this:

module.exports = async function (context, req)
{
    /*
     * Business Logic here
     */
    responseMessage = { customerId: 123, name: "Davide" }

    context.res = {       
        body: responseMessage
    };
}

it won't work. Even worse, it will work without any error, but you'll never get a result.

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 async keyword from the function definition and then make sure the HTTP response is properly sent back by setting it in the context object, provided by Azure Function runtime. Once the HTTP response is ready to be sent, a call to context.done() is needed to inform Azure Function that work is done. Everything is explained here:

Azure Functions JavaScript developer guide: context.done method

Here's the pseudo-code needed to make it work (GitHub repo with fully working code is also available, just keep reading)

define Connection object
define Request object
  if Request fails 
    set HTTP status to 500
  once Request is done
    put Request result into context.res
  call context.done()
open Connection
  if Connection fails 
    set HTTP status to 500
    call context.done()
  else
    execute Request

Once you know that, is pretty easy.

Ok, enough with columns and rows

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:

SELECT 
    [CustomerID] AS 'Id', 
    [CustomerName] AS 'Name'
FROM 
    [Sales].[Customers] 
FOR JSON PATH

and the result would be something like:

[
    {
        "Id": 832,
        "Name": "Aakriti Byrraju"
    },
    {
        "Id": 836,
        "Name": "Abel Spirlea"
    },
    {...}
]

This is great for receiving JSON data from Azure SQL. But what about sending 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:

{
    "CustomerName": "John Doe",
    "PhoneNumber": "123-234-5678",
    "FaxNumber": "123-234-5678",
    "WebsiteURL": "http://www.something.com",
    "Delivery": {
        "AddressLine1": "One Microsoft Way",
        "PostalCode": 98052
    }
}

then this is the T-SQL code you can use to turn JSON into a table that it can be easily inserted into existing structures:

SELECT 
    * 
FROM
    OPENJSON(@Json) WITH
    (       
        [CustomerName] NVARCHAR(100), 
        [PhoneNumber] NVARCHAR(20), 
        [FaxNumber] NVARCHAR(20), 
        [WebsiteURL] NVARCHAR(256),
        [DeliveryAddressLine1] NVARCHAR(60) '$.Delivery.AddressLine1',
        [DeliveryAddressLine2] NVARCHAR(60) '$.Delivery.AddressLine2',
        [DeliveryPostalCode] NVARCHAR(10) '$.Delivery.PostalCode'   
    )

That's it!

This way we can use the most convenient or correct structures for each language and tool we are using. JSON for Javascript and Tables for SQL.

If you want, of course, you could also store JSON also as-is, 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. 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’s benchmark some options!

Conclusion

Using Node with Azure SQL has never been easier thanks to JSON acting as a trait d'union between the two different worlds. It finally seems that the days of impedance mismatch are finally gone. So it's now time to fully take advantage of two truly amazing technologies like Node and Azure SQL.

With Azure SQL you can have Row-Level Security, Change Tracking, Encryption, Columnstore, Lock-Free tables, and much more....all usable via JSON integration.

Give it a test drive yourself, by deploying on Azure the code available here:

Creating a REST API with Azure Functions, Node and Azure SQL GitHub Code Repo

Have fun!