Simple Example of Nodejs Express With MSSQL

In this tutorial, I will explore How to connect the MSSQL database with nodejs and express. The nodejs is the most popular language and express is a nodejs framework.

We’ll create ExpressJS Rest API to read and write data from the Mssql database. There is a number of packages available to connect to the SQL Server database from Node.js.

I am using mssql package for mssql database driver. This is a popular third-party easy-to-use SQL Server connector for Node.js.

There are some features of mssql package as follows –

• Unified interface for multiple TDS drivers.
• Built-in connection pooling.
• Support for built-in JSON serialization (SQL Server 2016) as well as serialization of Geography and Geometry CLR types.
• Support for Stored Procedures, Transactions, Prepared Statements, Bulk Load and Table-Valued Parameters (TVP).
• Mapping of JS data type to SQL data type.
• Support for Promises, Streams and standard callbacks.

How To Connect MSSQL with Nodejs Express

I am using the express js framework to create a nodejs application. Let’s create a folder in nodejs workflow d:/nodejs-workflow/simple-http-mssql-example. Create a package.json file into this folder.

$npm init

or

{
  "name": "nodejs-example-mssql",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "cors": "^2.8.5",
    "express": "^4.17.1",
    "mssql": "^6.0.1"
  }
}

Create Nodejs Express Server With MSSQL

Let’s create index.js file into the folder. We are using cors module for middleware, body-parser for incoming request bodies in a middleware before your handlers, available under the req.body property.

var express = require("express");
var bodyParser = require("body-parser");
var sql = require("mssql");
var cors = require('cors');
var app = express(); 

// Body Parser Middleware
app.use(bodyParser.json()); 
app.use(cors());

//Setting up server
 var server = app.listen(process.env.PORT || 8080, function () {
    var port = server.address().port;
    console.log("App now running on port", port);
 });

How To Connect Nodejs Express with MSSQL

I have already installed and imported required packages into index.js, Lets create connection string and connect with MSSql database –

//Initializing connection string
var dbConfig = {
    user:  "username",
    password: "password",
    server: "hostname",
    database: "dbname"
};

Listing Nodejs, MSSQL Using Express

We will create GET type rest endpoint ’employee’, which will get all employee data from the Mssql database server.

//GET API
app.get("/api/v1/employee", function(req , res){
	getEmployees()
});
function getEmployees() {
    var dbConn = new sql.Connection(dbConfig);
    dbConn.connect().then(function () {
        var request = new sql.Request(dbConn);
        request.query("select * from employee").then(function (resp) {
            console.log(resp);
            dbConn.close();
        }).catch(function (err) {
            console.log(err);
            dbConn.close();
        });
    }).catch(function (err) {
        console.log(err);
    });
}

We have created getEmployees() method, that will connect the MSSQL database and get all employee data using mssql query.

Insert Record Into MSSQL Database Using ExpressJS

We will create HTTP Post type rest endpoint ’employee’, which will take posted JSON data as a payload and insert data into the mssql database table.

//POST API
app.post("/api/v1/employee", function(req , res){
	insertEmployees()
});
function insertEmployees() {
    var dbConn = new sql.Connection(dbConfig);
    dbConn.connect().then(function () {
		var transaction = new sql.Transaction(dbConn);
		transaction.begin().then(function () {
			var request = new sql.Request(transaction);
            request.query("INSERT INTO employee (name,salary,age) VALUES (req.body.name,req.body.salary,req.body.age")
			.then(function 	() {
				transaction.commit().then(function (resp) {
                    console.log(resp);
                    dbConn.close();
                }).catch(function (err) {
                    console.log("Error in Transaction Commit " + err);
                    dbConn.close();
                });
			}).catch(function (err) {
                console.log("Error in Transaction Begin " + err);
                dbConn.close();
            })
		}).catch(function (err) {
            console.log(err);
            dbConn.close();
        }).catch(function (err) {
        //12.
        console.log(err);
    });
  });
}

We have created insertEmployees() method, that will connect mssql database and insert data into the employee table.