Serverless REST API with Azure Functions, Node, JSON and Azure SQL
Tags: node, sql, azure, beginners
Published: 2020-09-08 17:00:15

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][1] and [here][2], 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](https://en.wikipedia.org/wiki/KISS_principle), so I decided to avoid any additional library and just go with [Tedious][4], 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](https://github.com/Azure/azure-functions-core-tools). Make sure you have it installed and than simply run

```bash
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

```bash
func new --template HttpTrigger --name customer
```

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

```bash
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][3] (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*)

```bash
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][4]; 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:

```javascript
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][5]

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:

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

and the result would be something like:

```json
[
    {
        "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:

```json
{
    "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:

```sql
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][6]. 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!][7]

# 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][1]....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][8]

Have fun!

 [1]: https://devblogs.microsoft.com/azure-sql/10-reasons-to-use-azure-sql-in-your-next-project/
 [2]: https://devblogs.microsoft.com/azure-sql/architecting-rest-api-with-python-flask-and-azure-sql/
 [3]: https://insomnia.rest/
 [4]: http://tediousjs.github.io/tedious/
 [5]: https://docs.microsoft.com/azure/azure-functions/functions-reference-node#contextdone-method
 [6]: https://docs.microsoft.com/en-us/sql/relational-databases/json/index-json-data
 [7]: https://devblogs.microsoft.com/azure-sql/json-in-your-azure-sql-database-lets-benchmark-some-options/
 [8]: https://github.com/Azure-Samples/azure-sql-db-node-rest-api