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
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: 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.
Timeline
- HTTP Auth with Restful Authentication
- TuneCore covered on TUAW!
- TabTerm Release
- write_inheritable_attribute.............. and friends !
- Liquid Filter Extensions
- Using and Testing Rails with Multiple Databases
- Securing your Models
- Installing RMagick properly in OSX
- RubyGems 0.91 and the "refresh" error
- ActiveRecord attribute update semantics
- Installing Webmin on Ec2
Comments
Using erb in database.yml does not work with your Plugin.
Her is a patch for it.
Index: lib/use_db.rb
-lib/use_db.rb (revision 108) + lib/use_db.rb (working copy)@ return options else str = ”#{prefix}#{rails_env}#{suffix}” - connections = YAML.load(File.read ”#{RAILS_ROOT}/config/database.yml”) + connections = YAML::load(ERB.new(IO.read(”#{RAILS_ROOT}/config/database.yml”)).result) raise “Cannot find database specification. Configuration ’#{str}’ expected in config/database.yml” if (connections[str].nil?) return connections[str] endThanks Zickzackv
Sorry … missed out the other alteration needed to get the previous post to work:
class 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| # note the additional arg for fixture.key_list() & fixture.value_list() connection.execute "INSERT INTO #{m.table_name} (#{fixture.key_list(connection)}) VALUES (#{fixture.value_list(connection)})", 'Fixture Insert' end end ... endThe patch to add ERB isn’t compatible with using ERB in fixtures. What happens is, when you use a model in your fixture, it calls use_db which, in turn, calls `connection = YAML::load(ERB.new….`
Wherever your fixtures use a model, the output of database.yml gets concatenated into the fixture! It’s because ERB.new(...).result in get_use_db_conn_spec overwrites & concatenated into _erbout, overwriting part of the fixture.
This is easy to recreate. Call a model from inside one of your fixtures (a model that uses use_db) ... call anything, like `User.count`. you can just say `User` because we just need the User constant to get loaded. Now, to demo what happens when the fixtures run, ./script/console >> puts ERB.new(‘path_to_fixture.yml’).result
Here’s a patch to fix this:
--- a/vendor/plugins/use_db/lib/use_db.rb +++ b/vendor/plugins/use_db/lib/use_db.rb @@ -55,7 +55,7 @@ module UseDbPlugin return options else str = "#{prefix}#{rails_env}#{suffix}" - connections = YAML.load(ERB.new(IO.read("#{RAILS_ROOT}/config/database.yml")).result) + connections = YAML.load(ERB.new(IO.read("#{RAILS_ROOT}/config/database.yml"), nil, nil, '_different_erb_out_variable_incase_ERB_is_being_evaluated').result) raise "Cannot find database specification. Configuration '#{str}' expected in config/database.yml" if (connections[str].nil?) return connections[str] endWhenever you use ERB, you need to use a different variable name for the string used for building the output if there’s a chance that the ERB will be evaluated inside of another ERB evaluation.
Here’s doco for ERB#new http://www.ruby-doc.org/stdlib/libdoc/erb/rdoc/classes/ERB.html#M000649
I forked the svn repo on github and patched it for the above fix. The commit diff for the patch can be found here: http://github.com/remi/use_db/commit/4311da14e765ddf8c0ef78cb389c9004c9b11e53
I had an issue with tests failing all over the place. And no, it wasn’t because my code didn’t work. :)
Any test case that defined a setup method was running into issue, I assume because it then didn’t use the setup path that would do all the fixture/model/connection checking.
By inserting alias_method :old_setup, :setup before each test’s definition of setup and then adding a call to old_setup at the end of the method, things started working again.