CODEWITHSUNDEEP
ruby-on-railsdatabaseactiverecordmodel
user1609682
user1609682

Reputation: 255

using activerecord with nested databases

I am building a rails app to store many different, user-supplied database connections and execute arbitrary sql against each of them. I am representing each database connection string as an instance of a 'connection' model and want to be able to write a method to query the database represented by each connection, ideally using the activerecord ORM. However, my code as written overwrites the database connection for the entire connections table when I use the establish_connection method in the following code. How would you advise me to change the code to query an arbitrary database, without overwriting the connection for the whole Connections table?

class Connection < ActiveRecord::Base
  validates_presence_of :host, :port, :db_name, :db_user, :db_password, :db_type

  def connect
    self.connection = ActiveRecord::Base.establish_connection(
      adapter: self.db_type,
      host: self.host,
      database: self.db_name,
      username: self.db_user,
      password: self.db_password
    )
  end

end

Upvotes: 0

Views: 317

Answers (1)

xmikex83
xmikex83

Reputation: 965

A good way to do that is making a model for each database connection you need, and then make other models as subclasses of them. So, for example:

  1. Define all the needed connections in database.yml (per-environment)
    # DB 1
    development:
      adapter: mysql2
      encoding: utf8
      database: db_1
      username: ****
      password: ****
      host: ********
      pool: 5
      ...

    # DB 2
    db2_development:
      adapter: mysql2
      encoding: utf8
      database: db_2
      username: ****
      password: ****
      host: ********
      pool: 5
      ...

    # Same for production (and/or other environments)
    production:
      ...
    db2_production:
      ...
  1. Define a "master" model for each database, which inherits from ActiveRecord::Base
    # DB1
    class DB1 < ActiveRecord::Base
      self.abstract_class = true
    end

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

    ...
  1. Now define all the database-specific models as subclasses of the models defined above, in this way:
    # DB1 specific model
    class DB1_model < DB1
      # model logic here
    end

    # DB2 specific model
    class DB2_model < DB2
       # model logic here
    end

    ...

And you're good to go.

In this way, you can connect to N databases in different environments (usually development, staging, preprod and production, but they may be different in your case).

Also, remeber that Rails will manage a pool of SQL connections for each database.

In the example above, Rails will open a maximum of 5 connections for each database, so total will be 10 (for a single instance of your application). If you use Phusion Passenger or Unicorn, and spawn 8 application instances, total SQL connections will be (at maximum) 10*8 = 80.

Upvotes: 1

Related Questions