Setting up a simple Rest interface with SQL Server

We’re going to set up a web service for a SQL Server database using node js on a windows server. This is intended for a mobile application, but has a variety of other uses where an ODBC connection isn’t possible. This service is purely done as a demonstration for people with a database background, so we’ll get it to l connect with a SQL Server and provide data from AdventureWorks as an illustration. We’ll just use stored procedures and simple SQL query, just sufficient to show how it is done. For more elaborate connections using prepared statements, batches and table-valued parameters, see the documentation on node-mssql. This article is all about the big picture. We won’t go into how to turn the rest interface into a service: Maybe a separate article.

You can set up node js on a local machine and access it locally for development work but fairly soon, you’ll want to set it up on a Server. Whichever way you do it, you’ll need to set up node.js if you haven’t done so yet, you’ll need to install it. On Windows, I reckon that this is best done with Chocolatey, so if you haven’t got Chocolatey, you will need to install this into PowerShell first.

running PowerShell as administrator, run this.

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

Then set up a directory for your project. I’ve chosen C:\Projects\Restful

Navigate to that directory with the Command Prompt  (running as Administrator!). You will now use npm, (npm is the package manager for JavaScript) which comes with the Node.JS install, in order to install the dependencies. Node JS comes with a big box of Lego for assembling any part of a web server that you need. This includes a component that allows you to run a Node-based application as a service. We now grab the two components we need. In Command prompt (as administrator) …

We now write the JavaScript app we need and save it in the directory.

Normally, you’ll break this into component scripts, particularly the authentication stuff. However, in the interests of getting up and running, we’ll just use a single file. The user has been created with just the required access.

with this in place, it is now simply a matter of running it. This is simplest done from the command prompt by navigating to the project and running the file

Through a separate command prompt we can run requests for the data

You can run the service from PowerShell. Here, I’m specifying where the modules live rather than making the project the current directory

This interface is very quick and uses connection pooling. I haven’t introduced async or promises but hopefully there is something to build on.

in PowerShell, you can easily query the service

I show you here in  ‘Importing JSON Data from Web Services and Applications into SQL Server‘ how you can query a rest service from SQL Server

While you are developing things, you’ll be continually stopping and restarting the Node JS application and staring disconsolately at various error messages, either at the source or displayed at the destination.

All this is best done on a local machine.

With this sort of work, I usually restrict the interface to stored procedures. You can easily get input and output parameters working, and work with multiple recordsets. You can read the messages separately, as well as the result code. The website people just make the requests and get the JSON back in a format that they’re happy with, especially if they are doing Single Page Applications. They are entirely protected from any ‘database stuff’. It just looks like a simple service to them.

Once you have a system up and running, it is time to put it on a server for more comprehensive tests. If it is a Windows machine, it will all be familiar. There are plenty of instructions on the internet for putting it on a Linux machine. On a Windows machine, you must, of course, knock a hole through the firewall to allow remote access to your port.

There are plenty of additional ways of increasing security for the server when your application moves towards production.

For a more detailed sample using Express and mssql, see the Todo List Sample Project by Jovan Popovic

Conclusion

NodeJS with mssql and express is a pretty mature combination with a lot of potential. I wish that the instructions for getting REST APIs working were less terse! Now that SQL Server is more capable with JSON, it opens up a lot of opportunities. One can, for example, deal with JSON parameters to stored procedures and return to the caller complex JSON structures such as JSON Schemas. This could easily reduce the mismatch between the JavaScript and the relational way of handling data, and place less burden on developers to turn their data into a tabular format and receive them in tabular format.