Skip to main content

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?

Regards,
John



You should be able to do this with the Apartment gem. Check out the docs, specifically this section https://github.com/influitive/apartment#tenants-on-different-servers


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 https://stackoverflow.com/a/1157008/277994)

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.
https://influitive.io/our-multi-tenancy-journey-with-postgres-schemas-and-apartment-6ecda151a21f


Login or Create An Account to join the conversation.

Subscribe to the newsletter

Join 27,623+ 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.