Multiple database connections in a single Rails app

A recent project required me to provide a centralised database for sharing common data for a number of sites on the same host without the overhead of going through ative resource that have their own databases and I needed a solution to a problem that at first glance seemed to be extremely and unneccesarily complex.

The actual solution left me totally breathless with it's sheer simplicity and once again I've been reminded that where Rails is concerned, if you find yourself doing something that seems to be difficult to do then you are doing it wrong.

Why does it seem so complicated to do?
The references I researched tended to point to the fact that establish_connection was my friend but I quickly found references to the fact that migrations would be a problem as indeed it was (despite a seemingly simple solution here http://databasically.com/2010/09/24/run … databases/ which just plain doesn't work when it comes to setting up the new database as the schema migrations table won't be created and the migration command will create the table but it won't record the fact that the migration has been run)
Also http://stackoverflow.com/questions/1404 … -or-develo left me running down a blind alley too.

My first error was in thinking that the migration belonged in the existing application when in fact what I needed was a a new applicaion just to deal with the new database. Once I decided that I actually needed a new application the whole solution took less than 5 minuites.

The point of the above is to really try to hammer home the fact that if you want to connect to a non native db you can do so very easily but the maintenace of that db belongs in a totally seperate application and in fact most of the time when you need this sort of functionality you will probably find that that application already exists, it's just that in my circumstances it didn't.

Once I set up the new app the rest was really simple with one line of code in a new model and a couple of additional database.yml entries obviously the new model needs to be created by hand rather than using the generators as you don't want the migration files.

show me the code!

Scenario is
App 1 needs to connect to a_table in a database called foreign which belongs to an app called foreign

Open up the database.yml file belonging to app 1 and paste in the entries of the foreign applications database.yml file.
You should now have a yml file that looks like this

# SQLite version 3.x
#   gem install sqlite3-ruby (not necessary on OS X Leopard)
development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

production:
    adapter: mysql
    encoding: utf8
    database: app_1
    username: xxxx
    password: xxxx
    host: localhost
 
# SQLite version 3.x
#   gem install sqlite3-ruby (not necessary on OS X Leopard)
development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

production:
    adapter: mysql
    encoding: utf8
    database: foreign_app
    username: xxxx
    password: xxxx
    host: localhost

Obviously that's not quite right. You really don't want two entries for each environment and neither do you want to be pointing the sqlite entries to the path for your native applications database so just change the names of your foreign applications entries to something like this

# SQLite version 3.x
#   gem install sqlite3-ruby (not necessary on OS X Leopard)
foreign_development:
  adapter: sqlite3
  database: path_to_foreign_app/db/development.sqlite3
  pool: 5
  timeout: 5000

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
foreign_test:
  adapter: sqlite3
  database: path_to_foreign_app/db/test.sqlite3
  pool: 5
  timeout: 5000

foreign_production:
    adapter: mysql
    encoding: utf8
    database: foreign_app
    username: xxxx
    password: xxxx
    host: localhost

So that you now have a database.yml that looks something like this

# SQLite version 3.x
#   gem install sqlite3-ruby (not necessary on OS X Leopard)
development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

production:
    adapter: mysql
    encoding: utf8
    database: app_1
    username: xxxx
    password: xxxx
    host: localhost

# SQLite version 3.x
#   gem install sqlite3-ruby (not necessary on OS X Leopard)
foreign_development:
  adapter: sqlite3
  database: path_to_foreign_app/db/development.sqlite3
  pool: 5
  timeout: 5000

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
foreign_test:
  adapter: sqlite3
  database: path_to_foreign_app/db/test.sqlite3
  pool: 5
  timeout: 5000

foreign_production:
    adapter: mysql
    encoding: utf8
    database: foreign_app
    username: xxxx
    password: xxxx
    host: localhost

That's it for the database setup.
But how does app_1 get to use the data a_table that lives in the foreign database?

That's real simple
Ceate a new file in app_1/models folder following the standard Rails naming conventions for a model which in this case would be a file called a_table.rb as it's connecting to a table called a_table then set up the class definition in the standard Rails model format like so

class ATable < ActiveRecord::Base
end

But how does tha model know to talk to the foreign database?
All you need to do is add the establish_connection declaration to the class so you end up with

class ATable < ActiveRecord::Base
  establish_connection "foreign_#{Rails.env}"
end

and just like magick whatever environment you are running app 1 under ATable will be picking up the corresponding environment entries in app 1's database.yml file
e.g. when running in production mode in the above example a_table will automatically connect to the foreign_production entry

And that's it.

Apart from....

Creating a seperate app I find is overkill for my needs. The application itself will only every have migrations added to it and needs no views, controllers, helpers etc... just a model and the ability to run migrations.

Any suggestions as to a lighter way of achieving this will be welcomely received.