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.

Let’s create a database in MSSQL server for user data. After creating the database, execute the below query by using that DB.

  1. CREATE TABLE [dbo].[User](  
  2.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [nvarchar](250) NULL,  
  4.     [Email] [nvarchar](250) NULL,  
  5.     [Phone] [nvarchar](50) NULL,  
  6.  CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED   
  7. (  
  8.     [Id] ASC  
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  10. ON [PRIMARY]  
  11. GO  
  12. 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.  
  13. CREATE PROCEDURE [dbo].[GetUsers]  
  14.     -- Add the parameters for the stored procedure here  
  15.       
  16. AS  
  17. BEGIN  
  18.     -- SET NOCOUNT ON added to prevent extra result sets from  
  19.     -- interfering with SELECT statements.  
  20.     SET NOCOUNT ON;  
  21.   
  22.     -- Insert statements for procedure here  
  23.     SELECT * FROM [dbo].[User]  
  24. END  
  25. GO  
  26.   
  27. CREATE PROCEDURE [dbo].[GetUserById]  
  28.     @Id Int  
  29. AS  
  30. BEGIN  
  31.     -- SET NOCOUNT ON added to prevent extra result sets from  
  32.     -- interfering with SELECT statements.  
  33.     SET NOCOUNT ON;  
  34.   
  35.     -- Insert statements for procedure here  
  36.     SELECT * FROM [dbo].[UserWHERE Id = @Id  
  37. END  
  38. GO  
  39. CREATE PROCEDURE [dbo].[PutUser]  
  40.     -- Add the parameters for the stored procedure here  
  41.     @Id INT,  
  42.     @Name NVarchar(250),  
  43.     @Email NVarchar(250),  
  44.     @Phone NVarchar(50)  
  45. AS  
  46. BEGIN  
  47.     -- SET NOCOUNT ON added to prevent extra result sets from  
  48.     -- interfering with SELECT statements.  
  49.     SET NOCOUNT ON;  
  50.   
  51.     -- Insert statements for procedure here  
  52.     Update [dbo].[User]   
  53.     SET [Name] = @Name,[Email] = @Email,[Phone] = @Phone  
  54.     WHERE [Id] = @Id  
  55. END  
  56. GO  
  57. CREATE PROCEDURE [dbo].[SetUser]  
  58.     -- Add the parameters for the stored procedure here  
  59.     @Name NVarchar(250),  
  60.     @Email NVarchar(250),  
  61.     @Phone NVarchar(50)  
  62. AS  
  63. BEGIN  
  64.     -- SET NOCOUNT ON added to prevent extra result sets from  
  65.     -- interfering with SELECT statements.  
  66.     SET NOCOUNT ON;  
  67.   
  68.     -- Insert statements for procedure here  
  69.     INSERT INTO [dbo].[User]([Name],[Email],[Phone])  
  70.     VALUES(@Name,@Email,@Phone)  
  71.   
  72. END  
  73. GO  
  74.   
  75. CREATE PROCEDURE [dbo].[DeleteUser]  
  76.     -- Add the parameters for the stored procedure here  
  77.     @Id Int  
  78. AS  
  79. BEGIN  
  80.     -- SET NOCOUNT ON added to prevent extra result sets from  
  81.     -- interfering with SELECT statements.  
  82.     SET NOCOUNT ON;  
  83.   
  84.     -- Insert statements for procedure here  
  85.     DELETE FROM [dbo].[User]  
  86.     WHERE [Id] = @Id  
  87. END  
  88.   
  89. --Exec DeleteUser 1  
  90. 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.

  1. //Body Parser Middleware  
  2. app.use(bodyParser.json());  
  3. 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. 

MEAN stack

Open the newly added JS file, then add this line.

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

  1. var dbConfig = {  
  2.     user: "sa",  
  3.     password: "sa@12345",  
  4.     server: "DESKTOP-80DEJMQ",  
  5.     database: "dbNode",  
  6.     pool: {  
  7.         max: 10,  
  8.         min: 0,  
  9.         idleTimeoutMillis: 30000  
  10.     }  
  11. };  

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

  1. var executeQuery = function (sql, res) {  
  2.     const conn = new mssql.ConnectionPool(dbConfig);  
  3.     conn.connect().then(function () {  
  4.         const req = new mssql.Request(conn);  
  5.         req.query(sql).then(function (data) {  
  6.             res(data);  
  7.         }).catch(function (err) {  
  8.             res(null, err);  
  9.         })  
  10.     }).catch(function (err) {  
  11.         res(null, err);  
  12.     })  
  13. }  

Connections

  1. const conn = new mssql.ConnectionPool(dbConfig);  

We are creating a SQL connection object using ConnectionPool.

Request

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

  1. module.exports = {  
  2.     executeQuery  
  3. }  

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.

  1. var express = require('express');  
  2. var router = express.Router();  
  3. var dbService = require('../dbService');  
  4.   
  5.   
  6. //GET API  
  7. router.get("/api/user/getAll"function (req, res) {  
  8.     var query = "GetUsers";  
  9.     dbService.executeQuery(query, function (data, err) {  
  10.         if (err) {  
  11.             throw err;  
  12.         } else {  
  13.             res.send(data.recordset);  
  14.         }  
  15.         res.end();  
  16.     });  
  17. });  
  18.   
  19. // GET API  
  20. router.get("/api/user/getUser/:id"function (req, res) {  
  21.     var query = "[GetUserById] " + parseInt(req.params.id) + "";  
  22.   
  23.     dbService.executeQuery(query, function (data, err) {  
  24.         if (err) {  
  25.             throw err;  
  26.         } else {  
  27.             res.send(data.recordset);  
  28.         }  
  29.         res.end();  
  30.     });  
  31. });  
  32.   
  33. //POST API  
  34. router.post("/api/user/setUser"function (req, res) {  
  35.     var query = "[SetUser] '" + req.body.Name + "', '" + req.body.Email + "', '" + req.body.Phone + "'";  
  36.     dbService.executeQuery(query, function (data, err) {  
  37.         if (err) {  
  38.             throw err;  
  39.         } else {  
  40.             res.send(data.recordset);  
  41.         }  
  42.         res.end();  
  43.     });  
  44. });  
  45.   
  46. //PUT API  
  47. router.put("/api/user/putUser"function (req, res) {  
  48.     var query = "[PutUser] " + parseInt(req.body.Id) + ", '" + req.body.Name + "','" + req.body.Email + "', '" + req.body.Phone + "'";  
  49.     dbService.executeQuery(query, function (data, err) {  
  50.         if (err) {  
  51.             throw err;  
  52.         } else {  
  53.             res.send(data.recordset);  
  54.         }  
  55.         res.end();  
  56.     });  
  57. });  
  58.   
  59. //DELETE API  
  60. router.delete("/api/user/deleteUser/:id"function (req, res) {  
  61.     var query = "[DeleteUser] " + parseInt(req.params.id) + "";  
  62.   
  63.     dbService.executeQuery(query, function (data, err) {  
  64.         if (err) {  
  65.             throw err;  
  66.         } else {  
  67.             res.send(data.recordset);  
  68.         }  
  69.         res.end();  
  70.     });  
  71. });  
  72.   
  73. module.exports = router;  

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

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

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

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

Finally, this is the overview of Node Server.

  1. 'use strict';  
  2. //var http = require('http');  
  3. var bodyParser = require("body-parser");  
  4. var path = require('path');  
  5. var express = require('express');  
  6. var app = express();  
  7. var port = process.env.port || 3000;  
  8.   
  9. //Body Parser Middleware  
  10. app.use(bodyParser.json());  
  11. app.use(bodyParser.urlencoded({ extended: true }));  
  12.   
  13. //Serve Static Files  
  14. app.use(express.static(path.join(__dirname, 'app')));  
  15. app.use(express.static(path.join(__dirname, 'public')));  
  16.   
  17. //Router Middleware  
  18. app.use('/', require('./data/userService/userDataService'));  
  19.   
  20. //CORS Middleware  
  21. app.use(function (req, res, next) {  
  22.     //Enabling CORS   
  23.     res.header("Access-Control-Allow-Origin""*");  
  24.     res.header("Access-Control-Allow-Methods""GET,HEAD,OPTIONS,POST,PUT");  
  25.     res.header("Access-Control-Allow-Headers""Origin, X-Requested-With, contentType,Content-Type, Accept, Authorization");  
  26.     next();  
  27. });  
  28.   
  29. app.get('/*'function (req, res) {  
  30.     res.sendFile(path.resolve('layout.html'));  
  31. });  
  32.   
  33. app.get('/*'function (req, res) {  
  34.     res.render('error');  
  35. });  
  36.   
  37. var server = app.listen(port, function () {  
  38.     console.log('Node server is running on port..' + port);  
  39. });  

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.

MEAN stack

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.

  1. <div class="container-fluid">  
  2.     <div class="row">  
  3.         <div class="col-sm-4">  
  4.             <h3>Add New User</h3>  
  5.   
  6.             <form name="frmUser" novalidate>  
  7.                 <input type="hidden" ng-model="userModel.Id" name="uid" />  
  8.   
  9.                 <div class="form-group">  
  10.                     <label for="uname" class="control-label">User Name</label>  
  11.                     <input type="text" class="form-control" ng-model="userModel.Name" name="uname" placeholder="" required />  
  12.                     <span class="error" ng-show="(frmUser.$dirty||submitted) && frmUser.uname.$error.required">Customer name is Required</span>  
  13.   
  14.                 </div>  
  15.                 <div class="form-group">  
  16.                     <label for="email" class="control-label">E-mail</label>  
  17.                     <input type="email" class="form-control" ng-model="userModel.Email" name="email" placeholder="" required />  
  18.                     <span class="error" ng-show="(frmUser.$dirty ||submitted) && frmUser.email.$error.required">EmailId is Required!</span>  
  19.                     <span class="error" ng-show="(frmUser.$dirty ||submitted) && frmUser.$error.email">Invalid EmailId!</span>  
  20.   
  21.                 </div>  
  22.                 <div class="form-group">  
  23.                     <label for="phone" class="control-label">Phone</label>  
  24.                     <input type="text" class="form-control" ng-model="userModel.Phone" name="phone" placeholder="" />  
  25.   
  26.                 </div>  
  27.                 <div class="form-group">  
  28.                     <button type="submit" class="btn btn-danger" ng-click="reset()">Reset</button>  
  29.                     <button type="submit" class="btn btn-primary" ng-click="saveUser()" ng-disabled="frmUser.$invalid"  
  30.                             ng-If="userModel.Id == 0">  
  31.                         Create  
  32.                     </button>  
  33.                     <button type="submit" class="btn btn-success" ng-click="updateUser()" ng-disabled="frmUser.$invalid"  
  34.                             ng-If="userModel.Id > 0">  
  35.                         Update  
  36.                     </button>  
  37.                 </div>  
  38.             </form>  
  39.             <span class="warning">{{resmessage}}</span>  
  40.         </div>  
  41.         <div class="col-sm-8">  
  42.             <h3>All User</h3>  
  43.             <table style="width:100%" class="table table-striped">  
  44.                 <tr>  
  45.                     <th>Sr.</th>  
  46.                     <th>Name</th>  
  47.                     <th>Email</th>  
  48.                     <th>Phone</th>  
  49.                     <th>Option</th>  
  50.                 </tr>  
  51.                 <tr ng-repeat="item in ListUser">  
  52.                     <td>{{ $index+1 }}</td>  
  53.                     <td>{{ item.Name }}</td>  
  54.                     <td>{{ item.Email }}</td>  
  55.                     <td>{{ item.Phone }}</td>  
  56.                     <td>  
  57.                         <a href="#" ng-click="getUser(item)" title="Edit Record" class="btn btn-primary btn-xs pull-right">  
  58.                             Edit  
  59.                         </a>  
  60.                         <a href="#" ng-click="deleteUser(item)" title="Delete Record" class="btn btn-danger btn-xs pull-right">  
  61.                             Delete  
  62.                         </a>  
  63.                     </td>  
  64.                 </tr>  
  65.             </table>  
  66.         </div>  
  67.     </div>  
  68. </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.

  1. templatingApp.controller('UserController', ['$scope''$http'function ($scope, $http) {  
  2.     $scope.title = "All User";  
  3.     $scope.ListUser = null;  
  4.     $scope.userModel = {};  
  5.     $scope.userModel.Id = 0;  
  6.     getallData();  
  7.   
  8.     //******=========Get All User=========******  
  9.     function getallData() {  
  10.         $http({  
  11.             method: 'GET',  
  12.             url: '/api/user/getAll/'  
  13.         }).then(function (response) {  
  14.             $scope.ListUser = response.data;  
  15.         }, function (error) {  
  16.             console.log(error);  
  17.         });  
  18.     };  
  19.   
  20.     //******=========Get Single User=========******  
  21.     $scope.getUser = function (user) {  
  22.         $http({  
  23.             method: 'GET',  
  24.             url: '/api/user/getUser/' + parseInt(user.Id)  
  25.         }).then(function (response) {  
  26.             $scope.userModel = response.data[0];  
  27.         }, function (error) {  
  28.             console.log(error);  
  29.         });  
  30.     };  
  31.   
  32.     //******=========Save User=========******  
  33.     $scope.saveUser = function () {  
  34.         $http({  
  35.             method: 'POST',  
  36.             url: '/api/user/setUser/',  
  37.             data: $scope.userModel  
  38.         }).then(function (response) {  
  39.             showNotif("Data Saved")  
  40.             $scope.reset();  
  41.             getallData();  
  42.         }, function (error) {  
  43.             console.log(error);  
  44.         });  
  45.     };  
  46.   
  47.     //******=========Update User=========******  
  48.     $scope.updateUser = function () {  
  49.         $http({  
  50.             method: 'PUT',  
  51.             url: '/api/user/putUser/',  
  52.             data: $scope.userModel  
  53.         }).then(function (response) {  
  54.             showNotif("Data Updated")  
  55.             $scope.reset();  
  56.             getallData();  
  57.         }, function (error) {  
  58.             console.log(error);  
  59.         });  
  60.     };  
  61.   
  62.     //******=========Delete User=========******  
  63.     $scope.deleteUser = function (user) {  
  64.         var IsConf = confirm('You are about to delete ' + user.Name + '. Are you sure?');  
  65.         if (IsConf) {  
  66.             $http({  
  67.                 method: 'DELETE',  
  68.                 url: '/api/user/deleteUser/' + parseInt(user.Id)  
  69.             }).then(function (response) {  
  70.                 showNotif("Data Deleted")  
  71.                 $scope.reset();  
  72.                 getallData();  
  73.             }, function (error) {  
  74.                 console.log(error);  
  75.             });  
  76.         }  
  77.     };  
  78.   
  79.     //******=========Clear Form=========******  
  80.     $scope.reset = function () {  
  81.         var msg = "Form Cleared";  
  82.         $scope.userModel = {};  
  83.         $scope.userModel.Id = 0;  
  84.         showNotif(msg)  
  85.     };  
  86. }]);  

Publishing the App

Let’s go to gulp modification to get the files published. 

  1. gulp.task('publish'function () {  
  2.     gulp.src('layout.html')  
  3.         .pipe(gulp.dest(paths.publish));  
  4.     gulp.src('package.json')  
  5.         .pipe(gulp.dest(paths.publish));  
  6.     gulp.src('server.js')  
  7.         .pipe(gulp.dest(paths.publish));  
  8.   
  9.     gulp.src('app/**/*')  
  10.         .pipe(gulp.dest(paths.publish + 'app'));  
  11.     gulp.src('public/**/*')  
  12.         .pipe(gulp.dest(paths.publish + 'public'));  
  13.     gulp.src('data/**/*')  
  14.         .pipe(gulp.dest(paths.publish + 'data'));  
  15.     gulp.src('bin/**/*')  
  16.         .pipe(gulp.dest(paths.publish + 'bin'));  
  17. });  

Go to the Task Explorer in Visual Studio like the below image.

MEAN stack

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

MEAN stack

Go to Publish folder.

MEAN stack

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.

MEAN stack

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

Output

MEAN stack

Source Code

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