Running the gem with a non-SQLite database?

[gaaady]

I went over the code and it seems most of it is DB agnostic.

would it be an option for this gem to have two modes of operation?

1. full mode which uses sqlite
2. partial mode which excludes the sqlite specific code (leaves tenant creation manual devops task)

this will allow us to collaborate gradually without committing to a full implementation of another db functionality

[flavorjones]

Hi!

seems most of it is DB agnostic

Yes, I think the only sqlite-specific bits are create/destroy (plus a file lock around migration to address race conditions), which should all get moved upstream into the Rails adapter. At that point, I think we can start testing against PostgreSQL and MySQL (I'm most concerned about race conditions).

would it be an option for this gem to have two modes of operation?

I don't think you need to have a switch to flip. Unless you explicitly call create_tenant then new databases shouldn't be created (though running parallel tests may be an exception to that statement).

I'm curious what breaks if you try to use the gem with PostgreSQL today and you're not creating or destroying tenants.

[brendon]

I'm so glad to see this being done as a core rails thing :) I've been doing this since about 2007 with a bit of hackery. For MySQL this is what I use to switch the database:

module ActiveRecord
  module ConnectionAdapters
    class Mysql2Adapter < AbstractMysqlAdapter
      # Allows us to connect to a new database in a clean way
      def change_database!(db)
        if @config[:database] != db
          @config.merge!(database: db)
          clear_query_cache
          reconnect!

          Current.reset
        end
      end

      def restore_default_database!
        database = YAML.unsafe_load_file("config/database.yml")[Rails.env]["primary"]["database"]
        change_database!(database)
      end
    end
  end
end

It works well for us. We run a CMS with about 250 clients as seperate tenants. There's of course work we've done around global_id so that jobs and streams work properly, and we segment our active storage by Tennant ID.

For a Tennant ID we just hash the domain name we first set the site up with (it doesn't change after that) and that seems to work well. It's reliable in terms of no special characters but it does require a mapping between domains and the Tennant record.

Postgresql's overlapping schema's always appealed to me where you could have one central schema and then a schema for each Tennant and switch out that way. That way you can join tables between the central schema and the client schema if necessary.

Anyway, I'll be keeping an eye on this and will be keen to test any MySQL implementation :)