Search code examples
mysqlruby-on-railsruby-on-rails-3ruby-on-rails-4devise

How to use multiple databases with the same structure but with different content for each user for rails/mysql?

Well, I know that it sound a bit weird but it's exactly what I want to do.

I got a rails application in which has 8 tables. One of them, a device created table, named: "partners".

I store each users in that table. But other than that, the other tables content should be unique for each partner. I was thinking to use some method after the authentication and set the database as db_"#{parentName}", but I don't know how to proceed with it. How can I create and use these different databases with the same structures?

Solution

  • SAAS

    What you're looking at is multi tenancy:

    enter image description here

    This basically means you have a set of different databases (not just datatables) for a multitude of users. You'll generally have a central set of "public" data (such as users & options), and then a series of "tenant" databases which will house all the tables you need

    Rails multi-tenancy has only really been achieved with PostgreSQL schemas before:

    enter image description here

    However, there is hope for MYSQL :)

    MYSQL Multi Tenancy

    The problem with MYSQL (true) multi tenancy is several-fold:

    1. Creating DataBASEes programatically is only possible with the "right" hosts
    2. Rails can only support one schema - meaning if you want "Tenant" DB's, you need to create functionality for multiple schemas
    3. You need a way to bind the creation of your databases with the creation of Accounts etc (probably with Resque)

    We've actually worked through the first issue right now - our host RackSpace actually provides an API for its MYSQL database instances. After working with one of their incredible developers (Evan Light),we managed to get it a system in place whereby we can create MYSQL databases on the fly. Totally legit & programmatic :)

    The next issue is something we're still working on - multiple schemas. This is somewhat trickier, as Rails seems to rely on creating the schema at db/schema.rb every time you perform a migration. For true multi-tenancy, you need to be able to handle schemas for your tenant db's, as well as your "main" db's. I don't have any resources for you on this right now unfortunately

    Finally, you need to be able to link it all together. It will be highly inefficient if you tried to create a database "synchronously" - meaning that you handle it in the same flow as your application. You need to handle it "asynchronously" (in the background), freeing up your application's processes. This will best be done with a queuing mechanism such as Resque