using an ORM it's very simple. once you setup the relationships between models, you always use the highest level model as the entry point of your query and the ORM will scope the query for you. ie in Rails, if your highest level model is Account you just always use @account.customer.find(18). the Rails ORM will automatically scope the query to the current account.
using multiple schemas becomes a nightmare when you want to perform a migration to alter the database schema. you have to somehow perform a migration across every single schema without breaking anything. god forbid you need to rollback. have everything in one database makes it really easy. as please, don't talk how using a single database restricts scaling later on. with how far databases have come and how cheap cloud computing is, scaling isn't an issue anymore.
> the Rails ORM will automatically scope the query to the current account.
While that is true for simple queries it is not a valid statement for secure multi tenancy.
For example in this scenario:
Account (Id). Order (Id, AccountId, CustomerId). Customer (Id, AccountId).
You need to ensure Order can only use CustomerId that belongs to Account.
Rails does not include support for restricting deep relationships (grand children) to an account. Basically an attacker can misuse foreign keys from other accounts in new records (add a CustomerId from another Account on Order) or read data in relationship queries when initiating objects (show customer info from another account in an order).
There are ways of mitigating this but it requires doing more complex manual validation methods.
> using multiple schemas becomes a nightmare
There are gems that will solve some of this complexity but it is a risk. The most popular one is somewhat unmaintained.
The highest level entry point makes so much sense. I’ve been building multitenant saas for last 3 years enforcing “where tenant = x” and you’ve just blown my mind.
On more than one occasion I've started with a simple table for all tenants (where tenant = x) because we didn't know at the time if we will have many.
Then progressively when we understood how the service will be used, refactored to keep separate tenant's data separate.
Ended up with each tenant in a given schema, we were using Java and relied on Hibernate (ORM) to help connecting to the correct DB (it does support multi-tenancy) as well as on a DB migration tool (Liquibase) so any changes we made to the schema get replicated over all tenants.
that's why we're here... learning something new everyday is a great things. now imagine how cool it will be implementing this is your projects from here out :)
no kidding and you're being unreasonable... yes, what ii stated will get you 99% of the way there. what you're belly aching about is the 1% where you have to veer from this and do it yourself. big deal.
look... you wouldn't forego taking a plane across the country cause you have to take a cab the last 2 miles to the hotel. same thing here. just because something falls out at the last 1% doesn't mean it a bad way to do things.
using multiple schemas becomes a nightmare when you want to perform a migration to alter the database schema. you have to somehow perform a migration across every single schema without breaking anything. god forbid you need to rollback. have everything in one database makes it really easy. as please, don't talk how using a single database restricts scaling later on. with how far databases have come and how cheap cloud computing is, scaling isn't an issue anymore.