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

nodejs-express-save-form-data-to-mysql-using-sequelize-orm-ajax-jquery-bootstrap-view-feature-image

In the tutorial, we show how to save form data to MySQL in NodeJS/Express application using Sequelize ORM with Ajax JQuery & Bootstrap view.

Related posts:
Sequelize ORM – Build CRUD RestAPIs with NodeJs/Express, Sequelize, MySQL
Integrate NodeJS/Express – JQuery Ajax POST/GET – Bootstrap view

Overview

Goal

Technologies
– NodeJS/Express
– Sequelize ORM
– MySQL
– JQuery Ajax
– Bootstrap

We create a NodeJS/Express project as below structure:


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

Run above project then makes Post/Get requests, results:

-> Bootstrap view:

NodeJS-Express-save-form-data-to-MySQL-with-Ajax-JQuery-Bootstrap-view-results

-> MySQL’s table:

NodeJS-Express-save-form-data-to-MySQL-with-Ajax-JQuery-Bootstrap-view-database-tables

Demo

Practice

Setting up NodeJS/Express project

Create a folder: ‘NodeJS-Express-Ajax-JQuery-Bootstrap-MySQL’:


mkdir NodeJS-Express-Ajax-JQuery-Bootstrap-MySQL
cd NodeJS-Express-Ajax-JQuery-Bootstrap-MySQL

Then init NodeJS project, see prompts:


NodeJS-Express-Ajax-JQuery-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-ajax-jquery-bootstrap-mysql)
version: (1.0.0)
description: NodeJS/Express save data form to MySQL - Ajax JQuery + Bootstrap view
entry point: (index.js) server.js
test command:
git repository:
keywords: NodeJS, Express, Bootstrap, Ajax-JQuery, MySQL
author: grokonez.com
license: (ISC)
About to write to C:\Users\pc\Desktop\nodejs\NodeJS-Express-Ajax-JQuery-Bootstrap-MySQL\package.json:

{
  "name": "nodejs-express-ajax-jquery-bootstrap-mysql",
  "version": "1.0.0",
  "description": "NodeJS/Express save data form to MySQL - Ajax JQuery + Bootstrap view",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "NodeJS",
    "Express",
    "Bootstrap",
    "Ajax-JQuery",
    "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-ajax-jquery-bootstrap-mysql",
  "version": "1.0.0",
  "description": "NodeJS/Express save data form to MySQL - Ajax JQuery + Bootstrap view",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "NodeJS",
    "Express",
    "Bootstrap",
    "Ajax-JQuery",
    "MySQL"
  ],
  "author": "grokonez.com",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.18.2",
    "express": "^4.16.3",
    "mysql2": "^1.5.3",
    "sequelize": "^4.37.6"
  }
}

Frontend

Create Bootstrap view

./views/index.html file:

<!DOCTYPE html>
<html lang="en">
<head>
  <title>JQuery Ajax - Bootstrap - NodeJS/Express - MySQL</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>
  <script src="/static/js/getrequest.js"></script>
  <script src="/static/js/postrequest.js"></script>
</head>
 
<body> 
<div class="container"> 
<div class="col-sm-4">
	<h3>NodeJS/Express Post/Get Ajax</h3>
	<div>		
		<form id="customerForm">
		  <div class="form-group">
			<label for="firstname">FirstName:</label>
			<input type="text" class="form-control" id="firstname" placeholder="Enter FirstName"/>
		  </div>
		  <div class="form-group">
			<label for="lastname">LastName:</label>
			<input type="text" class="form-control" id="lastname" placeholder="Enter LastName"/>
		  </div>
		  <button type="submit" class="btn btn-default">Submit</button>
		</form>
	</div>
	<div id="postResultDiv">
	</div>
	<br>
	<div>
		<button id="allCustomers" type="button" class="btn btn-default">Customers</button>
		<div id="getResultDiv">
	        <ul class="list-group">
	    	</ul>
    	</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 Ajax JQuery to Post/Get data

– For JQuery Ajax POST request, we implement a ./resources/static/js/postrequest.js file:


$( document ).ready(function() {
	
	// SUBMIT FORM
    $("#customerForm").submit(function(event) {
		// Prevent the form from submitting via the browser.
		event.preventDefault();
		ajaxPost();
	});
    
    
    function ajaxPost(){
    	
    	// PREPARE FORM DATA
    	var formData = {
    		firstname : $("#firstname").val(),
    		lastname :  $("#lastname").val()
    	}
    	
    	// DO POST
    	$.ajax({
			type : "POST",
			contentType : "application/json",
			url : window.location + "api/users/save",
			data : JSON.stringify(formData),
			dataType : 'json',
			success : function(customer) {
				$("#postResultDiv").html("

" + "Post Successfully!
" + "--> " + customer.firstname + " " + customer.lastname + ", createdAt: " + customer.createdAt+ "

"); }, error : function(e) { alert("Error!") console.log("ERROR: ", e); } }); // Reset FormData after Posting resetData(); } function resetData(){ $("#firstname").val(""); $("#lastname").val(""); } })

– For JQuery Ajax Get request, we implement a ./resources/static/js/getrequest.js file:


$( document ).ready(function() {
	
	// GET REQUEST
	$("#allCustomers").click(function(event){
		event.preventDefault();
		ajaxGet();
	});
	
	// DO GET
	function ajaxGet(){
		$.ajax({
			type : "GET",
			url : "/api/users/all",
			success: function(result){
				$('#getResultDiv ul').empty();
				var custList = "";
				$.each(result, function(i, customer){
					$('#getResultDiv .list-group').append(customer.id + ". " + customer.firstname + " " + customer.lastname + "
") }); console.log("Success: ", result); }, error : function(e) { $("#getResultDiv").html("Error"); console.log("ERROR: ", e); } }); } })

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

./app.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 Users:
-> Logs:


Post a Customer: {"firstname":"Jack","lastname":"Davis"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Jack','Davis','2018-05-03 19:26:14','2018-05-03 19:26:14');
Post a Customer: {"firstname":"Mary","lastname":"Taylor"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Mary','Taylor','2018-05-03 19:26:28','2018-05-03 19:26:28');
Post a Customer: {"firstname":"Peter","lastname":"Thomas"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Peter','Thomas','2018-05-03 19:26:44','2018-05-03 19:26:44');
Post a Customer: {"firstname":"Amos","lastname":"Nelson"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Amos','Nelson','2018-05-03 19:27:00','2018-05-03 19:27:00');
Post a Customer: {"firstname":"Craig","lastname":"White"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Craig','White','2018-05-03 19:27:16','2018-05-03 19:27:16');
Post a Customer: {"firstname":"Laura","lastname":"Lewis"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Laura','Lewis','2018-05-03 19:27:45','2018-05-03 19:27:45');
Post a Customer: {"firstname":"Steven","lastname":"Harris"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Steven','Harris','2018-05-03 19:27:57','2018-05-03 19:27:57');
Post a Customer: {"firstname":"Paul","lastname":"Moore"}
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Paul','Moore','2018-05-03 19:28:06','2018-05-03 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-03 19:29:00','2018-05-03 19:29:00');

Get all Users:
-> Logs:


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

-> Bootstrap view:

NodeJS-Express-save-form-data-to-MySQL-with-Ajax-JQuery-Bootstrap-view-results

-> MySQL tables:

NodeJS-Express-save-form-data-to-MySQL-with-Ajax-JQuery-Bootstrap-view-database-tables

Sourcecode

NodeJS-Express-Ajax-JQuery-Bootstrap-MySQL



By grokonez | May 5, 2018.

Last updated on April 30, 2021.



Related Posts


Got Something To Say:

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

*