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.

choco install nodejs.install -y

(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. 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 …

C:\Projects\Restful>npm install express

C:\Projects\Restful>npm install mssql

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.

var express = require('express'); // minimalist web framework for Node.js
var app = express();
var sql = require('mssql'); // Microsoft SQL Server client for Node.js


//In addition to configuration object there is an option to pass config
// as a connection string. Two formats of connection string are supported.
// configuration parameters.
var sqlConfig = {
  user: 'AdventureMobile',
  password: ' QAjOiJAtJ52jHvcH',
  server: 'MyServer',
  database: 'Adventureworks2016'
}

// 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)
});

const connection = new sql.ConnectionPool(sqlConfig) 

// if you plan to work with local temporary tables, use batch instead. //
//uses sp_ExecuteSQL
app.get('/adventure/:customerId/', function(req, res) {
  connection.connect().then(pool => { //Using a single connection pool is recommended
    var stringRequest = 'select * from Sales.Customer where customerId = ' + req.params.customerId;
    return pool.request().query(stringRequest)
  }).then(result => {
    let rows = result.recordset
    res.setHeader('Access-Control-Allow-Origin', '*')
    res.status(200).json(rows);
    connection.close();
  }).catch(err => {
    console.log(err);
    res.status(500).send({
      message: "${err}"
    })
    connection.close();
  });
});


app.get('/adventureproc/:customerId/', function(req, res) {
  connection.connect() //get a connection from the pool
    .then(function() {
      const request = new sql.Request(connection); //create a request
      request.input("BusinessEntityID", req.params.customerId);// name, type, value
      request.execute("uspGetEmployeeManagers")//the name of the procedure
        .then(result => {
          let rows = result.recordset //first recordset
          res.setHeader('Access-Control-Allow-Origin', '*')
          res.status(200).json(rows);
          connection.close();
        }).catch(err => {
          console.log(err);
          res.status(500).send({
            message: "${err}"
          })
          connection.close(); //send the connection back to the pool
        });
    })
});

 

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

CD C:\Projects\Restful
C:\Projects\Restful>node server.js
app listening at http://:::8081

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

C:\Users\Phil>curl -X GET http://localhost:8081/adventure/1
[{"CustomerID":1,"PersonID":null,"StoreID":934,"TerritoryID":1,"AccountNumber":"AW00000001",
  "rowguid":"3F5AE95E-B87D-4AED-95B4-C3797AFCB74F","ModifiedDate":"2014-09-12T11:15:07.263Z"}]
C:\Users\Phil>curl -X GET http://localhost:8081/adventureproc/12
[{"RecursionLevel":0,"BusinessEntityID":12,"FirstName":"Thierry","LastName":"D'Hers","Organizat
ionNode":"/1/1/5/1/","ManagerFirstName":"Ovidiu","ManagerLastName":"Cracium"},{"RecursionLevel"
:1,"BusinessEntityID":11,"FirstName":"Ovidiu","LastName":"Cracium","OrganizationNode":"/1/1/5/"
,"ManagerFirstName":"Roberto","ManagerLastName":"Tamburello"},{"RecursionLevel":2,"BusinessEntit
yID":3,"FirstName":"Roberto","LastName":"Tamburello","OrganizationNode":"/1/1/","ManagerFirstNam
e":"Terri","ManagerLastName":"Duffy"}]
C:\Users\Phil>

 

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

$env:NODE_PATH = " C:\Projects\Restful\node_modules"
node C:\Projects\Restful\Server.js

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

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.

$port=8081
$RuleName='NodeJSAdventure'
$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 }
}

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.

 

The post Setting up a simple Rest interface with SQL Server appeared first on Simple Talk.

from Simple Talk https://ift.tt/2Db5YcL
via