I am obsessed with the idea of per tenant databases. But I am afraid of migrations. Has anyone tried that?

Isn't this exactly what every mobile app does when the db is on a phone, and every app where the db is kept on the local machine? I do it with HashBackup and have done 35 db migrations over 17 years without much trouble. There have been 1 or 2 migrations that had a bug, but you fix that by doing another migration.

▲

c0n5pir4cy 6 hours ago | parent | prev | next [-]

It's pretty awful, generally best avoided unless you have a specific reason for doing so (e.g. encrypting the full SQLite DB per customer). It also introduces you to some pretty bad risks (what if there is a bug in a migration which only affects certain tenants?).

That being said it can be done and it's pretty normal for mobile apps, desktop apps etc. You just have to make sure the migrations are run when the tenant connects/unlocks/runs the app - and make sure that you minimise the risk of it going wrong!

▲

robertjpayne 7 hours ago | parent | prev [-]

We do it on Postgres with schemas but we have a very fixed amount of tenants so it works.

I don't think it scales when you have unbounded amounts of tenants.

	▲	roryirvine 3 hours ago \| parent [-]
		It works well enough if you deploy a separate version of the app for each tenant, and upgrade each tenant's app + db in lockstep. It's a bit of a 1990s setup (as the sibling comment says, it's more or less the same model as a widely-deployed desktop app), but it does scale and can be useful in situations where strict isolation between tenants is beneficial.