CODEWITHSUNDEEP
ruby-on-railsdatabaseruby-on-rails-3
jasonmklug
jasonmklug

Reputation: 1634

Query Remote Database from Rails 3.2 Application

I need to query a 3rd party database which is entirely separate from the Rails 3.2 application I'm building (belongs to a different application which my company uses internally).

Ultimately, I'll be setting up a cron to load new rows from the "other" database which my Rails application will be processing.

I have the access to otherdb set up, and I'm wondering where to go from here—do I create a new entry in config/database.yml? If so, how do I then specify when a query is to be directed to otherdb, instead of my default Rails development or production db?

Upvotes: 0

Views: 1364

Answers (2)

Brian Morearty
Brian Morearty

Reputation: 3348

I had the same problem yesterday. Since you are using Rails 3.2, all your models that connect to the external database will have to be subclasses of a single, abstract class that establishes the connection. In earlier versions of Rails, @Sasha's answer would have worked. But in 3.2, that answer will lead you to getting various confusing database errors. (What errors you get depends on what DB you use.)

In Rails 3.2, this is the only way I have found that will work:

  1. Make a common base class for all models that need to talk to a non-default database.
  2. Tell ActiveRecord that this base class is abstract by calling self.abstract_class = true.
  3. Call establish_connection in the base class.

Here is an example with students and courses from an external table:

# database.yml:
development:
  # default configuration goes here

other_development:
  # external db configuration goes here

class OtherTable < ActiveRecord::Base
  self.abstract_class = true
  establish_connection "other_#{Rails.env}"
end

class Student < OtherTable
end

class Course < OtherTable
end

If you'd like more detail, see the blog post I wrote titled Establishing a Connection to a Non-Default Database in Rails 3.2.

Upvotes: 1

Sasha
Sasha

Reputation: 485

There's a few ways to implement this requirement, the easiest of which would be to use config/database.yml and custom namespaced model(s).

Setting up something similar to the below, using a suffix of Rails.env to follow the naming convention will provide the functionality you outlined.

First, create new entries for the external database each of your existing environments. It'll help you to be able to test the functionality.

# database.yml
development:
  # add configuration as required

otherdb_development:
  # add configuration as required

Second, add a model for each of the specific tables you need to access in the otherdb database. I'd recommend adding a namespace directory for these models (otherdb in the example below) to avoid confusion and potential clobbering:

# /app/models/otherdb
class Otherdb::Foo < ActiveRecord::Base
  establish_connection "otherdb_#{Rails.env}"
  set_table_name "foo" # customize this if the table name will be different from the classname and is required
end

You can then use (as an example) methods on Otherdb::Foo and use the resulting data as required.

Upvotes: 1

Related Questions