Tutorial: Doctrine DBAL (PHP Database Abstraction Layer)

This tutorial will show you how to connect a database using Doctrine DBAL.  The examples are for Erdiko, but could be applied to any PHP framework that uses composer.


If you have not installed Erdiko, please go to http://erdiko.org/getStarted.html#installation

To install Erdiko via Composer. simply run

composer create-project erdiko/erdiko my-project-name

After you have installed Erdiko (or your other favorite framework), it is very easy to install Doctrine.

via composer:

composer require doctrine/dbal 2.3.*

Alternatively, you can do it by hand by modifying the composer.json file in the root folder.  You just need to add this line:

{“require”: {“doctrine/dbal”: “2.3.4”}}

Then run ‘composer update’ to install Doctrine DBAL.

Basic usage

1. Getting a connection

We can get a connection through the DoctrineDBALDriverManager class.

$connectionParams = array(
    ‘dbname’ => ‘database_name’,
    ‘user’ => ‘user_name’,
    ‘password’ => ‘user_password’,
    ‘host’ => ‘localhost’,
    ‘driver’ => ‘pdo_mysql’
$conn = DoctrineDBALDriverManager::getConnection($connectionParams, $config);

Now, you are ready to retrieve and manipulate data.

2. Data Retrieval And Manipulation

After you have established a connection with database, it is easy to manipulation data.

In this tutorial, we create a table “Products" and create three fields for it.

The three fields are Name, Qty, and Price.

Inserting Data

$sql = “INSERT INTO Products (Name, Qty, Price) VALUES (‘Mango’, ’10’,5)”;
$stmt = $conn->query($sql);

Retrieving Data

$sql = “SELECT * FROM Products”;

$stmt = $conn->query($sql);

while ($row = $stmt->fetch()) {

    echo $row[‘Name’].’, Qty: ’.$row[‘Qty’].’, Price: ’.$row[‘Price’];


The output should be “Mango, Qty:10, Price:5”.

Advanced usage:

To perform fancy data manipulation or query, we will need to set up the class loader before establishing a connection.

Setting up class loader

We can open the Example controller(Example.php) under Erdiko/app/controllers/.

In the Example controller, we will need to add the following line before the class scope and it will allow the program to access the class ClassLoader.

use DoctrineCommonClassLoader;

Then, we can add the following code to a page.

$classLoader = new ClassLoader(‘Doctrine’);


$config = new DoctrineDBALConfiguration();


It is not a good practice to set connection parameters every time we get a connection with database.  A better way to do it would be storing all these parameters to a config file and creating a data model to read the config file.

For example, we can create a db.json config file under Erdiko/app/config/local/

In the db.json, we can set the connection parameters.



          “dbname": ‘database_name’,

          “user": ‘user_name’,

          “password": ‘user_password’,

          “host": ‘localhost’,

          “driver": ‘pdo_mysql’



Then, we can create a data model and it should looking something like below.

public function getDbConfig($dbConfig)


     $config = Erdiko::getConfig(“local/db”);

     $connectionParams = array(

         ‘dbname’ => $config[“data"][“dbname”],

         ‘user’ =>  $config[“data"][“user”],

         ‘password’ =>  $config[“data"][“password”],

         ‘host’ =>  $config[“data"][“host”],

         ‘driver’ =>  $config[“data"][“driver”],


     return $connectionParams;


The getDbConfig function will make the program easy to maintain and also reduce the amount of code.

Now, getting a connection will be only required the following line:

$conn = DoctrineDBALDriverManager::getConnection($connectionParams, getDbConfig(‘data’));composer create-project erdiko/erdiko project-name

Leave a Reply