Mobile app using PouchDB-CouchDB and MySQL

Question

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

Indeed

Answer 1

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.

Natatorium

Thanks for the response. I think I'm going to hold off the client re-write for now since it is working & keep an eye out for a solution in the future. I found some interesting reading that spun off from your link (1), so thanks for that. (1) Good article on mobile app storage: html5rocks.com/en/tutorials/offline/quota-research – Indeed

Answer 2

I'm trying to achieve the very same schema with our ERP (SQL Server based).

Now i'm just trying to figure out if pouchdb on the mobile would be sufficient for the requirements, for example:

• To be able to filter a given "price list" by "product description". Think a LIKE in sql as on:

  SELECT * FROM Prices WHERE Description LIKE '%text%'

• To be able to filter a given "price list" by "product category", OR by "product vendor"

Also, the mobile app would just need a subset of the full SQL schema/data. And my idea was to make easy the mobile pouchdb <-> couchdb replication part, which can be challenging just with webSQL <-> SQL Server), and then later "replicate" the added data on coouchdb to the SQL Server with a process, think a cron task.

So far i've found:

• Building a pouchdb view on the client side can take ages to build, just for the first point of being able to do that LIKE operation. To solve this, i've built an auxiliar websql db which simply contains (pouchdb_id, pouchdb_text) where i rebuild it after replication, inserting the pouch keys, and object text fields concatenated. Then when i need a LIKE, i do it on webSQL, and fetch docs with pouchdb using db.allDocs( { keys: [sql returned keys array] })

• The second point is on my analysis right now...

Analysis is currently going on, and any idea would be nice to share.

Chem

Sorry about the slowness of view building. We know it's a problem, and we're working on it. – Natatorium

Hi Javier This sounds exactly like what I am trying to do & glad to hear that there are others out there trying the same thing. There are so many large web apps based on relational dbs that are likely requiring a mobile app, motivating a requirement for sql - no-sql integration. I was surprised to find so little info on the topic. So far these are the only 2 mildly interesting links I have found on the topic. They both use the words 'crazy' ... (1)github.com/kesla/mysqldown (2)percona.com/live/mysql-conference-2012/sessions/… – Indeed

Answer 3

You can use the following NodeJS package https://www.npmjs.com/package/couchdb-to-mysql.

The package listens for CouchDB changes and reflects them on MySQL.

Example

var converter = require('couchdb-to-mysql');
var cvr = converter();
cvr.connect();
cvr.on('created', function (change) {
    // replicate changes on mysql     
});

Methods

var converter = require('couchdb-to-mysql')
var cvr = converter(config={})
Optionaly pass in a config:

config.couch.host
config.couch.port
config.couch.database
config.mySQL.host
config.mySQL.port
config.mySQL.user
config.mySQL.password
config.mySQL.database
events
cvr.on('created', function (change) {})
Every time a document is created, a created event fires.

cvr.on('updated', function (change) {})
Every time a document is updated, a updated event fires.

cvr.on('deleted', function (change) {})
Every time a document is deleted, a deleted event fires.

Testosterone