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));
}
}
Gonzalo Ayuso - Web Architect 
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
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.
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.
Tkns. I understand and will test with PDO.
Absolutely a great solution. I was already on 50% implementing the same idea, bit I’m taking yours as a base.
Great tuts , I am thinking of implementing multiple database support for my php app. right now, it supports only MySql.
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
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.
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
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!
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).
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…
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
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 ?
My Bad. I solved it by injecting @service_container through service.
Hi
,I need one help I am trying to run my application in SAAS mode where each user will have different data base,and username , I wish that when user login to system username will get validated with master db once user is validated we will get database name,user,password form master db specific to logged in user and it will be used for him through out the application, Can u pls let me know your thoughts
Thanks
I’m curious what would be the best approach to include a custom database driver using this solution?
I have this driver I’d like to use because it automagically selects the schema I’ve specified in the yaml when connecting to pgsql.
exec(“SET SEARCH_PATH TO {$searchPath};”);
}
return $conn;
}
}
My code got truncated for some reason.
exec(“SET SEARCH_PATH TO {$searchPath};”);
}
return $conn;
}
}
A simple solution is to create a custom PDO class that extends PDO. Then instead pdo.class: PDO you will use pdo.class: MyPDO
Awesome solution! thanks for sharing. I was looking for exactly that.
cool!
I wonder how to prepare SQL statements to avoid SQL-injections while using your approach.
Using bind parameters with PDO and filtering the user input
How would I go about using PDO model classes using this approach (i.e. PDO::FETCH_CLASS for results)?
Gracias mi amigo. Funciona con symony 2.7.7
Necesito hacer una importación masiva , asi que voy a escapar los datso primero y luego un insert, con el $em->persist(), se cuelga.
Saludos
Gracias