The biggest challenge I foresee is applying migrations so that all tenant-specific databases have the same schema. Upon user sign-up, you’d have to create the database and apply all migrations. You’d also need to increase the maximum number of open file descriptors with ulimit, if you have too many users. An interesting discussion.
Also, you couldn’t perform cross-db queries; but given that you can use Task.async_stream/3 across the repos, and access is crazy fast on NVMe (and even SSD), and that each open SQLite DB also has some in-memory cache, the task then becomes writing a function that aggregates the results of multiple queries that you run in parallel across the databases.
On the other hand, it also means that the memory requirements scale with the number of tenants, depending on how you set up the size of the pool and the page-cache size. At the very least, it makes sense to not leave Repo processes of inactive tenants running all the time, but to start each tenant’s dynamic repo at login, and stop it at logout. Authentication would still require a central database.
The more I think about it, the more I like it, at the bare minimum as an interesting exercise, especially if coupled with OpenTelemetry so that you can track things on the LiveDashboard.
Edit: never used encryption with SQLite, but this could also be a way to have each tenant’s db encrypted with a different key.
