Mobile app using PouchDB-CouchDB and MySQL

mysql mobile couchdb pouchdb
trace picture trace · Jun 4, 2014 · Viewed 11.6k times · Source

I'm developing a mobile app wrapped in Cordova that runs alongside our web-based application, based on PHP & MySQL. The mobile app uses local-storage & gets data via a layer of services that have been written to exchange data between the mobile app & the MySQL database. The mobile app only uses a subset of data stored in the main MySQL DB.

I am looking to replace my mobile-app local-storage solution with pouch DB & see that it requires CouchDB ... which got me thinking of a potential configuration / solution that I would like to find out whether would be advisable and feasible ...

Would it be feasible to set up a CouchDB database that runs as a mediator/slave between the main MySQL database & the mobile app's PouchDB? The mobile service layer would use this database (as well as the main MySQL DB if necessary) & data updates between the main-SQL & couch-DB are pushed periodically via cron. The CouchDB would only store a subset of data from the MySQL DB that is relevant for the mobile app.

Does this solution sound like overkill / a good idea? Is there a better way of approaching the setup described above? I do like the idea of pouchdb-CouchDB ... but don't want to rewrite my entire web-app to use couch-DB, while an additional level of abstraction providing a subset of mobile-specific data seems useful.

Thanks Trace

Answer

nlawson picture nlawson · Jun 4, 2014

PouchDB running on Node can actually use any LevelDOWN-based adapter, and there is one for MySQL. I haven't tested it. More info here: http://pouchdb.com/adapters.html#pouchdb_in_node_js.

However, this is probably not a good fit for your use case, because the data that PouchDB will store in MySQL will be totally different from the data your app is currently using in MySQL. In order to support replication, PouchDB keeps the revision history of every document stored (think git), which is different from a traditional database like MySQL, which just stores tables and rows that can be deleted/inserted/updated. Databases like CouchDB and PouchDB were built from the ground up to support replication, which is why this versioning system exists.

That being said, if you write your own sync layer between MySQL and CouchDB it could work in theory, but it would probably be so much work that you would lose the benefits of CouchDB and PouchDB's built-in replication.

Related questions

How do I detect a cellular carrier dynamically through php/ajax/javascript?

I'm asking this because I've been on SO most of the day, and can’t seem to get/find an answer. This is what I'm trying to do (without using a paid gateway): -First of all I'm setting up a …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More