All threads / How do I switch from 1 database to 1 database per client?

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

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.

Join the discussion

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

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

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

    logo Created with Sketch.

    Ruby on Rails tutorials, guides, and screencasts for web developers learning Ruby, Rails, Javascript, Turbolinks, Stimulus.js, Vue.js, and more. Icons by Icons8

    © 2020 GoRails, LLC. All rights reserved.