{"id":81586,"date":"2018-11-08T11:43:21","date_gmt":"2018-11-08T11:43:21","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=81586"},"modified":"2018-11-29T10:35:27","modified_gmt":"2018-11-29T10:35:27","slug":"setting-up-a-simple-rest-interface-with-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/setting-up-a-simple-rest-interface-with-sql-server\/","title":{"rendered":"Setting up a simple Rest interface with SQL Server"},"content":{"rendered":"

We\u2019re going to set up a web service for a SQL Server database using node js<\/strong> on a windows server. This is intended for a mobile application, but has a variety of other uses where an ODBC connection isn\u2019t possible. This service is purely done as a demonstration for people with a database background, so we\u2019ll get it to l connect with a SQL Server and provide data from AdventureWorks as an illustration. We\u2019ll 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<\/a>. This article is all about the big picture. We won\u2019t go into how to turn the rest interface into a service: Maybe a separate article.<\/p>\n

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

running PowerShell as administrator, run this.<\/p>\n

choco install nodejs.install -y<\/pre>\n
(See here<\/a> for an alternative installation and also some basic checks)<\/p>\n
Then set up a directory for your project. I\u2019ve chosen C:\\Projects\\Restful<\/strong><\/p>\n
Navigate to that directory with the Command Prompt\u00a0 (running as Administrator!). You will now use npm<\/strong>, (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) …<\/p>\n
C:\\Projects\\Restful>npm install express\r\nC:\\Projects\\Restful>npm install mssql\r\nC:\\Projects\\Restful>npm install msnodesqlv8\r\n<\/pre>\n
We now write the JavaScript app we need and save it in the directory.<\/p>\n
Normally, you\u2019ll break this into component scripts, particularly the authentication stuff. However, in the interests of getting up and running, we\u2019ll just use a two files.<\/p>\n
The first file is a config file, config.js<\/code>, 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.\u00a0<\/p>\n
\/*In addition to being able to use a simple configuration object ...\r\n{\r\n  database: \"db_name\",\r\n  server: \"server_name\",\r\n  driver: \"msnodesqlv8\",\r\n  options: {\r\n    trustedConnection: true\r\n  }\r\n}\r\n... there is an option to pass config as a connection string. \r\nTwo formats of connection string are supported.\r\n\r\nServer=localhost,1433;Database=database;User Id=username;Password=password;Encrypt=true\r\nDriver=msnodesqlV8;Server=(local)\\INSTANCE;Database=database;UID=DOMAIN\\username;PWD=password;Encrypt=true\r\nDriver=msnodesqlv8;Server=(local)\\INSTANCE;Database=database;UID=DOMAIN\\username;PWD=password;Encrypt=true\r\nor \r\n\/\/mssql:\/\/username:password@localhost:1433\/database?encrypt=true\r\n\/\/mssql:\/\/username:password@localhost\/INSTANCE\/database?encrypt=true&domain=DOMAIN&driver=msnodesql\r\n\/\/mssql:\/\/username:password@localhost\/INSTANCE\/database?encrypt=true&domain=DOMAIN&driver=msnodesqlv8\r\n*\/\r\nvar config = {\r\n  production: {\r\n     driver: 'msnodesqlv8',\r\n     connectionString: 'Driver=SQL Server Native Client 11.0;Server=MyServerName;Database=MyDatabase;UID=MyUserName;PWD=MyPassword'\r\n     },\r\n  development: {\r\n     driver: 'msnodesqlv8',\r\n     connectionString: 'Driver=SQL Server Native Client 11.0;Server=ServerName;Database=MyDatabase;Trusted_Connection=yes'\r\n     }\r\n\r\n};\r\nmodule.exports = config;<\/pre>\n
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\u00a0 var env = process.env.NODE_ENV || 'development';<\/code> to\u00a0var env = process.env.NODE_ENV || 'production';<\/code><\/p>\n
 <\/p>\n
const express = require('express'); \/\/ minimalist web framework for Node.js\r\nconst app = express();\r\nconst sql = require('mssql\/msnodesqlv8') \/\/mssql with MS driver for SQL Server\r\n\/\/ added windows authentication\r\n\r\nvar env = process.env.NODE_ENV || 'development';\r\nvar sqlConfig = require('.\/config')[env];\r\n\r\n\/\/ Start server and listen on http:\/\/localhost:8081\/\r\nvar server = app.listen(8081, function() {\r\n  var host = server.address().address\r\n  var port = server.address().port\r\n\r\n  console.log(\"app listening at http:\/\/%s:%s\", host, port)\r\n});\r\n\r\nconst connection = new sql.ConnectionPool(sqlConfig, function(err){\r\n      if (err){\r\n      console.log(err);\r\n      }\r\n    }\r\n)\r\n\r\n\/\/ define a simple route\r\napp.get('\/', function(req, res) {\r\n    res.json({\"message\": \"Welcome to Adventureworks Sample App.\"});\r\n});\r\n\r\n\/\/ if you plan to work with local temporary tables, use batch instead. \/\/\r\n\/\/uses sp_ExecuteSQL\r\napp.get('\/adventure\/:customerId\/', function(req, res) {\r\n  connection.connect().then(pool => { \/\/Using a single connection pool is recommended\r\n    var conn=pool.request()\r\n    var forInteger = \/\\b\\d+\\b\/i; \/\/make sure that there is only an integer.\r\n    if (forInteger.test(req.params.customerId)) { \/\/check whether it was an integer\r\n       conn.input('input_parameter', sql.Int, req.params.customerId)}\r\n    else {conn.input('input_parameter', sql.Int, 1)} \/\/otherwise just pass a 1\r\n    var string = 'select * from Sales.Customer where customerId = @input_parameter'\r\n    return conn.query(string)\r\n  }).then(result => {\r\n    let rows = result.recordset\r\n    res.setHeader('Access-Control-Allow-Origin', '*')\r\n    res.status(200).json(rows);\r\n    connection.close();\r\n  }).catch(err => {\r\n    console.log(err);\r\n    res.status(500).send({\r\n      message: err\r\n    })\r\n    connection.close();\r\n  });\r\n});\r\n\r\n\r\napp.get('\/adventureproc\/:customerId\/', function(req, res) {\r\n  connection.connect().then(pool => { \/\/Using a single connection pool is recommended\r\n    var conn=pool.request()\r\n    var forInteger = \/\\b\\d+\\b\/i; \/\/make sure that there is only an integer.\r\n    if (forInteger.test(req.params.customerId)) { \/\/check whether it was an integer\r\n       conn.input(\"BusinessEntityID\", sql.Int, req.params.customerId)}\r\n    else {conn.input(\"BusinessEntityID\", sql.Int, 1)} \/\/otherwise just pass a 1\r\n      conn.execute(\"uspGetEmployeeManagers\")\/\/the name of the procedure\r\n        .then(result => {\r\n          let rows = result.recordset \/\/first recordset\r\n          res.setHeader('Access-Control-Allow-Origin', '*')\r\n          res.status(200).json(rows);\r\n          connection.close();\r\n        }).catch(err => {\r\n          console.log(err);\r\n          res.status(500).send({\r\n            message: err\r\n          })\r\n          connection.close(); \/\/send the connection back to the pool\r\n        });\r\n    })\r\n});<\/pre>\n
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<\/p>\n
CD C:\\Projects\\Restful\r\nC:\\Projects\\Restful>node server.js\r\napp listening at http:\/\/:::8081<\/pre>\n
Through a separate command prompt we can run requests for the data<\/p>\n
C:\\Users\\Phil>curl -X GET http:\/\/localhost:8081\/adventure\/1\r\n[{\"CustomerID\":1,\"PersonID\":null,\"StoreID\":934,\"TerritoryID\":1,\"AccountNumber\":\"AW00000001\",\r\n  \"rowguid\":\"3F5AE95E-B87D-4AED-95B4-C3797AFCB74F\",\"ModifiedDate\":\"2014-09-12T11:15:07.263Z\"}]\r\nC:\\Users\\Phil>curl -X GET http:\/\/localhost:8081\/adventureproc\/12\r\n[{\"RecursionLevel\":0,\"BusinessEntityID\":12,\"FirstName\":\"Thierry\",\"LastName\":\"D'Hers\",\"Organizat\r\nionNode\":\"\/1\/1\/5\/1\/\",\"ManagerFirstName\":\"Ovidiu\",\"ManagerLastName\":\"Cracium\"},{\"RecursionLevel\"\r\n:1,\"BusinessEntityID\":11,\"FirstName\":\"Ovidiu\",\"LastName\":\"Cracium\",\"OrganizationNode\":\"\/1\/1\/5\/\"\r\n,\"ManagerFirstName\":\"Roberto\",\"ManagerLastName\":\"Tamburello\"},{\"RecursionLevel\":2,\"BusinessEntit\r\nyID\":3,\"FirstName\":\"Roberto\",\"LastName\":\"Tamburello\",\"OrganizationNode\":\"\/1\/1\/\",\"ManagerFirstNam\r\ne\":\"Terri\",\"ManagerLastName\":\"Duffy\"}]\r\nC:\\Users\\Phil><\/pre>\n
You can run the service from PowerShell. Here, I\u2019m specifying where the modules live rather than making the project the current directory<\/p>\n
$env:NODE_PATH = \" C:\\Projects\\Restful\\node_modules\"\r\nnode C:\\Projects\\Restful\\Server.js<\/pre>\n
This interface is very quick and uses connection pooling. I haven\u2019t introduced async or promises but hopefully there is something to build on.<\/p>\n
in PowerShell, you can easily query the service<\/p>\n
(Invoke-RestMethod -Method 'Get' -Uri 'http:\/\/localhost:8081\/adventureproc\/12')|format-table<\/pre>\n
RecursionLevel BusinessEntityID FirstName LastName   OrganizationNode ManagerFirstName ManagerLastName\r\n-------------- ---------------- --------- --------   ---------------- ---------------- ---------------\r\n             0               12 Thierry   D'Hers     \/1\/1\/5\/1\/        Ovidiu           Cracium\r\n             1               11 Ovidiu    Cracium    \/1\/1\/5\/          Roberto          Tamburello\r\n             2                3 Roberto   Tamburello \/1\/1\/            Terri            Duffy<\/pre>\n
I show you here in\u00a0 ‘Importing JSON Data from Web Services and Applications into SQL Server<\/a>‘ how you can query a rest service from SQL Server<\/p>\n
While you are developing things, you\u2019ll 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.<\/p>\n
All this is best done on a local machine.<\/p>\n
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\u2019re happy with, especially if they are doing Single Page Applications. They are entirely protected from any \u2018database stuff\u2019. It just looks like a simple service to them.<\/p>\n
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.<\/p>\n
$port=8081\r\n$RuleName='NodeJSAdventure'\r\n$RuleDescription='Allow Remote Connections'\r\n\r\n$success = @(); #have we a rule in place already?\r\n$existingFirewallRule = Get-NetFirewallRule -DisplayName $RuleName `\r\n    -ErrorAction SilentlyContinue -ErrorVariable success\r\nif ($success.Count -gt 0)\r\n{\r\n<# Cut a hole in the firewall for the designated port #>\r\n$existingFirewallRule= New-NetFirewallRule <#now allow it through the firewall #> `\r\n\t\t\t\t\t\t-DisplayName $RuleName `\r\n\t\t\t\t\t\t-Description $RuleDescription `\r\n\t\t\t\t\t\t-Direction Inbound `\r\n\t\t\t\t\t\t-Protocol TCP `\r\n\t\t\t\t\t\t-LocalPort $port `\r\n\t\t\t\t\t\t-Action Allow\r\n\t\r\n}\r\nelse\r\n{\r\n\tif (($existingFirewallRule | Get-NetFirewallPortFilter).LocalPort -ne $Port)\r\n\t{ set-NetFirewallRule -DisplayName $RuleName -LocalPort $Port }\r\n}\r\n \r\n<\/pre>\n
There are plenty of additional ways of increasing security<\/a> for the server when your application moves towards production.<\/p>\n
For a more detailed sample using Express and mssql, see the Todo List Sample Project<\/a> by Jovan Popovic<\/p>\n
Conclusion<\/h1>\n
NodeJS<\/strong> with mssql<\/strong> and express<\/strong> 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.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
We\u2019re 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\u2019t possible. This service is purely done as a demonstration for people with a database background, so……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[4880],"coauthors":[6813],"class_list":["post-81586","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-json"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81586","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=81586"}],"version-history":[{"count":14,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81586\/revisions"}],"predecessor-version":[{"id":81839,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81586\/revisions\/81839"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=81586"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=81586"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=81586"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=81586"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}