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.
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.
E.g for the JVM, there's Liquibase and Flyway, and I agree with you, setting that up took a good part of a couple of weeks of dedicated effort.
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.
So for simple queries, fine, but anything else this won't work.
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.
https://influitive.io/our-multi-tenancy-journey-with-postgre...
Then they realise they've got a growing number of databases and webservers to maintain and account for, so they combine them all into a multi-tenant system.
I found a few tools early on that work for this in Rails. You can guarantee all of your sql includes the tenant.
- Apartment
- Tenancy
There's also several for the PHP ecosystem.
I've only done this at one company, and they self-hosted, and it was indeed a complete nightmare to manage, but part of that was the self-hosting part.
Interested?
And then providing enterprise service for large companies that want their own installation.
WHERE tenantId = foo
Everyone below a certain scale. Or when it just makes more sense, like Proboards' free tier.
Once you’ve done that, you don’t have to worry about forgetting a `WHERE tenantId = foo` somewhere and leaking data. The actual code is shared (i.e. not installed separately or in separate processes) and you would simply use your dependency injection or equivalent to resolve the DB connection contingent on $user’s validated claims.
“Networking” or sharing entities across tenants becomes harder this way.. but purer/cleaner. You essentially create a federation layer and communicate across instances via a hard code wall (via network requests to yourself and an API) rather than “in memory” which is less efficient and requires a lot of boiler plate, but it’s going to be what you’re going to end up doing anyway.