Safe way to iterate over multiple databases in Rails

Question

I have several Rails apps running on a single MySQL server. All of them run the same app, and all of the databases have the same schema, but each database belongs to a different customer.

Conceptually, here's what I want to do:

   Customer.all.each do |customer|
      connection.execute("use #{customer.database}")
      customer.do_some_complex_stuff_with_multiple_models
   end

This approach does not work because, when this is run in a web request, the underlying model classes cache different database connections from the A/R connection pool. So the connection on which I execute the "use" statement, may not be the connection the model uses, in which case it queries the wrong database.

I read through the Rails A/R code (version 3.0.3), and came up with this code to execute in the loop, instead of the "use" statement:

ActiveRecord::Base.clear_active_connections!
ActiveRecord::Base.establish_connection(each_customer_database_config)

I believe that the connection pool is per-thread, so it seems like this would clobber the connection pool and re-establish it only for the one thread the web request is on. But if the connections are shared in some way I'm not seeing, I would not want that code to wreak havoc with other active web requests in the same app.

Is this safe to do in a running web app? Is there any other way to do this?

Answer 1

IMO switching to a new database connection for different requests is a very expensive operation. AR maintains a limited pool of connections.

I guess you should move to PostgreSQL, where you have concept of schemas.

In an ideal SQL world this is the structure of a database

database --> schemas --> tables

In MYSQL, database and schemas are the same thing. Postgres has separate schemas, which can hold tables for different customers. You can switch schema on the fly without changing the AR connection by setting

ActiveRecord::Base.connection.set_schema_search_path("CUSTOMER's SCHEMA")

Developing it require a bit of hacking though.