# Multiple databases and establishing associations

**URL:** <https://www.ruby-forum.com/t/multiple-databases-and-establishing-associations/141237>\
**Category:** Rails\
**Created:** [June 23, 2008, 10:36pm UTC](https://www.ruby-forum.com/t/multiple-databases-and-establishing-associations/141237 "2008-06-23T22:36:38Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![danielb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/c57346/32.png) [@danielb](https://www.ruby-forum.com/u/danielb)\
**Post date:** [June 23, 2008, 10:36pm UTC](https://www.ruby-forum.com/t/multiple-databases-and-establishing-associations/141237/1 "2008-06-23T22:36:38Z")

</div>

Hi,

Rails 1.2.6

I’ve got two models from two different databases that I wish to  
associate if possible. The association is not based on a primary key  
for either model, but on a “job\_number” field that both tables have  
(which is unique in both tables). Here’s what I tried:

require ‘active\_record’

class Job \< ActiveRecord::Base  
establish\_connection(:adapter =\> “oracle”, …)

self.table\_name = ‘job’  
self.primary\_key = ‘job\_id’

has\_one :schedule, :foreign\_key =\> :job\_number  
end

class Schedule \< ActiveRecord::Base  
establish\_connection(:adapter =\> “postgresql”, …)

belongs\_to :job, :foreign\_key =\> :job\_number  
end

j = Job.find\_by\_job\_number(‘12345A’)  
j.schedule =\> nil # ???

# Also tried:

s = Schedule.find\_by\_job\_number(‘12345A’)  
s.job =\> nil # ???

No error, but the association should definitely not be nil.

What’s the proper way to do what I’m trying? On a side note, how can I  
inspect the sql that AR is generating?

Thanks,

Dan

---

<div class="post-metadata">

**Author:** ![danielb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/c57346/32.png) [@danielb](https://www.ruby-forum.com/u/danielb)\
**Post date:** [June 24, 2008, 10:22pm UTC](https://www.ruby-forum.com/t/multiple-databases-and-establishing-associations/141237/2 "2008-06-24T22:22:57Z")

</div>

On Jun 23, 2008, at 1:36 PM, Daniel B. wrote:

> I’ve got two models from two different databases that I wish to  
> associate if possible. The association is not based on a primary key  
> for either model, but on a “job\_number” field that both tables have  
> (which is unique in both tables). Here’s what I tried:

I’m no expert on this one, but considering Rails isn’t very excited  
about working with more than one DB to start with (no way to declare  
multiple DBs in it’s own config system), I can’t imagine it doing  
associations across databases for you – let alone ones with  
completely different RDBMS engines.

I would really expect you’ll have to build the composition of the  
object yourself.

> how can I inspect the sql that AR is generating?

Look at the file /log/development.log

– gw

---

<div class="post-metadata">

**Author:** ![danielb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/c57346/32.png) [@danielb](https://www.ruby-forum.com/u/danielb)\
**Post date:** [June 26, 2008, 6:58pm UTC](https://www.ruby-forum.com/t/multiple-databases-and-establishing-associations/141237/3 "2008-06-26T18:58:38Z")

</div>

On Jun 24, 2:22 pm, Greg W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> associations across databases for you – let alone ones with  
> completely different RDBMS engines.
> 
> I would really expect you’ll have to build the composition of the  
> object yourself.

Any suggestions on how to do that exactly?

> > how can I inspect the sql that AR is generating?
> 
> Look at the file /log/development.log

Ok, thanks. Is there a way to inspect the sql in a standalone AR  
script?

Thanks,

Dan

---

<div class="post-metadata">

**Author:** ![danielb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/c57346/32.png) [@danielb](https://www.ruby-forum.com/u/danielb)\
**Post date:** [June 26, 2008, 7:06pm UTC](https://www.ruby-forum.com/t/multiple-databases-and-establishing-associations/141237/4 "2008-06-26T19:06:20Z")

</div>

Daniel B. wrote:

> On Jun 24, 2:22�pm, Greg W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> > associations across databases for you – let alone ones with �  
> > completely different RDBMS engines.
> > 
> > I would really expect you’ll have to build the composition of the �  
> > object yourself.
> 
> Any suggestions on how to do that exactly?

What if you create something like ActiveRecord2 that uses ActiveRecord  
library but makes a connection to a database defined in database.yml  
(like schedule\_db:)?

It would be an interesting project. And I’d be willing to help if you go  
that route.

- Ivan V.

---

<div class="post-metadata">

**Author:** ![danielb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/c57346/32.png) [@danielb](https://www.ruby-forum.com/u/danielb)\
**Post date:** [June 26, 2008, 7:05pm UTC](https://www.ruby-forum.com/t/multiple-databases-and-establishing-associations/141237/5 "2008-06-26T19:05:40Z")

</div>

On 26 Jun 2008, at 17:57, Daniel B. wrote:

> > about working with more than one DB to start with (no way to declare  
> > multiple DBs in it’s own config system), I can’t imagine it doing  
> > associations across databases for you – let alone ones with  
> > completely different RDBMS engines.

Actually I can’t see why it wouldn’t work. All it’s going to do is run  
select from bars where foo\_id = x.

The real problem here is that active record doesn’t do assocations  
where the foreign key isn’t pointing at the primary key on some other  
table.

Fred

> script?

not that I know of

---

<div class="post-metadata">

**Author:** ![danielb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/c57346/32.png) [@danielb](https://www.ruby-forum.com/u/danielb)\
**Post date:** [June 26, 2008, 9:17pm UTC](https://www.ruby-forum.com/t/multiple-databases-and-establishing-associations/141237/6 "2008-06-26T21:17:50Z")

</div>

On Jun 26, 2008, at 9:57 AM, Daniel B. wrote:

> > multiple DBs in it’s own config system), I can’t imagine it doing  
> > associations across databases for you – let alone ones with  
> > completely different RDBMS engines.
> > 
> > I would really expect you’ll have to build the composition of the  
> > object yourself.
> 
> Any suggestions on how to do that exactly?

It’s just brute force. You write all the code to coordinate the two  
AR models, but encapsulate it inside a wrapper object (aka composition).

You build a plain Ruby class composed of a Job object and a Schedule  
object. You use your existing AR classes for each (minus the  
associations code).

Rough 2-minute idea:

Class ScheduledJob  
def initialize(job\_number)  
@job = Job.find\_by\_job\_number(job\_number)  
@schedule = Schedule.find\_by\_job\_number(job\_number)  
end  
end

Now you can

my\_job = ScheduledJob.new(‘12345A’)

and get at details of either sub-object

my\_job.job.description  
my\_job.schedule.completed?

Something like that. Then actions that have to deal with bits of info  
from each, you just add as methods to ScheduledJob and interface to  
Job and Schedule as necessary. That way you encapsulate the logic  
that manages ScheduledJob. From the oustide, you treat ScheduledJob  
as a single object, but on the inside it’s made up of more than one.

How do you save a ScheduledJob? Write a save method that accepts the  
possible params, then divy up the params into the respective Job and  
Schedule saves that are necessary. Brute force, but at least you get  
a nice interface to it all.

– gw

---

<div class="post-metadata">

**Author:** ![danielb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/c57346/32.png) [@danielb](https://www.ruby-forum.com/u/danielb)\
**Post date:** [June 27, 2008, 9:12pm UTC](https://www.ruby-forum.com/t/multiple-databases-and-establishing-associations/141237/7 "2008-06-27T21:12:08Z")

</div>

On Jun 26, 1:17 pm, Greg W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > > > associate if possible. The association is not based on a primary key  
> > > > object yourself.  
> > > > Rough 2-minute idea:  
> > > > my\_job = ScheduledJob.new(‘12345A’)  
> > > > as a single object, but on the inside it’s made up of more than one.
> 
> How do you save a ScheduledJob? Write a save method that accepts the  
> possible params, then divy up the params into the respective Job and  
> Schedule saves that are necessary. Brute force, but at least you get  
> a nice interface to it all.

Ok, I think this is the approach I’m going to take, probably combined  
with some sort of delegation scheme to ease the pain. Many thanks for  
the suggestion.

Regards,

Dan