A database per tenant makes the rest of the workflow significantly easier though. No need to add clauses to SQL WHERE statements for users/groups. Queries are faster (less data). And data can be moved much easier between servers.
Yes, it does add extra overhead at account creation, during DB migrations, and for backups.
But if you don’t need cross-account or public data access, it can make life much easier.
> No need to add clauses to SQL WHERE statements for users/groups.
This is basically what RLS does for you. You specify the access and you specify the current user(via a connection, SET ROLE, etc). Then it does all that complicated query filtering stuff for you, to ensure you don't screw it up.
> Queries are faster (less data). And data can be moved much easier between servers.
Not really, the overhead is just different(and likely more of it) in your solution. It's not wrong nor is using RLS right.
> No need to add clauses to SQL WHERE statements for users/groups.
ORMs have support to add in these conditions automatically, you only have to define the condition in 1 function. This gives you the best of both worlds. You can use your FK based separation and have application level guarantees that your tables with a tenant id are scoped to a tenant by default and you have the option to query multiple tenants to do super admin level reporting across tenants by using explicit function calls that basically say "yes I know I'm bypassing the auto-enforced tenancy check".
There's no performance issues since it's all 1 schema and there's no nightmare or overhead of having to run a DB migration for each database.
I'm not sure why the author didn't include this in their blog post because it's a popular option in Rails, Flask (SQLAlchemy), etc..
Yes, it does add extra overhead at account creation, during DB migrations, and for backups.
But if you don’t need cross-account or public data access, it can make life much easier.