Jonathan Smith
Jon Clausen
Jonathan,
The answer is both “Yes“ and “No”, depending on your requirements and platform restrictions for the app. Multi-tenancy is almost ridiculously easy with a NoSQL database like MongoDB , compared to RDBMS, where each system enforces its own rules on cross-database read/write communication. PostgreSQL is the most challenging, in my experience, to abstract multi-database logic, with MySQL and MSSQL being the easiest.
I’ve dealt with multi-tenancy in both and each has its challenges. The most recent multi-tenant/multi-database app I wrote was with MongoDB and it runs from within a single application scope. I haven’t found a clean way to do RDBMS multi-database/multi-tenant without maintaining separate Application scopes, albeit with shared mappings to the core application logic and assets. You can do it with a single App scope, but it’s much messier and places a higher burden on each request ( and can require a gaggle of conditionals, which make the application harder to maintain ). You also can’t use ORM for the tenants unless you have separate App scopes.
The easiest RDBMS, solution, IMHO, uses a single master database to maintain read/write views (or in PostgreSQL’s case Foreign Data Wrappers - https://wiki.postgresql.org/wiki/Foreign_data_wrappers ) between other databases. Views/FDW’s have their own tenant prefixes, which are used for aggregation and by the master app (see below) to perform updates on the tenants.
Tenancy records, including those tenant prefixes, are maintained by the master to handle reports aggregation between the tenants. The master app primarily uses the consolidated view database and is responsible for provisioning and migration of the individual applications and their databases, DSNs, schemas, and db views. Each tenant app speaks only to their own database, albeit using the same code as all of the other tenant apps (the only difference being the DSN name and the tenant-specific assets, logs, etc).
Ideally each tennant “app” consists of an Application.cfm and index.cfm and a WEB-INF directory, with mappings to the rest - shared logic and assets & tennant-specifc assets - in the web server and Lucee. If the master is also a tenant, you’ll still want a separate application scope for the global administration. A container infrastructure would work well to handle the provisioning.
The above RDBMS solution would meet all of your requirements. The downside would be the burden of maintaining the individual application scopes, which could may limit the number of tenants per application server, if that’s a consideration.
Mongo, as I said, makes it relatively easy to handle multi-tennancy within a single application scope. The downside, however, is that you have to handle aggregated information on the tenant databases with more intensive polling processes, rather than in a single cross-database aggregation, using the master db to maintain the polling results. The $lookup method, introduced in MongoDB 3.2, which allows cross-collection aggregation has potential to change the need for polling, but the 3.2 Mongo Java driver doesn’t yet implement it in a way that would allow you perform cross-database queries.
HTH,
Jon
--
Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html
---
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/7f80a3b3-c43d-4f5b-9fbd-4de667896310%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.