<p>I'm trying to understand the best way to get the connection to my databases.</p>

<p>At the moment I've got a method which parses the URL (depending on the URL called the application has to connect to a different database, like customer1.example.com will connect to the customer1 database) and calls </p>

<pre><code>ActiveRecord::Base.establish_connection(conn_string)
</code></pre>

<p>where conn_string contains the name of the database.</p>

<p>This method (set_db) is called with a</p>

<pre><code>before_filter :set_db
</code></pre>

<p>in my Application controller, so basically for each request I get, the URL is parsed and the application try to do an establish_connection. </p>

<p>I was wondering if I can have a connection pool somewhere....do you have any suggestion about that? Is it better to have a Singleton which keep all the connections made and gives back the right one?</p>

<p>Thanks!
Roberto</p>

I'm not a Ruby programmer, but generally speaking a connection pool is a good idea. You can make that connection pool a singleton and hand out/take back connections. The pool can check after taking back a connection whether everything is still in order.
You should have a distinct life cycle and ownership for each connection so two threads don't use a connection at the same time.
Be careful with temporary tables because they belong to one database session. We've had a bug where in high load situations we encountered weird errors. Finally we remembered that we were using temporary tables in our application. In the high load situation database connections broke down and were automatically regenerated by the connection object without the application code knowing it (we had implemented the reconnect, but forgot that it was a problem in such a situation). The temporary table we wanted to read had disappeared.