flouri.sh

Using multiple databases

I recently wrote a rails plugin called "use_db", which allowed you to use a different database for some of your ActiveRecord models. I started by reading this article (which was borrowed from the rails wiki), and decided to make a plugin out of it. You can use it in the following way:

class SomeBase < ActiveRecord::Base  
  use_db :prefix => "secdb_"
  self.abstract_class = true
end

class OtherDbModel < SomeBase
end

Now any calls to data in OtherDbModel will go to a database called "secdb_development" (or secdb_test, secdb_production, etc). The database.yml file could have the following additions to support this:

secdb_development:
  adapter: mysql
  database: secdb_development
  username: root

secdb_test:
  adapter: mysql
  database: secdb_test
  username: root

Testing multiple databases

One issue with my plugin, as stated on the original article, is that testing becomes very difficult. First, fixtures are automatically inserted into the primary database. Second, other databases will not automatically have their schemas migrated from dev to test.

Solving the fixture problem

I first examined active_record/fixtures.rb and noticed the following problem:

def delete_existing_fixtures
    @connection.delete "DELETE FROM #{@table_name}", 'Fixture Delete'
  end

  def insert_fixtures
    values.each do |fixture|
      @connection.execute "INSERT INTO #{@table_name} (#{fixture.key_list}) VALUES (#{fixture.value_list})", 'Fixture Insert'
    end
  end

These two methods are called automatically by the test helper when loading fixtures for a test. @connection was originally set to ActiveRecord::Base.connection, so the existing solution was not going to work. To solve this, I overrode those two methods in my plugin and replaced them with the following code:

alias_method :rails_delete_existing_fixtures, :delete_existing_fixtures

  def delete_existing_fixtures    
    m = get_model
    return rails_delete_existing_fixtures unless m && m.respond_to?(:uses_db?) && m.uses_db?
    connection = m.connection
    connection.delete "DELETE FROM #{m.table_name}", 'Fixture Delete'
  end

  alias_method :rails_insert_fixtures, :insert_fixtures

  def insert_fixtures
    m = get_model
    return rails_insert_fixtures unless m && m.respond_to?(:uses_db?) && m.uses_db?
    connection = m.connection
    values.each do |fixture|
      connection.execute "INSERT INTO #{m.table_name} (#{fixture.key_list}) VALUES (#{fixture.value_list})", 'Fixture Insert'
    end
  end

This first code attempts to get the model associated with a fixture. If found, it asks that model if it uses a different database. FInally, it uses the connection of the model to execute the fixture INSERT and DELETE SQL commands. If any of this process fails, it falls back on the existing rails fixture methods.

Solving the schema migration problem

Rails typically does schema migrations using a rake task which runs before "rake test". It typically divides the work into 3 segments, dump_db_structure, clone_db_structure, and purge_db. The sequence is as follows:

• dump_db_structure dumps the development schema without data to an adapter-specific SQL file
• purge_db deletes all rows from the test database
• clone_db_structure imports the SQL dump into the test database

I simply duplicated the existing rake code, and modified it to use a different database connection. At the end of the day, I could execute a single command to migrate a second database. I chose to execute the command in my test helped in the following manner:

unless defined?(MIGRATED_SEC_DB_FOR_TEST)
  UseDbTest.prepare_test_db(:prefix => "secdb_")
  MIGRATED_SEC_DB_FOR_TEST = true
end

The syntax is very similar to the "use_db" helper.

Source code

Download the first release 0.0.1 of use_db rails plugin here.

UPDATE: Look for the next version of this plugin in this blog

Originally posted on ELC

See ELC's other Rails Plugins