Microsoft Sql Server driver for Nodejs - Part 2
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.
Go to Server.js and include Restify in your solution. Then create the server object using restify.CreateServer() - SLICK - ha?
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.
You can also avoid writing call backs inline. Something like this.
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.
The following code will return a JSON response.
Again run the server using 'node server.js' and you are good to go. Now you can use the browser or fiddler or curl it if you like and once you go to - 'http://localhost:8080/Employees' the fiddler response is below.
Elegant Coding tip
Question: This sounds good. But I don't like the way I have to loop through and create the array with hardcoded values.
Answer: That's right. I am going to discuss what can be done differently to get a better output.
Source Code: I looked at the source code and found out that Microsoft passes back the results object which has two properties: 'meta' and 'rows'. Note: I could have done that without looking at the source code, too but it's just me.
'meta' as the name suggests gives you metadata regarding what is returned back to you. This includes the following: name, nullable, size and type information about the column. For example, name = id, nullable = True, size = 10, type = number.
'rows' returns the array of values from the database. rows = { [1, Chander Dhall], [2, Adi Dhall]};
So, this little coding snippet would help you not having to hard code values like 'id' and 'lastName'.
This gives us the nicely formatter JSON, with the names of database columns paired with value from the database.
Counter Argument : Since we are creating JSON values so why worry about this? Why not just send what we are getting from the database (also JSON) to the app?
Answer: Yes, you could. But I would not do that for two simple reasons:
1. The payload increases. In my case, the payload was 3 times more than what it was with the formatter JSON. was with the formatter JSON.
2. Also, I don't want the app to know the specific metadata regarding my properties.
What 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.