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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
/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’:
|
1 2 |
mkdir NodeJS-Express-Angular-Bootstrap-MySQL cd NodeJS-Express-Angular-Bootstrap-MySQL |
Then init NodeJS project, see prompts:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
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 <pkg>` 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:
|
1 |
npm install express body-parser sequelize mysql2 --save |
-> see package.json file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
{ "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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
<!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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<!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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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:
|
1 2 3 4 5 6 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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:
|
1 2 |
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