Using two or more databases in rails project

Question

Hi!

I am using external Users database for different projects.

Now I have got School model in my project, which has_many users and users has many schools.

class User < ActiveRecord::Base
  establish_connection "#{RAILS_ENV}_tunnel"
  has_many :memberships
  has_many :schools, :through => :memberships
end

class School < ActiveRecord::Base
  has_many :memberships
  has_many :users, :through => :memberships
end

class Membership < ActiveRecord::Base
  belongs_to :user
  belongs_to :school
end

So what problems now I have:

• I can't call school.users (because membership table is in my PROJECT db, not in exernal USERS db), but I can call user.schools
• I can't update in this way: current_user.school.find(params[:id]).update_attributes(params[:school]), because It opens connection for READ ONLY in this way.

I understand how can I hack this problems, i.e.

school.users I can call like this:

class School < ActiveRecord::Base
  has_many :memberships
  # has_many :users, :through => :memberships

  def users
    User.where("users.id in (?)", self.connections.map(&:user_id))
  end
end

but this hacks is not enough. Because now I can't add users like school.users << User.find(203), or school.users.find(params[:user_id]) and others features, that has_many relationship gives to me.

So the question is how to operate with two databases which connected to each other by many to many relationships with full feature support.

Answer 1

I don't think there is a way of doing this in Rails with full feature support - I think you'd be better trying to use something like MySQL's federated tables to bring the remote DB table into your production DB.

Answer 2

In oracle i would be using a database link for this.

I am not entirely sure, but I found that in mysql you can write

select * from db1.users , db2.schools where db1.users.school_id = db2.schools.id

so then you would be able to use that in Rails using by explicitly stating the table-name in your model:

class School
  set_table_name "db2.schools"
end

Does that help?