[转载]Dynamic Database Switching in Rails - How to do it

转载声明：http://ryanstenhouse.eu/tutorials/2010/02/07/dynamic-database-switching-rails-how-to-do-it/

If you have an application you offer as a service to others, youʼre going to run into the problem of needing to keep data separate between your customers. You will want to do this as efficiently as you can, with as little code as possible.

Wufoo is a good example of this kind of problem, where every user of their service sees the same application and uses the same tools; but by giving each user their own unique URI they are easily able to segregate data between users.

A variation of the fairly straight forward method I describe in this Tutorial has been live since 2007 in a web application developed by PCCL. It has proven to be fast enough to deal with the load of being in daily use by thousands of unique people. The original version of this code was written by my colleague Mark Somervilleand this is heavily based on his brilliant work.

The Problem & A Solution

Your application is fairly generic, it’ll work well for anyone – if only you could keep your clients' data separate. Of course, you could quite easily just deploy one instance of your application for each customer – after all, Rails only supports one production database.

The problem is then, how the heck do you manage these deployments? How do you keep them all up to date? As soon as you have more than two or three customers using your service, the cost to you in time and effort to keep the applications online and updated quickly becomes prohibitive.

But.. Since you’re needing separate URIs for each client anyway to serve the different instances to your clients, you could always just make your application clever enough to look at the incoming request’s address and then take the appropriate database-switching action.

Here’s how to do it:

1. Sniff the URI of the request
• request.host is there for a reason! Use it!
2. Connect to the Database for the URI
• Use client-specific DB config files
• Cache the contents of the files to reduce performance loss

Client Database Config Files

The messy part of this solution is that you need to create a file similar to your database.yml for each of the unique clients (or unique URIs) which are accessing your application. As a sensible convention, I suggest creating a config/databases directory in your RAILS_ROOT to place these files.

Inside this directory, each URI should be represented as a YML file. For this example, I’ll use my own domain name, so I’ll be creating ryanstenhouse.eu.yml. The contents of the file should be:

# Contents of ryanstenhouse.eu.yml

database_details:

  adapter: mysql

  database: client_db_name

  username: root

  password:

As you can see, it’s almost exactly the same as a regular database.yml file, with the exception that instead of development: or production: keys; adatabase_details: key is used instead.

You are going to need at least one client config file, so open config/clients/localhost.yml in your favourite editor, and fill it with the following:

<%= { "database_details" => ActiveRecord::Base.configurations["development"] }.to_yaml %>

This will allow you to visit http://localhost:3000 when your application is running and still be able to use the development environment you configured indatabase.yml.

In your Rails application

After the horror of having to put up with multiple client-specifc database files, you’d be relieved to know that in your application, all you need to do is add a rather simple before_filter to your ApplicationController.

For neatness, the filter we’re going to use to do the database switching will live in a module. Something like:

module ClientDatabaseSwitching

  # This is prepended to the filter chain for each action and will ensure that on each
  # request, the User is connected to the correct database.
  #
  def choose_database_from_host
    unless defined? @@_client_database_details
      @@_client_database_details = Hash.new
    end
    host = request.host
    if @@_client_database_details[host].nil?
      @@_client_database_details[host] = fetch_client_details_for host
    end
    connect_to_database_for @@_client_database_details[host]
  end

  # Looks in #{RAILS_ROOT}/config/databases for a configuration file which is called
  # #{client_hostname}.yml, which will be a ymlified Hash of the connection details
  # needed to establish a database connection.
  #
  def fetch_client_details_for(client_hostname)
    file_path = "#{RAILS_ROOT}/config/databases/#{client_hostname}.yml"
    if File.exists?(file_path)
      return YAML::load(ERB.new(IO.read(file_path)).result)['database_details']
    end
  end

  # Actually does the work of connecting to the database.
  #
  def connect_to_database_for(details)
    ActiveRecord::Base.establish_connection(details)
  end
end

Once that’s done, you just need to do something like the following in your ApplicationController:

include ClientDatabaseSwitching
prepend_before_filter :choose_database_from_host

So what is happening?

1. When the request comes in to your application, the before_filter you prepended to the start of the filter chain reads request.host.
2. The class-level Hash, @@_client_database_details is checked to see if it contains the connection details for this client.
3. If the @@_client_database_details contains the connection information for this host, the application connects to that client’s database before processing the action.
4. If not, the contents of the client database config file for this client are loaded into the @@_client_database_details hash and the application then connects to the database before processing the action.

Final working code

A working example of this solution is available on my GitHub for your downloading and running pleasure. There’s a fully passing test suite and a more robust implementation than the overview detailed here.

Next steps

I’ve deliberately left a lot of validation and error handling out of the example above to make the illustration of the concept used as clear as possible. If you were to use this method in production, you will need to, at least, be able to deal with cases where configuration files don’t exist.

This solution is rather Rails-Heavy. The nature of its implementation means it will work with all versions of the framework from 1.2.3 to 2.3.4, however there’s much more to the world of Ruby web-apps than just one Framework.

Rails 3 Compatibility

For Rails 3 compatibility, it should just be a case of substituting RAILS_ROOT for Rails.root.to_s in the code examples.