How To Connect SQL Server Database In NodeJS

In this article, we will learn how to connect SQL with node and display data from SQL.

1. First of all, create a project and add a src folder.

2. Create a  database/setting.js file and add the below code there.

const config = {
  user: 'abc', //sql user
  password: '12345', //sql password
  server: 'serverabc', //sql server name
  database: 'students', //database name
  options: {
    trustedConnection: true,
    trustServerCertificate: true,
  },
  port: 1433
}

module.exports = config;

3. After create the setting.js file install mssql module to connect with SQL  server from node.js

npm install mssql

4. Create a controller/studentController.js file and add the below code.

const config = require('../database/setting'); //include setting.js file
var sql = require("mssql"); //add mssql module


async function showStudentData(req, res) {
    try {
        let db = await sql.connect(config);
        let data = await db.request().query("select * from studentdata");
        res.status(200).json({
            success: true,
            data: data.recordsets[0]
        })
    } catch (error) {
        res.status(500).json({
            success: false,
            message: "error : data not show"
        });
    }
}

module.exports = {
    showStudentData
};

–In showStudentData function :

  • sql.connect method use for connecting to the database.
  •  after a successful connection execute the query. In this .query  we are passing a sql query.
  • it returns data from the studentdata table.

5. Create  routes/router.js file.

–install npm i express module.

const express = require("express");
const route = express.Router(); //import router 
const studentController = require('../controller/studentController'); //import studentController file

route.get('/student/show', studentController.showStudentData) //create get request

module.exports = route;

–In this file, we have created  a router

6. In the root directory add an app.js file for creating a API.

–install npm install cors

const express = require('express')
const url = require('./src/routes/router')
const app = express()
const cors = require('cors');
app.use(express.json());
app.use(cors());  
app.use(url)
const port = 3000 //project running on 3000 

app.listen(port, () => console.log(`server running in port ${port}`))

–Test the API

http://localhost:3000/student/show

output:

Submit a Comment

Your email address will not be published.

Subscribe

Select Categories