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.
|
1 |
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 (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 three components we need. In Command prompt (as administrator) …
|
1 2 3 |
C:\Projects\Restful>npm install express C:\Projects\Restful>npm install mssql C:\Projects\Restful>npm install msnodesqlv8 |
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 two files.
The first file is a config file, config.js, that allows you to change the user without changing the source. We’ll specify two different users, one a SQL Server Authentication, and the other one an integrated windows authentication. We’ll pretend that development work can be done withing the domain, but the production server is SQL Server credentials. This will cover most examples.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
/*In addition to being able to use a simple configuration object ... { database: "db_name", server: "server_name", driver: "msnodesqlv8", options: { trustedConnection: true } } ... there is an option to pass config as a connection string. Two formats of connection string are supported. Server=localhost,1433;Database=database;User Id=username;Password=password;Encrypt=true Driver=msnodesqlV8;Server=(local)\INSTANCE;Database=database;UID=DOMAIN\username;PWD=password;Encrypt=true Driver=msnodesqlv8;Server=(local)\INSTANCE;Database=database;UID=DOMAIN\username;PWD=password;Encrypt=true or //mssql://username:password@localhost:1433/database?encrypt=true //mssql://username:password@localhost/INSTANCE/database?encrypt=true&domain=DOMAIN&driver=msnodesql //mssql://username:password@localhost/INSTANCE/database?encrypt=true&domain=DOMAIN&driver=msnodesqlv8 */ var config = { production: { driver: 'msnodesqlv8', connectionString: 'Driver=SQL Server Native Client 11.0;Server=MyServerName;Database=MyDatabase;UID=MyUserName;PWD=MyPassword' }, development: { driver: 'msnodesqlv8', connectionString: 'Driver=SQL Server Native Client 11.0;Server=ServerName;Database=MyDatabase;Trusted_Connection=yes' } }; module.exports = config; |
The user has been created with just the required access. We have specified the development login but if you switch to the other, you just need to change the line var env = process.env.NODE_ENV || 'development'; to var env = process.env.NODE_ENV || 'production';
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
const express = require('express'); // minimalist web framework for Node.js const app = express(); const sql = require('mssql/msnodesqlv8') //mssql with MS driver for SQL Server // added windows authentication var env = process.env.NODE_ENV || 'development'; var sqlConfig = 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) }); const connection = new sql.ConnectionPool(sqlConfig, function(err){ if (err){ console.log(err); } } ) // define a simple route app.get('/', function(req, res) { res.json({"message": "Welcome to Adventureworks Sample App."}); }); // 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 conn=pool.request() var forInteger = /\b\d+\b/i; //make sure that there is only an integer. if (forInteger.test(req.params.customerId)) { //check whether it was an integer conn.input('input_parameter', sql.Int, req.params.customerId)} else {conn.input('input_parameter', sql.Int, 1)} //otherwise just pass a 1 var string = 'select * from Sales.Customer where customerId = @input_parameter' return conn.query(string) }).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().then(pool => { //Using a single connection pool is recommended var conn=pool.request() var forInteger = /\b\d+\b/i; //make sure that there is only an integer. if (forInteger.test(req.params.customerId)) { //check whether it was an integer conn.input("BusinessEntityID", sql.Int, req.params.customerId)} else {conn.input("BusinessEntityID", sql.Int, 1)} //otherwise just pass a 1 conn.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
|
1 2 3 |
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
|
1 2 3 4 5 6 7 8 9 10 11 |
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 the service from PowerShell. Here, I’m specifying where the modules live rather than making the project the current directory
|
1 2 |
$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.
in PowerShell, you can easily query the service
|
1 |
(Invoke-RestMethod -Method 'Get' -Uri 'http://localhost:8081/adventureproc/12')|format-table |
|
1 2 3 4 5 |
RecursionLevel BusinessEntityID FirstName LastName OrganizationNode ManagerFirstName ManagerLastName -------------- ---------------- --------- -------- ---------------- ---------------- --------------- 0 12 Thierry D'Hers /1/1/5/1/ Ovidiu Cracium 1 11 Ovidiu Cracium /1/1/5/ Roberto Tamburello 2 3 Roberto Tamburello /1/1/ Terri Duffy |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
$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.
Load comments