connecting to sql server from node dot js

Fri, Apr 18, 2014

I’ve been hearing some good things about Node.js recently so I thought I’d give it a go. Not on something easy like a quickie server but on something much more gnarly. Getting it to talk to MSSQL via ODBC.

First things first, you need to install the ODBC/FreeTDS infrastructure and I’ve already blogged about that here.

Once that lot is set up, I’ll be using a DSN to define the connection so some things need to match. The Servername in odbc.ini:

[SQL Server]
Driver          = FreeTDS
Description     = ODBC connection via FreeTDS
Trace           = Yes
TraceFile       = /tmp/sqlserver
Servername      = SQL Server
Database        = TestDatabase
Port            = 1433

must match the [] tag in freetds.conf:

[SQL Server]
host = ip.address.of.server
port = 1433
tds version = 8.0
client charset = UTF-8

To test it works give it a blast with tsql, as the Javascript app will be using the same connection details:

tsql -S SQL Server -U username -P password

Once it’s working, it’s time to install ODBC for Node.js. This is a little tricky if you haven’t installed unixODBC in the standard location, so I’ll cover doing it that way. First get a copy of ODBC for Node.js:

git clone

then we need to faff around with the config:

vi node-odbc/binding.gyp

make binding.gyp look like this (changing your paths and/or putting them in the OS == “mac” section if you’re doing this on a Mac):

[ 'OS == "linux"', {
  'libraries' : [ 
  'cflags' : [

then install it manually. This will make sure it finds the non standard install of unixODBC:

npm install ./node-odbc

Now for the good stuff. The Javascript code for connecting to SQL Server:

var db = require("odbc")()
    ;, function (err) {
  if (err) return console.log(err);

    db.query('select top 10 * from Something', function (err, rows, moreResultSets) {
        var noOfRows = rows.length;
        for (var count=0; count < noOfRows; count++) {
        db.close(function (err) {

One last thing to do is make sure it knows where the non standard unixODBC libraries are, otherwise you’ll see this if you just run it:

Error: cannot open shared object file: No such file or directory

So run it from a script:

export LD_LIBRARY_PATH=/home/vagrant/apps/unixODBC-2.3.2/lib
node odbc.js

Viola, Javascript talking to SQL Server.


comments powered by Disqus