Implementing SaaS (Multi Tenant) Model

Our CakePHP 3 app (a POS System) is going to use subdomain approach where clients will share a common code base but run on individual databases.

2 things from the config (app.php) file will be unique to each instance : database details and security salt. These details will be stored in a table of main site database (wordpress) and I want to load them based on the subdomain opened. (abc.maindomain.com or xyz.maindomain.com)

I am not sure where should I implement this stuff in the code. bootstrap.php seems to be the only viable solution for now. What are experts’ thoughts over this? I plan to store those details in a session once loaded so that system doesn’t need to call main database everytime.

I have checked this article by Mark Story but I would prefer not to put each connection details in config.app.php but fetch the details from main database (db user password encrypted) and create a new connection with it. Would that be a proper way to deal with this? If yes, can you please guide me how to create a new database connection and make it default from dispatcher?

1 Like

Making separate databases per customer sounds like an attractive thing at first. However, if you plan on having thousands of customers or more, these systems can introduce a non-trivial amount of complexity around schema management and operational complexity. Another approach I’ve found useful is to make all your primary keys composite keys that include the ‘tenant id’. All your model associations end up joining with this ‘tenant id’, and you can implement behaviors that ensure the tenant id is always added to queries.

If you are hard set on using multiple databases, you can use ConnectionManager::create() to define new connections at runtime.

3 Likes

Thank you @markstory. For now, I will have to continue with separate databases as app is much bigger and contains so many join tables where adding an additional column for tenant_id will be a challenging task. Planning to continue as the way I had mentioned in my question.

Thanks again. Best wishes.

@markstory : In you post, the line for bootstrap.php is :

DispatcherFactory::addFilter('Shard');

Shouldn’t it be? :

DispatcherFactory::add('Shard');

Yes it should. I’ll get that fixed.

I do not speak english very well.

When You say “tenant_id”.
Can I understand how Multi-Company?