Creating SQL schemas with Doctrine DBAL

A tedious task during web development is that of database schema creation. A schema containing a few tables comprising of a small set of rows is quick, while that containing dozens of tables and large numbers of columns is a tedious process. I usually resort to a small php script with some regular expression tossed in to automatically create a schema from a text file definition. But that is a little buggy as I’ve to manually add the indexes and other small things. Now that Doctrine has released a DBAL library, this will provide a nice ability to automatically create sql schemas.

Doctrine DBAL

Doctrine DBAL (available as RC4 as of this writing) provides a wonderful abstraction layer to process all the needed database actions. DBAL is a lightweight layer built around a PDO-like API which offers features like database schema introspection, schema migration, database access and manipulation through an OO API. Note that DBAL API can be used independently of the Doctrine ORM API. So you can use the DBAL library even if you are unfamiliar with the ORM API.

Creating a MySQL schema from DBAL

In this post I’ve created a small example script using the DBAL API to generate a MySQL schema for a couple of tables. Note that the DBAL API uses php namespaces, so you will need to be familiar with them to understand the code, which also means that you must be at least using PHP 5.3.0

use Doctrine\Common\ClassLoader;
/* Doctrine uses a class loader to autoload the required classes */
require 'Doctrine/doctrine-dbal/Doctrine/Common/ClassLoader.php';
/* Lets first load the Doctrine DBAL lbrary */
$classLoader = new ClassLoader('Doctrine', './Doctrine/doctrine-dbal/');
/* Provide DBAL with some initial database infor */
$config = new \Doctrine\DBAL\Configuration();
$connectionParams = array(
        'dbname'    => 'test',
        'user'      => 'root',
        'password'  => '',
        'host'      => 'localhost',
        'driver'    => 'pdo_mysql',
/* Connect to the database */
$conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams);
/* We will now generate a table definition, 
   but first lets get a Schema object .
$schema = new \Doctrine\DBAL\Schema\Schema();
/* Now use the Schema object to create a 'users' table */
$usersTable = $schema->createTable("users");
/* Add some columns to the table */
$usersTable->addColumn("id", "integer", array("unsigned" => true));
$usersTable->addColumn("first_name", "string", array("length" => 64));
$usersTable->addColumn("last_name", "string", array("length" => 64));
$usersTable->addColumn("email", "string", array("length" => 256));
$usersTable->addColumn("website", "string", array("length" => 256));
/* Add a primary key */
/* Create another table called 'login' and add some columns */
$loginTable = $schema->createTable("login");
$loginTable->addColumn("id", "integer", array("unsigned" => true));
$loginTable->addColumn("username", "string", array("length" => 64));
$loginTable->addColumn("password", "string", array("length" => 64));
/* Assign a foreign key constraint to the table */
                        array("onDelete" => "CASCADE")
/* Set the Schema output platform, as we are using MySQL
   a Mysql schema will be generated. */
$platform = $conn->getDatabasePlatform();
/* The 'queries' variable will now hold the 
   an array of sql statements.
$queries = $schema->toSql($platform);

This will generate the following MySQL statements.

     id         INT NOT NULL,
     first_name VARCHAR(64) NOT NULL,
     last_name  VARCHAR(64) NOT NULL,
     email      VARCHAR(255) NOT NULL,
     website    VARCHAR(255) NOT NULL,
     PRIMARY KEY(id)
     id       INT NOT NULL,
     username VARCHAR(64) NOT NULL,
     UNIQUE INDEX login_username_uniq (username),
     PRIMARY KEY(id)
login_id_fk FOREIGN KEY (id) REFERENCES users(

Migrating schemas to another platform

Now that you have generated the script to create sql schemas, you can easily migrate the generated schemas to another platform using a couple of statements. For example we can migrate the above MySQL statements to Oracle using the following code.

$myPlatform = new \Doctrine\DBAL\Platforms\OraclePlatform();
$queries = $schema->toSql($myPlatform);

Basic column types

Below are the basic types that you can use in the ‘addColumn’ method when creating tables.

'bigint', 'boolean', 
'datetime', 'date', 'time', 
'decimal', 'integer', 'smallint',
'object', 'string', 'text'.

In the next few posts we will explore some more features of Doctrine DBAL.

4 Responses

  1. dhanesh mane says:

    thanks for all your efforts to write such a nice and informative article. Is there any option to use this for versions lesser than 5.3.

  2. sameer says:

    Namespaces where added from version 5.3, so it is not possible to use the class prior to that version.

  3. >> Doctrine uses a class loader to autoload the required classes

    True; however, if you already have a PSR-0 compatible autoloader, you can skip the bundled option.

  4. Maksim says:

    Great article. I only trying find out how set other DB options, primary key, unique, autoincement and etc. There I can find full list of array key? Assocc is really horrible thing in PHP you never have a clue that you suppose put in function.