Has anyone ever used multiple SQLite databases per tenant/account?
For sake of argument, let's say I have a fixed schema/format that will never change and I never need to aggregate queries across multiple customer accounts. Also, let's say writes to a single database are never going to be more than a hundred concurrent users. Why shouldn't I store each tenant's data in its own SQLite database? It makes it very easy for local client apps to download their data all at once. Backups are incredibly easy. Peer-to-peer synchronization behaves like a git repository merge. Why shouldn't I do this?
When would this happen for any non-trivial multi-tenant service? The difficultly of performing migrations sounds like it would pretty quickly negate any simplicity gained.
Definitely use case specific, as it wouldn't work generally, especially for domain driven data models. The schema I'm thinking of specifically represents a data model that is flexible enough where migrations are extremely rare[1].
Even if there are migrations, it's treated similar to a file conversion (like upgrading an older excel file format to a new format on demand when the file is accessed).
If the users are never aware of other users and they only care about their own data, sure. Maybe you have a service where a user can sign up to do a specific task that never requires interaction with another user.
However, ff you want interaction across users (messaging, user-roles etc.) then you might want to have them in one database.
For sake of argument, let's say I have a fixed schema/format that will never change and I never need to aggregate queries across multiple customer accounts. Also, let's say writes to a single database are never going to be more than a hundred concurrent users. Why shouldn't I store each tenant's data in its own SQLite database? It makes it very easy for local client apps to download their data all at once. Backups are incredibly easy. Peer-to-peer synchronization behaves like a git repository merge. Why shouldn't I do this?