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:
