October 1, 2020
Sql Server with NodeJs CRUD Operations

Sql Server with NodeJs CRUD Operations

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.

1.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.

Sql Server with NodeJs CRUD Operations

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.

MongoDB with NodeJs CRUD Tutorial Guide

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

MongoDB with NodeJs CRUD Tutorial Guide

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

Sql Server with NodeJs CRUD Operations

Add the following code for the server to establish.

Sql Server with NodeJs CRUD Operations

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.

Sql Server with NodeJs CRUD Operations

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

Sql Server with NodeJs CRUD Operations

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. 

Sql Server with NodeJs CRUD Operations

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..’);
});

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..’);
});

Sql Server with NodeJs CRUD Operations

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😊

Similar Posts:

 

 

 

admin

Engineer Diaries started with the need to bridge the huge gap in what we are taught vs what the industry demands. We are based in Delhi, India but our blog is for everyone, in and outside tech industry❤ Feel free to reach out to us at engineerdiaries@gmail.com for any business/personal query.

View all posts by admin →

Leave a Reply