We do this for our wiki/intranet SaaS app. In our case we don't use it for all data in the app though, but rather for a specific feature. One of the blocks customers can add to their wiki pages are form fields so they can build custom forms. The app then automatically generates a corresponding database in the backend to store records for their form, using a separate SQLite db.
Our original idea was to allow customers access to their form db directly so they could even run custom SQL queries on it and so on. In the end we actually never used that part (users just view and filter their records in the UI), so unless we still need this in the future, one could argue it's.. just slightly overengineered. It works well otherwise though, and migrations aren't really a problem for us, because they're done dynamically by the app (when people make changes to their form). The scaling is also no issue as one db will always be small enough to fit on one server, so we can "shard" easily by file. So I think the only obvious lesson here was make sure you really really need it (and if migrations/scaling would have meant a lot of overhead, I don't think we would have even considered it).
Our original idea was to allow customers access to their form db directly so they could even run custom SQL queries on it and so on. In the end we actually never used that part (users just view and filter their records in the UI), so unless we still need this in the future, one could argue it's.. just slightly overengineered. It works well otherwise though, and migrations aren't really a problem for us, because they're done dynamically by the app (when people make changes to their form). The scaling is also no issue as one db will always be small enough to fit on one server, so we can "shard" easily by file. So I think the only obvious lesson here was make sure you really really need it (and if migrations/scaling would have meant a lot of overhead, I don't think we would have even considered it).