Sql Server with NodeJs CRUD Operations

by Priyanka Arora
May 25, 2020
1478 Views
1
SQL Server with NodeJs is a guide every developer needs who are switching their server from dot net, PHP, etc to NodeJs. Here, we will create a nodejs application from scratch so even you have not worked with nodejs before, you should be good to go. You need to have SQL server and studio installed which we will allow on TCP/IP protocols for connectivity. we will be using mssql node module to perform crud operations in the database. So, let’s get started with the tutorial.

Set up SQL Server on TCP/IP Protocol

We would be using server authentication which requires username and password also the server needs to be hosted for which go to Sql Server Configuration Manager from the start menu and enable the TCP/IP port as depicted in image below. This is a very important step without which the connection won’t establish.

Create a database in the server named test. And create a table with name Student , we have added two fields id, fullname here.
Sql Server with NodeJs CRUD Operations

2.Set up NodeJS application

As promised in this SQL server with nodejs tutorial we are going to create nodejs application from the start.

So, nodejs is javascript based server engine where you write all the server-side code for your applications. To set up the application, first, make sure you have installed node.js and npm in your system,

then we need to create a folder, in that folder type npm init to initialize the repository as node.js application. you should see package.json file in the directory.

Create app.js file and type the following code

var msg=’Hello world!’;

console.log(msg)

Now, to run the application type node app.js

Now, you have successfully set up your node application.But here we will also use express which the most popular framework for node.js and set up the server at port 5000.

So, type npm install express on the terminal

Add the following code for the server to establish.

var express = require(‘express’);
var app = express();
app.get(‘/’, function (req, res)  {
    res.send(‘<h1>Hello World!</h1>’)
});
var server =  app.listen(5000, function () {
     console.log(‘Server is running..’);
});

Run using node app.js. You should see server is running.. on the terminal and on browser type localhost:5000 you should see Hello World! Now you have successfully set up your node application.


3.Connect  SQL Server with NodeJs

Now, let’s look at connecting SQL server with NodeJs. First step is to install node modules mssql. Type npm install mssql in the terminal

And, now is the time to add the config variable. Always config is kept in a separate file as it becomes easier to change the credentials when kept at single place. But, here we are keeping it simple and at the same app.js. Also, replace your IP Address which you can find in ipconfig, server authentication username and password. 

If there is no error on console, you are successfully connected to the SQL server database. Now, you just need to add queries. As tougher part of SQL server with nodejs is now done.

var express = require(‘express’);
var app = express();
 var sql = require(“mssql”);
app.get(‘/’, function (req, res) {
    res.send(‘<h1>Hello World!</h1>’)
      // // config for your database
    var config = {
        user: ‘sqluser’,
        password: xxxx’,
        server: ‘1xx.1xx.x.xxx’, 
        database: ‘test’ ,
        port:1433
    };
    sql.connect(config, function (err) {
    
            if (err) console.log(err);
        });
});
var server = app.listen(5000, function () {
    console.log(‘Server is running..’);
});
Sql Server with NodeJs CRUD Operations

4.Insert Operation in SQL server with NodeJs

var express = require(‘express’);
var app = express();
 var sql = require(“mssql”);
app.get(‘/’, function (req, res) {
      // // config for your database
    var config = {
        user: ‘sqluser’,
        password: ‘xxxx’,
        server: ‘1xx.1xx.1.2xx’, 
        database: ‘test’ ,
        port:1433
    };
    sql.connect(config, function (err) {
            if (err) console.log(err);
            // create Request object
            var request = new sql.Request();
            request.query(“insert into Student(id,fullname)values(5,’Nidhi’)”, function (err, recordset) {
                if (err) console.log(err)
                // send records as a response
                res.send(“Inserted”);
            });
        });
});
var server = app.listen(5000, function () {
    console.log(‘Server is running..’);
});
One row should be inserted in the database. Also on browser you should see Inserted as message.

5.Update Operation in SQL Server with NodeJs

var express = require(‘express’);
var app = express();
 var sql = require(“mssql”);
app.get(‘/’, function (req, res) {
      // // config for your database
    var config = {
        user: ‘sqluser’,
        password: ‘xxxx’,
        server: ‘1xx.1xx.1.2xx’, 
        database: ‘test’ ,
        port:1433
    };
    sql.connect(config, function (err) {
    
            if (err) console.log(err);
            
            // create Request object
            var request = new sql.Request();

            request.query(“

update Student set fullname=’nidhi2′ where id=2

“, function (err, recordset) {

                
                if (err) console.log(err)
                // send records as a response
                res.send(“Updated”);
                
            });
        });
});
var server = app.listen(5000, function () {
    console.log(‘Server is running..’);
});

Record with id=2 would be updated with output on screen as Updated

6.Delete Operation in SQL Server with NodeJs

var express = require(‘express’);
var app = express();
 var sql = require(“mssql”);
app.get(‘/’, function (req, res) {
      // // config for your database
    var config = {
        user: ‘sqluser’,
        password: ‘xxxx’,
        server: ‘1xx.1xx.1.2xx’, 
        database: ‘test’ ,
        port:1433
    };
    sql.connect(config, function (err) {
    
            if (err) console.log(err);
            
            // create Request object
            var request = new sql.Request();

            request.query(“

delete from Student where id=2“, function (err, recordset) {
                
                if (err) console.log(err)
              
                res.send(“Deleted”);
                
            });
        });
});
var server = app.listen(5000, function () {
    console.log(‘Server is running..’);
});

Record with id=2 would be deleted with output on screen as Deleted


7.Select Operation in SQL Server with NodeJs

var express = require(‘express’);
var app = express();
 var sql = require(“mssql”);
app.get(‘/’, function (req, res) {
      // // config for your database
    var config = {
        user: ‘sqluser’,
        password: ‘xxxx’,
        server: ‘1xx.1xx.1.2xx’, 
        database: ‘test’ ,
        port:1433
    };
    sql.connect(config, function (err) {
    
            if (err) console.log(err);
            
            // create Request object
            var request = new sql.Request();

            request.query(“

select * from Student

“, function (err, recordset) {

                
                if (err) console.log(err)
                // send records as a response
                res.send(recordset);
                
            });
        });
});
var server = app.listen(5000, function () {
    console.log(‘Server is running..’);
});

Now, you will see the output of json objects on the screen

This was short tutorial on SQL server with nodejs. You are now set you use in your existing projects. Happy Learning😊


Frequently Asked Questions

Mongodb is nosql based database and based on document structure providing more flexibility than sqlserver

Yes the query part remain exact same.

Yes. You can find that post in similar post below

Upload the file on server and store its link in the database

Similar Posts

Mean stack tutorial in 30 minutes-2020
May 25, 2020, 1478 Views, 1
Express Js Routing Tutorial | Beginners
May 25, 2020, 1478 Views, 1
Mysql with NodeJs Connection and CRUD
May 25, 2020, 1478 Views, 1
MongoDB with NodeJs CRUD Tutorial Guide
May 25, 2020, 1478 Views, 1

Comments Section

Viraj
Nov 12, 2020

Nicely written. Worked in first go!