Multi-tenant rails application with multiple schemas

Question

So I am working on a multi-tenant rails application using rails 3.2.11. We chose the schema approach so we are using a single Postgres database. The correct schema is loaded based on the subdomain. We have added security on the database side by creating a database user for each schema that only has permissions to access data in its own schema.

All my code for checking the subdomain and establishing the proper database connection are inside a before_filter method inside my application controller.

My method simply changes the search_path and then I use the following ActiveRecord::Base.establish_connection to change the database username and password.

I've written several tests and gone through the app to make sure it all works properly and data is saved in the proper schemas (even when using multiple subdomains at the same time), so far so good.

But I have 2 big concerns that I would like to ask about:

I am now using that ActiveRecord::Base.establish_connection call to establish a database connection. So as a result I just deleted all of my code from my database.yml file, and everything still works properly. But... I've never done something like that before, is there a more correct way of doing this? I'm worried this may cause some bugs..
Like I said earlier, a before_filter method is called to establish the proper database connection. But like I said I am using a before_filter, so everytime a user changes the page the connection is re-established. I am concerned that this could cause performance issues, especially as the application grows. And also I'm not sure if I am overlooking anything and this could result in some unforeseen bugs. Has anyone had any experience with this and could offer some advice on an approach how to handle this? Maybe there is someway to add code that says if a proper initial database connection is made, then don't re-establish the connection again unless the subdomain changes?

Thanks.

I can guarantee that establishing connection on every request will have a severe performance impact. Don't do that :) You could establish all connections in an initializer (on an activerecord on load hook for example) and try switching the connection variable on each request? Also, check out stackoverflow.com/questions/2782758/… — bbozo

bbozo bbozo · Accepted Answer · 2013-12-05T06:39:48

This might be useful to you: https://github.com/influitive/apartment

Multitenancy for Rails 3 and ActiveRecord

Apartment provides tools to help you deal with multiple databases in your Rails application. If you need to have certain data sequestered based on account or company, but still allow some data to exist in a common database, Apartment can help.

Also, there's a whole host of multitenancy questions here on SO, check them out

Multi-tenant rails application with multiple schemas

1 Answers