Doctrine DBAL: PHP Database Abstraction Layer

Most people think ORM when they hear the name Doctrine, but what most people don’t know, or forget, is that Doctrine is built on top of a very powerful Database Abstraction Layer that has been under development for over a decade. It’s history can be traced back to 1999 in a library named Metabase which was forked to create PEAR MDB, then MDB2, Zend_DB and finally Doctrine1. In Doctrine2 the DBAL was completely decoupled from the ORM, components re-written for PHP 5.3 and made a standalone library.

What does it support?

  • Connection Abstraction
  • Platform Abstraction
  • Data Type Abstraction
  • SQL Query Builder
  • Transactions
  • Schema Manager
  • Schema Representation
  • Events
  • Prepared Statements
  • Sharding

Much more…

Creating a Connection

Creating connections is easy. It can be done by using the DriverManager:

<?php
$config = new \Doctrine\DBAL\Configuration();
//..
$connectionParams = array(
    'dbname' => 'mydb',
    'user' => 'user',
    'password' => 'secret',
    'host' => 'localhost',
    'driver' => 'pdo_mysql',
);
$conn = DriverManager::getConnection($connectionParams, $config);

The DriverManager returns an instance of Doctrine\DBAL\Connection which is a wrapper around the underlying driver connection (which is often a PDO instance).

By default we offer built-in support for many popular relational databases supported by PHP, such as:

  • pdo_mysql
  • pdo_sqlite
  • pdo_pgsql
  • pdo_oci
  • pdo_sqlsrv
  • oci8

If you need to do something custom, don’t worry everything is abstracted so you can write your own drivers to communicate with any relational database you want. For example, recently work has begun on integrating Akiban SQL Server with Doctrine.

How to work with your data

The Doctrine\DBAL\Connection object provides a convenient interface for retrieving and manipulating your data. You will find it is familiar and resembles PDO.

$sql = "SELECT * FROM articles";
$stmt = $conn->query($sql);

while ($row = $stmt->fetch()) {
    echo $row['headline'];
}

To send an update and return the affected rows you can do:

$count = $conn->executeUpdate('UPDATE user SET username = ? WHERE id = ?', array('jwage', 1));

It also provide a convenient insert() and update() method to make inserting and updating data easier:

$conn->insert('user', array('username' => 'jwage'));
// INSERT INTO user (username) VALUES (?) (jwage)

$conn->update('user', array('username' => 'jwage'), array('id' => 1));
// UPDATE user (username) VALUES (?) WHERE id = ? (jwage, 1)

Fluent Query Builder Interface

If you need a programatic way to build your SQL queries you can do so using the QueryBuilder. The QueryBuilder object has methods to add parts to a SQL statement. The API is roughly the same as that of the DQL Query Builder.

To create a new query builder you can do so from your connection:

$qb = $conn->createQueryBuilder();

Now you can start to build your query:

$qb
    ->select('u')
    ->from('users', 'u')
    ->where($qb->expr()->eq('u.id', 1));

You can use named parameters:

$qb = $conn->createQueryBuilder()
    ->select('u')
    ->from('users', 'u')
    ->where('u.id = :user_id')
    ->setParameter(':user_id', 1);

It can handle joins:

$qb = $conn->createQueryBuilder()
    ->select('u.id')
    ->addSelect('p.id')
    ->from('users', 'u')
    ->leftJoin('u', 'phonenumbers', 'u.id = p.user_id');

Updates and deletes are no problem:

$qb = $conn->createQueryBuilder()
    ->update('users', 'u')
    ->set('u.password', md5('password'))
    ->where('u.id = ?');

$qb = $conn->createQueryBuilder()
    ->delete('users', 'u')
    ->where('u.id = :user_id');
    ->setParameter(':user_id', 1);

If you want to inspect the SQL resulting from a QueryBuilder, that is no problem:

$qb = $em->createQueryBuilder()
    ->select('u')
    ->from('User', 'u')
echo $qb->getSQL(); // SELECT u FROM User u

The interface has much more and handles most everything you can do when writing SQL manually. It instantly makes your queries reusable, extensible and easier to manage.

Managing your Schema

One of my favorite features of the Doctrine 2.x series is the schema management feature. A SchemaManager instance helps you with the abstraction of the generation of SQL assets such as Tables, Sequences, Foreign Keys and Indexes.

To get a SchemaManager you can use the getSchemaManager() method on your connection:

$sm = $conn->getSchemaManager();

Now you can introspect your database with the API:

$databases = $sm->listDatabases();
$sequences = $sm->listSequences('dbname');

foreach ($sequences as $sequence) {
    echo $sequence->getName() . "\n";
}

List the columns in a table:

$columns = $sm->listTableColumns('user');
foreach ($columns as $column) {
    echo $column->getName() . ': ' . $column->getType() . "\n";
}

You can even issue DDL statements from the SchemaManager:

$table->addColumn('email_address', 'string');

Schema Representation

For a complete representation of the current database you can use the createSchema() method which returns an instance of Doctrine\DBAL\Schema\Schema, which you can use in conjunction with the SchemaTool or SchemaComparator.

$fromSchema = $sm->createSchema();

$toSchema = clone $fromSchema;
$toSchema->dropTable('user');
$sql = $fromSchema->getMigrateToSql($toSchema, $conn->getDatabasePlatform());

print_r($sql);

/*
array(
  0 => 'DROP TABLE user'
)
*/

The SchemaManager allows for some nice functionality to be built for the Doctrine ORM project for reverse engineering databases in to Doctrine mapping files. This makes it easy to get started using the ORM with legacy databases. It is also used in the Doctrine Migrations project to allow you to manage versions of your schema and easily deploy changes to production databases in a controlled and versioned fashion.

The next time you need to access a relational database in PHP, whether it be in a proprietary or open source application, consider Doctrine. Take advantage of our community and team of developers so you can focus on your core competency and really excel in it.