The ELC Community Blog

A knowledge exchange on Ruby on Rails and Agile Development

Using and Testing Rails with Multiple Databases

by stevend on March 08, 2007

Using multiple databases

   1  <pre>class SomeBase < ActiveRecord::Base  
   2    use_db :prefix => "secdb_"
   3    self.abstract_class = true
   4  end
   5  
   6  class OtherDbModel < SomeBase
   7  end</pre>

   1  <pre>secdb_development:
   2    adapter: mysql
   3    database: secdb_development
   4    username: root
   5  
   6  secdb_test:
   7    adapter: mysql
   8    database: secdb_test
   9    username: root</pre>

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:

   1  <pre>  def delete_existing_fixtures
   2      @connection.delete "DELETE FROM #{@table_name}", 'Fixture Delete'
   3    end
   4  
   5    def insert_fixtures
   6      values.each do |fixture|
   7        @connection.execute "INSERT INTO #{@table_name} (#{fixture.key_list}) VALUES (#{fixture.value_list})", 'Fixture Insert'
   8      end
   9    end</pre>

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:

   1  <pre>  alias_method :rails_delete_existing_fixtures, :delete_existing_fixtures
   2  
   3    def delete_existing_fixtures    
   4      m = get_model
   5      return rails_delete_existing_fixtures unless m && m.respond_to?(:uses_db?) && m.uses_db?
   6      connection = m.connection
   7      connection.delete "DELETE FROM #{m.table_name}", 'Fixture Delete'
   8    end
   9  
  10    alias_method :rails_insert_fixtures, :insert_fixtures
  11  
  12    def insert_fixtures
  13      m = get_model
  14      return rails_insert_fixtures unless m && m.respond_to?(:uses_db?) && m.uses_db?
  15      connection = m.connection
  16      values.each do |fixture|
  17        connection.execute "INSERT INTO #{m.table_name} (#{fixture.key_list}) VALUES (#{fixture.value_list})", 'Fixture Insert'
  18      end
  19    end</pre>

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:

   1  <pre>unless defined?(MIGRATED_SEC_DB_FOR_TEST)
   2    UseDbTest.prepare_test_db(:prefix => "secdb_")
   3    MIGRATED_SEC_DB_FOR_TEST = true
   4  end</pre>

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.

See our other Rails Plugins