Multiple, dynamic database connections in Symfony2

Question

I have a Symfony2 application that I want to make a multi-tenant with a single database tenant (some do not consider this a multi-tenant, but this is not entirely true).

The documentation describes how to do this. However, I want to be able to create tenants dynamically, and writing new database connection data (and entity managers) to the config.yml file directly seems messy. I would prefer to have a separate database that contains tenants and their connections, and then select the correct / em connection based on the identifier (for example, from the application subdomain - clientname.app.com).

Using this approach I should be able to accomplish this, but at the same time, it may violate the ability to specify a database connection and / or an object manager when running command-line commands to update database schemas, etc.

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

Answer 1

I installed my static database to process login and rental information and an additional database to store user data.

application / 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 the controllers instead

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

We have done:

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

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

Then, based on Symfony2, Dynamic DB Connection / Early override of the Doctrine Service, I configured the service to switch databases based on the query subdomain (for example, 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, we have an "additional" tenant database called XXX_template, with which system administrators connect when global changes occur. It is planned that this database will be copied into the tenant's database on the creation of the tenant.

Answer 2

Create a service that creates custom entity administrators based on user credentials.

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

Then your service will be something like this

 class EntityManagerService { function __construct($doctrine) { ... } function getEmForUser($user) { //look up Bob connection details in your connection db //and get them using the globally configured entity manager //create Entity Manager using bob config return $em. } 

This is the most reusable way to do something, and it follows the injection dependency pattern that Symfony2 uses.

You want to return instances of this class

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

Answer 3

I don't know if I satisfied the size of your question, but I connect to various 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; 

Answer 4

We had the same problem in our project.

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

A service is a multi-tone returning an entityManager by parameters.