71–80 of 264 posts
The reasoning was that customers data couldn't ever leak into each other, and moving a customer to a different server was easier. I vaguely recall Joel Spolsky speaking or writing about it.
This will be very inefficient due to the way DBMS commonly lay out data in pages. And if you want to do any kind of aggregate queries (e.g. analytics) you're probably in for some royal pain. If you want to do this for security, why not layer the DB behind some system that requires and verifies the users access tokens for each request? The only situation where such a setup might make sense is when you actually need pe…
The inability to reuse database connections would be a huge performance hit. In a traditional webapp backend, you have a pool of connections to the database. User01 hits your service, and grabs a connection off the pool. User02 does the same, and so on. These connections get put back in the pool for reuse once a user is done with them. In your design, every time a user hits your service, a new connection, specific to…
Not a problem with MySQL, "use `tenant`" switches a connection's schema. Rails migrations work reasonably well with apartment gem. Never had a problem with inconsistent database migrations. Sometimes a migration will fail for a tenant, but ActiveRecord migrations records that, you fix the migration, and reapply, a no-op where it's already done. We don't use a single mysqld for every tenant mind, it's not like migrati…
But if the idea is to isolate accounts form each other, the different schemas would be available to different DB users. You would have to re-authenticate to get access to the other DB.
Remember that a database is supposed to be shared. It's designed that way for performance reasons. If your big issue is you're constantly doing flyway and it's unmanageable, go schemaless, or refactor where/when/how you place your data. Rethink the architecture/design, not the tool. If it's a microservice-based application, remember that you are supposed to have independent state per service, so there shouldn't be one giant database anyway.
But for like 12 customers, sure, go for it. It's very common for "whale" accounts to get dedicated infra/databases while the rest get a common sharded layer.
What a lot of enterprise SaaS vendors do is have one single database for all customer data (single tenant). They then use features like Virtual Private Database to hide customer A data from customer B. So that if customer A did a “select *” they only see their own data and not all of the other customers data. This creates faux multi-tenancy and all done using a single db account.
Virtual Private Databases. What a lot of enterprise SaaS vendors do is have one single database for all customer data (single tenant). They then use features like Virtual Private Database to hide customer A data from customer B. So that if customer A did a “select *” they only see their own data and not all of the other customers data. This creates faux multi-tenancy and all done using a single db account.
Virtual Private Databases. What a lot of enterprise SaaS vendors do is have one single database for all customer data (single tenant). They then use features like Virtual Private Database to hide customer A data from customer B. So that if customer A did a “select *” they only see their own data and not all of the other customers data. This creates faux multi-tenancy and all done using a single db account.
This sounds very much like Row Level Security, but I've never heard the term "Virtual Private Database" to describe it.
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.
Virtual Private Databases. What a lot of enterprise SaaS vendors do is have one single database for all customer data (single tenant). They then use features like Virtual Private Database to hide customer A data from customer B. So that if customer A did a “select *” they only see their own data and not all of the other customers data. This creates faux multi-tenancy and all done using a single db account.