Today we’re going to look at connecting to Microsoft SQL Server for Linux through Node.js similar to how we connected in PHP earlier. Just as before, our goal is to enable web application development by connecting to an existing MS SQL Server running on our local host. This time, we’ll do so using Node, and the popular Node library Tedious.

If you haven’t already done so, you’ll want to take a look at our installation instructions to get your development environment ready. This guide will assume you’re running Ubuntu, but any Linux flavor will do. We’ll also assume you have node and npm binaries installed.

Install NPM Dependencies

First, you’ll need to install some npm libraries for interacting with SQL Server. I recommend tedious, which is a T-SQL only driver that Microsoft supports:

npm install tedious

Tedious works with version 4 of Node.js and later.  The tedious library is actually all you need to execute T-SQL commands. Other libraries, like the popular Sequelize library, can interact with MS SQL as well and provide a more full-featured ORM.

Test the Connection

A basic connection with tedious is fairly simple. You take the connection details and pass them to the connection constructor. Connections implement the EventEmitter API, which means you can wait on the asynchronous “connected” event to fire before continuing:

let tedious = require(‘tedious’);
let Connection = tedious.Connection;
const config = {
userName: 'myUser', // update me
password: 'myPass', // update me
server: 'localhost',
options: {
    database: 'MyDB'
  }
}

function handleConnection(err){
  if(err) console.error("error connecting :-(", err);
  else console.log("successfully connected!!")
}
let connection = new Connection(config);
connection.on('connect', handleConnection);

Our handleConnection function lets us asynchronously handle errors and notify us once we’ve connected. Put the above code in a module called connect.js and run it by typing node connect.js in a terminal. You should see “successfully connected!” pop up in your window.

Run Some Queries

Now that we’ve got a successful connection, we can use that as a basis for running more complicated queries. In tedious, these are done by creating new Request objects, and then calling a method to fill in the parameters. When the Request is executed and returns, it calls a callback that we provide.

As with our PHP example, we’ll assume you’ve got a basic employees table in your database. Here’s a simple select statement you can run with your connection from the previous section:

const statement = "SELECT Id, Name, Location FROM ExampleSchema.Employees;"
function handleResult(err, numRows, rows){
  if(err) return console.error("error running select statement", err);
  console.log("got", numRows, "rows back from the DB");
}

let results = [];
function handleRow(columns){
  columns.forEach(function(column){
    if(column.value !== null) results.push(column.value);
  });
}

let request = new tedious.Request(statement, handleResult);
request.on('row', handleRow);
connection.execSql(request);

Here we see the basic principles of MS SQL running in Node.js: everything is done asynchronously by creating requests and passing them to tedious to execute. The results are also events, and we use the EventEmitter “on row” syntax to build up the results.

Using the same paradigm we can also execute INSERT or UPDATE commands:

const statement = "INSERT INTO ExampleSchema.Employees OUTPUT INSERTED.Id (Name, Site) VALUES (@Name,@Site)"
const sampleUser = {name : "Joan", site : "Downtown"};
let request = new tedious.Request(statement, handleResult);
request.addParameter('Name', tedious.TYPES.NVarChar, sampleUser.name);
request.addParameter('Site', tedious.TYPES.NVarChar, sampleUser.site);
connection.execSql(request);

Best Practices

Tedious makes heavy use of the EventEmitter pattern, so you should be familiar with that before using the library in your code. In particular, you need to make sure that you’re not trying to execute statements before the connection is ready, or trying to read results before they’ve been written.