通过

Connecting to an external Database with Node.js backend in Azure Mobile Services

  • 项目

Azure Mobile Services has recently released the new Hybrid Connections feature that provides an easy and effective way to connect to on-premises resources from your mobile service, available for both Node.js and .NET backends.

One of the scenarios enabled by Hybrid Connections is utilizing a database hosted outside of Microsoft Azure from your Mobile Service and we have a step-by-step tutorial on how to set it up for apps that use the .NET backend. This post will help you get started with a Node.js backend.

All initial setup steps are the same as for the .NET backend, so please go ahead to that tutorial and go through steps 1 – 4:

  1. Prerequisites (you don’t need to install Visual Studio, though)
  2. Install SQL Server Express, enable TCP/IP, and create a SQL Server database on-premises
  3. Create a Hybrid Connection
  4. Install the on-premises Hybrid Connection Manager to complete the connection

Some Node.js sample code to get you started

Now that you’ve completed all setup steps, you’re ready to start writing some Node.js code that will connect to your on-premises resources. For example, this is a custom API script that will connect to an on-premises database.

var sql = require('sqlserver');

exports.get = function (request, response) {
    var onPremConnString = request.service.config.appSettings.onPremisesDatabase;
    /* Setup your connection string as an App-Setting through the Azure Portal.
       Example setting you should configure in the Portal:
         Setting Name: onPremisesDatabase
         Value:
           Driver={SQL Server};Server=MyServerName,1433;Uid=sa;Pwd=ThisIsMyPassword;

       Do not replace {SQL Server} with something else. Leave it as shown here.
    */

    console.log('Executing on-prem db test.');

    sql.query(
        onPremConnString,
        'SELECT * FROM dbo.MyTable', /* sample query, replace with something useful */
        function done(error, results) {
            if (error) {
                console.log('Query failed with error: ' + error);
                response.send(statusCodes.INTERNAL_SERVER_ERROR, {
                    result: 'Failed'
                });
            }
            else {
                console.log('Query succeeded, got data: ' + results);
                response.send(statusCodes.OK, {
                    result: 'Success',
                    data: results
                });
            }
        });
};

As mentioned in the code comments, you will also need to specify your on-premises connection string as an app-setting in the Configure tab of your Mobile Service in the Azure Portal.

And that’s it! A few things to note in this example:

  • It assumes that you already have a table called “MyTable” in the default schema “dbo”. You can easily change the query to use an existing table you might have or, if you don’t have a table yet, you can easily create one using SQL Server Management Studio.
  • As the developer, you didn’t have to write any code at all to setup the connection to your on-premises database. We take care of it in the backend for you.
  • The connection string is the same you would have used if you were actually running the Node.js code on-premises (in ODBC format). Thanks to BizTalk Hybrid Connections, you can now reuse that same connection string from the cloud securely and conveniently.
  • This sample code is meant to show how simple it is to use this new feature, and is not an example of production-quality code. When using this in production, consider implementing better error handling and logging.

Wrapping up

We hope this post will help you get started with Hybrid Connections in a Node.js Mobile Service. The main objective was to emphasize how simple it is to set it up and start using it, both from Node.js and .NET backends. We encourage you to play with the new feature and, as usual, to submit questions or comments to this post, the MSDN forums or contact us via Twitter @AzureMobile.

Enjoy!

Comments

  • Anonymous
    June 03, 2014
    Thanks!. this is really a powerful and useful feature for nodejs back end, let me try it out
  • Anonymous
    January 15, 2015
    Hello,Can you provide an example that uses Oracle or MySQL with Azure Websites?  Although it says it is supported I haven't found anything anywhere describing how you might do the same with Oracle or MySQL.  The part that has me confused is what node.js module would you use to connect to Oracle?  Since Azure Websites is running the node instance we can't really install any libraries or other prereqs that an oracle module may need to function (such as module "oracle").
  • Anonymous
    May 13, 2015
    Hello Can i use mongodb instance created straight from mongoLab to connect Azure mobile service custom api?