CouchDB is one of the most powerful DBMS nowadays. However, although its documentation is good, there is not enough topics of how-tos and best practices for common use cases.

One of these use cases is the database_per_user. Each user has his own private data that only that user can read or write.

I have been searching for a technique to implement a more complex application. I wanted to create an offline-ready system with remote database synchronization where some of the data are mutual between all users and some of the data are private for each user. The system should have an analytical admin panel with graphs and stuff like that for the whole thing.

This is the part where everything became very hard to implement for someone with SQL & MongoDB background like me. CouchDB has neither tables nor schemas. Just single key/value table per database where every thing can fit.

Implementing this in SQL based DBMS would be quite easy

1. Create users table.
2. Create table per entity
3. Create an application tier between the database and the frontend ( PHP, Node, …)
4. Handle authentication and authorization in the application tier.

Simple Solutions that will NOT work using couchDB includes :

1. Create an application tier, You will lose CouchDB native RESTful endpoints. Building replication endpoint by yourself would be a headache.
2. Create a single database and handle authentication in the client-side. That’s really bad! It would be easily hackable. The client-side code – even if compiled – can be easily inspected to get the DB credentials.

Many online topics about couchDB are talking about how cheap databases are. Every time you need a new database create one. Even if you needed hundred of thousands of databases. Couch can scale and replicate. So whenever you need a database, just create one.

That concept totally changed how I think about CouchDB. I thought of the following solution:

Single database per mutual data and one database_per_user for private data. Both of them replicate to the main database in a one-way replication. Like shown in the image below.

Let’s see how to create each of these components step by step.

1. Install couchDb on a server or localhost and enable CORS
2. Install CouchPerUser
   1. Follow this : https://github.com/etrepum/couchperuser
   2. On Archlinux I found the path to couchdb to be /usr/lib/couchdb/
   3. So on Arch the path to the plugin should be : /usr/lib/couchdb/plugins/couchperuser
3. Restart CouchDB
4. Now, whenever you create a new user, a new database will be automatically created for that user.

   • HINT : CouchDB admin panel : http://localhost:5984/_utils/ ( replace localhost with your domain name or IP )

5. Create the mutual remote database, let’s call it main-entities and create one database for the analytical dashboard to which every user database should replicate.
6. Now on the client-side :
   1. Create new PouchDB project ( You can use Angular-pouch for seamless integration with angular)
   2. You should create 2 pouch databases
      1. One for the mutual data and should sync with main-entities
      2. One private database that should sync with the private user database.
7. On creating new users, enable continuous integration between userdb and main database.

Let’s recap all databases

Remote databases :

1. Mutual data database ( Only One ) ( All users can read / write to this database. )
2. User database ( 1 per user ) ( Only the owner can read / write to this database )
3. Main database ( Only one ) ( For the analytical dashboard ) ( NO DATA SHOULD BE WRITTEN HERE BY ANY USER, ONLY REPLICATED DATA BY CONTINOUS REPLICATION )

Local Databases : Mutual db + User db.

Is that a perfect solution ?

Ofcource not! The main disadvantage of this solution is the absense of a central offline database. The only central database here is the main database on the server. My application didn’t require this. However, databases are very cheap. You can create a local central database to replicate all data to. So that you can generate offline reports if you wanted to.

Let’s see some code snippets

Connect to remote database and sync :

dbs.remote.private = pouchDB(DATABASE.URL + "userdb-" + _convertToHex(username), {
    auth: {
        username: username,
        password: password
    },
    skip_setup: true
});
 dbs.local.private.sync(dbs.remote.private).on('complete', function() {

 }).on('error', function(err) {
 });

_convertToHex is a function responsible of converting strings to hex representation. Why ? because couch_peruser uses hex-based usernames when creating databases.

So, for example, creating a user named “titrias” by setting _id to org.couchdb.user:titrias and doc.name to titrias should create a new database called `userdb-74697472696173` through the couch_peruser plugin

    function _convertToHex(str) {
        var hex = '';
        for (var i = 0; i < str.length; i++) {
            hex += '' + str.charCodeAt(i).toString(16);
        }
        return hex;
    }

User Doc :

{
   "_id": "org.couchdb.user:titrias",
   "_rev": "1-9cc65b3e62e0210fdbc89bf6390e3be1",
   "password_scheme": "xxx",
   "iterations": 10,
   "username": "titrias",
   "type": "user",
   "name": "titrias",
   "roles": [
   ],
   "derived_key": "xxx",
   "salt": "xxx"
}

If there is anything still not clear, Leave it in the comments. I will try my best to add more code snippets soon.