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

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

  1. 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.

  2. 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).

  3. 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
  4. 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.

  5. 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/
  6. 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
  7. 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.

  8. Open common.js in a text editor. For example:
    $ cd ~/node_odbc_dir/test
    $ vi common.js
            
  9. 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";
  10. 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

64-bit platforms

Further information