Connect to Azure SQL DB from a Azure Functions in JS

Question

I am trying to connect to my Azure SQL DB from an azure function written in nodeJS. I have set the connection strings in the application settings and it still does not work. I used ODBC key for connection settings. Did anyone try this? Below is the sample code for my function

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;

// Create connection to database
const config = process.env["sqldb_connection"];
var connection = new Connection(config);

// Attempt to connect and execute queries if connection goes through
connection.on('connect', function(err)
   {
     if (err)
       {
          console.log(err)
       }
    else
       {
           queryDatabase()
       }
   }
 );

function queryDatabase()
   { console.log('Reading rows from the Table...');

       // Read all rows from table
     request = new Request(
          "SELECT ORG_ID,ORG_NAME FROM org",
             function(err, rowCount, rows)
                {
                    console.log(rowCount + ' row(s) returned');
                    process.exit();
                }
            );

     request.on('row', function(columns) {
        columns.forEach(function(column) {
            console.log("%s\t%s", column.metadata.colName, column.value);
         });
             });
     connection.execSql(request);
   }

I am getting an error saying

Exception while executing function: Functions.HttpTriggerJS2. mscorlib: The given key was not present in the dictionary.

Try doing the connection as explained here learn.microsoft.com/en-us/azure/sql-database/… — Alberto Morillo
– Alberto Morillo, Commented Feb 13, 2018 at 19:15
@AlbertoMorillo That does not work with Azure functions, you have to set the connection settings string in Application settings of the function — Ross
– Ross, Commented Feb 13, 2018 at 19:51
Did you add: "package.json { "dependencies": { "tedious": "*" } }" as defined here: github.com/Azure/azure-functions-host/issues/682 — Alexey Rodionov
– Alexey Rodionov, Commented Feb 14, 2018 at 0:32

Ross · Accepted Answer · 2018-02-15 01:07:29Z

3

Run the npm install tedious command in the Kudu remote execution console under D:\home\site\wwwroot.
In Azure Functions, all of your code should be put inside the module.exports function.
You should use context.log() instead of console.log().
You really don't have to set the connection settings string in Application settings of the function.
You should inform the runtime when your code has finished by calling context.done().

The following code works for me:

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;

module.exports = function (context, req) {
    context.log('JavaScript HTTP trigger function processed a request.');

    var config = {
        userName: '<userName>',
        password: '<password>',
        server: '<AzureSQLDBName>.database.windows.net',

        // If you're on Windows Azure, you will need this:
    options:
        {
                database: 'your DB',
                  encrypt: true
        }
};

    var connection = new Connection(config);

    connection.on('connect', function(err) {

        if (err) {
            context.log(err);

            context.res = {
                status: 500,
                body: "Unable to establish a connection."
            };
            context.done();

        } else {
            executeStatement();
        }
    });

    function executeStatement() {

        request = new Request("select 42, 'hello world'", function(err, rowCount) {
            if (err) {
                context.log(err);

                context.res = {
                    status: 500,
                    body: "Failed to connect to execute statement."
                };
                context.done();

            } else {
                context.log(rowCount + ' rows');
            }
        });

        request.on('row', function(columns) {
            columns.forEach(function(column) {
                context.log(column.value);
            });

            context.done();
        });

        connection.execSql(request);
    }
};

For more information, see Azure Functions JavaScript developer guide.

edited Feb 15, 2018 at 1:07

Ross

1172 silver badges12 bronze badges

answered Feb 14, 2018 at 6:37

Aaron Chen

9,9701 gold badge20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ross Over a year ago

Thanks for your valuable inputs, yes I did the changes as you suggested and it works. But when I change the SQL query to read a table from my DB it does not work. For example, i have a table called fruits and a query as follows

var sqlQuery = "SELECT apples,oranges FROM fruits;    request = new Request(sqlQuery, function(err, rowCount, rows) {     ....   }

When i do this i get a error saying RequestError: Invalid object name 'fruits'

T.J. Crowder Over a year ago

@Ross - Did you ever resolve the problem> Sounds like you didn't have the database name in the connection string.

Collectives™ on Stack Overflow

Connect to Azure SQL DB from a Azure Functions in JS

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related