Need Support?

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)

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’).

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:

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.

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:

Was this article helpful?

About The Author

Phil Factor

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.Visit https://studio3t.com/knowledge-base/author/phil_factor/ for the complete list of Phil Factor's MongoDB tutorials.