Shifting from MYSQL to CouchDB: a User Study

Ryan Meredith talked to us about how he first discovered CouchDB and what his experience has been employing it in applications.

How did you hear about CouchDB, and why did you choose to use it?

I first heard of CouchDB when I talked to the boss of a company that was using it as the main database in their company. He liked how fast it was and the simplicity of using documents for rapid development. They found it complemented their JavaScript driven front-end and could create web content very quickly.

Did you have a specific problem that CouchDB solved?

We were given a project to create a warehouse management system from scratch. The system involved coordinating from the main-office, stock entering the warehouse and leaving to customers. The warehouse was 2 hours away in a forest with an intermittent internet connection.

My initial thoughts were to use MYSQL replication but with many tables over an unreliable connection this would have been fragile. The second idea was to use a messaging service like RabbitMQ to send the data between MYSQL instances, but this risked race conditions and version conflicts.

Finally, we looked at other solutions. We looked at other NoSQL databases but CouchDB was a clear winner in this use case. Replication is CouchDB’s superpower, the built-in conflict resolution made us feel confident we would not lose data. Even if the internet was very bad our databases in the two locations would eventually sync.

For the folks who are unsure of how they could use CouchDB–because there are a lot of databases out there—could you explain the use case?

The warehouse only had CouchDB, this ran the API for the barcode machines, scanners, and input/output lists. This information was then replicated back to the office so the office would know exactly what was in the warehouse and be able to set up deliveries to customers quickly.

We were using MYSQL and CouchDB at the office, this created tricky problems as we would have two sources of truth. There were fields in MYSQL that needed updating, for instance, when the stock reached the warehouse. There was too much code reliant on MYSQL to use CouchDB directly so we watched on a cron the CouchDB changes feed for certain documents written at the warehouse, which we then processed into MYSQL, updating the web portal.

The whole project took 6 months with two of us working on it, I think it would have taken a lot longer without CouchDB, especially as neither of us are dedicated system administrators.

What would you say is the top benefit of using CouchDB?

Replication is incredibly easy in CouchDB, it requires so little effort to set up. Using documents instead of tables and fields took a little getting used to and we initially made mistakes in our database design, but once you are used to it, it is a quick way to build applications.

What tools are you using in addition for your infrastructure? Have you discovered anything that pairs well with CouchDB?

Our backend runs on PHP, which is not the best match for CouchDB’s Javascript documents but with the rest API being so simple it did not take us long to set up some PHP models to curl information to and from the database. One note of caution though: with PHP being weakly typed (string, int) you need to make sure you send the correct type to CouchDB.

I have been using CouchDB with Docker on my personal projects and that is extremely easy to use. I have it set up with CouchDB Lucene for full-text search which is amazing as before I was struggling to implement text search with only regex mango queries.

What are your future plans with your project? Any cool plans or developments you want to promote?

I am currently working on a personal project, which, like most personal projects, is still in the pipeline.

I used to be a high school physics teacher so I am creating software for schools which I hope to deploy in the next couple of months. I have converted to using CouchDB because of its quick document-driven prototyping. I also want to run two servers as a load-balanced pair for redundancy, with simple replication I can be confident that all my data is safe.

Use cases are a great avenue for sharing useful technical information. Please consider joining the fun! Additionally, if there’s something you’d like to see covered on the CouchDB blog, we would love to accommodate. Email us!

For more about CouchDB visit couchdb.org or follow us on Twitter at @couchdb

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s