I’ve done this at several companies. Each enterprise account (in my case, each site) gets their own database. IMO it works extremely well.
You will need a way to detect schema version and bulk apply (and optionally rollback) schema updates. A ‘Schema’ table in each Site database with rows inserted/deleted after each update/rollback is sufficient.
A separate ‘Hosting’ database keeps track of all the sites and knows about each schema package, which is a version number, a function which can detect if the change was applied, and the SQL code to apply the schema change. Don’t ever store any site specific information other than the name/ID of the site in the Hosting database - because it could get out of sync when you restore a site backup, or if you have to restore a Hosting backup.
Ideally you would want to make schema changes always backward compatible, as in an old version of the code can always run fine against a newer schema. So, e.g. new columns are always nullable, as are new parameters to stored procedures. This has been a very useful property a number of times during deployments when you can switch the app binaries around without worrying about schema rollbacks.
You’ll of course need to script the database setup/creation process, so you can click a button to bring up a new site/customer/tenant. As much as possible don’t ever touch the database by hand, and if you follow this rule from the start you will stay in a sane happy place without much overhead at all.
I’ve done this with up to 4-figure number of databases and it’s served me just fine. There were many times that certain customers would get ahead in the schema and then later everyone would catch up as new code rolled out.
I think it would be a heck of a lot scarier doing DB operations if it was all a single database. For example, you’ll have a new customer who is using a new feature which you are beta testing with them. Easy to have just their database ahead of the mainline, and make any fixes there as you go, and then deploy the final GA schema worldwide.
The only cardinal rule I always followed was that a single binary had to work for all customers. I would not want to cross the line into customer-specific code branches at practically any cost. There were certainly feature flags that were only enabled for single customers, but ultimately every site could run on the same binaries and indeed the same app farm.
It’s particular useful to be able to backup/restore easily on a per-customer basis, and to be able to pull in just one customer DB into dev to reproduce the issue - without needing to pull everything over.
Not with Rails but with SQL Server and C#/ASP.NET. In this case it’s easy to setup so that the domain name would map to a database connection string at a very low level of the code. Everything above would have no concept of what site it was operating on. You never had to worry about writing any kind of code to isolate sites except for one thing — mixing the domain name into the session token so that a malicious user couldn’t try to reuse a session from another domain. Because of course it’s all the same set of app servers on the front-end.
You will need a way to detect schema version and bulk apply (and optionally rollback) schema updates. A ‘Schema’ table in each Site database with rows inserted/deleted after each update/rollback is sufficient.
A separate ‘Hosting’ database keeps track of all the sites and knows about each schema package, which is a version number, a function which can detect if the change was applied, and the SQL code to apply the schema change. Don’t ever store any site specific information other than the name/ID of the site in the Hosting database - because it could get out of sync when you restore a site backup, or if you have to restore a Hosting backup.
Ideally you would want to make schema changes always backward compatible, as in an old version of the code can always run fine against a newer schema. So, e.g. new columns are always nullable, as are new parameters to stored procedures. This has been a very useful property a number of times during deployments when you can switch the app binaries around without worrying about schema rollbacks.
You’ll of course need to script the database setup/creation process, so you can click a button to bring up a new site/customer/tenant. As much as possible don’t ever touch the database by hand, and if you follow this rule from the start you will stay in a sane happy place without much overhead at all.
I’ve done this with up to 4-figure number of databases and it’s served me just fine. There were many times that certain customers would get ahead in the schema and then later everyone would catch up as new code rolled out.
I think it would be a heck of a lot scarier doing DB operations if it was all a single database. For example, you’ll have a new customer who is using a new feature which you are beta testing with them. Easy to have just their database ahead of the mainline, and make any fixes there as you go, and then deploy the final GA schema worldwide.
The only cardinal rule I always followed was that a single binary had to work for all customers. I would not want to cross the line into customer-specific code branches at practically any cost. There were certainly feature flags that were only enabled for single customers, but ultimately every site could run on the same binaries and indeed the same app farm.
It’s particular useful to be able to backup/restore easily on a per-customer basis, and to be able to pull in just one customer DB into dev to reproduce the issue - without needing to pull everything over.
Not with Rails but with SQL Server and C#/ASP.NET. In this case it’s easy to setup so that the domain name would map to a database connection string at a very low level of the code. Everything above would have no concept of what site it was operating on. You never had to worry about writing any kind of code to isolate sites except for one thing — mixing the domain name into the session token so that a malicious user couldn’t try to reuse a session from another domain. Because of course it’s all the same set of app servers on the front-end.