This post is my journey building that demo. I was out of my comfort zone, coding in Node and working on a Linux machine, but not that far... Because these days, you can "do some Azure" from anywhere.
The Goal
Coding an Azure Function that will connect to an SQL Database (it could be any data source). Using Node.js and tools available on Unbuntu.
Note: In this post, I will be using Visual Studio Code, but you could also create your function directly in the Azure Portal or from Visual Stusio.
Getting Started
If you are a regular reader of this blog, you know how I like Visual Studio Code. It's a great tool available on Mac Linux and Windows and gives you the opportunity to enjoy all its feature from anywhere feeling like if you were in your cozy and familiar environment. If VSCode is not already installed on your machine, go grap your free version on http://code.visualstudio.com.
Many extensions are available for VSCode, and one gives us the capability to code and deploy Azure Function. To install it, open VSCode and select the extension icon and search for
Azure Function; it's the one with the yellow lighting and the blue angle brackets.
Create the Azure Function
To get started let's great an Azure Function project. By sure to be in the folder where you wish to create your Function App. Open the Command Pallette (Ctrl + Shift + p) and type Azure Function. Select
Azure Functions: Create New Project. That will add some configuration files for the Functions App.Now Let's create a Function. You could reopen again the Command Palette and search for Azure Function: Create Function, but let's use the UI this time. At the bottom left of the Explorer section, you should see a new section called AZURE FUNCTIONS. Click on the little lighting to Create a new Function.
After you specify the Function App name, the Azure subscription and other little essential, a new folder will be added in your folder structure, and the function is created. The code of our function is in the file
Index.js. At the moment, of writing this post only Javascript is supported by the VSCode extension.Open the file index.js and replace all its content by the following code.
var Connection = require('tedious').Connection;
var Request = require('tedious').Request
var TYPES = require('tedious').TYPES;
module.exports = function (context, myTimer) {
var _currentData = {};
var config = {
userName: 'frankadmin',
password: 'MyPassw0rd!',
server: 'clouden5srv.database.windows.net',
options: {encrypt: true, database: 'clouden5db'}
};
var connection = new Connection(config);
connection.on('connect', function(err) {
context.log("Connected");
getPerformance();
});
function getPerformance() {
request = new Request("SELECT 'Best' = MIN(FivekmTime), 'Average' = AVG(FivekmTime) FROM RunnerPerformance;", function(err) {
if (err) {
context.log(err);}
});
request.on('row', function(columns) {
_currentData.Best = columns[0].value;
_currentData.Average = columns[1].value;;
context.log(_currentData);
});
request.on('requestCompleted', function () {
saveStatistic();
});
connection.execSql(request);
}
function saveStatistic() {
request = new Request("UPDATE Statistic SET BestTime=@best, AverageTime=@average;", function(err) {
if (err) {
context.log(err);}
});
request.addParameter('best', TYPES.Int, _currentData.Best);
request.addParameter('average', TYPES.Int, _currentData.Average);
request.on('row', function(columns) {
columns.forEach(function(column) {
if (column.value === null) {
context.log('NULL');
} else {
context.log("Statistic Updated.");
}
});
});
connection.execSql(request);
}
context.done();
};
The code just to demonstrate how to connect to an SQL Database and do not represent the best practices. At the top, we have some declaration the used the package
tedious; I will get back to that later. A that, I've created a connection using the configuration declared just before. Then we hook some function to some event. On connection connect the function getPerformance() is called to fetch the data.On request
row event we grab the data and do the "math", then finally on requestCompleted we call the second sub-function that will update the database with the new value. To get more information and see more example about tedious, check the GitHub repository.Publish to Azure
All the code is ready; it's now time to publish our function to Azure. One more time you could to that by the Command Palette, or the Extension menu. Use the method of your choice and select Deploy to Function App. After a few seconds only our Function will be deployed in Azure.
Navigate to portal.azure.com and get to your Function App. If you try to Run the Function right now, you will get an error because tedious is not recognized.
Install the dependencies
We need to install the dependencies for the Function App, in this case tedious. A very simple way is to create a package.json file and to use the Kudu console ton install it. Create a package.json file with the following json in it:
{
"name": "CloudEn5Minutes",
"version": "1.0.0",
"description": "Connect to Database",
"repository": {
"type": "git",
"url": "git+https://github.com/fboucher/CloudEn5Minutes.git"
},
"author": "",
"license": "ISC",
"dependencies": {
"tedious": "^2.1.1"
}
}
Open the Kudu interface. You can reach it by clicking on the Function App then the tab Platform features and finally Advanced tools (Kudu). Kudu is also available directly by the URL [FunctionAppNAme].scm.azurewebsites.net (ex: https://clouden5minutes.scm.azurewebsites.net ). Select the Debug console CMD. Than in the top section navigate to the folder
home\site\wwwroot. Drag & drop the package.json file. Once the file is uploaded, type the command npm install to download and install all the dependencies declared in our file. Once it all done you should restart the Function App.
Wrapping up & my thoughts
There it is, if you go back now to your Function and try to execute it will work perfectly. It's true that I'm familiar with Azure Function and SQL Database. However, for a first experience using Ubuntu and Node.js in the mix, I was expecting more resistance. One more time VSCode was really useful and everything was done with ease.
For those of you that would like to test this exact function, here the SQL code to generate what will be required for the database side.
CREATE TABLE RunnerPerformance(
Id INT IDENTITY(1,1) PRIMARY KEY,
FivekmTime INT
);
CREATE TABLE Statistic(
Id INT IDENTITY(1,1) PRIMARY KEY,
BestTime INT,
AverageTime INT
);
INSERT Statistic (BestTime, AverageTime) VALUES (1, 1);
DECLARE @cnt INT = 0;
WHILE @cnt < 10
BEGIN
INSERT INTO RunnerPerformance (FivekmTime)
SELECT 9+FLOOR((50-9+1)*RAND(CONVERT(VARBINARY,NEWID())));
SET @cnt = @cnt + 1;
END;
