In a previous tutorial I covered how to create an offline application that syncs with a remote database when online. We created a todo application that used PouchDB to store data locally, which synced with a remote CouchDB database. If you’re not already familiar with CouchDB, it is a document based NoSQL database (rather than a typical relational database). If you’re also not familiar with NoSQL, I wrote a quick introduction to what NoSQL is, and how it’s relevant to mobile development.
The todo application that was created in the previous tutorial works fine, but there’s one glaring limitation, and that is that it can only ever be used by one person. The local database syncs all of its data to a single remote database, and the application makes use of all of the data stored in the remote database. That means that if two people were to use the application they would have to share the same set of data.
This was fine to demonstrate how PouchDB and CouchDB work together, but it doesn’t go far enough to be useful in a real life scenario. So, in this tutorial we are going to discuss how we can go about creating a PouchDB and CouchDB structure such that we are able to have multiple users using the same application. There is going to be a lot of theory, discussion, and back and forth in this tutorial as we work towards figuring out a solution (I want to cover the why rather than just the how, specifically we are going to cover some differences between a SQL and NoSQL approach). We’ll get into some action in the next tutorial.
DISCLAIMER: The world of CouchDB is a complex one with lots of grey areas. I’ve done my research but I am by no means an authority on the subject, so take everything with a grain of salt. Better yet, post in the comments if you have any other suggestions or ideas.
If we were using a relational database like MySQL the solution is well established and pretty simple. You would simply store the users id along with a specific todo (or create an additional table that links users and todos together via a foreign key) to identify which todo belongs to which user, which might look like this:
from that, we can tell the bread and milk todos belong to the user with an id of 32, and the eggs to do belongs to the user with an id of 40. So when Mr. Thirty-Two comes along we can simply retrieve any rows from that table that have a user_id of 32. This is something that is very easy to do with relational databases.
The answer in NoSQL is not as straightforward (even if we only consider specifically document based databases). We could store the data in a similar way with NoSQL, which could look like this:
{
"todos": {
"1": {
"user_id": 32,
"title": "bread"
}
"2": {
"user_id": 32,
"title": "milk"
}
"3": {
"user_id": 40,
"title": "eggs"
}
}
}but since we don’t have a Structured Query Language to use, we can’t simply grab all of a users todos by doing something like SELECT * FROM todos WHERE user_id = 32. To grab all of a users todos we would need to iterate over every todo, so this structure is not ideal for our use case.
Another issue to consider with the current setup is that the client (the Ionic 2 app using PouchDB for local storage) is interacting directly with the remote database (CouchDB):
as opposed to a solution with MySQL which would include a scripting layer in between to handle requests from the client (e.g. a PHP script hosted on a server):
With this architecture we can easily have our script in the middle verify who is making the request and only return the data for the appropriate user, which is a luxury we don’t have with our current set up.
So, the two main issues we need to solve with our PouchDB and CouchDB architecture are:
1) How do we keep track of which todo belongs to which user?
2) How do we make sure that a user can only retrieve their own data?
As I mentioned, the answer is not straightforward. With a relational approach there is, more or less, one “proper” way to approach the issue, but with NoSQL the answer is going to depend on what you are trying to achieve. NoSQL is much less structured, and much more flexible than relational databases (sometimes this might be a good thing, and somtimes it might be a bad thing).
Let’s consider a few potential options.
1. Create your own API
Similar to what we might do with a typical MySQL implementation, we can add in an intermediary step between the client and the CouchDB database. In the past I’ve covered how to set up a REST API with NodeJS, and we could do something similar here that might look something like this:
This way we would just need to hit the endpoints we create in our own API, and we will be able to run whatever logic we want on the server and return only what we want from the CouchDB database. In this case we might structure our data like this:
{
"todos": {
"329483424": {
"t1": {
"title": "bread"
},
"t2": {
"title": "milk"
}
},
"873298439": {
"t1": {
"title": "eggs"
}
},
"984560992": {
"t1": {
"title": "cookies"
}
}
}
}with this set up we would easily be able to grab the id of the user making the request when they hit our API, use NodeJS (or whatever else) to grab the entire JSON object for that users id (e.g. 329483424) from CouchDB, and then return it. Now we have all of that specfic users data without having to do any complex operations, and we can make sure that only their data is sent back.
The downside to this approach is that we lose the ability to automatically sync between the local PouchDB database and the remote CouchDB database, it would just be a normal REST API now with no fancy replication or offline syncing happening.
2. Use a single database
Let’s say that we don’t want to go with option one because we don’t just want to use CouchDB as a data store sitting behind a proxy, we want to make use of the integration with PouchDB. So we could go back to the structure of having our local PouchDB database syncing to a single CouchDB database, but this time we store multiple users data in that database, just like we might have done for option one:
{
"todos": {
"329483424": {
"t1": {
"title": "bread"
},
"t2": {
"title": "milk"
}
},
"873298439": {
"t1": {
"title": "eggs"
}
},
"984560992": {
"t1": {
"title": "cookies"
}
}
}
}But we don’t have that scripting layer now, so how do we enforce that a user can only access and modify their own data? By default, CouchDB has three user roles available:
- Server admin
- Database admin
- Database reader
A server admin is basically a super user that can do anything they want, a database admin can only read and write to a specific database, and a database reader can only read documents from a specific database.
So by default, there is no way for us to control access to specific documents on a per user basis, which makes implementing this kind of structure difficult. We could certainly make our application work with this model, but we could not guarantee that the users data would remain private.
Although this might not be achievable “out of the box”, per document access control is still certainly possible in CouchDB, using something like CoverCouch for example.
3. Create a database for each user
I think this is a great example of how different relational and NoSQL databases can be, and how you may need to approach problems completely differently. The idea of giving each user their own database in MySQL would usually be absurd, but with NoSQL it is actually quite a common solution. CouchDB can easily handle creating thousands of databases, so giving each user their own database isn’t an issue in itself.
So we could implement a solution that looks like this:
Now all we would have to do is sync each users local PouchDB database to their own remote database. In this case you will also need to make sure that each users database is only readable and writable by them, since by default in CouchDB everybody could read and write to everybody elses databases. We will get into security issues and how to handle this in the next tutorial though.
This solution allows us to easily replicate between PouchDB and CouchDB, while still being able to make sure that a user can only ever read and write their own documents. The downside of this approach is that it works best when the data is isolated (i.e. a user has their data and doesn’t care about anyone elses). If this were a different application, say one where you could share your todos with other users, then to pull in all todos a user should be able to see we would need to fetch data from a bunch of different databases. It is also harder to query the data when it is spread out among many different databases.
Similar alternatives to this approach also include creating “per group” or “per role” databases. If you were creating a company based todo application for example, you might create a database for each company that uses the application.
Summary
The best solution for our circumstance is clearly to create a database for each user – it’s the perfect situation for it since each user only cares about their own data. As I mentioned, there are additional security concerns that I haven’t got into in this tutorial, but we will be covering them in the next one when we implement a practical example.
Although there is one ideal approach for our particular use case, the answer may not always be so obvious. It’s important to understand the different approaches out there, as well as the strengths and weaknesses of the different approaches. I also think it’s important not to be too intimidated by all the options though, don’t get so caught up in figuring out the best approach that you don’t end up doing anything. Give things a go and see what happens.
If you want to look at some of the concepts I’ve discussed here in more detail, check out these great resources: