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