Skip to main content
Ask A Question
You’re not receiving notifications from this thread.

How do I switch from 1 database to 1 database per client?

Databases • Asked by John van Arkelen

I have a multi-tenancy app where everything is stored in 1 Postgres database. All models have a account_id, so the data a customer can see is based on his account_id. This works perfect, but due to security and privacy concerns, customers demand that their data is stored in a separate database. So what do I need to do to make this happen? When a custome logs in, then you connect to its own database? And what is the best way to migrate the current data to the several customer databases?


You should be able to do this with the Apartment gem. Check out the docs, specifically this section

Interesting, but what if I want to keep my own multi-tenancy with account_id's in place, because I don't want to implement a gem. Is there a way for instance in the application controller, where you can set the db settings and change the db connection based on the logged in user?

At a high level, you'd have to:

  1. Create a new database for every account in your system. Accounts probably live in the primary database, but then you'll have separate databases for their data.
  2. Migrate the data
  3. Have a before_action on ApplicationController that switches the ActiveRecord::Base connection based upon the account.

ActiveRecord::Base.establish_connection({:adapter => "postgresql", :database => current_account.db_name, :host => "wherever", :username => "username", :password => "password" })

Rails 6 is adding some support for multiple databases, but I'm not sure if it'll be useful for what you're trying to accomplish.

Also keep in mind (via

A PostgreSQL "schema" is roughly the same as a MySQL "database". Having many databases on a PostgreSQL installation can get problematic; having many schemas will work with no trouble. So you definitely want to go with one database and multiple schemas within that database.

Apartment uses schemas like this by default, so you may just want to use that.

Thanks, that's something I can work with.

I believe you should consider to read this before migrate to the multi tenant way.

Login or Create An Account to join the conversation.

Subscribe to the newsletter

Join 31,152+ developers who get early access to new screencasts, articles, guides, updates, and more.

    By clicking this button, you agree to the GoRails Terms of Service and Privacy Policy.

    More of a social being? We're also on Twitter and YouTube.