This web app created with Node.js and Express framework demonstrates how to parse a JSON POST and insert the data in to a MySQL database table.
First of all we create a HTML file that can be used to send the stringifyed JSON data to a Node server using AJAX post request. We will also create a server side app that listens to the post requests, extracts the JSON and insert in to the database.
This app uses the modules body-parser and mysql. The body-parser module parses the body of the incoming request and makes it available as req.body property.
To install body-parser module, run
npm install body-parser
The mysql module is a Node.js driver for MySQL. To install mysql package, run
npm install mysql
Client-side
Now lets create the client-side code for sending a simple JSON data consisting of name and age of some people to Node.js server.
File: myfile.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Node delivered HTML
</head>
<body>
<div>
<h1>Send JSON to Node</h1>
<button onClick="sendJSON()">Send</button>
<p id ="result">
</p>
</div>
<script>
var myData = [
{
"name": "Bill",
"age": 20
},
{
"name": "Lisa",
"age": 40
},
{
"name": "Ant",
"age": 80
}
]
function sendJSON(){
var xmlhttp = new XMLHttpRequest(); // new HttpRequest instance
xmlhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
document.getElementById("result").innerHTML =
this.responseText;
}
};
xmlhttp.open("POST", "http://localhost:3000");
xmlhttp.setRequestHeader("Content-Type", "application/json;charset=UTF-8");
xmlhttp.send(JSON.stringify(myData));
}
</script>
</body>
</html>
Save this code in a file called myfile.html. This file can be served from Nodejs inside a route handler.
Server-side
Here is the server-side code, to parse the incoming JSON and insert it into MySQL database.
File: server.js
//Parse data from JSON POST and insert into MYSQL
var express = require('express');
var app = express();
var path = require('path');
var bodyParser = require('body-parser');
var mysql = require('mysql');
// Configure MySQL connection
var connection = mysql.createConnection({
host: 'localhost',
user: 'node',
password: 'node',
database: 'node_project'
})
//Establish MySQL connection
connection.connect(function(err) {
if (err)
throw err
else {
console.log('Connected to MySQL');
// Start the app when connection is ready
app.listen(3000);
console.log('Server listening on port 3000');
}
});
app.use(bodyParser.json())
app.get('/', function(req, res) {
res.sendFile(path.join(__dirname+ '/myfile.html'));
});
app.post('/', function(req, res) {
var jsondata = req.body;
var values = [];
for(var i=0; i< jsondata.length; i++)
values.push([jsondata[i].name,jsondata[i].age]);
//Bulk insert using nested array [ [a,b],[c,d] ] will be flattened to (a,b),(c,d)
connection.query('INSERT INTO members (name, age) VALUES ?', [values], function(err,result) {
if(err) {
res.send('Error');
}
else {
res.send('Success');
}
});
});
The server side script starts by making a connection to MySQL database.. Since this app needs database connection, the application starts listening only after a connection has been made. The connection.connect() function accepts a callback to check for errors. The app.listen() statement is executed inside the callback only if there were no errors in establishing the connection.
To get the JSON from incoming POST we use bodyParser.json() middleware, which parse the request and makes the JSON data available in req.body property.
When you access the URL http://localhost:3000/ , the request is handled by the get route which serves our HTML file myfile.html.
app.get('/', function(req, res) {
When a user clicks on the Send button in the HTML file, the JSON data is send to the server by an AJAX POST. The post route on the server side script handles the incoming POST.
app.post('/', function(req, res) {
The corresponding route method creates a nested array out of req.body so that a bulk-insert can be performed. Our JSON is converted to a nested array as below
[ ["Bill",20],["Lisa",40],["Ant",80]]
Nested arrays are converted in to grouped lists for bulk insert. So it finally becomes
("Bill",20),("Lisa",40),("Ant",80)
and gets inserted as multiple rows.
The connection.query() function which insert the data to MySQL also accepts a callback. Inside the callback the AJAX response message is set based on whether the insert was successful or not and this message will be displayed to the user.
connection.query('INSERT INTO members (name, age) VALUES ?', [values], function(err,result) {
if(err) {
res.send('Error');
}
else {
res.send('Success');
}
});
Instead of the bulk insert method, you could insert the rows individualy using a for loop.
This code is just for demonstrating how to parse JSON and insert in to MySQL. An actual production app might require validation and sanitation of the data, reading from live streams or interactive user input.
