Chander Dhall's Blog

Sponsors

Tags

Microsoft Sql Server driver for Nodejs

Introduction

NOTE: Part 2 is available here

Node is one of the recent geek fever everyone has been going through. With the continued support from Microsoft for Nodejs on both Windows and Windows Azure, it is bound to reach great levels. Last week, during TechEd, Microsoft released it's own version of a Sql server driver for Nodejs. Yes, now you can write javascript code on the server that will talk to Sql Server.

Question: I haven't done any programming on Nodejs before this?

Answer: Don't worry about it. As long as you know typing on a notepad. You should be good.

NOTE: Documentation on this is still under construction but this blog post should get you started easily. If you have any questions during installation please feel free to contact me and I would be more than willing to help out.

First database call:

  • Only on windows? Before we talk about it in detail it might be worthwhile to note that it only works if the nodejs server is running on a windows machine. Yes, that's true.
  • Does that mean you can't code on a mac? No, you can. In that case you can run the server on a windows box or one of the Windows Azure VMs. Cool!

  Alrite. Let's take a small Hello world example. I have database called Test with a table Employee and two columns EmployeeID and Name.

  • Creating the server: I am assuming you have Nodejs installed on your system. If not, please follow the directions here. Once Nodejs is installed on your computer, make sure you have NPM. NPM stands for Node Package Manager (you might be familiar with the Microsoft version Nuget). Go to a directory of your choice. I am using "C:\Chander\Blog\Node-SqlServer\Test" for this example. Open up command prompt and type

    NOTE: Make sure you have followed all the step discusses in blog post above, otherwise you will get an error. Also, make sure you have the environment variables set as described in the  blog post.

    File System

    NOTE: The Visual studio solution is not necessary, you can do this without it. But for people who prefer to have Visual Studio as their IDE, you can create a blank solution and do the same.

    Now add a blank file Server.js in the Test directory.

    Add the following code snippet to the Server.js file. Note this is a copy of the code from nodejs.org.

  • Running the server: The next step is to run the server.  Go to your browser and type http://localhost:1337/ and you should get a response Hello World .

You can also use Fiddler or curl to compose this request.

NOTE: This step is to make sure Nodejs is working on your machine. If it is not, please make sure it does before proceeding to the next section.

  • Sql server: In order to use the Sql server driver for Node js, the first step is to include the module in your code. For now, we will add all the code in the same file i.e Server.js. In a future post, we will talk about how to do it more elegantly.

Also, do add your connection string in the following format.

 

 Now that we have included 'node-sqlserver', we can use the functions inside that module. So, go ahead and use sql.open and open up a database connection.

Cool: So, we can see when we run the Server again using the command 'node Server.js', we can see the values returned in the console.

If you are running this on a command prompt, hit 'Ctrl-C' to kill the server before using 'Node Server.js' to re-start it.

  • Error: If you run this you will get an error - Cannot find module '.sqlserver.node'

 Error

This can be easily fixed by going to the following directory 'C:\Chander\Blog\Node-SqlServer\Test\node_modules\node-sqlserver\build\Release' and then copying 'sqlserver.node' and 'sqlserver.pdb' over to 'C:\Chander\Blog\Node-SqlServer\Test\node_modules\node-sqlserver\lib'. Please follow the directory structure on your machine.

Here you go and that's the result on your console.

 Result

Summary

In this post we had a cursory glance at the Microsoft SQL Server driver for Node.js. This post is just to get started and lots of cool stuff like creating your APIs and how to use this driver in better ways, will come up in the following posts.

NOTE: Part 2 is available here

You can find Chander Dhall on Google+ and Twitter

Email Chander Dhall

Comments

college term paper said:

Thanks for post! I really like it.

# June 19, 2012 2:38 PM

Nik said:

I'm getting "Database connection failed!"

Anything that should be done specially for security/permissions (running SQL Express Locally).

Tried both Windows Authentication & SQL mode.

# July 2, 2012 10:58 PM

chanderdhall said:

Are you still getting this error? Do you mind emailing me your code? csdhall at chanderdhall dot com

# July 2, 2012 11:29 PM

Joy said:

Is this implemented as async callback because when I tried to execute the query WAIT FOR DELAY '0:0:10' I could see it is blocking the execution of subsequent requests.

Thanks in Advance

Joy

# July 6, 2012 10:20 AM

chanderdhall said:

I have tried this on Sql server 2008 R2 Native client and it works. However, I haven't tried this on windows xp though so you are right I hope. Thanks for sharing.

# July 6, 2012 3:05 PM

chanderdhall said:

Zee you are right. I have tried it and it works with both Sql Server 2008 R2 and 2012. havent' tried it on xp. thanks for sharing.

# July 6, 2012 3:08 PM

chanderdhall said:

Joy,

   If you look at this line

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

//function is the callback.

So anything inside that callback will be sequential but it should not stop the main thread on which sql.open is called.

one good way of testing that would be printing console.log('hello') right after sql.open statement.

and if the query takes more than a millisecond you will see the 'hello' printed before the web browser gets the results from the database. hope that helps.

# July 6, 2012 3:11 PM

chanderdhall said:

for the sake of separation of concerns, a config file (doen't have to be called web.config necessarily though) is a better approach. i added everything in server.js file as it's a beginner level tutorial. all you have to make sure is that config file is not under a public folder. good question.

# July 15, 2012 1:37 PM

Jon said:

Nice article!

I try to run this sample with msnodesql and SQL Server Native Client 10.0,  without success. Is it normal or I miss the point.

# January 11, 2013 6:18 PM

napstar said:

thanks..really liked..ran with no problems

# January 25, 2013 2:26 AM
Leave a Comment

(required) 

(required) 

(optional)

(required)