Switching database dynamically based on incoming request

Rails
#1

So I have this idea to reduce code replication in our rails setup.

Currently we have our rails app serving two distinct sites, soon to be
three. At the moment we check out a separate set of code for each site,
and configure it by adjusting the database setting and some config files
that pull in separate style sheets.

To remove this replication I’d like to have a single codebase, but
adjust the databases and config settings based on what site the user is
trying to view, e.g.

http://virtualhost1.us.com ==> use virtualhost1 db & stylesheets
http://virtualhost2.us.com ==> use virtualhost2 db & stylesheets

Currently the two virtual hosts map to two different directories that
both include the same version of our application, just with slightly
different configurations.

It seems fairly straightforward to make the config settings dynamic,
since we can use request.host to work out which host the user is
hitting, and switch style sheets etc. based on that. [note that ENV
can’t be used in this fashion since it appears to only pick up
enviroment variables set for the fcgid process in general]

In principle we could do the same for the database by adjusting
database.yml like so:

development:
adapter: mysql
database: <%= request.env[‘DEV_DATABASE’] %>
host: localhost
username: <%= request.env[‘DEV_DATABASE_USERNAME’]
password: <%= request.env[‘DEV_DATABASE_PASSWORD’]

The idea being to have a virtual host config like:

ServerName virtualhost1.us.com SetEnv DEV_DATABASE virtualhost1 SetEnv DEV_DATABASE_USERNAME virtualhost1_username SetEnv DEV_DATABASE_PASSWORD virtualhost1_password DocumentRoot "/var/www/our_rails_app" Options ExecCGI FollowSymLinks AllowOverride all Allow from all Order allow,deny AddHandler cgi-script .cgi AddHandler fastcgi-script .fcgi

However the request variable is not available for use in database.yml,
and while the ENV variable is, the following:

development:
adapter: mysql
database: <%= ENV[‘database’] %>
host: localhost
username: <%= ENV[‘database_username’]
password: <%= ENV[‘database_password’]

won’t work because the ENV settings under fcgid don’t pick up anything
set in the virtual host settings, or any other SetEnv directive.
Currently ENV under fcgid appears to only have variables set as part of
the fcgid config which are global for all processes, so this won’t allow
us to dynamically switch the database based on user request.

request.env appears to pick up environment variables set up the Apache
SetEnv directive, and I’ve been trying to work out how this happens in
actionpack, but experiments like the following:

<% cgi = CGI.new %>
<%= debug cgi.send(:env_table)%>

appear to produce access to ENV, i.e. only the global variables, and not
the specific ones to the current virtual host request.

I would be very grateful for any input on how to resolve this problem,
or any information that might help me better understand the difference
between ENV and request.env in rails.

Many thanks in advance.

CHEERS> SAM

#2

Hmm, so I think I found one way round this which is to include the
following in application.rb:

before_filter :dynamic_db

def dynamic_db
ActiveRecord::Base.configurations[RAILS_ENV][‘database’] =
request.env[RAILS_ENV+’_DATABASE’]
ActiveRecord::Base.establish_connection
end

where we have the following in our virtual host specification:

ServerName virtualhost1 SetEnv RAILS_ENV development SetEnv development_DATABASE dev_db SetEnv production_DATABASE prod_db SetEnv test_DATABASE test_db DocumentRoot "/var/www/myrailsapp/" Options ExecCGI FollowSymLinks AllowOverride all Allow from all Order allow,deny AddHandler cgi-script .cgi AddHandler fastcgi-script .fcgi

I’m not sure if this setup with the reconnection in application.rb will
adversely affect performance, but superficially it appears to work fine.

CHEERS> SAM

#3

Impressive… I’m trying to do something similar but a little different,
maybe
you could help me out. i want to make 2 sets of identical tables: one
for the
actual data and the other (fake) data that will be used to train
people/demo the
rails app. so i want my code to do something like: user clicks on a
button to
switch to training mode, which sets a session variable to flag that the
app is
in training mode and i want it to switch to using the training database
during
that time. I thought that this might not have been possible with rails
and gave
up much hope, but after reading your post i have regained hope :slight_smile:

i’m not exactly an expert when it comes to the nitty gritty details of
rails
configuration/environment… definetly have much to learn.

Thanks in advance,

stuart

#4

Hi Stuart,

Hmm, I think there are a lot of different ways to do what you describe.

Depending on your setup I would have thought that the easiest thing
might be to use the same approach as the one I described previously, and
have slighlty different urls for the training site and the live site,
e.g.

http://train.stuartsite.com/ ==> hit’s training db
http://www.stuartsite.com/ ==> hit’s live db

and each page could have a link to “TRAIN” or “LIVE” that would switch
from one site to the other, but to exactly the same page, e.g.

if you are on http://train.stuartsite.com/items/list, then hitting the
“LIVE” button takes you to http://www.stuartsite.com/items/list and v.v.
for “TRAIN”

I think this would have the added benefit that it would always be clear
from the url which mode you were in.

Of course you may be wanting to avoid completely replicating your entire
db, then you could go for something much simpler such as having two
models like this:

class StuartItemTrain < StuartItem
set_table_name ‘stuart_item_train’
end

class StuartItem < ActiveRecord::Base
set_table_name ‘stuart_item_train’
def stuarts_funky_method
# funky stuff
end
end

Then you could just switch from:

http://www.stuartsite.com/stuartitems/list

to

http://www.stuartsite.com/stuartitemtrains/list

to get the same effect for a single model.

HTH

CHEERS> SAM

#5

Hi Sam,

Thanks for the excellent recommendations. I appreciate it very much! :slight_smile:

Stuart