Using PouchDB with SQL Server and CouchDB

Question

I know that PouchDB only works with CouchDB compatible servers. Is there a way to use SQL Server as a backend while maintaining its original schema?

I have a thought how to accomplish this but I am not entirely sure if it is feasible. Any answer that will lead this to fruition will be appreciated.

The way I see it can be done is by using CouchDB to store the sync metadata like revisions and all things needed for a successful sync. The actual latest data will then be stored on the SQL Server following its original schema.

To be able to accomplish this I need to create a CouchDB compatible server using my stack which is C#. This will act like a proxy in between CouchDB and PouchDB so I don't actually need to implement all methods. I will have to intercept certain methods that will allow me to keep SQL Server and CouchDB in sync (following business rules) on the server at the same time take advantage of CouchDB's sync capability over the SQL Server Data. With this in place I could also add a bunch of middleware I wish in the future.

Is this thing feasible? I know that express-pouchdb exist but it is just a CouchDB compatible server with a PouchDB backend. What I wanted is to make the CouchDB and SQL Server work together. If so, how should I go about it?

Chappelka

Sounds tough... Basically you'll end up storing JSON documents (from Pouch) in a SQL database. Maybe it would be easier if you used a SQL database like SQLite on the clientside. It seems there are even out of the box solutions for this. – Scamper

I did a proof of concept (sync only in polling and for only one particular dataset) for something similar to this. My setup was an Angular2 Client using PouchDB that directly syncs with a Mirosoft SQL Server. I basically implemented this docs.couchdb.org/en/2.0.0/replication/protocol.html protocol in .Net, and it worked just fine. I'm pretty sure it is feasible in general, but I'm not sure yet if it is worth it. I hope i get the time to work on getting this thing working in a more general case and keep you posted if i find out something new. – Ariannaarianne

Hey I have the same case. Did you solve it ? – Crosseyed

Has anyone found a general solution to this yet? I just got the ressources to try my approach in a general way. – Ariannaarianne

@MaxKuchenkiller that sounds cool. Did you ever put anything together? – Venue

@Venue yes, we actually did, however we never developed a package for easy usage. What we ended up doing was, as stated above, implementing the sync protocol in .NET. We got it running in production for a very simple example. You can find a demo project in my github (link), if you are interrested and want to take a look contact me. – Ariannaarianne

Answer 1

Might sound like a crazy idea, but you can use PouchDB on the server and plug in an alternative storage adapter that uses SQL Server. That way you don't need to reimplement the replication logic (this is really hard), you can concentrate on the storage part.

Some pointers:

• https://pouchdb.com/adapters.html
• https://github.com/Level/leveldown
• https://github.com/calvinmetcalf/sqldown

Schaaff

Have anyone worked on this? It's interesting.! Any of these ideas seems fine. – Furr

Answer 2

You could setup a server side listener on the _changes API of Couchdb. This API allows the server to run a change listener process that triggers as data is changed in CouchDB. By inspecting the change list (docs) and type of change, it is possible to map the JSON document (or parts of it) to be mirrored in SQL using some custom logic.

Mapping JSON to relational tables may be difficult depending on the schema. Complexity can be reduced by only mapping the data that makes sense to store as relational. Call it a hybrid approach. For example, customer definitions could stay schema-less in CouchDB (thereby retaining the advantage of schema-less) but sale line items could be mirrored in SQL for easier reporting purposes. Most likely there is only certain data that really makes sense to store in SQL Server. Couchdb is your source of truth.

You asked about maintaining the SQL Schema. In reality you most likely do not want to or have to maintain the SQL Schema completely. Using the above approach, you probably do not care in SQL if a customer's address changes; the customer schema remains flexible in CouchDB. You may however want to make an SQL entry for the customer ID (only) to enforce referential integrity and handle the case if the customer is deleted.

The _changes can be replayed from a point in time to recover from failure.

For reference:

http://guide.couchdb.org/draft/notifications.html

Ayres