MEAN Stack

• Download sample - 6.5 MB

With this application sample, we can create a new user, show all the users, modify user data & also delete the user through API build with Express from frontend using AngularJS.

Components

• MSSql - SQL database
• Express - NodeJS Framework
• Angular - JavaScript frameworks for Frontend
• Node.js - JavaScript Execution environment (Server)

Dependencies

It’s highly recommended to review those previous posts to follow this post:

• Startup NodeJS
• Package Manager NodeJS
• Basic Templating

Let’s create a database in MSSql server for user data storing which is going to operate by our application. After creating the db, execute the below query by using that db.

CREATE TABLE [dbo].[User](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](250) NULL,
	[Email] [nvarchar](250) NULL,
	[Phone] [nvarchar](50) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

This will create a new table named user. After that, we need to create some stored procedure for SQL operation, copy the below script and execute those by pasting it in MSSql query window.

CREATE PROCEDURE [dbo].[GetUsers]
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM [dbo].[User]
END
GO

CREATE PROCEDURE [dbo].[GetUserById]
	@Id Int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM [dbo].[User] WHERE Id = @Id
END
GO
CREATE PROCEDURE [dbo].[PutUser]
	-- Add the parameters for the stored procedure here
	@Id INT,
	@Name NVarchar(250),
	@Email NVarchar(250),
	@Phone NVarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	Update [dbo].[User] 
	SET [Name] = @Name,[Email] = @Email,[Phone] = @Phone
	WHERE [Id] = @Id
END
GO
CREATE PROCEDURE [dbo].[SetUser]
	-- Add the parameters for the stored procedure here
	@Name NVarchar(250),
	@Email NVarchar(250),
	@Phone NVarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO [dbo].[User]([Name],[Email],[Phone])
	VALUES(@Name,@Email,@Phone)

END
GO

CREATE PROCEDURE [dbo].[DeleteUser]
	-- Add the parameters for the stored procedure here
	@Id Int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DELETE FROM [dbo].[User]
	WHERE [Id] = @Id
END

--Exec DeleteUser 1
GO

We are done with the database work. Let’s start with the application development plan. From our previous sample of application, we are going to start.

Download it from GitHub, then open the application using Visual Studio 2017. We need to install two more packages to fulfill our requirements.

• mssql - Microsoft SQL Server client for Node.js
• body-parser - Node.js body parsing middleware

Server-Side

Package Installation

Right click on project, Go to > Open Command Prompt here. Run this command for mssql.

npm install mssql

Run this command for body-parser.

npm install body-parser

After completing this installation, we need to add those to our node server. Here, we have added by enabling.

var bodyParser = require("body-parser");

Then, we have to use it to our application.

//Body Parser Middleware
app.use(bodyParser.json()
app.use(bodyParser.urlencoded({ extended: true }));

In this sample, operations are going to perform through APIs using Express route. Let’s get started with creating API’s process.

APIs

Let’s create data service to perform the operations in database. Add a common dbService.js file to serve the requests like the below image:

Open the newly added js file, then add this line:

var mssql = require('mssql');

This means we are requiring mssql module by require() function to load the source code in a new function.

After that, we are adding the database server connection configuration:

var dbConfig = {
    user: "sa",
    password: "sa@12345",
    server: "DESKTOP-80DEJMQ",
    database: "dbNode",
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000
    }
};

As we can see, the max pool is 10 & min is 0 & timeout in milliseconds before closing an unused connection which is default.

Get more details on Pool: https://github.com/coopernurse/node-pool

var executeQuery = function (sql, res) {
    const conn = new mssql.ConnectionPool(dbConfig);
    conn.connect().then(function () {
        const req = new mssql.Request(conn);
        req.query(sql).then(function (data) {
            res(data);
        }).catch(function (err) {
            res(null, err);
        })
    }).catch(function (err) {
        res(null, err);
    })
}

Connections

const conn = new mssql.ConnectionPool(dbConfig);

We are creating a SQL connection object using connectionpool.

Request

const req = new mssql.Request(conn);

Then we are executing the request using the global connection pool.

Get more details on connection: https://www.npmjs.com/package/mssql#connections-1

Finally, we are exporting the module for another module call.

module.exports = {
    executeQuery
}

Let’s create a specific data service using Express router. Create a new JS file, then add those code snippets below to newly added file.

var express = require('express');
var router = express.Router();
var dbService = require('../dbService');

//GET API
router.get("/api/user/getAll", function (req, res) {
    var query = "GetUsers";
    dbService.executeQuery(query, function (data, err) {
        if (err) {
            throw err;
        } else {
            res.send(data.recordset);
        }
        res.end();
    });
});

// GET API
router.get("/api/user/getUser/:id", function (req, res) {
    var query = "[GetUserById] " + parseInt(req.params.id) + "";

    dbService.executeQuery(query, function (data, err) {
        if (err) {
            throw err;
        } else {
            res.send(data.recordset);
        }
        res.end();
    });
});

//POST API
router.post("/api/user/setUser", function (req, res) {
    var query = "[SetUser] '" + req.body.Name + "', '" + 
                  req.body.Email + "', '" + req.body.Phone + "'";
    dbService.executeQuery(query, function (data, err) {
        if (err) {
            throw err;
        } else {
            res.send(data.recordset);
        }
        res.end();
    });
});

//PUT API
router.put("/api/user/putUser", function (req, res) {
    var query = "[PutUser] " + parseInt(req.body.Id) + ", '" + 
    req.body.Name + "','" + req.body.Email + "', '" + req.body.Phone + "'";
    dbService.executeQuery(query, function (data, err) {
        if (err) {
            throw err;
        } else {
            res.send(data.recordset);
        }
        res.end();
    });
});

//DELETE API
router.delete("/api/user/deleteUser/:id", function (req, res) {
    var query = "[DeleteUser] " + parseInt(req.params.id) + "";

    dbService.executeQuery(query, function (data, err) {
        if (err) {
            throw err;
        } else {
            res.send(data.recordset);
        }
        res.end();
    });
});

module.exports = router;

After finishing all those, we need to enable by app.use() function in the node server as middleware.

//Router Middleware
app.use('/', require('./data/userService/userDataService'));

Also, we are giving access to other user agent by enabling Cross-Origin Resource Sharing (CORS).

//CORS Middleware
app.use(function (req, res, next) {
    //Enabling CORS 
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Methods", "GET,HEAD,OPTIONS,POST,PUT");
    res.header("Access-Control-Allow-Headers", 
    "Origin, X-Requested-With, contentType,Content-Type, Accept, Authorization");
    next();
}); 

Finally, this is the overview of Node Server:

'use strict';
//var http = require('http');
var bodyParser = require("body-parser");
var path = require('path');
var express = require('express');
var app = express();
var port = process.env.port || 3000;

//Body Parser Middleware
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));

//Serve Static Files
app.use(express.static(path.join(__dirname, 'app')));
app.use(express.static(path.join(__dirname, 'public')));

//Router Middleware
app.use('/', require('./data/userService/userDataService'));

//CORS Middleware
app.use(function (req, res, next) {
    //Enabling CORS 
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Methods", "GET,HEAD,OPTIONS,POST,PUT");
    res.header("Access-Control-Allow-Headers", "Origin, 
    X-Requested-With, contentType,Content-Type, Accept, Authorization");
    next();
});

app.get('/*', function (req, res) {
    res.sendFile(path.resolve('layout.html'));
});

app.get('/*', function (req, res) {
    res.render('error');
});

var server = app.listen(port, function () {
    console.log('Node server is running on port..' + port);
});

Client-Side

We need to create user interface to operate data in database. First, we need create folder for specific module like below:

As you can see, we have created an AngularJS controller with HTML page.

Html View

<div class="container-fluid">
    <div class="row">
        <div class="col-sm-4">
            <h3>Add New User</h3>

            <form name="frmUser" novalidate>
                <input type="hidden" ng-model="userModel.Id" name="uid" />

                <div class="form-group">
                    <label for="uname" class="control-label">User Name</label>
                    <input type="text" class="form-control" 
                     ng-model="userModel.Name" name="uname" placeholder="" required />
                    <span class="error" ng-show="(frmUser.$dirty||submitted) && 
                     frmUser.uname.$error.required">Customer name is Required</span>

                </div>
                <div class="form-group">
                    <label for="email" class="control-label">E-mail</label>
                    <input type="email" class="form-control" 
                     ng-model="userModel.Email" name="email" 
                     placeholder="" required />
                    <span class="error" ng-show="(frmUser.$dirty ||submitted) && 
                     frmUser.email.$error.required">EmailId is Required!</span>
                    <span class="error" ng-show="(frmUser.$dirty ||submitted) && 
                     frmUser.$error.email">Invalid EmailId!</span>

                </div>
                <div class="form-group">
                    <label for="phone" class="control-label">Phone</label>
                    <input type="text" class="form-control" 
                     ng-model="userModel.Phone" name="phone" placeholder="" />

                </div>
                <div class="form-group">
                    <button type="submit" class="btn btn-danger" 
                     ng-click="reset()">Reset</button>
                    <button type="submit" class="btn btn-primary" 
                     ng-click="saveUser()" ng-disabled="frmUser.$invalid"
                            ng-If="userModel.Id == 0">
                        Create
                    </button>
                    <button type="submit" class="btn btn-success" 
                     ng-click="updateUser()" ng-disabled="frmUser.$invalid"
                            ng-If="userModel.Id > 0">
                        Update
                    </button>
                </div>
            </form>
            <span class="warning">{{resmessage}}</span>
        </div>
        <div class="col-sm-8">
            <h3>All User</h3>
            <table style="width:100%" class="table table-striped">
                <tr>
                    <th>Sr.</th>
                    <th>Name</th>
                    <th>Email</th>
                    <th>Phone</th>
                    <th>Option</th>
                </tr>
                <tr ng-repeat="item in ListUser">
                    <td>{{ $index+1 }}</td>
                    <td>{{ item.Name }}</td>
                    <td>{{ item.Email }}</td>
                    <td>{{ item.Phone }}</td>
                    <td>
                        <a href="#" ng-click="getUser(item)" 
                         title="Edit Record" class="btn btn-primary btn-xs pull-right">
                            Edit
                        </a>
                        <a href="#" ng-click="deleteUser(item)" 
                         title="Delete Record" class="btn btn-danger btn-xs pull-right">
                            Delete
                        </a>
                    </td>
                </tr>
            </table>
        </div>
    </div>
</div>

AngularJS Controller

In our AngularJS controller, we have to use $http service to communicate with the APIs. Methods that are used:

• $http.get: get data
• $http.post: post new data
• $http.put: update existing data
• $http.delete: delete existing data

More about $http service here.

templatingApp.controller('UserController', 
    ['$scope', '$http', function ($scope, $http) {
    $scope.title = "All User";
    $scope.ListUser = null;
    $scope.userModel = {};
    $scope.userModel.Id = 0;
    getallData();

    //******=========Get All User=========******
    function getallData() {
        $http({
            method: 'GET',
            url: '/api/user/getAll/'
        }).then(function (response) {
            $scope.ListUser = response.data;
        }, function (error) {
            console.log(error);
        });
    };

    //******=========Get Single User=========******
    $scope.getUser = function (user) {
        $http({
            method: 'GET',
            url: '/api/user/getUser/' + parseInt(user.Id)
        }).then(function (response) {
            $scope.userModel = response.data[0];
        }, function (error) {
            console.log(error);
        });
    };

    //******=========Save User=========******
    $scope.saveUser = function () {
        $http({
            method: 'POST',
            url: '/api/user/setUser/',
            data: $scope.userModel
        }).then(function (response) {
            showNotif("Data Saved")
            $scope.reset();
            getallData();
        }, function (error) {
            console.log(error);
        });
    };

    //******=========Update User=========******
    $scope.updateUser = function () {
        $http({
            method: 'PUT',
            url: '/api/user/putUser/',
            data: $scope.userModel
        }).then(function (response) {
            showNotif("Data Updated")
            $scope.reset();
            getallData();
        }, function (error) {
            console.log(error);
        });
    };

    //******=========Delete User=========******
    $scope.deleteUser = function (user) {
        var IsConf = confirm('You are about to delete ' + 
                              user.Name + '. Are you sure?');
        if (IsConf) {
            $http({
                method: 'DELETE',
                url: '/api/user/deleteUser/' + parseInt(user.Id)
            }).then(function (response) {
                showNotif("Data Deleted")
                $scope.reset();
                getallData();
            }, function (error) {
                console.log(error);
            });
        }
    };

    //******=========Clear Form=========******
    $scope.reset = function () {
        var msg = "Form Cleared";
        $scope.userModel = {};
        $scope.userModel.Id = 0;
        showNotif(msg)
    };
}]);

Publishing the App

Let’s go to gulp modification to get publish files finally.

    gulp.task('publish', function () {
    gulp.src('layout.html')
        .pipe(gulp.dest(paths.publish));
    gulp.src('package.json')
        .pipe(gulp.dest(paths.publish));
    gulp.src('server.js')
        .pipe(gulp.dest(paths.publish));

    gulp.src('app/**/*')
        .pipe(gulp.dest(paths.publish + 'app'));
    gulp.src('public/**/*')
        .pipe(gulp.dest(paths.publish + 'public'));
    gulp.src('data/**/*')
        .pipe(gulp.dest(paths.publish + 'data'));
    gulp.src('bin/**/*')
        .pipe(gulp.dest(paths.publish + 'bin'));
});

Go to task explorer in Visual Studio like the below image:

Run the task, this will copy all our application files to the published folder.

Go to Publish folder:

Open command prompt here (Shift + Right Mouse), then type nodemon. We are starting our application using nodemon. If we have any change in our application, nodemon will automatically restart the application.

Now open the browser, type the URL: http://localhost:3000.

Output

Hope this will help. :)

History

• 21^st February, 2018: Initial version