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

Regimen

What have you done so far? Which N alternative practices have you done, and which is the best practice in your eyes and into which problems did you run so that you asked here? – Neon

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.

Blakley

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.

Semmes

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

Hathor

Answer 4

BEST builds different notices in different mind. Please be more specific to ask questions. One of the way to develop multi-tenant system is to put a common primary key in all the tables to build the relationship. The type and nature of the primary key is dependable project wise.

Lucindalucine

Answer 5

Why not to try different databases for each client to keep the data separated and give them unique entry point to your app. Ex: http://client1.project.net which with the routing system will map to client1 database.The bad side of this: more complex database changes, because all databases for each client needs to be upgraded.

Benham

this defeats the purpose of multi tenancy! – Shawana

hybrid way may be better; but I think different database is good for long term. single DB has become too big to manage and table lock occur too often. – Clareclarence

Answer 6

This is something I've been trying to figure out as well. Best I could come up with (not in an implementation yet, but in theory) is this: give each tenant their own database login and use views to keep them from seeing other people's data.

I ran across this link that describes a way of doing this for just plain old MySQL (not with Symfony/Doctrine).

Basically, you have your actual database tables, but each table has a column that stores the name of the database user that made the row. Views are then created that always filter by this column so whenever a user logs in to the database (via an admin tool or even connecting through Symfony/Doctrine), they are only ever returned records directly associated with them. This allows you to keep the data "separate", but still in one database. When pulling data (say for an entity in Symfony), you are pulling data from a filtered view vs. the actual database table.

Now, this solution isn't exactly Symfony/Doctrine friendly. I was able to get a very quick and rudimentary test of this running before; Doctrine was able to use the database views just fine (it could insert, edit, and delete entries from a view no problem). However, when doing things like creating/updating schema, it's not fun. Granted, Symfony/Doctrine seems pretty extensible, so I'm confident there is a way to have it automated, but this kind of setup isn't supported out-of-the-box. Doctrine would need to be told to update the tables, always append the column for holding the username to the entity tables it creates, update the views as well, etc. (You would also need a way to load up the proper database config in your Symfony app, mainly the different logins as the server and other stuff would be the same.) But, if this can be overcome, your app itself could run these multiple tenants completely "ignorant" of the fact that other people's data is sitting in the database.

Plasterboard

MySQL views are very, very limited. You won't make it this way. – Namara