Handling several PDO Database connections in Symfony2 through the Dependency Injection Container with PHP


I’m not a big fan of ORMs, especially in PHP world when all dies at the end of each request. Plain SQL is easy to understand and very powerful. Anyway in PHP we have Doctrine. Doctrine is a amazing project, probably (with permission of Symfony2) the most advanced PHP project, but I normally prefer to work with SQL instead of Doctrine.

Symfony framework is closely coupled to Doctrine and it’s very easy to use the ORM from our applications. But as I said before I prefer not to use it. By the other hand I have another problem. Due to my daily work I need to connect to different databases (not only one) in my applications. In Symfony2 we normally configure the default database in our parameters.yml file:

# parameters.yml
parameters:
    database_driver: pdo_pgsql
    database_host: localhost
    database_port: 5432
    database_name: symfony
    database_user: username
    database_password: password

Ok. If we want to use PDO objects with different databases, we can use something like that:

# parameters.yml
parameters:
  database.db1.dsn: sqlite::memory:
  database.db1.username: username
  database.db1.password: password

  database.db2.dsn: pgsql:host=127.0.0.1;port=5432;dbname=testdb
  database.db2.username: username
  database.db2.password: password

And now create the PDO objects within our code with new \PDO():

$dsn      = $this->container->getParameter('database.db1.dsn');
$username = $this->container->getParameter('database.db1.username');
$password = $this->container->getParameter('database.db1.password')

$pdo = new \PDO($dsn, $username, $password);

It works, but it’s awful. We store the database credentials in the service container but we aren’t using the service container properly. So we can do one small improvement. We will create a new configuration file called databases.yml and we will include this new file within the services.yml:

# services.yml
imports:
- { resource: databases.yml }

And create our databases.yml:

# databases.yml
parameters:
  db.class: Gonzalo123\AppBundle\Db\Db

  database.db1.dsn: sqlite::memory:
  database.db1.username: username
  database.db1.password: password

  database.db2.dsn: pgsql:host=127.0.0.1;port=5432;dbname=testdb
  database.db2.username: username
  database.db2.password: password

services:
  db1:
    class: %db.class%
    calls:
      - [setDsn, [%database.db1.dsn%]]
      - [setUsername, [%database.db1.username%]]
      - [setPassword, [%database.db1.password%]]
  db2:
    class: %db.class%
    calls:
      - [setDsn, [%database.db2.dsn%]]
      - [setUsername, [%database.db2.username%]]
      - [setPassword, [%database.db2.password%]]

As we can see we have created two new services in the dependency injection container called db1 (sqlite in memory) and db2 (one postgreSql database) that use the same class (in this case ‘Gonzalo123\AppBundle\Db\Db’). So we need to create our Db class:

<?php

namespace Gonzalo123\AppBundle\Db;

class Db
{
    private $dsn;
    private $username;
    private $password;

    public function setDsn($dsn)
    {
        $this->dsn = $dsn;
    }

    public function setPassword($password)
    {
        $this->password = $password;
    }

    public function setUsername($username)
    {
        $this->username = $username;
    }

    /** @return \PDO */
    public function getPDO()
    {
        $options = array(\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION);
        return new \PDO($this->dsn, $this->username, $this->password, $options);
    }
}

And that’s all. Now we can get a new PDO object from our service container with:

$this->container->get('db1')->getPDO();

Better, isn’t it? But it’s still ugly. We need one extra class (Gonzalo123\AppBundle\Db\Db) and this class creates a new instance of PDO object (with getPDO()). Do we really need this class? the answer is no. We can change our service container to:

# databases.yml
parameters:
  pdo.class: PDO
  pdo.attr_errmode: 3
  pdo.erromode_exception: 2
  pdo.options:
    %pdo.attr_errmode%: %pdo.erromode_exception%

  database.db1.dsn: sqlite::memory:
  database.db1.username: username
  database.db1.password: password

  database.db2.dsn: pgsql:host=127.0.0.1;port=5432;dbname=testdb
  database.db2.username: username
  database.db2.password: password

services:
  db1:
    class: %pdo.class%
    arguments:
      - %database.db1.dsn%
      - %database.db1.username%
      - %database.db1.password%
      - %pdo.options%
  db2:
    class: %pdo.class%
    arguments:
      - %database.db2.dsn%
      - %database.db2.username%
      - %database.db2.password%
      - %pdo.options%

Now we don’t need getPDO() and we can get the PDO object directly from service container with:

$this->container->get('db1');

And we can use something like this within our controllers (or maybe better in models):

<?php

namespace Gonzalo123\AppBundle\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;

class DefaultController extends Controller
{
    public function indexAction($name)
    {
        // this code should be out from controller, in a model object.
        // It is only an example
        $pdo = $this->container->get('db1');
        $pdo->exec("CREATE TABLE IF NOT EXISTS messages (id INTEGER PRIMARY KEY, title TEXT, message TEXT)");
        $pdo->exec("INSERT INTO messages(id, title, message) VALUES (1, 'title', 'message')");
        $data = $pdo->query("SELECT * FROM messages")->fetchAll();
        //

        return $this->render('AppBundle:Default:index.html.twig', array('usuario' => $data));
    }
}
About these ads

About Gonzalo Ayuso

Web Architect specialized in Open Source technologies. PHP, Python, JQuery, Dojo, PostgreSQL, CouchDB and node.js but always learning.

Posted on January 7, 2013, in PDO, php, Symfony2, Technology and tagged , , , , . Bookmark the permalink. 22 Comments.

  1. interesing, thanks for sharing

    wondering what is exactly your use case as regularly we use not these services directly but we use doctrine and it is fine

  2. Great post. Why you use this solution instead of doctrine? Do not lose any of the functionality and security of doctrine?.
    I’m thinking right now about this because my aplicación have intensive use of dynamic name tables: Note2010, Note2011…. NoteXXXX mapping from same entity Note and I can not solve all the details. See this post:

    http://forum.symfony-project.org/viewtopic.php?f=23&t=68761

    What do you think about this?.
    Thnks.

    • Gonzalo Ayuso

      I normally don’t use Doctrine because I feel very comfortable with SQL. Also The projects that I normally work in aren’t exclusively PHP projects (we share the database). With Doctrine simple things are simple (with raw sql too) and complex problems become nightmares.

  3. Absolutely a great solution. I was already on 50% implementing the same idea, bit I’m taking yours as a base.

  4. Great tuts , I am thinking of implementing multiple database support for my php app. right now, it supports only MySql.

  5. Out of interest when and where does the connection get closed? Does syfmony / doctrine do that or is it simply php?

    • If you don’t close the connection php (PDO) closes the connecion at the end of the script. Anyway you can close the connection when you want

  6. One other scenario I was playing out is where a controller has more than one service and each service talks to the database. That would mean more than one connection would be established for a single request. Do you see that as a concern and would it be better to make sure a single connection is used?

    • If you get the connection from the dependecy injection container you don’t need to take care about that. Symfony’s DIC will reuse the connecion (only new connection is done the first time you retrieve the connection)

      • That makes it handy. Out of interest does silex / pimple reuse its connections like symfony 2 DIC?

      • with pimple it depens on the way on you create the service. The default way don’t reuse (factory pattern), but you can use share() to reuse service (sigleton pattern). Silex’s DoctrineServiceProvider uses share(), so it reuses connection.

  7. Hi, great post! I have a question, when I log to mysql using the terminal, and try to open my symfony app with the PDO connection, I get this “ContextErrorException: Warning: PDO::__construct(): MySQL server has gone away in “, did that happened to you as well?

    Thanks for your reply

  8. I am curious about the “moving into model” part. And skiny controllers.
    the statement “$this->container->get(‘db1′);” would work only i controller, because it extends base controller class.
    If you create a custom model class, let say Gonzalo123\AppBundle\Model\MessagesModel
    class MessagesModel
    {
    }
    how would you get access to container? Would you inject whole container in there as a function parameter? Or is there any better solution, how to get access to the registeerd container service inside the custom class?
    Thanks!

    • Gonzalo Ayuso

      Inject the whole container is easy and it works, but your model will be strongly coupled to the container. The best approach is always build to decoupled components. If your model doesn’t know anything about your container, better. Maybe here you can inject the database connection to the model (obviously I assume that you aren’t using Doctrine here).

  9. Hi Gonzalo. Thanks for the tutorial… it’s exactly that i’m looking for. I don’t like ORM’s neither. But i get a problem: I followed the tutorial to use a PDO object (get a PDO directly from service container get me a lot of problems), but when i used in security configuration (login form), throw ‘invalid data source name’ message. Do you have any idea that what about this message?… i’m really stuck with this, and I would greatly appreciate any help.
    Sorry for my english…

    • Gonzalo Ayuso

      It’s looks like the PDO’s dsn ins’t correct. Try first to perform a PDO connection with a simple PHP script (without symfony). Another way to detect the problem is to use a remote debugger (with xdebug).

      • I tried to perform PDO alone and work… but I get a new problem, maybe you can help me: I try to get an instance of the db service, but instead from a Controller class using $this->container->get(), I try to call from class that is mapped like a service too… so, i can’t get the container and use get() method… do you have any idea for how to do that?… I readed in other pages that i must adding ‘arguments: [@service_container]‘ in db service definition, but that don’t work.
        thanks a lot again and regards

  10. Hi,
    Thank you for this wonderful post. This seems to be doing what I wanted to achieve for last 2 weeks.

    However, when I run this I get an error saying “Container” is undefined on “$this->container->get(‘db1′)->getPDO();” call.

    Can you please guide how can I solve this ?

  1. Pingback: Handling several DBAL Database connections in Symfony2 through the Dependency Injection Container with PHP « Gonzalo Ayuso | Web Architect

  2. Pingback: Scaling Silex applications « Gonzalo Ayuso | Web Architect

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 990 other followers

%d bloggers like this: