How to sync CouchDB and PostgreSQL

Question

We have a web application built on RoR, its using postgresql, now we have to integrate mobile application with it, the mobile application have to be able to manage offline mode, so we need to sync information between web app and mobile app.

The question is, if I have the data stored on postgresql and I need to use couchdb and pouchdb for offline syncing, what can I do??

Thanks!!

Reider

Take a look at loopback. It allows offline sync with many databases including postgres (it needs nodejs, though) – Pilotage

@Pilotage FYI, with loopback v4, they do not have plans to build offline sync. They are open to the community contributing it, though. – Communize

@Communize yes, that's unfortunate. It's understandable as it's not trivial and everyone wants a different kind of 'offline support' with different tradeoffs. Great opportunity for someone from outside though – Pilotage

Any chance the majority of those issues have to do with how to handle conflicts (i.e., nonlinear updates to same data from two places)? In my use case, conflicts would be quite odd as expected access patterns dont call for simultaneous editing. The human is my single thread. – Baxy

Answer 1

Can you make sure that you write only on CouchDb and PostgreSQL is only used for reading data? In that case you can use the _changes feed of CouchDb to replicate all the data back to your PostgreSQL database for readonly-access.

Elmiraelmo

Answer 2

With: https://github.com/pramsey/pgsql-http You could use triggers on the tables for any inserts/updates/deletes and turn them into http post requests to couch as well as updating postgres. Have a read of https://github.com/sysadminmike/couch-to-postgres about half way down.

Breakaway

Answer 3

I haven't seen any direct solutions for your question yet, but you can get some ideas from this technology that just came out of the oven.

If you want mobile online/offline sync you can use GraphQL replication protocol instead of CouchDB replication protocol.

A quick trick is to use Hasura "plug and play" GraphQL reactive backend in front of PostgreSQL, empowering it with an offline/online GraphQL synchronization feature. In the frontend you will need RxDB (which uses PouchDB as a storage engine) or Apollo JS (which is a GraphQL client). These are javascript libraries, something similar may exist in the mobile world.

Regarding the DB achitecture it may not be as plug n' play as think. You have to do some tweaks in the tables you want to replicate. According to CAP theorem, PostgreSQL (as a SQL database) provides you with data consistency and availability (no partitionability). Then CouchDB/PouchDB provides you with data availability and partitionability (eventual consistency).

Databases for offline first apps must be eventually consistent and capable of handlig version conflicts because that can happen when a documnet/row is changed at the same time in two different places.

So the requirement is to implement eventual consistency feature on PostgreSQL. That can be done by creating a column called revision_id in each of your offline-first ready tables, making it part of the table's primary key and another column with a list of previous revision_ids.

Never tried it myself but here is the tutorial explaining everything.

Headpin