# Switching database dynamically based on incoming request

**URL:** <https://www.ruby-forum.com/t/switching-database-dynamically-based-on-incoming-request/100752>\
**Category:** Rails\
**Created:** [July 31, 2006, 10:17pm UTC](https://www.ruby-forum.com/t/switching-database-dynamically-based-on-incoming-request/100752 "2006-07-31T22:17:58Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![samj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/77aa72/32.png) [@samj](https://www.ruby-forum.com/u/samj)\
**Post date:** [July 31, 2006, 10:17pm UTC](https://www.ruby-forum.com/t/switching-database-dynamically-based-on-incoming-request/100752/1 "2006-07-31T22:17:58Z")

</div>

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](http://virtualhost1.us.com) ==\> use virtualhost1 db & stylesheets  
[http://virtualhost2.us.com](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

---

<div class="post-metadata">

**Author:** ![samj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/77aa72/32.png) [@samj](https://www.ruby-forum.com/u/samj)\
**Post date:** [August 1, 2006, 4:32am UTC](https://www.ruby-forum.com/t/switching-database-dynamically-based-on-incoming-request/100752/2 "2006-08-01T04:32:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![samj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/77aa72/32.png) [@samj](https://www.ruby-forum.com/u/samj)\
**Post date:** [August 1, 2006, 5:01pm UTC](https://www.ruby-forum.com/t/switching-database-dynamically-based-on-incoming-request/100752/3 "2006-08-01T17:01:39Z")

</div>

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 🙂

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

---

<div class="post-metadata">

**Author:** ![samj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/77aa72/32.png) [@samj](https://www.ruby-forum.com/u/samj)\
**Post date:** [August 1, 2006, 8:33pm UTC](https://www.ruby-forum.com/t/switching-database-dynamically-based-on-incoming-request/100752/4 "2006-08-01T20:33:27Z")

</div>

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/](http://train.stuartsite.com/) ==\> hit’s training db  
[http://www.stuartsite.com/](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](http://train.stuartsite.com/items/list), then hitting the  
“LIVE” button takes you to [http://www.stuartsite.com/items/list](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](http://www.stuartsite.com/stuartitems/list)

to

[http://www.stuartsite.com/stuartitemtrains/list](http://www.stuartsite.com/stuartitemtrains/list)

to get the same effect for a single model.

HTH

CHEERS\> SAM

---

<div class="post-metadata">

**Author:** ![samj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/77aa72/32.png) [@samj](https://www.ruby-forum.com/u/samj)\
**Post date:** [August 1, 2006, 8:58pm UTC](https://www.ruby-forum.com/t/switching-database-dynamically-based-on-incoming-request/100752/5 "2006-08-01T20:58:53Z")

</div>

Hi Sam,

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

Stuart