[How To] PouchDB and CouchDB : Database Per User Made Simple

by | Aug 22, 2016 | NoSQL | 9 comments

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.

a

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 continous 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.

 

Have a great idea to implement? We can help

Comments

9 Comments

  1. Mirza Abazovic

    Nice article. I have question about mutal data for scenario when user A wants to share data only with user B and not with others (user C, user D etc …).
    Folowing this pattern solution is to create mutal database only for users A and B and that is now 3 local PouchDb for sync. This could keep going share data only for users A, C and D

    Is maybe solution for this filtered replication (on server side) with one local puchdb and one CouchDB database (server) but with documents that have some obligatory data for example { owner: “user A”, readers: [“user B”,”user C], writers [“user D”]}.

    Some kind of ACL contained in every doc that would be used in filtered replication to sync data to right pouchDb and also Validate Document Update Function.

    What is Your opinion ? Is this idea possible to implement ?

    Reply
    • Fady S. Ghatas

      Thanks for stopping by.
      The couchdb way would be to just have a new separate database on the server side where you can use validate_doc_update to strict writing to this database to specific users. You can go one further step and add only allowed users to permissions table of the database to allow the specified users to read/write and forbid other non-administrator users from accessing this database.

      Another way would be to use role where, for example, userA & userB are in HR team so there would be a role named “HR” and another for sales team and so on. So you can permit access to roles instead of users. Again this would require you to create database per role.

      If you want to implement that with one database where only a set of users can read some and not all documents, you must use a proxy server (Application layer) which wouldn’t be very “Couchy”. The simplest way would be to create a “_list” function, add all logic to the “_list” function and call the list using the proxy. Of course, you will need to add extra fields for each document specifying who can read/write this document. This would add extra complexity and slow down everything as list function would deal with documents one by one. For the replication part you will need to use filtered replication with some query_params.

      Reply
      • Mirza Abazovic

        Thank You for answering

        Reply
  2. emiliano

    Sorry for the question, but i don’t know how to install the plugin couchperuser. Yes, i see the steps described in the github page, but still i don’t know how to do it. Is it possible that you can tell me how to do it? Thanks!

    Reply
    • Fady S. Ghatas

      Hi Emiliano, I would need extra information to be able to help. What’s your distribution? Does any error message show up?

      Reply
  3. Tim

    Great Post

    Reply
  4. NoSQLer

    Amazing post. Do you still use Couch?

    Reply
  5. Bastian Torres

    Hey Fady, great article!

    I tried to implement this “database-per-user” architechture with no success. Maybe you can help me unlock my path. This is my scenario: I’ve started working with a new fresh installation, so I’ve turned off the “Admin Party” feature, created an admin user and enabled CORS.
    I’m using Couch V2, so the database-per-user it’s just a flag that you can enable from the Configuration tab within the Fauxton admin panel. I’m on Mac.

    The first thing I’ve noticed is that I had to manually create the _users database. Without this, the user creation proccess failed because there was no _users database to add in. Maybe you can add that detail to your post.

    Secondly, and most important thing, when I create a new user (I’m using Postman to hit the Couch API), I’m seeing that the user is created, but I dont see the user database. Why is this? I have to manually create the user’s database too? I thought that this was done automatically when a new user is created…

    Thank you for your time!

    Reply

Submit a Comment

Your email address will not be published.