Dynamic Database Connections

Hey everyone! I have a problem that's been making me a little nuts.
I would like to use RoR for a upcoming project, but if I can't get
past this hiccup I'm
afraid I'll have to use PHP or Java. Ugh.

The application needs to have a database-per-customer model. So when
I do a Product.find(:all) I need to make sure i'm connecting to say
acme.products and not xyz_company.products.

I toyed around with the magic_multi_connections gem but I'm having
trouble getting it to work without knowing the connection up front.

In the examples it shows how to connect when you know which db you'll
need ... in their example :Private.

I attempted to look at the code, but there is some meta programming
stuff in there that is beyond my newb level i'm afraid.

The other problem with the magic_multi is that if there are 140
customers then I need 140 modules added in the environment.rb. That
doesn't sound fun!

If anyone has done something like that and can't get me started I'd be
so grateful!

And please, if I wasn't clear enough, ask me to elaborate rather than
ignoring the post. I only have a couple more days to decide which
language to use, and i'd really like to go with Ruby and Rails.

Thanks Amar. Sharding isn't exactly what I need here, but some
interesting info nonetheless.

I have a main db ( users, applications, etc ) and a database for each
customer ( products, orders, etc )

When I want to get all the products, for example, I need to connect
to
a particular customers version of products. I can do it by using
ActiveRecord::Base.establish_connection (:connection_name) in the
controller
but its not perfect... also there are some plugins that use some
activeRecord
stuff in them and the model doesn't know which db its supposed to
connect to
so I have to modify all those gems and put the establish_connection in
there too.

I put the customer in session on user login, and then I can easily get
which customer
db I need, but again that doesn't work so well in the models since the
session isn't
easily available in the models.

The usual trick in situations like this is to use a thread-local
variable, set in the controller action and accessed from the models.
It's a somewhat leaky abstraction, but probably the most reliable way
to do things (ActiveSupport uses it for the Time.zone stuff). The code
would look like (sketch):

in application_controller.rb (or elsewhere):

before_filter :set_customer_db

def set_customer_db
  Thread.current[:customer_db] = ...
end

I can't think of how exactly you'll end up accessing it in the models,
but the magic_multi_connections gem should provide some guidance.

--Matt Jones

Thanks for your suggestion Matt. I'll look into it a bit and see if
it will work. I'm not stuck
on using the magic_multi_connections at this point. I'd go with an
easier way if I could figure it out.
I tinkered with just having a method in the application_controller
that sets the connection via establish_connection
( from a value in session ), and it seems to work okay. Its just a
little clunky in a few parts and it doesn't solve
the problem when you need to make a db call in the model ( or some
plugin tries to use a model find(:all) or something ).

Marnen - We need a db per customer for a few reasons. It will keep
the clients data separate ( some of them are weird like that about
their data, and require us to separate ), and also a particular
customer could have a customer feature they purchase that won't make
sense
for all clients. That feature will cause for extra tables or columns
in that particular customers db that won't be spread to everyone.

Also, this way we can easily backup a particular customer, or grab a
customer's db a dump it on our local machine
really quick and test with their data, rather than pulling down a
giant db.

And finally, we can take down a particular customer rather easy and
not affect anyone else.

I'm in some ways trying to convince myself that its a good thing to do
as well, since I don't have much choice in the matter haha! Thanks
for the input though!

Hi again Marnen. I think we're a little off topic.
The problem at hand is connecting to multiple databases, not
poo poo'ing on the requirements haha!

Thanks again for your input though! All thoughts are welcome.

Very often the solution to a problem is to change the approach so that
the problem does not exist in the first place, rather than code around
a tricky issue. To suggest such alternatives here is entirely valid.
In a particular instance there may be a good reason for using the
problematic approach of course.

Colin

jmamma wrote:

Hi again Marnen. I think we're a little off topic.
The problem at hand is connecting to multiple databases, not
poo poo'ing on the requirements haha!

As Colin said, it is entirely appropriate to question the requirements
here. You're making your life difficult with these requirements, and
you don't seem to have a particularly good reason for them, so why keep
them?

Thanks again for your input though! All thoughts are welcome.

If you really mean that, then please give my suggestions some serious
consideration.

On Nov 2, 5:49�pm, Marnen Laibow-Koser <rails-mailing-l...@andreas-

Best,