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
Contents
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:
-> MySQL’s table:
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:
-> Bootstrap view:
-> MySQL’s 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
Last updated on May 12, 2021.