Node.js REST API with MS SQL Server
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.
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.
Recommended Posts
Key Strategies to Boost API Performance in ASP.net
March 10, 2025
Building Secure APIs: Best Practices Every Developer Should Follow
February 18, 2025