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

<?php
 
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/');
$classLoader->register();
 
/* 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 */
$usersTable->setPrimaryKey(array("id"));
 
 
/* 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));
$loginTable->addUniqueIndex(array("username"));
$loginTable->setPrimaryKey(array("id"));
 
/* Assign a foreign key constraint to the table */
$loginTable->addForeignKeyConstraint($usersTable, 
                        array("id"), 
                        array("id"), 
                        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.

CREATE TABLE users
  (
     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)
  )
ENGINE = INNODB 
 
CREATE TABLE login
  (
     id       INT NOT NULL,
     username VARCHAR(64) NOT NULL,
     PASSWORD VARCHAR(64) NOT NULL,
     UNIQUE INDEX login_username_uniq (username),
     PRIMARY KEY(id)
  )
ENGINE = INNODB 
 
ALTER TABLE login ADD CONSTRAINT 
login_id_fk FOREIGN KEY (id) REFERENCES users(
id) ON DELETE CASCADE

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.

This site is a digital habitat of Sameer Borate, a freelance web developer working in PHP, MySQL and WordPress. I also provide web scraping services, website design and development and integration of various Open Source API's. Contact me at metapix[at]gmail.com for any new project requirements and price quotes.

4 Responses

1

dhanesh mane

December 22nd, 2010 at 10:31 pm

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.

sameer

December 22nd, 2010 at 10:55 pm

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

3

Wil Moore III

December 25th, 2010 at 6:02 pm

>> 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.

http://groups.google.com/group/php-standards/web/psr-0-final-proposal

4

Maksim

November 6th, 2012 at 12:56 am

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.

Your thoughts

Sign up for fresh content in your email