Learn About MEAN Stack

MEAN stack

MongoDB, Express, AngularJS, Node.js are abbreviated as MEAN. These are application development packs all by JavaScript. In this sample, we are going to use MSSQL Server instead of MongoDB.

With this application sample, we can create a new user, show all the users, modify user data, and also delete the user through API built with Express from the front-end using AngularJS.

Components

• MSSQL - SQL database
• Express - NodeJS Framework
• Angular - JavaScript frameworks for front-end
• Node.js - JavaScript Execution Environment (Server)

Dependencies

It’s highly recommended to review these previous posts to be able to follow this post.

• Startup NodeJS
• Package Manager NodeJS
• Basic Templating

Let’s create a database in MSSQL server for user data. After creating the database, 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 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 application development plan. We are going to start from our previous sample application.

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 - Installation 

Right-click on project and open Command Prompt here.

Run this command for installing MSSQL.

npm install mssql

Run this command for installing body-parser.

npm install body-parser

After completing those installations, we need to add those to our Node server. Here, we have added it by enabling the following command.

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

Then, we have used the following code.

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

Operations are going to be performed through APIs using Express route. Let’s get started with creating the API process.

APIs

Let’s create a data service to perform the operations in the 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 that 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 and the min is 0 and the timeout is 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 the 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 the 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 agents 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 a user interface to operate data in the database. First, we need to create a folder for a specific module like below.

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

HTML View

This View is going to render while the routing state is changed.

<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 used $http service to communicate with the APIs. Mehods that were used are as shown below.

• $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 the files published. 

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 the Task Explorer in Visual Studio like the below image.

Run the task. This will copy all our application files to a published folder.

Go to Publish folder.

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

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

Output

Source Code

I’ve uploaded the full source code to download/clone at GitHub. Hope this will help.