NodeJS/Express – POST/GET to MySQL using Sequelize – AngularJS + Bootstrap form example

In the tutorial, we show how to POST/GET form data to/from MySQL in NodeJS/Express application using Sequelize with AngularJS & Bootstrap view.

Related posts:
NodeJS/Express – POST/GET form data to MySQL using Sequelize ORM – Ajax JQuery + Bootstrap view

Goal

Technologies

– NodeJS/Express
– Sequelize
– MySQL
– AngularJS
– Bootstrap

We create a NodeJS/Express project as below structure:


/NodeJS-Express-Angular-Bootstrap-MySQL
	/app
		/config
			env.js
			db.config.js
		/controllers
			user.controller.js
		/models
			user.model.js
		/routes
			user.route.js
	/node_modules
	/resources
		/static
			/js	
				controller.js
	/views
		404.html
		index.html
	server.js
	package.json

Run above project then makes POST/GET requests, results:

-> Bootstrap view:

NodeJS-Express-AngularJS-Bootstrap-Sequelize-MySQL-POST-GET-views

-> MySQL’s table:

NodeJS-Express-AngularJS-Bootstrap-Sequelize-MySQL-POST-GET-records

Practice

Setting up NodeJS/Express project

Create a folder: ‘NodeJS-Express-Angular-Bootstrap-MySQL’:


mkdir NodeJS-Express-Angular-Bootstrap-MySQL
cd NodeJS-Express-Angular-Bootstrap-MySQL

Then init NodeJS project, see prompts:


NodeJS-Express-Angular-Bootstrap-MySQL>npm init
This utility will walk you through creating a package.json file.
It only covers the most common items, and tries to guess sensible defaults.

See `npm help json` for definitive documentation on these fields
and exactly what they do.

Use `npm install ` afterwards to install a package and
save it as a dependency in the package.json file.

Press ^C at any time to quit.
package name: (nodejs-express-angular-bootstrap-mysql)
version: (1.0.0)
description: Building a NodeJS Express web-application to POST/GET data to/from MySQL database - using Sequelize + AngularJS +  Bootstrap view
entry point: (index.js) server.js
test command:
git repository:
keywords: NodeJS, Express, Bootstrap, AngularJS, Sequelize, MySQL
author: grokonez.com
license: (ISC)
About to write to C:\Users\pc\Desktop\nodejs\NodeJS-Express-Angular-Bootstrap-MySQL\package.json:

{
  "name": "nodejs-express-angular-bootstrap-mysql",
  "version": "1.0.0",
  "description": "Building a NodeJS Express web-application to POST/GET data to/from MySQL database - using Sequelize + AngularJS +  Bootstrap view",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "NodeJS",
    "Express",
    "Bootstrap",
    "AngularJS",
    "Sequelize",
    "MySQL"
  ],
  "author": "grokonez.com",
  "license": "ISC"
}


Is this ok? (yes) yes

-> Install Express, Body-Parser, Sequelize, MySQL:


npm install express body-parser sequelize mysql2 --save

-> see package.json file:


{
  "name": "nodejs-express-angular-bootstrap-mysql",
  "version": "1.0.0",
  "description": "Building a NodeJS Express web-application to POST/GET data to/from MySQL database - using Sequelize + AngularJS +  Bootstrap view",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "NodeJS",
    "Express",
    "Bootstrap",
    "AngularJS",
    "Sequelize",
    "MySQL"
  ],
  "author": "grokonez.com",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.18.2",
    "express": "^4.16.3",
    "mysql2": "^1.5.3",
    "sequelize": "^4.37.7"
  }
}

Frontend

Create Bootstrap views

./view/index.html file:

<!DOCTYPE html>
<html lang="en">
<head>
  <title>AngularJS - Bootstrap - NodeJS/Express - MongoDB</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.6.9/angular.min.js"></script>
  <script src="/static/js/controller.js"></script>
</head>
 
<body> 
<div class="container" ng-app="app"> 
<div class="col-sm-4">
	<h3>NodeJS/Express - Post/Get</h3>
	<div ng-controller="postController">
		<div>		
			<form name="userForm" ng-submit="submitUserForm()">
			  <div class="form-group">
				<label for="firstname">FirstName:</label>
				<input ng-model="firstname" type="text" class="form-control" id="firstname" placeholder="Enter FirstName"/>
			  </div>
			  <div class="form-group">
				<label for="lastname">LastName:</label>
				<input ng-model="lastname" type="text" class="form-control" id="lastname" placeholder="Enter LastName"/>
			  </div>
			  <button type="submit" class="btn btn-default">Submit</button>
			</form>
		</div>
		
		<div>{{postMsg}}</div>
		<div>{{postErrMsg}}</div>
	</div>
	<br>
	<div ng-controller="getController">
		<button ng-click="getAllUsers()" type="button" class="btn btn-default">Users</button>
		<ul>
			<li ng-repeat="usr in users">{{usr.id + ". " + usr.firstname + " " + usr.lastname}}</li>
		</ul>
		<div>{{getErrMsg}}</div>
	</div>
</div>	
</div>
</body>
</html>

./views/404.html file:

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Bootstrap Example</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
  
<div class="jumbotron text-center">
  <h1>404 Error!</h1>
  <p>PAGE NOT FOUND</p>
</div>
</body>
</html>

Implement AngularJS to Post/Get data

– For AngularJS Ajax POST/GET request, we implement a file ./resources/static/js/controller.js:


var app = angular.module('app', []);
 
//app.controller('postcontroller', function($scope, $http, $location) {
app.controller('postController', ($scope, $http, $location) => {
	$scope.submitUserForm = function(){
		var url = "/api/users/save";
		
		var config = {
                headers : {
                    'Accept': 'text/plain'
                }
        }
		var data = {
            firstname: $scope.firstname,
            lastname: $scope.lastname
        };
		
		//$http.post(url, data, config).then(function (response) {
		$http.post(url, data, config).then((response) => {
			var user = response.data;
			$scope.postMsg = "Posted User: "  + user.firstname + " " + user.lastname;
		}, (response) => {
			$scope.postErrMsg = "Error Status: " +  response.statusText;
		});
		
		// reset data fields
		$scope.firstname = "";
		$scope.lastname = "";
	}
});
 
app.controller('getController', ($scope, $http, $location) =>{
	$scope.getAllUsers = function(){
		var url = "/api/users/all";
		
		$http.get(url).then((response) => {
			$scope.users = response.data;
		}, (response) => {
			$scope.getErrMsg = "Error Status: " +  response.statusText;
		});
	}
});

Backend

Create Sequelize model

./app/models/user.model.js file:


module.exports = (sequelize, Sequelize) => {
	const User = sequelize.define('user', {
	  firstname: {
		  type: Sequelize.STRING
	  },
	  lastname: {
		  type: Sequelize.STRING
	  }
	});
	
	return User;
}

Configure MySQL connection

./app/config/env.js file:


const env = {
  database: 'testdb',
  username: 'root',
  password: '12345',
  host: 'localhost',
  dialect: 'mysql',
  pool: {
	  max: 5,
	  min: 0,
	  acquire: 30000,
	  idle: 10000
  }
};
 
module.exports = env;

./app/config/db.config.js file:


const env = require('./env.js');
 
const Sequelize = require('sequelize');
const sequelize = new Sequelize(env.database, env.username, env.password, {
  host: env.host,
  dialect: env.dialect,
  operatorsAliases: false,
 
  pool: {
    max: env.max,
    min: env.pool.min,
    acquire: env.pool.acquire,
    idle: env.pool.idle
  }
});
 
const db = {};
 
db.Sequelize = Sequelize;
db.sequelize = sequelize;
 
db.user = require('../models/user.model.js')(sequelize, Sequelize);
 
 
module.exports = db;

Create Express Routes

./app/routes/user.route.js file:


module.exports = function(app) {
 
	var express = require("express");
	var router = express.Router();
	
    const users = require('../controllers/user.controller.js');
	
	var path = __basedir + '/views/';
	
	router.use(function (req,res,next) {
		console.log("/" + req.method);
		next();
	});
	
	app.get('/', (req,res) => {
		res.sendFile(path + "index.html");
	});
 
    // Save a User to MySQL
    app.post('/api/users/save', users.save);
 
    // Retrieve all Users
    app.get('/api/users/all', users.findAll);
	
	app.use("/",router);
 
	app.use("*", (req,res) => {
		res.sendFile(path + "404.html");
	});
}

Implement Controllers

./app/controllers/user.controller.js file:


const db = require('../config/db.config.js');
const User = db.user;
 
// Save FormData - User to MySQL
exports.save = (req, res) => {
	console.log('Post a Customer: ' + JSON.stringify(req.body));
	
	User.create({
		firstname: req.body.firstname,
		lastname: req.body.lastname,
	},{
		attributes: {include: ['firstname', 'lastname']}
	}).then(user => {
		res.send(user);
	})
};
 
// Fetch all Users
exports.findAll = (req, res) => {
	console.log("Get All Customers");
	User.findAll({
		attributes: ['id', 'firstname', 'lastname']
	}).then(users => {
	   res.send(users);
	});
};

Implement Server.js

./server.js file:


var express = require('express');
var app = express();
var bodyParser = require('body-parser');
app.use(bodyParser.json());
 
app.use(express.static('resources'));
 
global.__basedir = __dirname;
 
const db = require('./app/config/db.config.js');
 
// force: true will drop the table if it already exists
db.sequelize.sync({force: true}).then(() => {
  console.log('Drop and Resync with { force: true }');
}); 
 
require('./app/routes/user.route.js')(app);
 
// Create a Server
var server = app.listen(8081, function () {
 
  var host = server.address().address
  var port = server.address().port
 
  console.log("App listening at http://%s:%s", host, port)
 
})

Run & Check results

Start NodeJS server:
-> Logs:


App listening at http://:::8081
Executing (default): DROP TABLE IF EXISTS `users`;
Executing (default): DROP TABLE IF EXISTS `users`;
Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER NOT NULL auto_increment , `firstname` VARCHAR(255), `lastname` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `users`
Drop and Resync with { force: true }

-> Post’s Logs:


Post a Customer: {"firstname":"Jack","lastname":"Davis"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Jack','Davis','2018-05-05 19:26:52','2018-05-05 19:26:52');
Post a Customer: {"firstname":"Mary","lastname":"Taylor"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Mary','Taylor','2018-05-05 19:27:01','2018-05-05 19:27:01');
Post a Customer: {"firstname":"Peter","lastname":"Thomas"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Peter','Thomas','2018-05-05 19:27:09','2018-05-05 19:27:09');
Post a Customer: {"firstname":"Amos","lastname":"Nelson"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Amos','Nelson','2018-05-05 19:27:27','2018-05-05 19:27:27');
Post a Customer: {"firstname":"Craig","lastname":"White"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Craig','White','2018-05-05 19:27:37','2018-05-05 19:27:37');
Post a Customer: {"firstname":"Laura","lastname":"Lewis"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Laura','Lewis','2018-05-05 19:27:48','2018-05-05 19:27:48');
Post a Customer: {"firstname":"Steven","lastname":"Harris"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Steven','Harris','2018-05-05 19:27:59','2018-05-05 19:27:59');
Post a Customer: {"firstname":"Paul","lastname":"Moore"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Paul','Moore','2018-05-05 19:28:06','2018-05-05 19:28:06');
Post a Customer: {"firstname":"Mary","lastname":"Cook"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Mary','Cook','2018-05-05 19:28:14','2018-05-05 19:28:14');

-> Get’s Logs:


Get All Customers
Executing (default): SELECT `id`, `firstname`, `lastname` FROM `users` AS `user`;

-> All Requests:

NodeJS-Express-AngularJS-Bootstrap-Sequelize-MySQL-POST-GET-requests

-> Bootstrap view:

NodeJS-Express-AngularJS-Bootstrap-Sequelize-MySQL-POST-GET-views

-> MySQL’s records:

NodeJS-Express-AngularJS-Bootstrap-Sequelize-MySQL-POST-GET-records

Sourcecode

To run below sourcecode, follow the guides:


step 0: download & extract zip file -> we have a folder ‘NodeJS-Express-AngularJS-Bootstrap-MongoDB’
step 1: cd NodeJS-Express-AngularJS-Bootstrap-MongoDB
step 2: npm install express body-parser mongoose –save
step 3: node app.js

-> Sourcecode:
NodeJS-Express-Angular-Bootstrap-MySQL



By grokonez | May 7, 2018.

Last updated on May 12, 2021.



Related Posts


Got Something To Say:

Your email address will not be published. Required fields are marked *

*