Multiple-Databases In Single Rails Application

This is a simple post to list some of the challenges faced during multiple database setup in a single rails application. The content in the post is available over the internet, we have consolidated it here to bring it at one place.

While integrating multiple third party services we felt the need to store each and every pair of request-response shared between our application and others. We decided to use database tables to store this information instead of files or other options so to have an easy disintegration of data. This helped us in filtering for some particular handshakes based on certain scenarios. Also we could pick requests that failed to respond or if any request claimed to be sent to us but never did.

We thought it would be better to place this in a separate database to avoid clouding our main database. Plus we could also avoid picking up this content while backing up our already big database, as this was not that “critical”.

So we set our sails in this direction and Rails made it very easy by letting us set up different database connections for such classes/models.

To start with, we needed a common place to hook this for all targeted models, so we made a parent model:

#.. /app/models/api_log.rb
class ApiLog < ActiveRecord::Base
  #very important to set this up as an abstract class
  self.abstract_class = true 
  #tell rails to use a specific connection instead of default
  establish_connection "log_database_#{Rails.env}" 
end

Note: Making this as an abstract class is very important. You can try removing it and then you shall see the importance of doing this.

And then add configuration for this new connection in database.yml, like this:

log_database_development:
  adapter: mysql2
  encoding: utf8
  reconnect: true
  database: api_logs_development
  pool: 5
  username: root
  password: 
  host: 127.0.0.1
  socket: /tmp/mysql.sock

api_logs_development is the name of the database for storing log tables other than the main database. So now we have two separate database configurations, one for our main application and other for accessing logs database.

Now, to use this logs database, to maintain some particular third party api logs we can inherit a new model from our ApiLog class as:

class SomeThirdPartyApiLog < ApiLog
  ...
  ...
end

And use this model to save all the log information in a separate log database.

Tweak needed in migrations :
Our migration to create log tables would look something like this:

class CreateSomeThirdPartyApiLogs < ActiveRecord::Migration
  def connection
    # Forcing to use the connection bound with the logs database
    @connection = ActiveRecord::Base.establish_connection("log_database_#{Rails.env}").connection
  end

  def change
    create_table :some_third_party_api_logs do |t|
      #…
    end

    # Forcing to use the environments default db connection further to ensure schema_migrations table 
    # update with an entry for this migration. Needed so that it does not try to re-run this migration 
    # on further attempts and break since the table had already been created.
    @connection = ActiveRecord::Base.establish_connection("#{Rails.env}") 
  end
end

So to sum it up for migrations:
1) Set up connection to new db connection before the migration is run to have table in the new db.
2) We want the schema to be updated in the default database so that this migration is not re-run. The schema is updated after the migration has been executed successfully so we need to reset connection to default db at the end of our change method.

Here, we have moved a specific category of data to a different database. You can use this for more specific and complex needs for some other data management in your application.

Integration with Rspec.
Another catch with this is that this might not work well with Rspec. Plus anyway a need for separate database is not needed in test environment. When we would run with Rspec we would see that at times the table look ups are mixed in wrong databases. So we chose to simply skip it for testing. So a little modification need to be done to our abstract class and migrations:

Our mother ApiLog class now looks like :

#.. /app/models/api_log.rb
class ApiLog < ActiveRecord::Base
  #very important to set this up as an abstract class
  self.abstract_class = true 
  #tell rails to use a specific connection instead of default
  establish_connection "log_database_#{Rails.env}" unless Rails.env.test?
end

Tweak needed in migrations :

class CreateSomeThirdPartyApiLogs < ActiveRecord::Migration
  def connection
    if Rails.env.test?
      @connection = ActiveRecord::Base.establish_connection("#{Rails.env}").connection
    else
      @connection = ActiveRecord::Base.establish_connection("log_database_#{Rails.env}").connection
   end
  end

  def change
    create_table :some_third_party_api_logs do |t|
      #…
    end

    #avoiding the tweak for test environment
    @connection = ActiveRecord::Base.establish_connection("#{Rails.env}").connection unless Rails.env.test?
  end
end

By doing this we are telling rails to NOT to worry about an alternate DB for test environment.

That’s all. Hope this helps you somewhere in case you are stuck with integrating multiple databases or you would now go for the same considering the ease and the use of integrating it.

Tagged as:

Leave a Reply

Your email address will not be published. Required fields are marked *