Best Practices for developing a multi-tenant application with Symfony2 and Doctrine2

Question

I am working on an application that needs to support the multi-tenant model. I am using the symfony2 php framework and doctrine2.

I'm not sure the best way to go about architecting this requirement. Does Symfony's ACL functionality provide a part of the solution?

What recommendations or ideas could you provide? Are there any sample symfony2 applications or open source applications that are available which have implemented this strategy?

My first thought is to use a tenant_id column in all the tables and have this relate to the account object in the application. I'm not sure though if ACL is supposed to take care of what i'm wanting to do, or if your still responsible for all of the queries against your objects so they don't return un-authorized data.

If I wasn't using Doctrine, it might be easy to say just append Where tenant_id = @accountid to each query, but i'm not sure that is the right approach here.

Thanks

Answer 1

We've been doing this for some time now (although not with symfony and doctrine but the problems remain the same) - we started with one huge database and implemented a 'environment identifier' per row in each of the tables. This way schema migrations were easy: all the code was unified thus a schema change was a single change to code and schema.

This however lead to problems with speed (large tables), agility (moving/backuping etc large datasets is alot more intensive than alot of smaller ones) and of course more easily breakable environments since a single failure will pull down every dataset in the system...

We then switched to multiple databases; each environment its own schema. By utilizing the migrations provided with Doctrine (1 in our case) we're able to quickly update the entire app or just a single environment. Also the ability to move specific changes between tentants allows for better precision in speed and optimization.

My advice would be: create a single core which is extended into the different tenants and keep the local customized database configuration per tentant. (in a app.ini-like structure)

i.e.

/
    apps/
        tentant1/
            models/ <--- specific to the tenant
            libraries/ <--- specific to the tenant
            config/
                app.ini <--- specific configuration
        tentant2/
        /**/ etc
    core/
        models/ <--- system wide models
        libraries/ <--- system wide libraries (i.e. doctrine)
        core.ini <--- system wide configuration

This could keep everything organized. We are even going as far as having the comeplete structure of core available per tentant. Thus being able to override every aspect of the 'core' specific to the tenant.

Answer 2

We do this with one of our main solutions at work, and it's certainly possible. We use Symfony2's bundles to create a "base" bundle which is then extended by other per-client bundles.

That said, we're looking at moving away from doing things this way in the future. The decision to go multi-tenant was not the right one for us, as many of our clients are uncomfortable with their data being in the same database tables as everyone else's data. This is completely aside from the potential issues of slow performance as the tables grow.

We've also found that Doctrine 2 has some pretty serious issues unless it's kept well under control. Whilst this may be a side effect of poorly structured code and database logic, I feel it's a bit of a hole for an ORM to be able to get to the point where it throws a fatal error because it's used too much memory - particularly when the only reason it's using so much memory is because it's batching up SQL queries so that they can be made "more efficient".

This is purely my opinion, of course :) What doesn't work for us may well work for you, but I do feel you'd be better off keeping separate databases per-client, even if they're all stored on the same server.

Answer 3

I think that, To manage multi-tenant multi-database with symfony 2/3. We can config auto_mapping: false for ORM of doctrine. file: config.yml

doctrine:
    dbal:
        default_connection: master
        connections:
            master:
                driver:   pdo_mysql
                host:     '%master_database_host%'
                port:     '%master_database_port%'
                dbname:   '%master_database_name%'
                user:     '%master_database_user%'
                password: '%master_database_password%'
                charset:  UTF8
            tenant:
                driver:   pdo_mysql
                host:     '%tenant_database_host%'
                port:     '%tenant_database_port%'
                dbname:   '%tenant_database_name%'
                user:     '%tenant_database_user%'
                password: '%tenant_database_password%'
                charset:  UTF8

    orm:
        default_entity_manager: master
        auto_generate_proxy_classes: "%kernel.debug%"
        entity_managers:
            master:
                connection: master
                auto_mapping: false
                mappings:
                    AppBundle:
                        type: yml
                        dir: Resources/master/config/doctrine
            tenant:
                connection: tenant
                auto_mapping: false
                mappings:
                    AppBundle:
                        type: yml
                        dir: Resources/tenant/config/doctrine

After that, we cannot handle connection of each tenant by override connection info in request_listener like article: http://mohdhallal.github.io/blog/2014/09/12/handling-multiple-entity-managers-in-doctrine-the-smart-way/ I hope that, this practice can help someone working with multi-tenant

Regards,

Vuong Nguyen