MongoDB with PostgreSQL in One Rails App

Question

Can I use MongoDB and a PostgreSQL in one rails app? Specifically I will eventually want to use something like MongoHQ. So far I have failed to get this working in experimentation. And it concerns me that the MongoDB documentation specifically says I have to disable ActiveRecord. Any advice would be appreciated.

Haemolysin

Answer 1

You don't need to disable ActiveRecord to use MongoDB. Check out Mongoid and just add the gem plus any models along side any of your existing ActiveRecord models. You should note that MongoHQ is just a hosting service for MongoDB and can be used alongside any Object Document Mapper (ODM).

For further details check http://mongoid.org/en/mongoid/docs/installation.html. Just skip the optional 'Getting Rid of Active Record' step.

Surculose

If you read the Mongoid documentation where it refers to getting rid of ActiveRecord, the specific caveat is You don't need ActiveRecord unless you're trying to use Mongo in concert with a SQL database. – Shona

@Shona Exactly. Thus, if you do want to use them in tandem just don't ActiveRecord. – Surculose

I was actually stuck on MongoMapper and not having much success. And it was in the MongoDb docs where they instructed you to get rid of ActiveRecord [ mongodb.org/display/DOCS/Rails+3+-+Getting+Started ] I will play with mongoid and see if I have better luck. I don't understand this last comment of "just don't ActiveRecord"... – Haemolysin

@MarkLybrand I meant just don't delete ActiveRecord. – Surculose

Okay. Thanks for the clarification. I am playing with mongoid/mongohq (where I was using mongo_mapper/local mongodb) now and when I get something working I will accept the answer. Stay tuned. :) – Haemolysin

I am taking this answer, since with Mongoid, I did manage to get a small app working with both sqlite3 and MongoDb, and feel confident that I can now do the same with Posgresql and MongoHQ. My hard drive is littered with false starts, as I tried one thing after another. Thanks for all the help. – Haemolysin

Answer 2

On a recent client site I worked with a production system that merged MySQL and MongoDB data with a single Java app. To be honest, it was a nightmare. To join data between the two databases required complex Java data structures and lots of code, which is actually databases do best.

One use-case for a two database system is to have the pure transactional data in the SQL database, and the aggregate the data into MongoDB for reporting etc. In fact this had been the original plan at the client, but along the way the databases became interrelated for transactional data.

The system has become so difficult to maintain that is is planned to be scrapped and replaced with a MongoDB-only solution (using Meteor.js).

Postgres has excellent support for JSON documents via it's jsonb datatype, and it is fully supported under Rails 4.2, out of the box. I have also worked with this and I find it a breeze, and I would recommend this approach.

This allows an easy mix of SQL and NoSQL transactions, eg

select id, blast_results::json#>'{"BlastOutput2","report","results","search","hits"}' 
from blast_caches 
where id in 
(select primer_left_blast_cache_id
from primer3_output_pairs where id in (185423,185422,185421,185420,185419) )

It doesn't offer the full MongoDB data manipulation features, but probably is enough for most needs.

Some useful links here:
http://nandovieira.com/using-postgresql-and-jsonb-with-ruby-on-rails
https://dockyard.com/blog/2014/05/27/avoid-rails-when-generating-json-responses-with-postgresql

There are also reports that it can outperform MongoDB on json:
http://www.slideshare.net/EnterpriseDB/the-nosql-way-in-postgres

Another option would be to move your Rails app entirely to MongoDB, and Rails has very good support for MongoDB.

I would not recommend running two databases, based on personal observations on how it can go bad.

Dreeda