Nodejs, Sql server and Json response
with Rest
This post is part 2 of Microsoft Sql Server driver for Node js.In this post we will look at the
JSON responses from the Microsoft Sql Server driver for Node js.
Pre-requisites:
If you have read the Part 1 of the series, you should be good.
We will be using a framework for Rest within Nodejs - Restify, but that would
need no prior learning.
Restify:
Restify is a simple node module for building RESTful services. It is slimmer
than Express. Express is a complete module that has all what you need to create
a full-blown browser app. However, Restify does not have additional overhead of
templating, rendering etc that would be needed if your app has views. So, as the
name suggests it's an awesome framework for building RESTful services and is
very light-weight.
Set up -
You can continue with the same directory or project structure we had in the
previous post, or can start a new one.
Install restify using npm and you are good to go.
npm install restify
Go to Server.js and include Restify in your solution.
Then create the server object using restify.CreateServer() - SLICK - ha?
var restify = require('restify');
var server = restify.createServer();
server.listen(8080, function () {
console.log('%s listening at %s', server.name, server.url);
});
Then make sure you provide a port for the Server to listen at. The call back
function is optional but helps you for debugging purposes.
Once you are done, save the file and then go to the command prompt and hit 'node
server.js' and you should see the following:
To test the server, go to your browser and type the address 'http://localhost:8080/' and oops you will
see an error.
Why is that? - Well because we haven't defined any routes. Let's go ahead and
create a route. To begin with I'd like to return whatever is typed in the url
after my name and the following code should do it.
server.get('/ChanderDhall/:status', function respond(req, res, next) {
res.end("hello " + req.params.name + "")
});
You can also avoid writing call backs inline. Something like this.
function respond(req, res, next) {
res.end("Chander Dhall " + req.params.name + "");
}
server.get('/hello/:name', respond);
Now if you go ahead and type
http://localhost:8080/ChanderDhall/LovesNode you will get the response
'Chander Dhall loves node'.
NOTE: Make sure your url has the right case as it's case-sensitive. You could
have also typed it in as 'server.get('/chanderdhall/:name', respond);'
Stored procedure:
We've talked a lot about Restify now, but keep in mind the post is about being
able to use Sql server with Node and return JSON.
To see this in action, let's go ahead and create another route to a list of
Employees from a stored procedure.
server.get('/Employees', Employees);
The following code will return a JSON response.
function Employees(req, res, next) {
res.header("Content-Type: application/json");
//Need to specify the Content-Type which is
//JSON in our case.
sql.open(conn_str, function (err, conn) {
if (err) {
//Logs an error
console.log("Error opening the database connection!");
return;
}
console.log("before query!");
conn.queryRaw("exec sp_GetEmployees", function (err, results) {
if (err) {
//Connection is open but an error occurs whileWhat else can be done? May be create a formatter or may be even come up with a
hypermedia type but that may upset some pragmatists. Well, that's going to be a
totally different discussion and is really not part of this series.
Summary:
We've discussed how to execute a stored procedure using Microsoft Sql Server
driver for Node. Also, we have discussed how to format and send out a clean JSON
to the app calling this API.
