{"id":748,"date":"2014-11-29T22:26:44","date_gmt":"2014-11-29T16:56:44","guid":{"rendered":"http:\/\/codeforgeek.com\/?p=748"},"modified":"2021-06-20T06:05:35","modified_gmt":"2021-06-20T00:35:35","slug":"sync-app-mysql-indexeddb","status":"publish","type":"post","link":"https:\/\/codeforgeek.com\/sync-app-mysql-indexeddb\/","title":{"rendered":"Synchronization between mysql and IndexedDB"},"content":{"rendered":"

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 <\/a>tutorial before moving ahead.<\/p>\n

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

<\/i>LIVE DEMO<\/a> <\/i>DOWNLOAD<\/a><\/center><\/p>\n

Motivation behind Synker:<\/h3>\n

This app is not for production payload or not created for any commercial purpose so there is room for bug and feature enhancement.<\/p>\n

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.<\/p>\n

How Synker works:<\/h3>\n

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

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.<\/p>\n

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.<\/p>\n

Database design:<\/h3>\n

Our database is in MySQL. Create database named “synker”<\/strong> using PhpMyadmin<\/strong> or command line whichever suits you best and execute following command to create our table.
\n
\ncreate table content
\n(
\n user_id int(100) PRIMARY KEY AUTO_INCREMENT,
\n content TEXT
\n);
\n<\/code><\/p>\n

Directory structure:<\/h3>\n


\n--node_module
\n | -- mysql
\n | -- express
\n--views
\n | --index.html
\n--Server.js
\n--package.json
\n<\/code><\/p>\n

Synker – Code :<\/h3>\n
package.json<\/strong><\/div>\n


\n{
\n \"name\": \"Synker\",
\n \"version\": \"0.1.1\",
\n \"dependencies\": {
\n \"express\": \"~4.10.1\",
\n \"mysql\": \"~2.5.2\",
\n \"ejs\": \"~1.0.0\"
\n }
\n}
\n<\/code>
\ninstall the dependencies by typing npm install<\/code>
\nThis is node.js <\/strong>script which basically does the job of syncing. It contains API which we call according to connection scenario.<\/p>\n

Server.js<\/strong><\/div>\n


\nvar express=require(\"express\");
\nvar mysql=require(\"mysql\");
\nvar app=express();<\/p>\n

var connection = mysql.createConnection({
\n host : 'localhost',
\n user : 'root',
\n password : '',
\n database : 'synker'
\n});<\/p>\n

connection.connect();<\/p>\n

app.set('views',__dirname + '\/view');
\napp.use(express.static(__dirname + '\/js'));
\napp.set('view engine', 'ejs');
\napp.engine('html', require('ejs').renderFile);<\/p>\n

app.get('\/',function(req,res){
\n res.render('index.html');
\n});<\/p>\n

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

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

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

app.listen(3000,function(){<\/p>\n

console.log(\"I am live at PORT 3000.\");<\/p>\n

});
\n<\/code>
\nLike i said we are using simple textarea with no fancy css, so here is the html i am using.<\/p>\n

index.html<\/strong><\/strong><\/div>\n

<\/p>\n

\n