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

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

interested

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