MongoDB and SQL Server: Communicating via REST Service

Now that SQL Server can understand and shred JSON data, SQL Server and MongoDB can easily cohabit and pass data between them.

Although you can get a good ODBC connection to MongoDB, the REST interface is easier to manage and prevents development work having to be close-coupled (where the two databases are so closely linked that changes and releases have to be done in sync).

MongoDB has uses even where relational systems are required to manage the transactional side. It is particularly useful for offloading the less structured data that requires less analysis and aggregation, and for assimilating data quickly.

We are going to set up a simple REST service and query it from SQL Server. For the sake of the demonstration, we will query our sample database of Marine Mammals that I introduced in my article, MongoDB, A Database with Porpoise. It is a taxonomy of marine mammals, part of the WORMS (World Register of Marine Species) database.

We want to do the following in a SQL Server stored procedure:

Provide a simple string to search for, such as ‘manatee’ or ‘walrus’
Get a list of all the species, and alternatively, give part of the scientific (latin) name
End up with a tabular result in SQL Server

I’m not pretending that this information is always useful, but it is easy to substitute your real information.

Setting things up

First you’ll need to set up the database from the article, MongoDB, A Database with Porpoise. I’ll assume that even the mention of Node.js grips you with slight feelings of dread, so I’ll keep things simple.

Using chocolatey to download Node.js

If you haven’t got Node.js, install it. I’ll be using Windows for the REST interface because this is usually where SQL Server lurks, so I suggest that you use Chocolatey to do this. If you do as much as you can of your Windows installation work in Chocolatey, it is easy to keep everything updated.

If you haven’t got Chocolatey, you will need to install this into PowerShell first.

choco install nodejs.install -y

(See here for an alternative installation and also some basic checks.)

Using NMP to create and install packages

The first step for any Node project is to create a directory for your project. I’ve chosen C:\Projects\Restful\MarineMammals

You use NMP to set things up. Firstly, it’s best to create a package.json file that will be used by NPM to list the packages your project depends on, and makes your build easier to reproduce and share.

This is done easily at the command prompt.

First, navigate the command shell to the project directory.

C:\Projects\Restful\MarineMammals>npm init
This utility will walk you through creating a package.json file.
It only covers the most common items, and tries to guess sensible defaults.
 
See `npm help json` for definitive documentation on these fields
and exactly what they do.
 
Use `npm install ` afterwards to install a package and
save it as a dependency in the package.json file.
 
Press ^C at any time to quit.
package name: (marinemammals)

…and so on, filling in all the parameters.

Now we need to install our packages.

C:\Projects\Restful\MarineMammals>npm install express --save
npm notice created a lockfile as package-lock.json. You should commit this file.
+ express@4.16.4
added 48 packages in 6.461s
 
C:\Projects\Restful\MarineMammals>npm install mongodb  --save
+ mongodb@3.1.9
added 9 packages in 4.2s
 
C:\Projects\Restful\MarineMammals>

The –save parameter saves the dependency information to the project file you’ve just created.

Using npm-mongodb

We are now ready to save our node application.

I’ve used npm-mongodb rather than mongoose as it is a bit simpler and for this application we don’t need mongoose’s special features.

We will have just two functions: The first gets the common names and scientific names that match your string to the common name(e.g. ‘whale’, and the other that does the same thing for the scientific name (e.g. ‘inia’).

In the latter case, you would get this returned:

{ 
    "scientificName" : "Inia boliviensis", 
    "family" : "Iniidae", 
    "commonName" : "Bolivian Inia"
}

We will keep the actual connection details in a separate file so that we can keep any credentials out of the source code.

const express = require('express'); // minimalist web framework for Node.js
//var path = require('path');
const app = express();
const MongoClient = require('mongodb').MongoClient;
var env = process.env.NODE_ENV || 'production';
var config = require('./config')[env];


// Start server and listen on http://localhost:8081/
var server = app.listen(8081, function() {
  var host = server.address().address
  var port = server.address().port

  console.log("app listening at http://%s:%s", host, port)
});

MongoClient.connect(config.url, {
  useNewUrlParser: true
}, function(err, client) {
  if (err) throw err

  app.get('/', function(req, res) {
    res.json({
      "message": "Welcome to Marine Mammals Sample App.",
      "Find Common Name": "curl -X GET http://:8081/CommonName/walrus",
      "Find Scientific Name": "curl -X GET http://:8081/ScientificName/trichechus"
    })
  });
  app.get('/CommonName/:name/', function(req, res) {

    var db = client.db('MarineMammals')

    db.collection("validSpecies").find({

      commonName: {
        $regex: '\\N*' + req.params.name + '\\N*',
        $options: "si"
      }
    }).project({
      "commonName": 1,
      "scientificName": 1,
      "family": 1,
      "_id": 0
    }).sort({
      "commonName": 1.0
    }).toArray(function(err, result) {
      if (err) throw err
      res.setHeader("Content-Type", "application/json");
      res.json({
        result
      });
    })

  });

  app.get('/ScientificName/:name/', function(req, res) {

    var db = client.db('MarineMammals')

    db.collection("validSpecies").find({

      scientificName: {
        $regex: '\\N*' + req.params.name + '\\N*',
        $options: "si"
      }
    }).project({
      "scientificName": 1,
      "commonName": 1,
      "family": 1,
      "_id": 0
    }).sort({
      "scientificName": 1.0
    }).toArray(function(err, result) {
      if (err) throw err
      res.setHeader("Content-Type", "application/json");
      res.json({
        result
      });
    })
  })
});

The credentials are in this format. On config.js:

var config = {
//'mongodb://localhost:27017/MyDatabase'
//'mongodb:// MyID:MyPassword@MyServer:27017/?authMechanism=SCRAM-SHA-1&authSource=myprojectdb';
//'mongodb:// MyID:MyPassword@MyServer:27017/?authMechanism=MONGODB-CR&authSource=myprojectdb';
//'mongodb://MyID:MyPassword@MyServer:27017?authMechanism=MONGODB-X509&ssl=true`;

  development: {
    //url to connect to MongoDB in development
    url: 'mongodb://localhost:27017/MarineMammals'
  },
  production: {
    //url to to MongoDB in production
    url: 'mongodb://MyID:MyPassword@MyServer:27017/admin?readPreference=primary?authMechanism=MONGODB-X509&ssl=true'
  }

};
module.exports = config;

Running the REST service

Now we have everything we need for the service.

If we want to run this on a Windows server, then we need to create a firewall rule to allow this service to work.

You can do this manually, but here is a PowerShell script that does it.

$port=8081
$RuleName='NodeJSMarineMammals'
$RuleDescription='Allow Remote Connections'
 
$success = @(); #have we a rule in place already?
$existingFirewallRule = Get-NetFirewallRule -DisplayName $RuleName -ErrorAction SilentlyContinue -ErrorVariable success
if ($success.Count -gt 0)
{
<# Cut a hole in the firewall for the designated port #>
$existingFirewallRule= New-NetFirewallRule <#now allow it through the firewall #> `
						-DisplayName $RuleName `
						-Description $RuleDescription `
						-Direction Inbound `
						-Protocol TCP `
						-LocalPort $port `
						-Action Allow
	
}
else
{
	if (($existingFirewallRule | Get-NetFirewallPortFilter).LocalPort -ne $Port)
	{ set-NetFirewallRule -DisplayName $RuleName -LocalPort $Port }
}

We now check that it is working.

You can test it from a browser:

Check if the PowerShell script is working and test it from a browser

…or by using curl from the command line:

C:\>curl -X GET http://MyServer:8081/scientificName/inia
{"result":[{"scientificName":"Inia","family":"Iniidae","commonName":""},{"scientificName":"Inia araguaiaensis","family":"Iniidae","commonName":""},{"scientificName":"Inia boliviensis","family":"Iniidae","commonName":"Bolivian Inia"},{"scientificName":"Inia geoffrensis","family":"Iniidae","commonName":"Geoffroy's river dolphin"},{"scientificName":"Inia geoffrensis boliviensis","family":"Iniidae","commonName":"Bolivian bufeo "},{"scientificName":"Inia geoffrensis geoffrensis","family":"Iniidae","commonName":"common boto "},{"scientificName":"Inia geoffrensis humboldtiana","family":"Iniidae","commonName":"Humboldt boto"}]}
C:\>curl -X GET http://MyServer:8081/commonName/walrus
{"result":[{"scientificName":"Odobenus rosmarus rosmarus","family":"Odobenidae","commonName":"Atlantic walrus"},{"scientificName":"Odobenus rosmarus divergens","family":"Odobenidae","commonName":"Pacific walrus"},{"scientificName":"Odobenus","family":"Odobenidae","commonName":"Walrus"},{"scientificName":"Odobenidae","family":"Odobenidae","commonName":"Walrus family"}]}

Now we can go to SQL Server. You’ll need SQL Server 2016 upwards for its JSON handling. We will need to use the ‘Ole Automation Procedures’ which are switched off by default to make it more difficult for a SQL Injection attacker to dispatch the payload. You need to switch it on:

IF NOT EXISTS (SELECT * FROM sys.configurations WHERE name ='Ole Automation Procedures' AND value=1)
  	BEGIN
     EXECUTE sp_configure 'Ole Automation Procedures', 1;
     RECONFIGURE;  
     End

Then you will need a procedure to execute REST services on SQL Server:

GO
  IF Object_Id('dbo.GetWebService','P') IS NOT NULL 
  	DROP procedure dbo.GetWebService
  GO
  CREATE PROCEDURE dbo.GetWebService
    @TheURL VARCHAR(255),-- the url of the web service
    @TheResponse NVARCHAR(4000) OUTPUT --the resulting JSON
  AS
    BEGIN
      DECLARE @obj INT, @hr INT, @status INT, @message VARCHAR(255);
      /**
  Summary: >
    This is intended for using web services that 
    utilize JavaScript Object Notation (JSON). You pass it the link to
    a webservice and it returns the JSON string
  Note: >
    OLE Automation objects can be used within a Transact-SQL batch, but 
    SQL Server blocks access to OLE Automation stored procedures because
    this component is turned off as part of the security configuration.
   
  Author: PhilFactor
  Date: 26/10/2017
  Database: PhilFactor
  Examples:
     - >
     DECLARE @response NVARCHAR(MAX) 
     EXECUTE dbo.GetWebService 'http://headers.jsontest.com/', @response OUTPUT
     SELECT  @response 
  Returns: >
    nothing
  **/
      EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT;
      SET @message = 'sp_OAMethod Open failed';
      IF @hr = 0 EXEC @hr = sp_OAMethod @obj, 'open', NULL, 'GET', @TheURL, false;
      SET @message = 'sp_OAMethod setRequestHeader failed';
      IF @hr = 0
        EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type',
          'application/x-www-form-urlencoded';
      SET @message = 'sp_OAMethod Send failed';
      IF @hr = 0 EXEC @hr = sp_OAMethod @obj, send, NULL, '';
      SET @message = 'sp_OAMethod read status failed';
      IF @hr = 0 EXEC @hr = sp_OAGetProperty @obj, 'status', @status OUT;
      IF @status <> 200 BEGIN
                          SELECT @message = 'sp_OAMethod http status ' + Str(@status), @hr = -1;
        END;
      SET @message = 'sp_OAMethod read response failed';
      IF @hr = 0
        BEGIN
          EXEC @hr = sp_OAGetProperty @obj, 'responseText', @Theresponse OUT;
          END;
      EXEC sp_OADestroy @obj;
      IF @hr <> 0 RAISERROR(@message, 16, 1);
      END;
  GO

Now you have this, you are ready to go.

Set ‘ourServer’ to the server you have set up the service and execute this:

DECLARE @response NVARCHAR(MAX) 
  EXECUTE dbo.GetWebService 'http://MyServer:8081/commonName/manatee/', @response OUTPUT

  SELECT * FROM OpenJson(@response,'$.result')
  WITH(scientificName NVARCHAR(80),commonName NVARCHAR(80),family NVARCHAR(80))

Results showing entries with commonName = manatee

Or this:

DECLARE @response NVARCHAR(MAX) 
  EXECUTE dbo.GetWebService 'http://MyServer:8081/scientificName/Lagenorhynchus/', @response OUTPUT

  SELECT * FROM OpenJson(@response,'$.result')
  WITH(scientificName NVARCHAR(80),commonName NVARCHAR(80),family NVARCHAR(80))

Results showing entries with scientific name = Lagenorhynchus

Conclusions

You can do mighty things with Node.js and MongoDB, and a simple REST interface can be extended to give a wide range of features for controlling a database (see loopback, for example).

The MEAN stack is becoming quite mature. However, this article just illustrates how to get started, showing how you can use it to provide a simple service for SQL Server or anything that can make http requests.

It can easily be extended to POST, PUT, and DELETE. I’ve already shown in Setting up a simple Rest interface with SQL Server how one can go the other way round and access a REST interface provided by SQL Server.

There are a lot of possibilities from databases such as SQL Server and MongoDB communicating via REST. The pain is all in getting things up and running, but the task of extending a working system is much easier.

Got time for another MongoDB tutorial? Here are a few reader favorites:

– How to Create MongoDB Joins Using SQL
– How to Set Up a MongoDB Service Under Windows via PowerShell
– How to Develop an Effective MongoDB Indexing Strategy