Auto sync data between couch and mysql

Question

I need to develop offline app for the web application which is already developed with apache, mysql and php. I just found that we can develop offline app with couch and pouch db.

But the think is I need to auto sync data from mysql to couch db (and couch to mysql also ). From my understanding that I can write php code to read data from mysql and put the data to couch db ( and also with couchdb to mysql ). But I am not sure that how we can keep couch db and mysql data as updated. ( I mean auto sync the data when the changes are happen at the one end ).

can anyone suggest me the tool or anything to find out the way to auto sync data ?

Hitormiss

Answer 1

There is not a generic solution for this kind of synchronization. You should write your own code for that.

You should write a mapping mechanism that converts your relational data model into documents and vice versa.

From MySQL you'll require a change detection mechanism to determine the data that should be transferred to CouchDB after each synchronization cycle (ej. use timestamps as checkpoints), convert the data into json docs and then save/merge it with the CouchDB content.

From CouchDB, you can listen for document changes using the _changes endpoint and for every change transfer it to the MySQL by transforming them into DML operations.

All of this depends on your use case and could be difficult to writte a generic integration mechanism.

Boris

Thanks Juanjo! I understand your points. I just found one npm package to trigger the couch db changes (npmjs.com/package/couchdb-to-mysql). So I can call my put api for mysql changes regarding to the changes. But I am struggling with the mysql triggers which can run the api to replicate the changes to couch db. Hope you understand my issue – Hitormiss

I see your issue but I think there is not a generic way to solve it. Data Change Detection mechanism in relational databases is an issue with its own diffiuclties. Maybe this project can help you to draw a solution debezium.io/docs/connectors/mysql – Boris

Thank you for your help Juanjo!! I read the solution you provided, it may help me some other time. But right now I am changing the code to save the data directly to the couch db. Then I don't need this synchronization for my current project. Thank you again. – Hitormiss