Node.js REST API with MS SQL Server

Photo by James Harrison on Unsplash

Learn to build a REST API in Node.js

Let’s create a node application that will connect with MS SQL Database and return result set in required format.

First, let’s install Node Js on windows machine

Download Node from https://nodejs.org/en/. Install its dependent packages. (The installation may take few minutes… Let’s wait for the best)

To confirm Node installation, type node -v command.

To confirm NPM installation, type npm -v command.

In addition we need two modules Express (a Web framework for Node.js) and mssql (MS SQL Server client for Node.js).

use the commands to install these modules

npm install express
npm install mssql --save

Now make sure that your SQL server Browser service is up and running from Sql server configuration manager

Now we have finished basic configurations to kick start your node applications!

Next, we will start with our new application.

To create a project, create a blank folder. Open Command prompt, and change directory to the created one. In the directory run the command:

npm init -y

This command will create a package.json file with default values in the project folder. We have to modify it as below

{
  "name": "nodeapi",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start":"nodemon api.js"
  },
  "dependencies": {
    "body-parser": "~1.0.1",
    "cors": "2.8.1",
    "express": "~4.0.0",
    "mssql": "^6.2.1"
  },
  "devDependencies": {
    "nodemon": "^2.0.4"
  },
  "keywords": [],
  "author": "Your Name",
  "license": "ISC"
}

After saving your package.json file, on the command prompt run the following command:

npm install

This will install all necessary modules. After a successful installation, you will find the node_modules subfolder added to the project. You are all set!

Next you have to add a file dbconfig.js file. This is the file we use to save database configurations. Put the below code to it.

const  config = {
  user: 'username',
  password: 'password',
  server: 'serverName',
  database: 'dbName',
  options: {
    trustedconnection:  true,
    enableArithAbort:  true
  },
  port:  1433
}

module.exports = config;

Please note the module.exports — This will make the config publicly accessible from outside.

Now we can create a generic js file for our db operations

I have named it as getCustomers.js

var  config = require('./dbconfig');
const  sql = require('mssql');

async  function customers() {
    try {
    
      let  pool = await  sql.connect(config);
      let  customers = await  pool.request().query("SELECT * from table");
      return  customers.recordsets;
    }
    catch (error) {
      console.log(error);
    }
  }

  module.exports.customers= customers;

Now lets create the API

Add a file called api.js in the project, and put the below code in it.

var  Db = require('./getCustomers');
var  express = require('express');
var  bodyParser = require('body-parser');
var  cors = require('cors');
var  app = express();
var  router = express.Router();

app.use(bodyParser.urlencoded({ extended:  true }));
app.use(bodyParser.json());
app.use(cors());
app.use('/api/v1', router);

router.use((request, response, next) => { //just a test snippet
  console.log('middleware concept running');
  next();
});
 
 
router.route('/getCustomers').get((request, response) => {
 
  Db.customers().then((data) => {
    response.json(data[0]);
  })
})
 
  
var  port = process.env.PORT || 8081;
app.listen(port);
console.log('Customers API is runnning at ' + port);

Now install nodemon. This will eliminate the need of restarting node for every changes you make.

npm install -g nodemon

Now run the following command

nodemon api.js

If you get error …cannot be loaded because running scripts is
disabled on this system, then run the following command.

Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

Now run

nodemon api.js

This will run the instance in 8081 port . Now to access your api, in a browser or Postman hit

http://localhost:8081/api/v1/getCustomers

You should get response in relation with your table.

Explore more !