Multiple, dynamic database connections in Symfony2

Question

I have a Symfony2 application that I want to make multi-tenant by the use of one database pr tenant (some don't consider this to be multi-tenancy, but that's not really the point).

The documentation describes how to accomplish this. However, I want to be able to create tenants dynamically, and writing the new database connection details (and entity managers) to the config.yml file directly seems messy. I would rather have a separate database which holds the tenants and their connections, and then select the proper connection/em based on an identifier (fetched, for instance, from a the subdomain of the app - clientname.app.com).

Using this approach I should be able to accomplish this, but will at the same time probably break the ability to specify the database connection and/or entity manager when running the command line commands for updating database schemas and the likes.

Provided that what I want to do make sense, is there a clever way to achieve this?

Hi Eirik, did you figure this out eventually? I'm having the same problem and was thinking about the same approach. Thanks in advance! — Peter, Feb 07 '16 at 23:23
@Pknife I ended up creating a script that dynamically created databases and updated the config.yml file. I did have some cache issues with this, though, and since this was a hobby project it has yet to make it into production. — Eirik A. Johansen, Feb 08 '16 at 07:24

score 3 · Answer 1 · edited May 23 '17 at 12:31

I set ours up with a static database to handle login and tenancy information and a secondary database to hold user data

app/config/config.yml:

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                driver:   "%database_driver%"
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8
            tenantdb:
                driver:   "%database_driver%"
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8
    orm:
        default_entity_manager: default
        entity_managers:
            default:
                 connection: default
                 mappings:
                    MyCoreBundle: ~
            tenantdb:
                 connection: tenantdb
                 mappings:
                     MyAppBundle: ~

And then, in controllers, instead of

         $something = $this->getDoctrine()
                           ->getManager()
                           ->getRepository('MyAppBundle:Thing')
                           ->findAll();

we did:

         $something = $this->getDoctrine()
                           ->getManager('tenantdb')
                           ->getRepository('MyAppBundle:Thing', 'tenantdb')
                           ->findAll();

which you can find details of here: http://symfony.com/doc/current/cookbook/doctrine/multiple_entity_managers.html

Then, based on Symfony2, Dynamic DB Connection/Early override of Doctrine Service I set up a service to switch databases based on the subdomain of the request (e.g. tenant1.example.com tenant2.example.com)

src/MyCoreBundle/Resources/config/services.yml:

services:
    my.database_switcher:
        class: MyCoreBundle\EventListener\DatabaseSwitcherEventListener
        arguments:  [@request, @doctrine.dbal.tenantdb_connection]
        scope:      request
        tags:
            - { name: kernel.event_listener, event: kernel.request, method: onKernelRequest }

MyCoreBundle\EventListener\DatabaseSwitcherEventListener.php

namespace MyCoreBundle\EventListener;

use Symfony\Component\HttpFoundation\Request;
use Doctrine\DBAL\Connection;

class DatabaseSwitcherEventListener {

    private $request;
    private $connection;

    public function __construct(Request $request, Connection $connection) {
        $this->request = $request;
        $this->connection = $connection;
    }

    public function onKernelRequest() {
        $connection = $this->connection;
        if (! $connection->isConnected()) {
            $params = $this->connection->getParams();
            $subdomain = __GET_SUBDOMAIN__();
            $oldname = preg_replace (
                "/_tenant_$subdomain|_template/",
                '',
                $params['dbname']
            );
            $params['dbname'] =  $oldname . ($subdomain ? "_tenant_$subdomain"
                                                        : "_template");
            $connection->__construct(
                $params,
                $connection->getDriver(),
                $connection->getConfiguration(),
                $connection->getEventManager()
            );
            $connection->connect();
        }
    }

}

For convenience sake, we have an "extra" tenant database called XXX_template which system admins connect to when making global changes. The plan is that this database is copied to tenant databases on tenant create.

A problem with this method of dynamically assigning the databasename means that commands such as: `php app/console doctrine:database:create --connection=tenantdb` no longer work as they read the name from the config file without reference to the event listener. — Andy Preston, Jun 12 '15 at 10:33

score 2 · Answer 2 · answered Apr 02 '13 at 14:27

Create a service that produces your custom entity managers based on the user's credential.

$this->get('my.db.service')->getEmForUser('bob');

Then your service would be something like this

class EntityManagerService
{

   function __construct($doctrine)
   { ... }

   function getEmForUser($user)
   {
      //look up Bob's connection details in your connection db
      //and get them using the globally configured entity manager

      //create Entity Manager using bob's config

      return $em.

    }

This is the most reusable way to do things and it fits with the Dependency Injection pattern Symfony2 uses.

You'll want to return instances of this class

https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/EntityManager.php

Thanks for your reply. However, this does not really address the root of my concern which is how the connection details can be dynamic while still maintaining command-line functionality. — Eirik A. Johansen, Apr 03 '13 at 05:56

score 0 · Answer 3 · answered May 09 '13 at 14:49

Don't know if I gresped the extent of your question, but I connect to different databases using this:

    $connectionFactory = $this->container->get('doctrine.dbal.connection_factory');
    $conn = $connectionFactory->createConnection(array(
        'driver' => 'pdo_mysql',
        'user' => 'mattias',
        'password' => 'nkjhnjknj',
        'host' => 'fs1.uuyh.se',
        'dbname' => 'csmedia',
    ));
    return $conn;

score 0 · Answer 4 · answered Aug 09 '13 at 07:14

0

We had the same problem on our project.

We created a new service in vendor bundle named : connectionManager.

The service is a multiton which return an entityManager by parameters.

answered Aug 09 '13 at 07:14

ludovicraymond

11
4

Multiple, dynamic database connections in Symfony2

4 Answers4