Multitenancy

This is the third post about accounts in the app. I should make something simple, change every query in the app, and add “account_id” everywhere. Probably yes. But not this time. If you follow my posts maybe you realized all data in this app are user(account) oriented. There is no reason to allow one account to have access to another account’s data. So I decide to use multitenancy pattern.

Multitenancy is a software architecture pattern where a single instance of an application serves multiple tenants or customers. Each tenant’s data and configuration are isolated from each other, providing the illusion that each tenant has its own dedicated instance of the application.

In a multitenant application, tenants are typically identified by unique identifiers, such as account IDs or subdomains. The application uses this identifier to route requests and ensure that data and resources are only accessible to the corresponding tenant.

There are different types of multitenancy:

• Single Database, Shared Schema: In this approach, all tenants share the same database schema, but their data is isolated using filtering or partitioning. Each table includes a column to identify the tenant, and queries include filters based on the tenant identifier.

• Single Database, Separate Schema: Each tenant has its own separate schema within the same database. This approach offers stronger isolation between tenants and can be more performant than the shared schema approach.

• Multiple Databases: In this approach, each tenant has its own dedicated database instance. This provides the highest level of isolation but may require more resources to manage.

If I would have Postgres probably I would go to multiple schemas, but since I have chosen sqlite it would be nice and easy to use multiple databases.

As I mentioned tenants are typically identified by unique identifiers. And in a previous post, I created middleware where I set this id in almost every request. Now I can create another middleware where I can set a database based on tenant id.

class MultitenancyMiddleware
  def initialize(app)
    @app = app
  end

  def call(env)
    tenant_id = env["account_id"]

    if tenant_id
      unless File.exist?("#{ENV["DB_DIRECTORY"] + ENV["RACK_ENV"]}_#{tenant_id}.sqlite3")
        create_tenant_database(tenant_id)
      end
      ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: "#{ENV["DB_DIRECTORY"] + ENV["RACK_ENV"]}_#{tenant_id}.sqlite3")
    else
      ActiveRecord::Base.configurations = YAML.load_file("db/configuration.yml")
      ActiveRecord::Base.establish_connection(ENV["RACK_ENV"].to_sym)
    end

    @app.call(env)
  end

  private

  def create_tenant_database(tenant_id)
    ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: "#{ENV["DB_DIRECTORY"] + ENV["RACK_ENV"]}_#{tenant_id}.sqlite3")
    ActiveRecord::MigrationContext.new("db/migrations").migrate
  end
end

As you can see, I’m still using the main database. I have to store accounts somewhere. This is only one global thing in this application. So if someone wants to create an account, or login into these requests it hit the main database, otherwise it hits to separate DB.

Because I set the database connection in middleware I don’t need to setup it up inside the application so I can remove it from the repositories

  def setup_database
    ActiveRecord::Base.configurations = YAML.load_file("db/configuration.yml")
    ActiveRecord::Base.establish_connection(ENV["RACK_ENV"].to_sym)
  end

One important thing. Now we don’t have one database, we have multiple databases and if we run rake db:migrate it runs migrations only for main database. If I want to migrate everydatabase I need custom script for that.

require "active_record"
require "dotenv"
require_relative "./records/account"

Dotenv.load(".env.development") if ENV["RACK_ENV"] == "development"
Dotenv.load(".env.test") if ENV["RACK_ENV"] == "test"

ActiveRecord::Base.configurations = YAML.load_file("db/configuration.yml")
ActiveRecord::Base.establish_connection(ENV["RACK_ENV"].to_sym)

Db::Records::Account.find_each do |tenant|
  tenant_id = tenant.id
  db_config = {
    adapter: "sqlite3",
    database: "#{ENV["DB_DIRECTORY"] + ENV["RACK_ENV"]}_#{tenant_id}.sqlite3"
  }

  ActiveRecord::Base.establish_connection(db_config)

  ActiveRecord::Migration.verbose = false

  begin
    ActiveRecord::MigrationContext.new("db/migrations", ActiveRecord::SchemaMigration).migrate
  rescue StandardError => e
    puts "Migration failed for tenant #{tenant_id}: #{e.message}"
  end
  ActiveRecord::Base.remove_connection
end

Is it worth?

That’s a good question. Probably it would be nice to have some benchmarks. But I don’t have any. Also with my potential amount of data performance would never be a benefit (issue). But from a code perspective, it’s much nicer (in my opinion). We don’t care about users(accounts) we just have the logic of our application and the database is set at the beginning, that’s all. Of course, multitenancy doesn’t fit every application, but in this case, it fits perfectly

Like always, the whole code is on github