MS SQL Server driver for Node.js with JSON response

We covered installing and running the MS SQL driver for Node.js in the last post. The demonstration
was a simple web page that listed statically queried results from a database. For this post, we’ll
be demonstrating how to serve the results in JSON; which is a more practicle usage of the MS SQL
driver.

In the previous example, we utilized the built in http server to serve up the static web page and
the query was run once when the server was launched. This time we’ll create a RESTful service and
query the database each time there is a request.

Restify is a Node.js module for creating REST web services. To install it, open a command prompt in
the folder that you wish to create the demo and type the following:

1npm install restify

Once restify is installed, you can create a basic server and run it to verify that the installation
was successful. Create a text file named server.js and copy the next block of code.

1

2

3

4

5

6

var restify = require('restify');

var server = restify.createServer();

server.listen(8080, function () {

   console.log('listening at %s', server.url);

});

Run the server with:

1

node server.js

If Restify was installed correctly, you should see ‘listening at http://0.0.0.0:8080’ in the console.
You can hit the page from your browser at this time, but you will get an error result similar to the
following:

1

{"code":"ResourceNotFound","message":"/ not found"}

This is expected behavior and it simply means that a route for the requested page does not exist. To
define a route, you will need to set up the “GET” callback.

1

server.get('/people', respond);

The first parameter is the route and the second parameter is the function that provides the response.
In this example, we have defined a route so that requests can be made to the url of the server followed
by /people. For example, you should now be able run the server and browse to it with
http://localhost:8080/people once the ‘respond’ function is defined.

To serve the JSON response, the respond function will query the database and put the results into an
array object with key / value pairs. Once populated, the array object is sent as the response.

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

function respond(request, response, next) {

   response.header("content-type: application/json");

   var data = [];

   sql.open(conn_str, function (err, conn) {

       if (err) {

       data = "cannot open connection!";

       return;

       }

       conn.queryRaw("SELECT TOP 10 FirstName, LastName FROM Person.Contact", function (err, results) {

       if (err) {

           console.log("query failed!");

           return;

       }

       for (var i = 0; i < results.rows.length; i++) {

           data.push({

               firstname: results.rows[i][0],

               lastname: results.rows[i][1]

           });

       }

       response.send(data);

       });

   });

};

It is important that you set the content-type to json. This may seem obvious, but it is worth noting
in order to prevent a few moments of head scratching :).

We now have Node ready to serve JSON responses with data from a MS SQL Server. The complete script
should look similar to the following:

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

var restify = require('restify');

var server = restify.createServer();

var sql = require('node-sqlserver');

var connection_string = "Driver={SQL Server Native Client 10.0};Server=YOUR_SERVER;Database=YOUR_DB;uid=YOUR_USER;pwd=YOUR_PASSWORD";

function respond(request, response, next) {

   response.header("content-type: application/json");

   var data = [];

   sql.open(connection_string, function (err, conn) {

       if (err) {

       data = "cannot open connection!";

       return;

       }

       conn.queryRaw("SELECT TOP 10 FirstName, LastName FROM Person.Contact", function (err, results) {

       if (err) {

           console.log("query failed!");

           return;

       }

       for (var i = 0; i < results.rows.length; i++) {

           data.push({

               firstname: results.rows[i][0],

               lastname: results.rows[i][1]

           });

       }

       response.send(data);

       });

   });

};

server.listen(8080, function () {

   console.log('listening at %s', server.url);

});

server.get('/people', respond);

If you have an AdventureWorks database installed, you will just need to update the connection string
for this demo. If not, be sure to update the connection string as well as the actual query.

To run the server, use the following command at the command prompt in the folder where your script
is located.

1

node server.js

To view the data, you can browse to http://localhost:8080/people. I recommend using either Chrome or Firefox
to do so. If you use Chrome, I highly recommend the JSONView extension as it will present the data in a more readable fashion.

‍