# A note on multiple databases & associations

**URL:** <https://www.ruby-forum.com/t/a-note-on-multiple-databases-associations/106833>\
**Category:** Rails\
**Created:** [June 14, 2007, 1:49pm UTC](https://www.ruby-forum.com/t/a-note-on-multiple-databases-associations/106833 "2007-06-14T13:49:17Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Allan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ccd318/32.png) [@Allan](https://www.ruby-forum.com/u/Allan)\
**Post date:** [June 14, 2007, 1:49pm UTC](https://www.ruby-forum.com/t/a-note-on-multiple-databases-associations/106833/1 "2007-06-14T13:49:17Z")

</div>

Hi,

I have two databases (due to legacy) and have tried the two methods of  
having records stored in multiple databases as mentioned in  
[http://groups.google.com/group/rubyonrails-talk/browse\_frm/thread/9b002dee39ecb9f4/06f8856e8a82300e?lnk=gst&q=multiple+database&rnum=3#06f8856e8a82300e](http://groups.google.com/group/rubyonrails-talk/browse_frm/thread/9b002dee39ecb9f4/06f8856e8a82300e?lnk=gst&q=multiple+database&rnum=3#06f8856e8a82300e)

The models are:

Revision:  
belongs\_to :project

Project:  
has\_many :revision

The tables are:

ProjectDB.projects (storing the Project model)

val\_results\_development.revisions (to store the Revision models)

So for the first approach to dealing with multiple databases, in  
config/database.yml I have:

ProjectsDB:  
adapter: mysql  
database: ProjectsDB  
…

and in the Project model:

establish\_connection “ProjectsDB”

Which all seems to work. It can find projects

But if I do:

Project.find(:all, :include=\>:revisions)

I get:

ActiveRecord::StatementInvalid: Mysql::Error: #42S02Table  
‘ProjectsDB.revisions’ doesn’t exist: SELECT projects.`id` AS t0\_r0,  
projects.`name` AS t0\_r1, projects.`configfile` AS t0\_r2,  
projects.`descript` AS t0\_r3, projects.`updated` AS t0\_r4,  
revisions.`id` AS t1\_r0, revisions.`tag` AS t1\_r1,  
revisions.`project_id` AS t1\_r2 FROM projects LEFT OUTER JOIN  
revisions ON revisions.project\_id = projects.id

It’s looking in the ProjectsDB for the revisions table. The revisions  
table actually lives in the database called  
‘val\_results\_development’.

There’s a similar problem with Revision.find(:all,:include=\>:project),  
it searches for the projects table in the ‘val\_results\_development’  
database when it should be looking in the ‘ProjectsDB’ database.

OK, so let’s try another approach, in the Project model I say:

set\_table\_name “ProjectsDB.projects”

and this seems to work, I can get projects from revisions and vice  
versa.

But one of the caveats (as described in the Wiki) is that this second  
approach won’t work if the database is on another host machine - which  
is what I’m going to hit soon!

Does anyone have a way around that, other than resorting to custom  
sql? I don’t really want custom sql since I’m hoping to scaffold much  
of the app.

Thanks,

Allan

PS This entry  
[http://groups.google.com/group/rubyonrails-talk/browse\_frm/thread/57e275b63c3e4813/d3151e812114bde5?lnk=gst&q=multiple+database&rnum=9#d3151e812114bde5](http://groups.google.com/group/rubyonrails-talk/browse_frm/thread/57e275b63c3e4813/d3151e812114bde5?lnk=gst&q=multiple+database&rnum=9#d3151e812114bde5)  
suggests that it is not possible, but the set\_table\_name trick works  
as far as I can see.

---

<div class="post-metadata">

**Author:** ![Allan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ccd318/32.png) [@Allan](https://www.ruby-forum.com/u/Allan)\
**Post date:** [June 15, 2007, 11:18am UTC](https://www.ruby-forum.com/t/a-note-on-multiple-databases-associations/106833/2 "2007-06-15T11:18:08Z")

</div>

Bad form to follow up on one’s own post but I have been thinking …

On Jun 14, 12:48 pm, Allan [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> But one of the caveats (as described in the Wiki) is that this second  
> approach won’t work if the database is on another host machine - which  
> is what I’m going to hit soon!
> 
> Does anyone have a way around that, other than resorting to custom  
> sql? I don’t really want custom sql since I’m hoping to scaffold much  
> of the app.

So this isn’t going to work, you can’t access stuff on another server  
in a ‘standard’ query.

But it would be nice to not have to use the set\_table\_name trick.

Allan