Lost in multiple rails?
So, I’m playing with Ruby on Rails, which has gotten a lot of press lately. This is a “good thing,” in many ways as it’s nice to see some press for things that aren’t as horrendously painful to even contemplate as J2EE and JSP—or whatever the gobbledygook acronyms are for this week. Unfortunately, I’ve hit a stumbling block, and I’ve not yet figured out my way around it.
For a project I’m working on, I need to access at least 2 separate databases at the same time. While I’m not doing queries across them, the same application may need to pull from both of them—and they may be held on different servers—to fulfill a single web request. I’ve found information on how to use multiple databases depending on the request, but not how to link up mutliple databases in the same request. It seems as though it’s “locked” to a single database.
While this is fine for new projects—or at least not totally useless—it does make it hard to adapt it to an existing ER model that you can’t change. Digging through the ActiveRecord documentation, I find this excerpt:
Connections are usually created through
ActiveRecord::Base.establish_connectionand retrieved byActiveRecord::Base.connection. All classes inheriting fromActiveRecord::Basewill use this connection. But you can also set a class-specific connection. For example, ifCourseis aActiveRecord::Base, but resides in a different database you can just sayCourse.establish_connectionand Course and all its subclasses will use this connection instead.
This feature is implemented by keeping a connection pool in
ActiveRecord::Basethat is aHas@h indexed by the class. If a connection is requested, the @retrieve_connectionmethod will go up the class-hierarchy until a connection is found in the connection pool.
So, it seems ActiveRecord can do what I need, but I’m not sure how to do it inside Rails without blowing the framework apart. Time to dig.
Solution in the comments.
This entry was posted at 4:12 pm on 30 June 2005 and is filed under Ruby. You can follow any responses to this entry through the post-specific RSS 2.0 feed.
Wow, I’m flattered. Thanks for the pointer Michael. I had gotten some leads from tufty on #rubyonrails and had started to go there, unfortunately I’ve run smack into some scaffold problems. It is late, and I just got back, so I will post about them more tomorrow.
The solution you gave is a bit more elegant, and solves that problem. Now I just have to figure out how to solve my scaffold problem.
Thanks so much for your comment, Michael. Step 2 was just what I needed to clarify the API docs on establish_connection. (Woot. The hardest part with Rails is trying to do things “right” :p Once you know how, it seems so obvious … Sigh.)
Hi,
Since I am very new to RoR, there are a lot of things I don’t know. I am very confused about connecting multiple databases from one application. My application works great for single database and I came to a point that I am required to connect a second database and pull some information and return results.
I still have no idea how to do this yet, could you by chance explain this a little more on your blog..
Thank you
Deniz.
There is actually a good introductory article in the Rails wiki on how to do this. It worked fine for me, though it’s perhaps not the way I would have approached the problem.
[...] Now that this things working, it’s time to break it. In my very first iteration I created the app with a single db, but in reality – it’s going to span two. Today I needed to figure out exactly how this is done. From what I read, this is something that is quite possible… but just isn’t all that straight forward. [...]
I just came across an interesting problem while trying this out. I have five tables I need to access in my rails app. Three are in Oracle and two are in PostgreSQL.
I first had the Oracle tables extend an ActiveRecord::Base subclass named Auth so that in environment.rb I could just write:
Auth.establish_connection “auth_#{ENV[“RAILS_ENV”]}”
This worked well, and only one connection to Oracle was created.
However, I then tried to do the same with my PostgreSQL models, creating an ActiveRecord::Base subclass named Dns, and the following line in environment.rb:
Dns.establish_connection “dns_#{ENV[“RAILS_ENV”]}”
This didn’t work so well, and I’m not sure if the problem is in the Oracle adapter or the PostgreSQL adapter.
The issue is that the PostgreSQL adapter complains that it can’t find a table named “dns”. The only reason I’m using subclassing is as a hack to not have to specify the database connection used by every model class, which causes a different connection to be used for each one, which prevents any sort of transactional behavior between those models and wastes database resources.
In the end, what I’m doing is the following in environment.rb:
Record.establish_connection “dns_#{ENV[“RAILS_ENV”]}”
Redirect.connection = Record.connection
This seems kind of nasty, but at least it gives me just one connection. I’d like to see a cleaner solution, but it’s something you can use if you run into the same situation.
See p65 of Rails Recipes by Chad Fowler
I’m trying out rails on a new app for multiple customers where each customer will have a seperate database of 38 tables. I need a clean method of selecting a database based on user input on the login form. For example if the user is not logged in already they will be presented with a form containing fields for username, password, and account. The account name will corrispond to the database that they should be accessing and their username and password will then be validated in a user table in that same database. Is there a clean way to do this where I don’t have to add each database to the database.yml? We hoping for around 100 customers for this app.
there is no ActiveRecord::Base.establish_connection line in my environment.rb file… where could I find it ?
Actually, Erwan, what you do is attach your classes to the database directly. So if you had a class “Foo” that needed to be retrieved from the non-default database, you would use:
Foo.establish_connection …
Like that.
I’m running into an issue with relationships across multiple databases. I have 2 different applications in place running on 2 different databases. Everything is working just fine, though I had to fix an issue with it. The same issue I’m about to get to. I now want to add a third application that interacts with both applications. The first app is an inventory control program that runs locally at the company. This is in case the internet ever goes down, which unfortunately happens on occasion, they can still operate. Next is a site served elsewhere for their sales reps to use. Everything in that database is stored on the web server, except for the user table which already exists on the company database. A problem occured when using code like this:
@customer.users
where a User habtm Customers. The error that I got indicated that the linker table didn’t exist in the database that the users were stored in. But when I switched the order of the code:
@user.customers
it worked. So this must mean that I can never call a method on a model that is stored in one database and include a model from another database, which will essentially kill any possibilities of eager loading information.
I was able to bypass the issue in the first scenario, but now with my third application, it will be interacting a lot between the two databases. About half will be in one database, half in another. Is there a better way of approaching this problem, or a way to get around this issue? Or am I just doomed to having to pulling in tons of information and having to work with is server side?
I have the same situation as Chris. It would be nice to have a clean way to to that. Thanks.
Responses are currently closed, but you can trackback from your own site.
Hey there,
You’ve actually got a few of the core rails team reading your blog. Your ‘the red herring of performance’ piece led a lot of us to subscribe.
So, here’s some help:
1) Edit database.yml and add the configuration for your second database. Just like the others, but instead of using the environment as the key, use some other name
2) You want to make the Course.establish_connection call in your environment.rb file, just under the ActiveRecord::Base.establish_connection
Course.establish_connection “your_config_name”
And if you wanted per-environment options for the second database, just call them something like “your_config_name_production” and call Course.establish_connection “your_connection_name#{RAILS_ENV}”