# Strategies for Unit testing 2 databases

**URL:** <https://www.ruby-forum.com/t/strategies-for-unit-testing-2-databases/102399>\
**Category:** Rails\
**Created:** [February 8, 2006, 5:17pm UTC](https://www.ruby-forum.com/t/strategies-for-unit-testing-2-databases/102399 "2006-02-08T17:17:18Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![evronm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/f1d935/32.png) [@evronm](https://www.ruby-forum.com/u/evronm)\
**Post date:** [February 8, 2006, 5:17pm UTC](https://www.ruby-forum.com/t/strategies-for-unit-testing-2-databases/102399/1 "2006-02-08T17:17:18Z")

</div>

Hi,

My application spans 2 (or more) databases for some very specific  
reasons… My models are working fine, even dynamically establishing  
connections as needed at runtime, and spanning relationships across the  
databases (which really impressed me). The problem is, I can’t seem to  
force Units for these models (that use a secondary db) to load their  
fixtures into and use the secondary database.

I tried applying this:

Project.establish\_connection(:test\_group)  
(where :test\_group is a database connection defined in database.yml)

at various locations within the test itself, and test\_helper.rb… but  
my unit is failing with this error:

ActiveRecord::StatementInvalid: Mysql::Error: Table  
‘scoot\_test.projects’ doesn’t exist: DELETE FROM projects  
(and scoot\_test is the ‘main’ database, but projects should be a table  
in the ‘secondary’ database)

So, any advice on how I might be able to get this to work?

Thanks,  
Mike

---

<div class="post-metadata">

**Author:** ![evronm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/f1d935/32.png) [@evronm](https://www.ruby-forum.com/u/evronm)\
**Post date:** [February 8, 2006, 5:46pm UTC](https://www.ruby-forum.com/t/strategies-for-unit-testing-2-databases/102399/2 "2006-02-08T17:46:11Z")

</div>

Hi Mikes,

Rails stores the database tablename within the  
corresponding ActiveRecord::base subclass. inside the  
“connection\_specification” class there is a hash which  
contains information about which class is using which  
db connection. and if there is no connection define  
for a class it will go to its parent.

So what I understand, is that your have two databases,  
and the class can finf its table? am I right.

if it is the case, it is because the class is using  
the wrong connection, so the worng database.

SO I suggestion you to create two parents for your  
databases. like db1 \<activeRecords::base and db2  
\<activeRecords::base. all the table-class from db1  
should inherited not directly form  
activeRecords::base, but db1 and same for the  
table-class from db2. when you connect dynamically to  
your database, uses

db1.establish\_connection(…) so all the table-class  
used db1 will have the same db connection and the same  
for db2.

actually, I’m using 2 db also. it works for me, so I  
hope it will work for you.

Saiho

— Mike E. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> force Units for these models (that use a secondary  
> test\_helper.rb… but  
> work?
> 
> Thanks,  
> Mike
> 
> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

* * *

Do You Yahoo!?  
Tired of spam? Yahoo! Mail has the best spam protection around

> **[\[object](https://mail.yahoo.com/)**
>
> \[object

---

<div class="post-metadata">

**Author:** ![evronm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/f1d935/32.png) [@evronm](https://www.ruby-forum.com/u/evronm)\
**Post date:** [February 8, 2006, 8:05pm UTC](https://www.ruby-forum.com/t/strategies-for-unit-testing-2-databases/102399/3 "2006-02-08T20:05:43Z")

</div>

Hi Mike,

Did you try to connect to 2 db within the same action?  
if it is the case, I have an example code, that may  
help, but not for all db, I’m using postgresql. so not  
sure that it will work for all other databse.

if you are trying to use two different database in two  
different action/controllers, there is an example on  
[http://wiki.rubyonrails.org/rails/pages/HowtoUseMultipleDatabases](http://wiki.rubyonrails.org/rails/pages/HowtoUseMultipleDatabases)

May be you should try:  
Project \< ActiveRecord::Base  
if (RAILS\_ENV == “test”) then  
Project.establish\_connection(:db2\_test)

Project.establish\_connection(:db2)

> end  
> end

— Mike E. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> when trying to write  
> end  
> Saiho Y. wrote:  
> define
> 
> > SO I suggestion you to create two parents for your  
> > used db1 will have the same db connection and the
> 
> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

If God really exists, I would like to  
know what the dinosaurs have done to  
deserve their extinction.

Water is unknown to fishes,  
until they discover air.

> **[Yahoo | Mail, Weather, Search, Politics, News, Finance, Sports & Videos](https://www.yahoo.com)**
>
> Latest news coverage, email, free stock quotes, live scores and video are just the beginning. Discover more every day at Yahoo!

* * *

Do You Yahoo!?  
Tired of spam? Yahoo! Mail has the best spam protection around

> **[\[object](https://mail.yahoo.com/)**
>
> \[object

---

<div class="post-metadata">

**Author:** ![evronm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/f1d935/32.png) [@evronm](https://www.ruby-forum.com/u/evronm)\
**Post date:** [February 8, 2006, 6:07pm UTC](https://www.ruby-forum.com/t/strategies-for-unit-testing-2-databases/102399/4 "2006-02-08T18:07:15Z")

</div>

Thanks Saiho,

If I understand you correctly, that’s kind of what I’m doing…  
Project \< ActiveRecord::Base  
Project.establish\_connection(:db2)  
end  
And all other models in db2 inherit from Project.

That works fine from the console… the problem is, when trying to write  
unit tests, the fixtures for Project are being loaded into (well,  
attempted at least) db1, but I can’t figure out why that would be.

I tried this:  
Project \< ActiveRecord::Base  
Project.establish\_connection(:db2)  
if (RAILS\_ENV == “test”) then  
Project.establish\_connection(:db2\_test)  
end  
end

But it’s still trying to load things into db1\_test… it almost seems  
like ActiveRecord::Base.establish\_connection is turned off during  
testing…

M.

---

<div class="post-metadata">

**Author:** ![evronm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/f1d935/32.png) [@evronm](https://www.ruby-forum.com/u/evronm)\
**Post date:** [February 8, 2006, 8:11pm UTC](https://www.ruby-forum.com/t/strategies-for-unit-testing-2-databases/102399/5 "2006-02-08T20:11:29Z")

</div>

I took a look at this a few months ago.

It looks like the test code is just broken for multiple databases. The  
only  
database the code uses is the one set for Base, so your per-model  
database  
won’t work during testing.

It looked to be a non-trivial piece of work to make the test code work  
with  
multiple databases, so I moved on to something else.

In my case, it doesn’t matter too much, as I only have one very simple  
model  
in my second DB, and it’s real obvious in my app when it breaks!

---

<div class="post-metadata">

**Author:** ![evronm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/f1d935/32.png) [@evronm](https://www.ruby-forum.com/u/evronm)\
**Post date:** [February 8, 2006, 8:17pm UTC](https://www.ruby-forum.com/t/strategies-for-unit-testing-2-databases/102399/6 "2006-02-08T20:17:10Z")

</div>

sorry,

I pressed on the wrong button:) what I want ot say is:

May be you should try:  
Project \< ActiveRecord::Base  
if (RAILS\_ENV == “test”) then  
Project.establish\_connection(:db2\_test)  
else  
Project.establish\_connection(:db2)  
end

I think this is better, because the class will not be  
assigned two times.

— Saiho Y. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> two  
> different action/controllers, there is an example on

[http://wiki.rubyonrails.org/rails/pages/HowtoUseMultipleDatabases](http://wiki.rubyonrails.org/rails/pages/HowtoUseMultipleDatabases)

> > end  
> > I’m doing…  
> > attempted at least) db1, but I can’t figure out
> > 
> > > for a class it will go to its parent.  
> > > your  
> > > table-class  
> > > Saiho
> 
> Do You Yahoo!?  
> Tired of spam? Yahoo! Mail has the best spam  
> protection around  
> [http://mail.yahoo.com](http://mail.yahoo.com)
> 
> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

* * *

Do You Yahoo!?  
Tired of spam? Yahoo! Mail has the best spam protection around

> **[\[object](https://mail.yahoo.com/)**
>
> \[object

---

<div class="post-metadata">

**Author:** ![evronm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/f1d935/32.png) [@evronm](https://www.ruby-forum.com/u/evronm)\
**Post date:** [February 8, 2006, 8:47pm UTC](https://www.ruby-forum.com/t/strategies-for-unit-testing-2-databases/102399/7 "2006-02-08T20:47:29Z")

</div>

Tom F. wrote:

> in my second DB, and it’s real obvious in my app when it breaks!  
> Did you try to connect to 2 db within the same action?  
> if (RAILS\_ENV == “test”) then  
> Project.establish\_connection(:db2\_test)
> 
> Project.establish\_connection(:db2)

Ok, I’ve been digging into ActiveRecord and fixtures.rb and it looks  
like the reason this is broken is because fixtures don’t really look at  
models to do their magic… they (by default) use the db connection of  
ActiveRecord::Base.

I found a couple ways around this… The more complicated way (which  
doesn’t seem to work anyway) was to create your own instance of  
Fixtures:  
class ProjectTest \< Test::Unit::TestCase  
fixtures :groups  
Project.establish\_connection(:test\_group)  
@fix = Fixtures.new(Project.connection, “projects”,  
File.dirname( **FILE** ) + “/…/fixtures”)  
@fix.insert\_fixtures  
…

The problem is insert\_fixtures isn’t really doing anything… I’m sure  
there’s a way I just don’t completely understand the API.

I moved onto another method, which is to create my own custom rake tasks  
that will load my custom fixtures into the 2nd database as needed. Then  
I’ll insert it into the default test\_units and test\_functional tasks.  
I’ll post the results as soon as I’m done.

m

---

<div class="post-metadata">

**Author:** ![evronm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/f1d935/32.png) [@evronm](https://www.ruby-forum.com/u/evronm)\
**Post date:** [February 22, 2006, 11:03pm UTC](https://www.ruby-forum.com/t/strategies-for-unit-testing-2-databases/102399/8 "2006-02-22T23:03:24Z")

</div>

> end
> 
> in the test - instead of “fixtures :people” something like this:
> 
> Fixtures.create\_fixtures(File.dirname( **FILE** ) + “/…/fixtures”,  
> [“people”]){Person.connection}
> 
> the connection can be passed in a block. I’d love to see this or better  
> solution in the chapter on using multiple databases in Rails Recipes.  
> Please 🙂

Great idea! I’m adding this (or something like it) to my TODO list.

## Thanks!

Chad F.  
[http://chadfowler.com](http://chadfowler.com)  
[http://pragmaticprogrammer.com/titles/fr\_rr/](http://pragmaticprogrammer.com/titles/fr_rr/) (Rails Recipes - In Beta!)  
[http://pragmaticprogrammer.com/titles/mjwti/](http://pragmaticprogrammer.com/titles/mjwti/) (My Job Went to India,  
and All I Got Was This Lousy Book)  
[http://rubycentral.org](http://rubycentral.org)  
[http://rubygarden.org](http://rubygarden.org)  
[http://rubygems.rubyforge.org](http://rubygems.rubyforge.org) (over one million gems served!)

---

<div class="post-metadata">

**Author:** ![evronm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/f1d935/32.png) [@evronm](https://www.ruby-forum.com/u/evronm)\
**Post date:** [February 22, 2006, 12:49pm UTC](https://www.ruby-forum.com/t/strategies-for-unit-testing-2-databases/102399/9 "2006-02-22T12:49:44Z")

</div>

Mike E. wrote:

> Ok, I’ve been digging into ActiveRecord and fixtures.rb and it looks  
> like the reason this is broken is because fixtures don’t really look at  
> models to do their magic… they (by default) use the db connection of  
> ActiveRecord::Base.
> 
> I found a couple ways around this… The more complicated way (which  
> doesn’t seem to work anyway) was to create your own instance of  
> Fixtures:  
> class ProjectTest \< Test::Unit::TestCase  
> fixtures :groups  
> Project.establish\_connection(:test\_group)  
> @fix = Fixtures.new(Project.connection, “projects”,  
> File.dirname( **FILE** ) + “/…/fixtures”)  
> @fix.insert\_fixtures  
> …
> 
> The problem is insert\_fixtures isn’t really doing anything… I’m sure  
> there’s a way I just don’t completely understand the API.
> 
> I moved onto another method, which is to create my own custom rake tasks  
> that will load my custom fixtures into the 2nd database as needed. Then  
> I’ll insert it into the default test\_units and test\_functional tasks.  
> I’ll post the results as soon as I’m done.
> 
> m

Hello,

this seems to be working:

in the model:

if ENV[“RAILS\_ENV”]==“test”  
Person.establish\_connection “db\_test”  
else  
Person.establish\_connection “db”  
end

in the test - instead of “fixtures :people” something like this:

Fixtures.create\_fixtures(File.dirname( **FILE** ) + “/…/fixtures”,  
[“people”]){Person.connection}

the connection can be passed in a block. I’d love to see this or better  
solution in the chapter on using multiple databases in Rails Recipes.  
Please 🙂

–  
Agnieszka F.