How YOU can build your first Serverless Logic app, part II

Follow me on Twitter, happy to take your suggestions on topics or improvements /Chris

This article is part of #ServerlessSeptember. You'll find other helpful articles, detailed tutorials, and videos in this all-things-Serverless content collection. New articles are published every day — that's right, every day — from community members and cloud advocates in the month of September.

Find out more about how Microsoft Azure enables your Serverless functions at https://docs.microsoft.com/azure/azure-functions.

TLDR; this covers how to connect towards a SQL Server database from Node.js.

This is the second part of a series on how to build a Logic App with an accompanying Serverless API

• Part I scaffolding a database and building the Logic App
• Part II, we are here, we will build a Serverless function that connect to a SQL Server database that we set up in part one

Resources

• Sign up for a free Azure account If you want to build Logic Apps and Azure Functions you will need an Azure account, it's free.
• Logic Apps overview Great docs page that gives an overview of Logic Apps what it is and some links to tutorials
• Logic apps LEARN Great intro module with a lot of concepts.
• Logic apps LEARN II This module takes you through a real example with Twitter, Cognitive Services and SQl Server.
• 5 Modules in total on aka.ms/learn on Logic Apps 5 Modules on Logic Apps that cover "Hello World" to more advanced custom scenarios.
• Connectors in Logic Apps This goes deeper to talk about connectors, what they are, which ones exist
• Create a custom connector Great documentation on how to create a custom connector
• Azure functions in VS Code

 Recap

We learned what a Logic App was, a framework containing a lot of great integrations. The way you code in Logic Apps is by connecting components together and decide things like when to trigger, what actions should be carried out and why.

Not let's continue. We have a SQL Database in which all of our results from the Logic App will be stored.

 Build a Serverless API

Ok then, we have a logic app, awesome :) !

Now we need to build Serverless API that, reads from the database and spits out the database data like a nice JSON.

We need to do the following:

1. Install prerequisites, that means we need Core tools and a VS Code extension
2. Scaffold a function app and a function
3. Add some code that reads from a database and returns the response like a JSON

Install prerequisites

We need first Azure Functions Core tools. Have a look at the headline Installing at this link

Azure Functions Core Tools install

That should provide you with instructions for Linux, Windows and Mac.

Next we need the Azure Functions extension that should look like this:

Scaffold app and Function

For this step, select View/Command Palette.

Select your current directory, JavaScript, HttpTrigger, give your function a suitable name like Mentions, select Anonymous. Under a Mentions directory, you now have an index.js file that we will change.

Add code

You need to add some additional files to our Mentions directory:

• util.js, this file will help us transform a SQL server response to JSON
• query.js, this file will help us connect to our SQL server database
• config.js, this where we define a config object we will use to connect to our database

First, we have query.js

// query.js

const {formatResponse} = require('./util');
var Request = require('tedious').Request;

function execute(query, connection) {
  return new Promise(resolve => {
    executeQuery(query,connection, (data) => {
      resolve(data);
    })
  }); 
}

var executeQuery = (query, connection, cb) => {
  request = new Request(query, (err, rowCount, rows) => {
    if (err) {
      console.log('error', err);
    } else {
      const formatted = formatResponse(rows);
      connection.close();
      cb(formatted);
    }
  });
  connection.execSql(request);
}

module.exports = execute;

Next, we have config.js where we set up all the database connection bits.

// config.js

// Create connection to database
const config = {
  authentication: {
    options: {
      userName: process.env.USER_NAME,
      password: process.env.PASSWORD 
    },
    type: 'default'
  },
  server: process.env.SERVER, 
  options: {
    database: process.env.DATABASE, 
    encrypt: true,
    rowCollectionOnRequestCompletion: true
  }
}

module.exports = config;

Not above how we read from process.env to get config data. Let's explain those:

• USER_NAME, that's the username for your database
• PASSWORD, that's the password
• SERVER, that's the server name, you can find that on your databases overview page
• DATABASE, that's the name of your database

You can either place this data in the app settings for your function app or you can choose to place them in a file in your project called local.settings.json under the property Values. Then when you deploy you can choose to upload this file to AppSettings

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "FUNCTIONS_WORKER_RUNTIME": "node"
  }
}

Thirdly we have util.js where we transform our SQL Server response into something JSON like looking like this:

[{
  "column": "value"
}]

Lastly, we have our index.js file that looks like this:

// index.js

var Connection = require('tedious').Connection;
const execute = require('./query');
const config = require('./config');

async function get() {
  return new Promise(resolve => {
    var connection = new Connection(config);
    connection.on('connect', async function (err) {
        if (err) {
            console.log(err);
        } else {
            const res = await execute("SELECT * FROM Mentions ORDER BY TweetDate desc;", connection);
            resolve(res);
        }
    });
  });  
}

module.exports = async function (context, req) {
    context.log('JavaScript HTTP trigger function processed a request.');
    const res = await get();
    context.res = {
        contentType: 'application/json',
        body: res
    }
};

You will need to open up in the firewall of your SQL Server if you want to test this locally.

Make sure to remove the firewall rule once you are done testing :)

Don't forget to install the NPM library tedious that we need to connect to SQL server:

npm install tedious

Deploy

Deploying is as simple as clicking the Azure icon in the left toolbar. Then scroll to the Functions area.

Thereafter click the blue arrow pointing up and choose your subscription and everything else it prompts for and your function should be in the Cloud in minutes.

 Implement a UI

This is the last step in which we consume our API. We are just going to point to a GitHub repo doing just that. The end result looks like this:

and you can find the code for the frontend here

repo

 Summary

We went through a lot of things. Our focal point was logic apps. As you saw once you set up a database there are a ton of connectors to play with. In some instance you don't even need a database, it really depends on the scenario you are trying to model. We barely scratched the surface of what Logic Apps can do but hopefully, you are able to say I know what they are, their basic concepts and what to use them for. I've left enough resource link for you to add more knowledge and get more practice. So happy coding :)

Comments

[u000fsr]

great post; I was just working on adding tedious to my function app and was struggling to make it async as Microsoft suggests as a best practice. The callbacks in tedious were causing the function to complete before the data came back unless the index.js entrypoint was made synchronous with context.done() called at the end. After finding your post I was able to change it back to async. Thanks!

[Chris Noring]

hi, great to hear that it helped :)

[Matías]

I'm really really new with JS and this was exactly what i needed. Now, I'm having problems with the "util.js" file but for now i'm throwing the raw dataset without "jsonify".

Thanks!!!

[Chris Noring]

are you able to show me code, maybe a repo?