Using Sql Server from NodeJS

- Developer and Blogger
Published: Sun May 10 2015

In this post I will show how to use Sql Server with NodeJS.

There are a few options available for integrating Sql Server with NodeJS, but this article will be using a library called Tedious.

Tedious can be downloaded using npm

npm install tedious

I generally use MongoDB with Node these days, but I ended up having to do a data migration from Sql Server to MongoDB, so I figured I'd do a write up of the node script I ended up creating.

var Connection = require('tedious').Connection; var mongoose = require('mongoose'); var rows = []; var config = { userName: '[Username]', password: '[Password]!', server: '[Your Server]' }; var connection = new Connection(config); connection.on('connect', function(err) { //Add error handling here getSqlData(); } ); var Request = require('tedious').Request; function insertIntoMongoDb(){ console.log('inserting data into MongDB'); mongoose.connect('mongodb://localhost:27017/someMongoDB'); var articleSchema = new mongoose.Schema({ title: { type: String }, intro: { type: String }, body: { type: String }, key: {type: String,default:'unittesting'}, viewCount:{type:Number,default:0} },{ versionKey: false }); var articleModel = mongoose.model('Articles', articleSchema); for(var i = 0; i < rows.length; i++){ var article = new articleModel(rows[i]); article.save(function(error){ if(error){ console.log('error saving'); } else{ console.log('saved successfully') } }); } } function getSqlData() { console.log('Getting data from SQL'); request = new Request("SELECT Title as title, Body as body, Introduction as intro FROM Article WHERE createdDate >= '12/1/2013' ", function(err, rowCount) { if (err) { console.log(err); } else { insertIntoMongoDb(); } }); request.on('row', function(columns) { var row = {}; columns.forEach(function(column) { row[column.metadata.colName] = column.value; }); rows.push(row); }); connection.execSql(request); }

There you have it! As you can tell from the script above – integrating with Sql Server from NodeJS is pretty straight forward.

More info about Tedious can be found here