Node.js REST API with MS SQL Server

February 13, 2025

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 !

intertoons

Interoons aim at providing electronically intelligent and comprehensive range of digital marketing solutions that exceed customer expectations. We implement revolutionary digital marketing ideas to achieve a common as well as the aggregate growth of the organization. Long-term customer relations and extended support are maintained.