Ask A Question

Notifications

You’re not receiving notifications from this thread.

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

John van Arkelen asked in Databases

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

Reply

interested

Reply

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

Reply

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?

Reply

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.

Reply

Thanks, that's something I can work with.

Reply

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

Reply
Join the discussion
Create an account Log in

Want to stay up-to-date with Ruby on Rails?

Join 85,972+ developers who get early access to new tutorials, screencasts, articles, and more.

    We care about the protection of your data. Read our Privacy Policy.