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.
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.
(
user_id INT(100) PRIMARY KEY AUTO_INCREMENT,
content TEXT
);
Directory structure:
| -- mysql
| -- express
--views
| --index.html
--Server.js
--package.json
Synker – Code :
"name": "Synker",
"version": "0.1.1",
"dependencies": {
"express": "~4.10.1",
"mysql": "~2.5.2",
"ejs": "~1.0.0"
}
}
install the dependencies by typing
This is node.js script which basically does the job of syncing. It contains API which we call according to connection scenario.
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.
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.
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>
$(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
to run it. Visit localhost:3000 to view the app.
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.
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.
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.
