How to use Doctrine's DBAL Layer
Edit this pageWarning: You are browsing the documentation for Symfony 2.0, which is no longer maintained.
Read the updated version of this page for Symfony 6.2 (the current stable version).
How to use Doctrine's DBAL Layer
Note
This article is about Doctrine DBAL's layer. Typically, you'll work with the higher level Doctrine ORM layer, which simply uses the DBAL behind the scenes to actually communicate with the database. To read more about the Doctrine ORM, see "Databases and Doctrine".
The Doctrine Database Abstraction Layer (DBAL) is an abstraction layer that sits on top of PDO and offers an intuitive and flexible API for communicating with the most popular relational databases. In other words, the DBAL library makes it easy to execute queries and perform other database actions.
Tip
Read the official Doctrine DBAL Documentation to learn all the details and capabilities of Doctrine's DBAL library.
To get started, configure the database connection parameters:
1 2 3 4 5 6 7 8
# app/config/config.yml
doctrine:
dbal:
driver: pdo_mysql
dbname: Symfony2
user: root
password: null
charset: UTF8
1 2 3 4 5 6 7 8 9 10
<!-- app/config/config.xml -->
<doctrine:config>
<doctrine:dbal
name="default"
dbname="Symfony2"
user="root"
password="null"
driver="pdo_mysql"
/>
</doctrine:config>
1 2 3 4 5 6 7 8 9
// app/config/config.php
$container->loadFromExtension('doctrine', array(
'dbal' => array(
'driver' => 'pdo_mysql',
'dbname' => 'Symfony2',
'user' => 'root',
'password' => null,
),
));
For full DBAL configuration options, see Doctrine Configuration Reference.
You can then access the Doctrine DBAL connection by accessing the
database_connection
service:
1 2 3 4 5 6 7 8 9 10
class UserController extends Controller
{
public function indexAction()
{
$conn = $this->get('database_connection');
$users = $conn->fetchAll('SELECT * FROM users');
// ...
}
}
Registering Custom Mapping Types
You can register custom mapping types through Symfony's configuration. They will be added to all configured connections. For more information on custom mapping types, read Doctrine's Custom Mapping Types section of their documentation.
1 2 3 4 5 6
# app/config/config.yml
doctrine:
dbal:
types:
custom_first: Acme\HelloBundle\Type\CustomFirst
custom_second: Acme\HelloBundle\Type\CustomSecond
1 2 3 4 5 6 7 8 9 10 11 12 13 14
<!-- app/config/config.xml -->
<container xmlns="http://symfony.com/schema/dic/services"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:doctrine="http://symfony.com/schema/dic/doctrine"
xsi:schemaLocation="http://symfony.com/schema/dic/services http://symfony.com/schema/dic/services/services-1.0.xsd
http://symfony.com/schema/dic/doctrine http://symfony.com/schema/dic/doctrine/doctrine-1.0.xsd">
<doctrine:config>
<doctrine:dbal>
<doctrine:type name="custom_first" class="Acme\HelloBundle\Type\CustomFirst" />
<doctrine:type name="custom_second" class="Acme\HelloBundle\Type\CustomSecond" />
</doctrine:dbal>
</doctrine:config>
</container>
1 2 3 4 5 6 7 8 9
// app/config/config.php
$container->loadFromExtension('doctrine', array(
'dbal' => array(
'types' => array(
'custom_first' => 'Acme\HelloBundle\Type\CustomFirst',
'custom_second' => 'Acme\HelloBundle\Type\CustomSecond',
),
),
));
Registering Custom Mapping Types in the SchemaTool
The SchemaTool is used to inspect the database to compare the schema. To achieve this task, it needs to know which mapping type needs to be used for each database types. Registering new ones can be done through the configuration.
Let's map the ENUM type (not supported by DBAL by default) to a the string
mapping type:
1 2 3 4 5 6 7 8
# app/config/config.yml
doctrine:
dbal:
connections:
default:
// Other connections parameters
mapping_types:
enum: string
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
<!-- app/config/config.xml -->
<container xmlns="http://symfony.com/schema/dic/services"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:doctrine="http://symfony.com/schema/dic/doctrine"
xsi:schemaLocation="http://symfony.com/schema/dic/services http://symfony.com/schema/dic/services/services-1.0.xsd
http://symfony.com/schema/dic/doctrine http://symfony.com/schema/dic/doctrine/doctrine-1.0.xsd">
<doctrine:config>
<doctrine:dbal>
<doctrine:dbal default-connection="default">
<doctrine:connection>
<doctrine:mapping-type name="enum">string</doctrine:mapping-type>
</doctrine:connection>
</doctrine:dbal>
</doctrine:config>
</container>
1 2 3 4 5 6 7 8 9 10 11 12
// app/config/config.php
$container->loadFromExtension('doctrine', array(
'dbal' => array(
'connections' => array(
'default' => array(
'mapping_types' => array(
'enum' => 'string',
),
),
),
),
));