Node.js Express : Parse and insert JSON into MySQL Database

Updated on 19th December 2016

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.


Post a comment

Name

Your Comment

Email (We dont publish it)

Comments

Nikita | March 9, 2017 9:04 PM |

I am getting the below error on chrome: XMLHttpRequest cannot load url. No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost:3000' is therefore not allowed access. Its a header issue, so i wanted to know that where we can write the below code: res.setHeader('Access-Control-Allow-Origin', '*'); As i tried in .connect(), but its not working.