Does it make sense to use BOTH mongodb and mysql in the same rails application?

Question

I have a good reason to use mongodb for part of my app. But people generally describe it as not a good fit for "transactional" applications like a bank where transactions have to be exact/consistent, etc.

Does it make sense to split the models up in Rails and have some of them use MySql and others mongo? Or will this generally cause more problems than it's worth?

I'm not building a banking app or anything, but was thinking it might make sense for my users table or or transactions table (recording revenue) to do that part in MySql.

Answer 1

That's what we do with CouchDB and PostgreSQL.

All our users and groups kinds of things are in the postgresql database.
Anything else (in our case, some records with statistical datas) are in the couchdb database.

In our case, it allows us to have one couchdb database per client (the app connects itself to one or an other depending of the user's host).
And only one postgresql database having all the users in it.

So yes I think it's a good idea to have a SQL and a NOSQL database in the same application.

Answer 2

I would be wary of a split approach though I wouldn't exclude it.

If you need MySQL + InnoDB and it sounds like you do, I'd only introduce MongoDB for preferably more isolated parts that have something significant to gain from the benefits that MongoDB brings, and have minimal relations to the core MySQL tables.

I'm currently wrestling with the same problem, a Rails app with 20ish tables on MySQL. Using Mongodb would solve some database design woes (nested sub-objects and indexable array columns especially), but it's hard to justify the extra overhead of:

• 2 different querying methods
• Awkward custom code along the seams of MySQL/Mongo objects that relate to each other.
• A second database platform to support in production.

I'm in the process of rolling out MongoDB for our logging table, and I'm going to take it from there.

Answer 3

I'd say you've answered your own question here.

I have a good reason to use mongodb for part of my app.

Assuming you also have a good reason for keeping other parts in MySQL, I would say the answer is yes. Your question implies (at least to me) that you have a good and well-researched understanding of the various options and their strengths and weaknesses and have therefore reached a sensible conclusion in that splitting your models is doable.

Assuming the two halves are not linked in some way (relationships between the two sounds like a recipe for pain later), I would suggest that you go for it and use each tool for what it is best at.

It is possible to address some of the concerns Michael raises with this approach. since you are focusing on using Rails, you can use ActiveRecord for your MySQL-based models and use MongoMapper for your MongoDb based models. This way, you won't have to deal with two completely different querying methods since MongoMapper provides a very ActiveRecordish approach. Of course, you can easily drop down into Mongo-specific querying as and when you need to.

The concern regarding relationships cross-DB are valid in my opinion and if this is something you'd end up having a lot of, I would definitely advise examining the situation to make sure this is something you're happy to live with. I imagine you might be saving up a lot of pain for later in that particular case.

Overall, I'd suggest that as long as your two halves are relatively disconnected from each other, a split-personality persistence layer will work well for you.

Answer 4

Think also about durability: http://blog.mongodb.org/post/381927266/what-about-durability, for example when you lose power (electricity).

Answer 5

There's no reason you can't keep your users table in MongoDB. Whether you decide to keep a transactions table in MongoDB is another question. If you need multi-object transactions, then you have to use a relational database that supports transactions. If you don't need this style of transaction, then using MongoDB for this can still be a good idea.

Keep in mind that if you use MongoDB, we recommend running with replication for failover.

The overall thing to keep in mind is that if you're using a relational database because of the consistency guarantees, you still need to make sure that your hardware is configured to take advantage of those features. See the cautionary note here:

http://dev.mysql.com/doc/refman/4.1/en/innodb-configuration.html

If you're not taking these precautions, then there's not a huge difference between MongoDB's durability and that of a relational database.