In Symfony 1.4 you typically define your database schema using XML or YAML. These files define the tables, columns, and relationships between them. Symfony then generates the PHP classes based on this schema so you can interact with the database. You can have in a single project, multiple models from different databases and keep querying them as you like whenever you need thanks to the approach that uses Symfony to connect to the database.
How to use 2 or more databases in a single project
Everything starts with the configuration file used to define database connections for your application. In Symfony 1.4, this file is called databases.yml and it is usually stored in the /config directory of your project.
1. Configure new single/multiple database connection(s)
This file contains configuration for different database connections, including the required parameters to connect to it such as the database name, the host, credentials, and other relevant settings such as the encoding. Each connection has a given name, and your application can then refer to that name to use that connection to retrieve or persist data.
An example of a single database connection looks like this:
all:
doctrine:
class: sfDoctrineDatabase
param:
dsn: mysql:host=localhost;dbname=mydatabase
username: myuser
password: mypasswordIn this file we:
- We have a single connection called
doctrine. - It's a MySQL connection.
- It uses the sfDoctrineDatabase driver.
- Connects to a database called mydatabase.
So if you need to have multiple databases, you can simply add an extra connection providing a new name for this connection and the details to connect to it, for example, let's create a new connection called doctrine2 that connects to mydatabase2:
all:
doctrine:
class: sfDoctrineDatabase
param:
dsn: mysql:host=localhost;dbname=mydatabase
username: myuser
password: mypassword
doctrine2:
class: sfDoctrineDatabase
param:
dsn: mysql:host=localhost;dbname=mydatabase2
username: myuser
password: mypasswordAnd that's it! You should be able to use both connections at the same time in your project.
2. Using multiple database connections in your project
Let's learn how to use both connections in the project. The first scenario is where you have already some existing models, for example, consider the Example model. The Example model is in the first database (the one with the name doctrine), so we need to bind the component to the doctrine connection:
<?php
// Connection Component Binding
// First argument: Doctrine class name
// Second argument: Connection name
Doctrine_Manager::getInstance()->bindComponent('Example', 'doctrine');
/**
* BaseExample
*
* This class has been auto-generated by the Doctrine ORM Framework
*
* @property integer $id
*
* @method integer getId() Returns the current record's "id" value
*/
abstract class BaseExample extends sfDoctrineRecord
{
public function setTableDefinition()
{
$this->setTableName('example');
/// ... ///
}
public function setUp()
{
parent::setUp();
/// ... ///
}
}Now for a model that uses another connection (SecondExample, a model that is in the second database only), you need to do the same thing, specifying the name of the connection when binding it:
<?php
// Connection Component Binding
// First argument: Doctrine class name
// Second argument: Connection name
Doctrine_Manager::getInstance()->bindComponent('SecondExample', 'doctrine2');
/**
* BaseSecondExample
*
* This class has been auto-generated by the Doctrine ORM Framework
*
* @property integer $id
*
* @method integer getId() Returns the current record's "id" value
*/
abstract class BaseSecondExample extends sfDoctrineRecord
{
public function setTableDefinition()
{
$this->setTableName('second_example');
/// ... ///
}
public function setUp()
{
parent::setUp();
/// ... ///
}
}By doing this, in your code, you will only need to query models without any extra configuration because you're setting which connection to use inside the model, for example:
<?php
// This will query the 'example' table, located in the database 'mydatabase'
// Using the first doctrine connection
$entityFromDb1 = ExampleTable::getInstance()->find(1);
// This will query the 'second_example' table, located in the database 'mydatabase2'
// Using the second doctrine connection
$entityFromDb2 = SecondExampleTable::getInstance()->find(1);And that should be enough in most of the projects when using models.
Important bug notice
In Symfony 1.4 there's a common bug that causes all of the Doctrine base model classes, that don't have the proper connection binding defined, to use the last configured database connection in the databases.yml file, which will cause in most of the cases an exception if the table doesn't exist in the database. Consider the following example of database configurations. We have 2 connections defined (first_database and second_database) in the databases.yml file:
all:
first_database:
class: sfDoctrineDatabase
param:
dsn: "mysql:host=host;dbname=db1"
phptype: mysql
hostspec: host
port: 3306
database: dbname
username: username
password: password
encoding: utf8
second_database:
class: sfDoctrineDatabase
param:
dsn: "mysql:host=host;dbname=db2"
phptype: mysql
hostspec: host
port: 3306
database: dbname
username: username
password: password
encoding: utf8The Example entity (named BaseExample.class.php) is in the first_database identifier, not in the second_database. If the class of the example entity, doesn't have the component binding to use the proper connection, like in the following example:
<?php
// Important: to cause this bug, we comment the binding line below
// Connection Component Binding
//Doctrine_Manager::getInstance()->bindComponent('Example', 'first_database');
/**
* BaseExample
*
*/
abstract class BaseExample extends sfDoctrineRecord
{
public function setTableDefinition()
{
// ... ///
}
}Then if you query the table, using code like the following one:
<?php
// This will always throw an exception, even if the component is bonded to the proper connection
//$exampleEntity = Doctrine_Core::getTable('Example')->find(1);
// Always use the TableClass to query
// In this example, the query will succeed only if the component is bonded to the proper connection
$exampleEntity = ExampleTable::getInstance()->find(1);You will find an exception because the query will be executed in the last connection defined in the databases.yml file:
To fix this problem, be sure to bind the component to the proper database connection (to the table where the entity is really stored):
<?php
// Important: define the component to the proper connection, in this case, the example table is in the first database
// So when you query the ExampleTable, the connection to the first database will be used
Doctrine_Manager::getInstance()->bindComponent('Example', 'first_database');
/**
* BaseExample
*
*/
abstract class BaseExample extends sfDoctrineRecord
{
public function setTableDefinition()
{
// ... ///
}
}Happy coding ❤️!
