Support for MySQL and PostgreSQL

[flavorjones]

👋 Hi, everybody!

I've had a few chats with people who are interested in support for MySQL and PG. Notably there's been

• An email thread and discussion with @gaaady about Postgres
• Chat with @andrewmarkle about MySQL
• Email thread with @mikedhart about MySQL

So I wanted to tag you all and try to bring everybody together to chat a bit.

SQLite was the first database supported simply because that's where I currently have a fast feedback loop. There's no technical reason that I know of that would prevent the gem running against other database.

In summary, 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.

In the meantime, though, I think we can start testing against PostgreSQL and MySQL (I'm most concerned about race conditions) and see what breaks. Has anybody tried running their test suite or an app in development yet?

[gaaady]

Hi,
We’re currently implementing multi-tenant support at the application layer (Rails + Postgres). We don’t have extensive tests for this feature yet.

When you say “running the test suite,” do you mean adding the gem to our existing app and running our own tests against it, or spinning up a new Rails app that uses the gem and running the gem’s test suite?

[flavorjones]

@gaaady I meant the application test suite, but I'm really asking if anybody who's interested in other database support has started exploring what's broken.

[flavorjones]

Just a small update: I merged #204 by @andrewmarkle today, which takes the first big step of isolating the SQLite-specific code.

[flavorjones]

Another big step forward by @andrewmarkle on MySQL support is getting close to being merged: #246

[klenis]

Hi!

I've spent countless of hours thinking about and trying to come up with a clean solution to multi-tenancy in our Rails application, from the database layer to the application layer.

It is really exciting to see this functionality coming to Rails. I did a test with @andrewmarkle's mysql-adapter branch (thanks!) and it kind of works, at least the basics.

The two biggest problems I've encountered when implementing our current multi tenancy solution is how to make sure every framework in Rails as well as any third party library we use respects the boundaries and works like Rails does in single tenancy. It brings me relief to see that this project aims to resolve the first one and I see similarities on how we solved it. What worries me a bit is the third party libraries.

We use a few libraries that inherits from ActiveRecord::Base and we also have a fail safe similar to Cannot use an untenanted ActiveRecord::Base connection. If you have a model that inherits directly from ActiveRecord::Base, make sure to use 'subtenant_of'. In development, you may see this error if constant reloading is not being done properly.

As an example, we use the Mobility gem and one way of making it work in our current multi tenancy solution and with active-record-tenanted is to find and make sure we add the multi tenancy logic like Mobility::Backends::ActiveRecord::Table::Translation.send :subtenant_of, "ApplicationRecord" in an initializer. It works but I am not proud of it. I find it hacky and brittle.

I wonder what your thoughts are on the vast number of existing libraries that are not active-record-tentanted-aware? Is it up to the user to find the correct monkey patch to make the library work? Is it up to library authors to make sure their library is tenant aware in the future since this will be a part of Rails? Maybe both? Maybe something else?

Thanks for all the hard work on this. I will happily continue to test and contribute.

[flavorjones]

we add the multi tenancy logic like Mobility::Backends::ActiveRecord::Table::Translation.send :subtenant_of, "ApplicationRecord" in an initializer. It works but I am not proud of it. I find it hacky and brittle

This is exactly what subtenant_of is intended for. It might feel hacky, but it's fine (for now). You can also abbreviate that as:

# no need for `send`!
Mobility::Backends::ActiveRecord::Table::Translation.subtenant_of "ApplicationRecord"

I do feel like there is a missing abstraction in Rails, which a distinction between ActiveRecord::Base (which should never be directly used by models IMHO) and an explicit "application record base class" which is what any gem/engine with models should use to get connected to the application's default database. Something like ActiveRecord::ApplicationRecordBase would largely obviate the need for subtenant_of.

I wonder what your thoughts are on the vast number of existing libraries that are not active-record-tentanted-aware? Is it up to the user to find the correct monkey patch to make the library work?

For now, yes. But this gem should emit very readable exceptions that should point you in the right direction! If it doesn't, please open an issue and we can try to make it as literate and helpful as possible.

Is it up to library authors to make sure their library is tenant aware in the future since this will be a part of Rails? Maybe both? Maybe something else?

Again, if Rails offered a standard "This is the base class for application models" then libraries could -- over time -- start to use that class instead of ActiveRecord::Base, and that would make it easier to solve this particular problem.

[klenis]

Thank you for taking the time to share your thoughts and insights on this. I totally agree with you that it's fine for now.

[JoshReedSchramm]

Looking into this problem now for a project that requires data isolation and found this. We're on postgresql so i'll give this a shot to see how well it works with that.

[coreyaus]

Hey team,

Apologies if this is an obvious question, but when discussing support for Postgres, is the main approach that people have in mind to use a separate database for each tenant, or is it likely there would also be support to use a separate schema for each tenant (within a shared database)?

The ActiveRecord PostgreSQLAdapter permits a schema_search_path within the database.yml: https://github.com/rails/rails/blob/main/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L36-L37, so I could imagine something like this:

production:
    <<: *default
    host: some-host.com
    port: 5432
    username: postgres
    password: <%= ENV['PASSWORD'] %>
    schema_search_path: %{tenant}
    tenanted: true

Or just setting a tenant-specific username and scoping the DB roles of that user (i.e. only granting privileges to the relevant schema for each tenant-specific DB user)

I don't have a strong sense of potential security implications, even if this approach were viable - I assume that schema_search_path option effectively just sets something like ?options=--search_path%3D{tenant} in the connection string URL under the hood.

Do others with more experience have a general sense whether an architecture of separate DB schemas in a shared database could become a viable development path with this gem or:

1. would it likely be incompatible with the current activerecord-tenanted internals?
2. or are there other reasons this might be a plain Bad Idea (i.e. why completely separate databases might be the expected default?)

Thanks for any insights you can offer!

[flavorjones]

@coreyaus Thanks for asking!

I have very little experience with PostgreSQL, so I'm not confident I can give you good answers here without spending some time exploring. Really, I'm hoping for someone experienced to appear who can help guide (and validate) some of these design decisions.

I can tell you that the mature tenanting gem Apartment supports a few different approaches for PG, including two that use schemas (and one that does not):

https://github.com/influitive/apartment/blob/development/lib/apartment/adapters/postgresql_adapter.rb#L6-L11

Based on that, they both seem like they're probably viable (but maybe there are tradeoffs for different use cases?). It might be worth poking around in that project's code and git history to see what lessons they've learned.

[darinwilson]

According to the README for apartment, it uses schemas by default, and says:

One can optionally use the full database creation instead if they want, though this is not recommended

(it doesn't provide any detail about why it's not recommended).

That said, the README hasn't been updated in 6 years, so things may have changed.

[robacarp]

We have a running postgres multi-tenant rails installation which supports tenants as separate logical databases with separate connection strings (we have thousands). I would strongly prefer to be able to fetch connection strings for each tenant, not just username but physical host and password too.

This enables a security/resiliency model with many baskets of eggs, not just many eggs in one basket.