Persistent schemas, excluded models, shared extensions, SQL VS .rb and future plans

[NielsKSchjoedt]

Hi all,

I'm a happy user of apartment with my rails 4 app and a postgresql database. My use case touches all areas of issues that comes from functionalities mentioned in the headline.

Now, I'm starting to get confused about all the PR's coming in tackling various pieces of the problems that comes from: having the excluded models and the tenanted models only reside in either a shared schema or a tenanted schema #117, having FK constraints between an excluded model and a tenanted model, having shared extensions like hstore #141, being able to correctly dump the schema including the cross-scema constraints and the shared extensions, and again loading it all correctly when creating a new tenant #16 or a tests database.

Now I thought that it might be a good idea to have a small discussion about the current state of the gem and the goal of how it "should be", so we can work in the same direction, when fixing bugs and implementing new functionality.

Now consider the following setup:

Let's say we have a blogging app with multiple blogs about food and wine. The blog contains a model called post which has many comments. The blog uses both hstore functionality and postgis functionality, so they need to be alway available. A user can create posts in all blogs, so the user model needs to be an excluded_model. From my knowledge, the best way to set this up using apartment and postgres would be like so:

config.use_sql = true # to be able to dump the hstore and postgis functions
config.default_schema = "public"
config.persistent_schemas = ["extensions"]
config.excluded_models = ["User"]

Question 1:
Do we agree that the diagram above represents the current recommended setup?

Question 2:
Does this setup imply any issues with migrations, setup of test databases, creating new tenants etc.?

Now consider this diagram of a future version of Apartment:

Question 3:
Do we agree that this diagram above represents how we want apartment to work in the future?

Question 4:
If so, what are the current obstacles to make this work?

I'm sure a lot more of these questions and issues have been touched in many other threads, but I really would appreciate getting a "current state" overview and a more common guideline for the future development.

[bradrobertson]

1. Our current setup does NOT have the public schema in the persistent_schemas option. I feel like I played with that and had problems, but I can't quite remember. The idea of an extensions schema is new, again we don't use that, but I think it makes sense for the current situation.
2. No issues as it stands with test setup/migrations since everything just runs based on the schema.rb
3. This is how I've envisioned Apartment working in an ideal case
4. It's slightly more complicated than that however. Reason being is that the schema file is generated from the default_schema, which right now is public. As such, public needs to have all the tables in there in order for the schema file to generate properly. This is where I haven't really come to a proper solution. At a glance, yes your setup looks ideal (ie tenant tables only in tenants, excluded only in public, extensions in public), but again there's the issue of how the schema dumper reads the current database to do it's thing. So there's a missing piece in all of that.

The current state of development of Apartment hasn't moved much as:

1. I've been too busy
2. This is, with a few exceptions of Pull Requests, largely been a singular effort on my part and I'm finding it difficult to manage my personal time with the open source project
3. It does EXACTLY what we need at the moment without issue so there's not a lot of motivation to change it

Now... I'd be happy to help in all of the matters, discuss best next steps etc, but I also can't have it taking up much more of my time (unfortunately). I really appreciate your enthusiasm though so hopefully we can make all of this work, and even get a group of peeps helping out etc.

Other Ideas I've thought of for Apartment

1. One idea we've been throwing around is to use table inheritance in each schema so that migrations are much faster. Currently, with 100's of schemas, migrations can be agonizingly slow. We'd like to see a fix for that if possible, and table inheritance might solve it. (ie only ever migrate the parent tables, on the tentant tables just run index migrations). This is really just an idea though to solve what is a very real problem of slow migrations with lots of tenants.
2. Doing a multi-tenant setup without using schemas, just using scoping. This would also solve the migration issue, but has it's own problems because now EVERY model needs a tenant_id and all queries need to automatically be scoped to that id (except for excluded models)

[NielsKSchjoedt]

Very interesting. Thanks for giving such a detailed feedback. I'll need to have a bit of a deeper look into the internals of rails.

Any comments on the use_sqlfeature? Is it considered as solid alternative to using the rails SQL format instead of schema.rb?

[bradrobertson]

Very good question. I honestly have no idea. I've never tried it myself, it was added through a PR. The reason we couldn't use it before was that it would dump every schema into the file. This file is used for creating new tenants so obviously that would lead to a bad, compounding issue.

Maybe continue that discussion on that issue ?