Multiple Databases in Rails

Question

Can this be done? In one application that manages many projects using SQLite. I want me to have a different database for each project that my application manages .. there are so many copies of the same structured database, but with different data in them. I will choose which copy to use the base on params in the URI.

It is made for 1. safety. I am new to this programming, and I do not want this to happen, because for some reason the other one gets damaged while working on the project .. 2. A simple backup and archive of old projects

Answer 1

The rails are not intended for multi-database architectures by default, and in most cases this makes no sense. But yes, you can use different databases and connections.

Here are some links:

• ActiveRecord: connecting to multiple databases in different models
• Multiple database connections in Ruby on Rails
• Magic Multi-Connections

Answer 2

If you can control and configure each instance of Rails, and you can afford to spend resources because they are in standby mode, save some problems and just change database.yml to change the database connection used for each instance . If you are concerned about performance, this approach will not reduce it.

For models bound to one unique table on only one database, you can call connection_connection inside the model:

establish_connection "database_name_#{RAILS_ENV}" 

As described here: http://apidock.com/rails/ActiveRecord/Base/establish_connection/class

You will have some models using tables from one database and other models using tables from other databases.

If you have the same tables, common to different databases and shared by one model, ActiveRecord will not help you. Back in 2009, I needed this in a project that I was working on using Rails 2.3.8. I had a database for each client, and I named the databases with their identifiers. Therefore, I created a method to change the connection inside the ApplicationController:

 def change_database database_id = params[:company_id] return if database_id.blank? configuration = ActiveRecord::Base.connection.instance_eval { @config }.clone configuration[:database] = "database_name_#{database_id}_#{RAILS_ENV}" MultipleDatabaseModel.establish_connection configuration end 

And added this method as before_filter to all controllers:

 before_filter :change_database 

So, for each action of each controller, when params [: company_id] is defined and set, it will change the database to the correct one.

To handle migrations, I extended ActiveRecord :: Migration with a method that searches for all clients and iterates the block with each ID:

 class ActiveRecord::Migration def self.using_databases *args configuration = ActiveRecord::Base.connection.instance_eval { @config } former_database = configuration[:database] companies = args.blank? ? Company.all : Company.find(args) companies.each do |company| configuration[:database] = "database_name_#{company[:id]}_#{RAILS_ENV}" ActiveRecord::Base.establish_connection configuration yield self end configuration[:database] = former_database ActiveRecord::Base.establish_connection configuration end end 

Note that by doing this, it would be impossible to make queries within the same action from two different databases. You can call change_database again, but it will be frustrating when you try to use methods that execute queries from objects that are no longer associated with the correct database. In addition, it is obvious that you will not be able to join tables that belong to different databases.

To handle this, ActiveRecord needs to be greatly expanded. At the moment, there should be a plugin that will help you in solving this problem. A quick study gave me the following:

DB-Charmer: http://kovyrin.github.com/db-charmer/

I am ready to try. Let me know what works for you.

Answer 3

I walked past this by adding this to the top of my models using a different database

 class Customer < ActiveRecord::Base ENV["RAILS_ENV"] == "development" ? host = 'devhost' : host = 'prodhost' self.establish_connection( :adapter => "mysql", :host => "localhost", :username => "myuser", :password => "mypass", :database => "somedatabase" ) 

Answer 4

You should also check out this project called DB Charmer: http://kovyrin.net/2009/11/03/db-charmer-activerecord-connection-magic-plugin/

DbCharmer is a simple but powerful plugin for ActiveRecord that does a few things:

• Allows easy management of AR model connections ( switch_connection_to )
• Allows you to switch the standard connections of AR-models to individual servers / databases
• Allows you to easily choose where your request should go ( on_* family)
• Allows you to automatically send read requests to your slaves, while wizards process all updates.
• Adds multiple database migrations to ActiveRecord

Answer 5

It is worth noting that in all these solutions, you need to remember to close user connections to the database. Otherwise, you will encounter connections and see problems with the request timeout.

A simple solution is clear_active_connections! in the after_filter file of your controller.

 after_filter :close_custom_db_connection def close_custom_db_connection MyModelWithACustomDBConnection.clear_active_connections! end 

Answer 6

in your config / database.yml do something like this

 default: &default adapter: postgresql encoding: unicode pool: 5 development: <<: *default database: mysite_development test: <<: *default database: mysite_test production: <<: *default host: 10.0.1.55 database: mysite_production username: postgres_user password: <%= ENV['DATABASE_PASSWORD'] %> db2_development: <<: *default database: db2_development db2_test: <<: *default database: db2_test db2_production: <<: *default host: 10.0.1.55 database: db2_production username: postgres_user password: <%= ENV['DATABASE_PASSWORD'] %> 

then in your models you can reference db2 with

 class Customers < ActiveRecord::Base establish_connection "db2_#{Rails.env}".to_sym end 

Answer 7

In this question, you described multi-user (identically structured databases with different data in each). The pearl of the apartment is great for this.

For the general question about multiple databases in Rails: ActiveRecord supports multiple databases, but Rails does not provide a way to manage them. I recently created a Multiverse gem to solve this problem.

Answer 8

The best solution I have found so far is this: there are 3 database architectures that we can access. - A single database for one tenant - A separate scheme for each tenant - A general scheme for tenants

Note: they have certain pros and cons, depending on your use case.

I got it from this blog ! It is very useful for me.

You can use gem flat for rails