How do I connect Node.js on UNIX and Linux to SQL Server?

Use the SQL Server ODBC driver and the node-odbc interface to connect Node.js to Microsoft SQL Server. You can then access data stored in SQL Server from your Node.js application on Linux and UNIX platforms.

The SQL Server ODBC driver is available for 32-bit and 64-bit Linux and UNIX (AIX, HP-UX, and Solaris) platforms.

Prerequisite Software

Python 2.6 or later
Node.js
unixODBC
The unixODBC Driver Manager is included with most Linux distributions and some UNIX distributions. You need both the Driver Manager and its development headers (the package for this may be called unixODBC-dev or unixODBC-devel).

The SQL Server ODBC driver distribution includes both unixODBC and its header files. It's possible to build node-odbc against the Driver Manager files included in the SQL Server ODBC distribution, although the default behaviour is to use the unixODBC files that come with the operating system.

How to access SQL Server from Node.js on UNIX and Linux

Download the SQL Server ODBC driver for your Node.js client platform.)
If your client machine is 64-bit, refer to this note, before downloading the driver.

If the SQL Server ODBC driver is not currently available for your platform, check the list of ODBC-ODBC Bridge client platforms. The ODBC-ODBC Bridge is an alternative SQL Server solution from Easysoft, which you can download from this site.
Install and license the SQL Server ODBC driver on the machine where Node.js is installed.
For installation instructions, refer to the SQL Server ODBC driver documentation. Refer to the documentation to find out which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH, or SHLIB_PATH depending on the driver, platform, and linker).

Create an ODBC data source in /etc/odbc.ini that connects to the SQL Server database you want to access from Node.js. For example:

[MSSQL-NODE-JS-DSN]
Driver          = Easysoft ODBC-SQL Server
Server          = my_machine\SQLEXPRESS
User            = my_domain\my_user
Password        = my_password
# If the database you want to connect to is the default
# for the SQL Server login, omit this attribute
Database        = Northwind

Use isql to test the new data source. For example:
```
cd /usr/local/easysoft/unixODBC/bin
./isql -v MSSQL-NODE-JS-DSN
```
At the prompt, enter help to display a list of tables. To exit, press Return in an empty prompt line.

If you are unable to connect, refer to this article and the SQL Server ODBC driver knowledge base for assistance.
Install node-odbc on your Node.js machine.
To do this, do one of the following:
- If you have the Git client, you can clone the node-odbc repository. For example:
```
$ mkdir ~/node_odbc_dir
$ cd ~/node_odbc_dir
$ git clone git://github.com/wankdanker/node-odbc.git
```
- If you have the Subversion client, you can check out the node-odbc repository. For example:
```
$ mkdir ~/node_odbc_dir
$ cd
$ svn co https://github.com/wankdanker/node-odbc/trunk node_odbc_dir
```
- You can download the node-odbc source distribution from: https://github.com/wankdanker/node-odbc/releases/

Use node-gyp to configure and build node-odbc. For example:

$ cd ~/node_odbc_dir
$ /usr/local/lib/node_modules/npm/bin/node-gyp-bin/node-gyp configure build

Use npm to install node-odbc. For example:
```
$ cd ..
$ sudo npm -g install node_odbc_dir
```
To test that you can connect to SQL Server from Node.js and fetch some data, use the sample CLI application that is included with node-odbc.

Open common.js in a text editor. For example:

$ cd ~/node_odbc_dir/test
$ vi common.js

Replace the default connection string with one that specifies your ODBC data source. Your common.js file should look similar to this:
```
//exports.connectionString = "Driver={SQLite3};DATABASE=data/sqlite-test.db";
exports.connectionString = "DSN=MSSQL-NODE-JS-DSN";
```

Run sql-cli.js. For example:

$ cd ~/node_odbc_dir/test
$ NODE_PATH=/usr/local/lib/node_modules/odbc/node_modules/bindings \
  LD_LIBRARY_PATH=/usr/local/lib:/usr/local/easysoft/sqlserver/lib:/usr/local/easysoft/lib:$LD_LIBRARY_PATH \
  node sql-cli.js
$ SELECT 1 AS "COLINT", 'some test' AS "COLTEXT";
[ { COLINT: 1, COLTEXT: 'some test' } ]

Notes

Poxy server

If you use an HTTP proxy server at your site, you need to set the http_proxy environment variable to download and build node-odbc. For example:
```
export http_proxy=http://myproxyserver:8080
```

64-bit platforms

By default, node-odbc is a built as a 64-bit shared object on 64-bit platforms, as the output from the file command on a 64-bit Linux system demonstrates:
```
$ file build/Release/odbc_bindings.node
build/Release/odbc_bindings.node: ELF 64-bit LSB shared object, AMD X86-64, version 1 (SYSV), not stripped
```
When node-odbc is 64-bit, it needs to be built against a 64-bit version of unixODBC and used with a 64-bit version of the SQL Server ODBC driver.

If you built node-odbc against unixODBC 2.2.13 or later (odbcinst --version), node-odbc will have been built with 64-bit SQLLEN types and you need to use it with a 64-bit SQLLEN version of the SQL Server ODBC driver. (Unless you set the BUILD_LEGACY_64_BIT_MODE flag when building unixODBC, in which case you need to use node-odbc with a 32-bit SQLLEN version of the SQL Server ODBC driver.)

If you built node-odbc against unixODBC 2.2.12 or earlier, node-odbc will have been built with 32-bit SQLLEN types and you need to use it with a 32-bit SQLLEN version of the SQL Server ODBC driver. (Unless you set the BUILD_LEGACY_64_BIT_MODE flag when building unixODBC, in which case you need to use node-odbc with a 64-bit SQLLEN version of the SQL Server ODBC driver.)

If you're not sure which version of the SQL Server ODBC driver to use, download the 64-bit version of the driver from the Easysoft web site and build node-odbc against the unixODBC Driver Manager that's included with the SQL Server ODBC driver distribution. You can then be sure that node-odbc, unixODBC and the SQL Server ODBC driver will all be the same architecture and have the same size SQLLEN types. To build node-odbc against the unixODBC Driver Manager included with the driver distribution, you need to:
1. Tell node-odbc where to find the unixODBC headers by setting the CXXFLAGS environment variable:
```
$ export CXXFLAGS=-Ieasysoft_installation_dir/easysoft/unixODBC/include
```
  Replace easysoft_installation_dir with the directory under which you installed the SQL Server ODBC driver. The default installation directory is /usr/local.
2. Tell node-odbc where to find the unixODBC shared object by setting the LDFLAGS environment variable. For example:
```
$ export LDFLAGS=-L/usr/local/easysoft/unixODBC/lib
```
3. Build or rebuild node-odbc. For example:
```
$ cd ~/node_odbc_dir
$ rm -rf build
$ CXXFLAGS=-I/usr/local/easysoft/unixODBC/include \
  LDFLAGS=-L/usr/local/easysoft/unixODBC/lib \
  /usr/local/lib/node_modules/npm/bin/node-gyp-bin/node-gyp configure build
$ ldd odbc_bindings.node
linux-vdso.so.1 => (0x00007fff5172e000)
libodbc.so.1 => /usr/local/easyoft/unixODBC/lib/libodbc.so.1 (0x00007ff9e42bf000)
```