Synchronization between mysql and IndexedDB

IndexedDB is powerful api for web application developers. In last tutorial I mentioned the basics about IndexedDB and about how to perform SQL transaction in it. Check out the IndexedDB tutorial before moving ahead.

In this tutorial i am going to show you one demo application which i named “Synker” which basically sync the data between MySQL and IndexedDB.

LIVE DEMO DOWNLOAD

Motivation behind Synker:

This app is not for production payload or not created for any commercial purpose so there is room for bug and feature enhancement.

I was amazed by the IndexedDB api and wanted to put it some real world use. Hope this piece of code helps you to understand the use of IndexedDB.

How Synker works:

Basic working is like this, when our client is not able to connect to Node server we assume that either internet connection is down or something happened at client end.

In order to keep the user working we will store data in IndexedDB and as soon as our connection is up with Server whole data will be updated to MySQL as well as to IndexedDB in order to keep the data consistent.

I used simple HTML textarea which let user type something and in 5 seconds or so Synker will sync the content present in the textarea to MySQL or IndexedDB depending upon the connection to web server.

Database design:

Our database is in MySQL. Create database named “synker” using PhpMyadmin or command line whichever suits you best and execute following command to create our table.

CREATE TABLE content
(
    user_id INT(100) PRIMARY KEY AUTO_INCREMENT,
    content TEXT
);

Directory structure:

--node_module
             | -- mysql
             | -- express
--views
             | --index.html
--Server.js
--package.json

Synker – Code :

package.json
{
  "name": "Synker",
  "version": "0.1.1",
  "dependencies": {
    "express": "~4.10.1",
    "mysql": "~2.5.2",
    "ejs": "~1.0.0"
  }
}

install the dependencies by typing

npm install

This is node.js script which basically does the job of syncing. It contains API which we call according to connection scenario.

Server.js
var express=require("express");
var mysql=require("mysql");
var app=express();


var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'synker'
});

connection.connect();


app.set('views',__dirname + '/view');
app.use(express.static(__dirname + '/js'));
app.set('view engine', 'ejs');
app.engine('html', require('ejs').renderFile);


app.get('/',function(req,res){
        res.render('index.html');
});

app.get('/get_from_db',function(req,res){
        connection.query("SELECT * from content",function(err,rows){
          res.json(rows[0]);
        });
});

app.get('/ping',function(req,res){
        res.json({"alive":"yes"});
});

app.get('/update',function(req,res){
        var content=req.query.data;
        /*Check if there is any row else put one row for all time*/
        connection.query("SELECT * from content",function(err,rows,field){
            if(rows.length===0)
              {
                /*add one row*/
                connection.query("INSERT into content(user_id,content) VALUES (1,'')",function(err,rows){
                    if(err)
                      {
                        console.log(err);
                        res.json({"error":"1"});
                      }
                      else
                        {
                          res.json({"yes":"1"});
                        }
                });
              }
            else
              {
                /*Sync exisiting data*/
                connection.query("UPDATE content set content='"+content+"' where user_id=1",function(err,rows){
                    if(err)
                      {
                        console.log(err);
                        res.json({"error":"1"});
                      }
                    else
                      {
                        res.json({"yes":"1"});
                      }
                });
              }
        });
});

app.listen(3000,function(){

    console.log("I am live at PORT 3000.");

});

Like i said we are using simple textarea with no fancy css, so here is the html i am using.

index.html
<div id="container">
      <textarea rows="20" cols="120" id="my_container"></textarea><br><br>
      <input type="button" id="save" value="Save"></input><br><br>
      <span id="message"></span>
</div>

Here is JavaScript code which is responsible for following things.

  • Create IndexedDB in browser if not present.
  • Update the IndexedDB with latest data present in MySQL.
  • Retrieve data from IndexedDB in case user is offline.
index.html : IndexedDB JavaScript code (important)
  <script type="text/javascript">
          var request = indexedDB.open("synker");
          var db;
          request.onupgradeneeded = function() {
          // The database did not previously exist, so create object stores and indexes.
          db = request.result;
          var store = db.createObjectStore("notes", {keyPath: "ID"});
          var ourindex = store.createIndex("content","user_content");
        };
        request.onsuccess = function() {
          db = request.result;
        };
        function addData(data)
        {
            var tx = db.transaction("notes", "readwrite");
            var store = tx.objectStore("notes");
            store.put({content: data, ID:1});
        }
        function addinsql(data)
        {
          $.get("/update?data="+data,function(data){
            if(data.yes==1)
              {
                alert("data updated");
              }
            else
              {
                alert("error");
              }
          });
        }
        function check_alive(){
            var a;
            $.ajax({
                    url: '/ping',
                    type: 'GET',
                    async: false,
                    success: function(data){
                          //Ready for MySQL insertion.
                          console.log("MySQL is UP");
                          a=0;
                    },
                    error: function(data) {
                            //Go in the indexDB
                            a=1;
                    }
            });
            return a;
        }
        function getalldata()
        {
                  var all_content;
                  var self=this;
                  var tx = db.transaction("notes", "readonly");
                  var store = tx.objectStore("notes");
                  var request = store.openCursor();
                  request.onsuccess = function() {
                    var cursor = request.result;
                    if (cursor) {
                      self.all_content=cursor.value.content;
                      cursor.continue();
                    }
                  };
              return self.all_content;
        }
  </script>
index.html : jQuery code (to handle Request/Response to Server)
 <script>
  $(document).ready(function(){
        $("#message").empty().html("<b>We are good ! Keep writing.</b>").css("color","green");
        $.get("/get_from_db",function(data){
          $("#my_container").val(data.content);
        });
        setInterval(function(){
                if(check_alive()==0)
                  {
                    $("#message").empty().html("<b>We are good ! Keep writing.</b>").css("color","green");
                    //get all data from indexDB and update it in MySQL.
                    $("#message").empty().html("<b>Syncing...</b>").css("color","green");
                    var c2=getalldata();
                    console.log(c2);
                    $.get('/update?data='+c2,function(data){
                        if(data.error==1)
                          {
                            console.log("Some issue.");
                          }
                          else
                            {
                              $("#message").empty().html("<b>We are good ! Keep writing.</b>").css("color","green");
                            }
                    });
                  }
                else
                  {
                    $("#message").empty().html("<b>Connection lost ! keep writing. We will deal with it.</b>").css("color","red");
                  }
          }, 10000);
        $("#save").click(function(){
          var content=$("#my_container").val();
          var control=check_alive();
          if(control==0)
            {
              console.log("Put data in Mysql");
              addinsql(content);
              addData(content);
            }
          else
            {
              console.log("Putting in indexDB.");
              addData(content);
            }
        });
  });
  </script>

How to run:

I hope you have XAMPP or WAMP server, turn on MySQL and keep it up. Switch to folder where our code present and type

node Server.js

to run it. Visit localhost:3000 to view the app.

Server is up ! Start typing,
Server is up ! Start typing,
Now while using the app stop Node server and see the error message on client page. Just keep typing and after a minute or so start node server again. Within 5 seconds your data will be updated to MySQL engine.
Turn off the server and restart again.
Turn off the server and restart again.
Now to validate whether we have consistent data in MySQL and IndexedDB let’s open and see both of them. View my screenshot for reference.
image3

Conclusion:

Synker is designed to demonstrate uses of IndexedDB. I am not saying this is correct use or this is what you should use. All i am saying is that you can do something like this with IndexedDB api.

Further reading:

Official MDN doc about IndexedDB is awesome ! Give it a shot.

Shahid (UnixRoot) Shaikh

Hey there,

This is Shahid, an Engineer and Blogger from Bombay. I am also an Author and i wrote a programming book on Sails.js, MVC framework for Node.js.

Related Posts

16 Comments

  1. I am trying to use the the same source code that you have put and i am getting an error.
    XMLHttpRequest cannot load file:///C:/get_from_db. Cross origin requests are only supported for protocol schemes: http, data, chrome, chrome-extension, https, chrome-extension-resource.

    Can you please let me know whats the solution for this?

    1. 1.Failed to load resource : net :: ERR_FILE_NOT_FOUND
      file://ajax.googleapis.cpm/ajax/libs/jquery/1.11.1/jquery.min.js

      2.Uncaught ReferenceError: $ is not defined index.html:83

      This is what i get when i run exactly the same code as your and the previous one i got when i added jquery.min.js

  2. Hi. I am using exactly the same code, just changed files:// to http://. It runs fine but it disconnects from the server itself and never gets back online. The data is being saved in the indexedDB but it doesnt sync to the mySQL. I am using wamp server FYI. The error that i am getting is as below:

    1. Failed to load resource: the server responded with a status of 404 (Not Found)

    http://localhost/get_from_db

    2. Failed to load resource: the server responded with a status of 404 (Not Found)

    http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js

    3. GET http://localhost/ping 404 (Not Found)

    send @ jquery.min.js:4
    m.extend.ajax @ jquery.min.js:4
    check_alive @ index.html:48
    (anonymous function) @ index.html:90

  3. I installed node server and i am trying to run it. The problem is i have never used a node server so i am quite uncomfortable with usage of node server. Can you tell me what to write in the node command prompt if i want to run Server.js and i have stored it on my desktop.

    1. Its absolutely ok if you don’t know how to run it. if you have installed Node.js in your system, you can run Server.js by typing node Server.js on command prompt. Then visit URL from browser.

  4. Can you share your email address so that i can send you the screenshots for the step by step process i am doing?

  5. Awesome tutorial. I learned it well and i am quite comfortable now with the usage. Thanks Shahid for guiding me step by step.

Leave a Reply

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