Comparison of Propel, Doctrine and Agile Toolkit ORM

Sunday, April 24th, 2011|Brainstorming, Version 4|by Romans

In this article I am comparing many different aspects of the 3 different ORM systems. ORM means Object Relational Mapping and it is a way to present database data in object-oriented way. Agile Toolkit is a full PHP UI Framework but it comes with a built-in ORM (Object Relational Mapping). Propel and Doctrine are the two most popular ORM engines for PHP today. In this article I am comparing many different aspects of the 3 different ORM systems.

When to use ORM?

There are 2 purposes why ORM is used. Firstly it is to provide database abstraction. When you write code with ORM, it should be compatible with any database driver. Other purpose of ORM is to accumulate database logic and separate it from the UI.

General approach and Integration

Doctrine and Propel are capable of looking into an existing database and generate PHP code “cache” for existing database structure. Those “entries” classes can then be used for holding record data.

Agile Toolkit’s approach is different. It focuses on transparency and abstraction of the ORM and keeps amount of PHP code to the minimum. Loaded data is stored in associative array. While Propel and Doctrine come with large amount of core classes, Agile Toolkit uses only two classes Model_Table and DBlite_dsql.

Defining Entries/Entities

Propel

Propel loads its rules during build time from an XML file. This XML file describes each table fields as well as relations, keys and types. Once file is created, you can run propel-gen script to create structure of PHP files. Below is the sample definition for a table:

  <table name="book" phpName="Book">
    <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true"/>
    <column name="title" type="varchar" size="255" required="true" />
    <column name="isbn" type="varchar" size="24" required="true" phpName="ISBN"/>
    <column name="publisher_id" type="integer" required="true"/>
    <column name="author_id" type="integer" required="true"/>
    <foreign-key foreignTable="publisher" phpName="Publisher" refPhpName="Book">
      <reference local="publisher_id" foreign="id"/>
    </foreign-key>
    <foreign-key foreignTable="author">
      <reference local="author_id" foreign="id"/>
    </foreign-key>
</table>

http://www.propelorm.org/wiki/Documentation/1.5/BuildTime

From here on, you can also build your database schema and import it into SQL. Propel can build initial database and also supports Migrations

Doctrine

Doctrine has a lengthy introduction and supports many different scenarios. At least few ways to define models. Doctrine relies on a many different classes each handling an independent task: Search, Relation, Validatior.

Doctrine only support initial generation of the table. It does however support “upgrade” sequence which is defined as php array and Doctrine_Export. Doctrine also generates PHP Proxy Classes which can’t be edited directly.

Doctrine also introduces YML format as well as allows to specify queries in their parametrical way. Alternatively, however, doctrine also supports dynamic creation of the records:

[php]
class BlogPost extends Doctrine_Record
{
public function setTableDefinition()
{
$this->hasColumn(‘title’, ‘string’, 200);
$this->hasColumn(‘body’, ‘clob’);
}
}
[/php]

Although I’m not sure if it’s sufficient only to have this class.

Agile Toolkit

Agile Toolkit follows a minimalistic approach and tries to be very simple and developer-friendly. Developer need to use PHP code to describe the structure.

[php]
class Model_Book extends Model_Table {
public $entity_code=’book’;
public $table_alias=’b';
function init(){
parent::init();
$this->addField(‘name’);
$this->addField(‘isbn’)->mandatory(true);
$this->addField(‘publisher_id’)->refModel(‘Model_Publisher’);
$this->addField(‘author_id’)->refModel(‘Model_Author’);
}
}
[/php]

As you can see, Agile Toolkit assumes few things about your table. Firstly “id” field is defined through parent::init(); For those, however, who wish to name their ID fields differently they might need to re-define it by overriding few methods. “name” also gets a special treatment if defined – it is used by default when displaying records through UI.

Foreign keys defined using a class name. refModel also adds 2 extra fields ‘publisher’ and ‘author’ which are dynamically mapped to show publisher’s name and author’s name.

Agile Toolkit also contains a sql generation script. Agile Toolkit does not have ability to alter existing structure either. It however includes support for database upgrades by sequentially executing hand-written “sql” scripts for greater flexibility. Agile Toolkit do not focus on flexibility and support of every possible database vendor and focuses primarily on MySQL.

Conclusions on Structure Set-up.

Propel seems to focus a lot on XML but also tries to be simple and fast to setup. Doctrine seemed to be very complex to start using initially and required many steps. Comparing to others Agile Toolkit does not rely on XML and do not require file-write abilities or command-line access. Agile Toolkit came with few assumptions (and possibly limitations) for the database structure but provided the simplest and shortest syntax to defining entities.

Integration in your project

Propel

Propel relies on it’s own XML files for configuration. It is quite easy to include into any possible PHP script. The code is stand-alone:

[php]
require_once ‘propel/Propel.php’;
Propel::init("/path/to/bookstore/build/conf/bookstore-conf.php");
set_include_path("/path/to/bookstore/build/classes" . PATH_SEPARATOR . get_include_path());
[/php]

Doctrine

Doctrine relies on EntityManager and if not used with CodeIgniter, here would be initialization code

[php]
require ‘/path/to/libraries/Doctrine/Common/ClassLoader.php’;
$classLoader = new \Doctrine\Common\ClassLoader(‘Doctrine’, ‘/path/to/libraries’);
$classLoader->register(); // register on SPL autoload stack</pre>
$classloader = new \Doctrine\Common\ClassLoader(‘Symfony’, ‘/path/to/libraries/Doctrine’);
$classloader->register();
[/php]

Agile Toolkit

Agile Toolkit ORM is bundled with the whole framework. However it can be used without full initialization by using a lightweight API class:

[php]
include ‘atk4/loader.php’;
$api=new ApiCLI();
$api->addLocation(‘atk4-addons’,array(‘php’=>’mvc’));
[/php]

Naturally ORM is available in any application using Agile Toolkit. It’s impossible to use Agile Toolkit with different ORM. Propel defines include directory through php_include, while Agile Toolkit relies on PathFinder component and by default would look for them inside lib/Model/*.

Conclusion for integrating

All three ORM libraries can be used used with any project. Agile Toolkit will probably drag a bunch of files with it, but no unnecessary files are initialized by ApiCLI. Doctrine and Agile Toolkit have support for production versus development environments.

Interfacing with ORM

Propel and Agile Toolkit interface through the class itself. Doctrine interfaces with entries through a class ‘Doctrine’. Only Agile Toolkit works with objects, other ORMs initiate through static method. Below are some syntax comparisons based on http://redotheweb.com/2008/07/08/comparing-propel-doctrine-and-sfpropelfinder.

Retrieving an article by its primary key

[php]
// Propel
$book = BookPeer::retrieveByPk(123);
// Doctrine
$book = Doctrine::getTable(‘Book’)->find(123);
// Agile Toolkit
$book = $this->add(‘Model_Book’)->loadData(123);
[/php]

Get book title

[php]
// Propel
$title = $book->getTitle();
// Doctrine
$title = $book->Title;
// Agile Toolkit
$title = $book->get(‘title’);
[/php]

Get book by title

[php]
// Propel
$c = new Criteria();
$c->add(BookPeer::TITLE, ‘FooBar’);
$book = BookPeer::doSelectOne($c);
// Doctrine (1.x)
$book = Doctrine::getTable(‘Book’)->findOneByTitle(‘FooBar’);
// Doctrine (2.x)
$book = $entityManager->getRepository(‘Book’)
->findBy(array(‘title’ => ‘FooBar’));
// Agile Toolkit
$rows=$book->getBy(‘title’,'FooBar’);
$book->loadData($rows['id']);
[/php]

Important note to add here is that Agile Toolkit inherits conditions defined

Retrieving limited and ordered list of books

[php]
// Propel
$c = new Criteria();
$c->addDescendingOrderByColumn(BookPeer::TITLE);
$c->setLimit(5);
$articles = ArticlePeer::doSelect($c);
// Doctrine (1.x)
$articles = Doctrine_Query::create()
->from(‘Book a’)->orderby(‘a.title DESC’)
->limit(5)->  execute();
// Doctrine (2.x)
$repo = $entityManager->getRepository(‘Book’);
$articles = $repo->createQueryBuilder()
->add(‘orderBy’, ‘a.title DESC’)->setMaxResults(5)->getResult();

// Agile Toolkit
$data=$book -> setQueryFields(‘last5′)
-> dsql(‘last5′) -> field(‘*’) -> order(‘title desc’)
->limit(5)->do_getAllHash();

[/php]

Counting Books

[php]
// Propel
$nbBooks = BookQuery::create()
->count($con);
// Agile Toolkit
$nbBooks = $book->dsql()->field(‘count(*)’)->do_getOne();
[/php]

Conclusion

While syntax of Agile Toolkit focuses on simplicity and transparency. Agile Toolkit ORM focuses on practical cases and unusual cases are not so obvious, but still possible.Its’s evident that Agile Toolkit ORM does not allow more than 1 record to be loaded at the same time.

Propel allows multiple IDs to be specified when loading and both Propel and Doctrine return object when doSelect/execute() is performed.

All ORMs use method chaining (allowing to use return object to call).

Joins

While Propel and Doctrine-produced classes focus on a single-table interfaces, they support join syntax too, such as:

[php]
$books = BookQuery::create()
->useAuthorQuery(‘a’, ‘left join’)
->filterByName(‘Leo Tolstoi’)
->endUse()
->find();
[/php]

Agile Toolkit  tries to embed the join logic inside the model. To maintain flexibility Agile Toolkit allows to inherit existing class and add additional relations or criteria for a child class.

[php]
class Model_BookWithAuthor extends Model_Book {
function init(){
parent::init();
$this->addRelatedEntity(‘a’,'author’,'author_id’,'left’);
$this->addField(‘author_name’)->relEntity(‘a’,'name’);
}
}

// Elsewhere

$data=$this->add(‘Model_BookWithAuthor’)
->getBy(‘author_name’,'Leo Tolstoi’);
[/php]

The philosophy of ORMs start to differ here.

  • Agile Toolkit defines model as a “VIEW” in SQL. It can contain joins, dynamic fields or conditions and is based on one or several tables.
  • Models provided by Agile Toolkit ORM are easier to use by UI developer who is not familiar with database structure and will only have list of models and fields.
  • Agile Toolkit focuses on integration between UI and Model layer. When Grid is integrated with Filter and both use a model, they would still allow to build filter based on fields retrieved from joins.

Calculated Fields

Agile Toolkit has a support for calculated fields. Those fields are defined inside a model and they show up as a sub-select or expression inside the query. Other than this, they appear to be like a normal fields, it’s possible to sort by those fields, add conditions etc.

[php]
// inside model init()
$this->addField(‘namelength’)->calculated(true);
$this->addField(‘lastchapter’)->calculated(true);

// in model
function calculate_namelength(){
return ‘length(‘.$this->table_alias.’name)’;
}

function calculate_total_books(){
// calculates how many books this author have written
return $this->add(‘Model_Chapter’)->dsql()->field(‘count(*)’)
->where(‘c.book_id=b.id’)->select();
}

//anywhere
$namelength=$this->add(‘Book’)->loadData(123)->get(‘namelength’);

[/php]

Calculated field methods are only called once when query is formed.

Behaviours (ActAs)

Behavior is a way to add some common pattern to multiple models. Doctrine (1.x) supports behaviors though YML definition.

[php]
// models/TimestampListener.php

class TimestampListener extends Doctrine_Record_Listener
{
public function preInsert(Doctrine_Event $event)
{
$event->getInvoker()->created = date(‘Y-m-d’, time());
$event->getInvoker()->updated = date(‘Y-m-d’, time());
}

public function preUpdate(Doctrine_Event $event)
{
$event->getInvoker()->updated = date(‘Y-m-d’, time());
}
}
// models/TimestampBehavior.php

class TimestampTemplate extends Doctrine_Template
{
public function setTableDefinition()
{
$this->hasColumn(‘created’, ‘date’);
$this->hasColumn(‘updated’, ‘date’);

$this->addListener(new TimestampListener());
}
}
[/php]

Propel support behaviors but does not allow to add new ones dynamically. There seems to be no built-in support for behaviors in 2.0 although there are some extensions to add this support. Agile Toolkit supports behaviors but is much more flexible in how it can be used. For instance it’s suggested to use a simple Controller to add timestamps:

[php]
class Controller_Timestampeale extends AbstractController(){
function init(){
parent::init();
$this->owner->addField(‘created’)->datatype(‘date’)->defaultValue(date(‘Y-m-d’))->system(true);
$this->owner->addField(‘modified’)->datatype(‘date’);
$this->owner->setMasterField(‘modified’,date(‘Y-m-d’));
}
}

// place following into model’s init()
$this->add(‘Controller_Timestampeable’);
[/php]

Implementation of soft delete is done by simply defining the field. Actually created/modified timestamps will also be applied if you define “created_dts” “modified_dts” fields.

[php]
$this->addField(‘deleted’)->datatype(‘boolean’);
[/php]

For a more complex cases, there are methods to modify default behavior in Agile Toolkit:

  • beforeModify
  • afterModify
  • beforeInsert
  • afterInsert
  • beforeDelete
  • afterDelete
  • beforeUpdate
  • afterUpdate

Those functions can also be used for validation. Updates and inserts are automatically processed as transaction, so if you raise exception in afterInsert() the record will not be inserted.

Inheritance

Doctrine supports 3 types of inheritance.

  • Inheriting model from another model class
  • Inheriting and using different table name
  • Column aggregation

Propel supports 2 types of inheritance.

Agile Toolkit supports all of those techniques. Different table name can be set by changing property. Field definitions can be moved to a different functions and be overridden, or fields can also be removed with unset($this->fields['oldfield']); Agile Toolkit supports column aggregation through setMasterField().

Additionally Agile Toolkit allows to globally introduce functionality through the technique called class pushing. It allows a local installation to insert “parent” class for all the models in the system (also 3rd party ones) without touching their code. This allows to add behaviors, conditions and other things to bundled models too.

Validation

All the ORMs support validation. Propel/doctrine requires you to use one of the standard validators or define your own class for validation. Agile Toolkit allows to add validation on per-field basis using validate($callable). Callable receives row data as an argument and can make a simple decision by returning true or false.

Agile Toolkit focuses on custom validators allowing developer to use a built-in filter_var() or 3rd party validation class / methods.

Record Consistency

Propel and Doctrine focus on flexibility and also allow developer to do things which they are not supposed to. Agile Toolkit focuses more on security and model conditioning. Consider the following example:

[php]
class Model_Author extends Model_Table {
function init(){
parent::init();
$this->addField(‘Name’);
}
function getBooks(){
$this->add(‘Model_Book’)->setMasterField(‘author_id’,$this->get(‘id’));
}
}
// Anywhere in the code
$author=$this->add(‘Model_Author’)->loadData(123);
$books = $author->getBooks()->getRows();
// automatically shows only relevant books.
$author->getBooks()->set(‘name’,'New Book’)->update();
// adds new book for this author
[/php]

This being rather practical example shows how the relationship is enforced outside the model.

Integration with UI

The very strong benefit of Agile Toolkit ORM is it’s integration with UI elements. For example to show Model contents in a grid:

[php]
// Grid with book and author name
$this->add(‘MVCGrid’)->setModel(‘BookWithAuthor’,
array(‘name’,authorname’)); // defines which fields to show and order

// CRUD for editing authors
$this->add(‘CRUD’)->setModel(‘Author’);

// Loads editable form preloaded with data and saving it back to database
$a=$this->add(‘Model_Author’)->loadData(123);
$this->add(‘FormAndSave’)->setModel(a);

// Add a form with auto-complete field filled from model
// autocomplete lists only books by certain authors
$this->add(‘Form’)->addField(‘autocomplete’,'book_id’,'Select a book’)
->setModel($a->getBooks());
[/php]

Doctrine and Propel use hydration which basically lets them fetch rows sequently without storing all the result set in memory. Agile Toolkit does not support hydration. However views integrate dsql() object from a model into themselves and iterate it to show data, which essentially eliminates need for hydration.

Maturity

According to http://agiletoolkit.org/about/history, support for DynamicSQL was introduced in 2003 with beta version of PHP5 and have been a significant part of the system design in Agile Toolkit since. Dynamic Query module is quite powerful and flexible on its own. Remaining ORM part (Models) in Agile Toolkit were only available since 2010 but the final core changes have been done throughout 2011 making it relatively new. At the time of writing documentation for Agile Toolkit ORM wasn’t still published.

Propel was also started along with beta version of PHP5. Version 2.0 is now actively being developed. In this article I have compared with stable 1.x version.

Doctrine has been released in 2008 making those projects more mature. Due to age of DBlite/dsql.php Database abstraction layer in Agile Toolkit (DBlite) comes way back from the 1999 and is supposed to be replaced by PDO in upcoming 4.1 release of the framework. I tried to look both into 1.x and 2.x branches but if I have missed out on some important syntax or features, please let me know and I will update the article.

Conclusion

Doctrine and Propel

Both ORMs are quite similar in their ways and I have grouped them under the same category. They come with unimaginable rich feature set implementing any possible SQL operation. This allows to eliminate any resemblance of SQL code altogether.

Propel rely on code generation and will need from developer to understand how command-line and permissions work.

I would recommend those ORMs to developers with 5+ experience who are writing libraries or applications relying heavily on SQL interactions with multiple database support. The knowledge of relational databases is still required. For developers to use those ORM in the applications they have to be very well familiar with the data layer.

Doctrine and Propel slow development down. If you need to develop application quickly, it’s better not to use them.

Doctrine 2.0 and Propel 2.0

Newly released Doctrine2 and upcoming Propel2 seems to introduce more dynamics. The included QueryBuilder is an analogue for DSQL in Agile Toolkit and is step to create a more lightweight and flexible code. Unlike DSQL in Agile Toolkit, QueryBuilder goes down to replace all the logical operations of a query, probably making requests heavier.

DQL support is still there, which, if I understand correctly, parses the query inside PHP, caches it, then rebuilds query based on the vendor.

Agile Toolkit ORM

A really strong benefit of Agile Toolkit ORM is integration with web User Interfaces. It focuses on transparency and tries to stay out of the way. When used with Agile Toolkit, then Views would use ORM to access database, while developer can enjoy integration between them.

Ability to create Models with multiple table relations and calculated columns enables one developer in your team to work on Models and other team members using them without knowing database structure or capabilities. Separation of business logic is main focus of Agile Toolkit and it implemented much better.

Agile Toolkit is also simpler to use. It does not attempt to rely on any possible technique such as XML, YAML and PHP Namespaces. It tries to keep things as simple as possible and would be much easier to learn.

I would recommend to use Agile Toolkit ORM to developers with small or almost no experience at all. Agile Toolkit ORM documentation is not finished yet, so you need to stay in touch by following @atk4 on twitter or subscribing to newsletter. Agile Toolkit approach of business logic separation is well suitable for larger teams end development of enterprise / commercial application with narrow deployment base and more complicated logic.

Agile Toolkit ORM speed development up. Basic setup requires virtually nothing. For many actions it’s possible to fallback to DynamicSQL. It can even be used without defining any structure at all. I can’t think of any reason why not to use Agile Toolkit ORM if you have already decided to use MySQL.

Corrections

I was trying to keep this guide objective and also look at all aspects of the ORM. If I did a mistake, please correct me. Thank you for reading and feel free to share this article if you find it useful.

4 Comments

Lukas
Posted April 24, 20119:14 am

You are aware that Doctrine 2.0.x is released as stable and that Propel 2.x is under heavy development? You are comparing with the 1.x versions.

Romans
Posted April 25, 20113:39 pm

Thanks for pointing this out, Lukas. I have updated the article.

Francois
Posted April 27, 201111:33 am

There are numerous inaccuracies on the Propel syntax. Can’t list them all, but at least:

– Propel supports modifying an existing database. Propel 1.6 has migrations
– The redotheweb.com article (which I wrote) is outdated. Both Propel 1.x and Doctrine 1.x have evolved a lot since then.
– Peer and Criteria syntax is no longer the primary way to make queries in Propel. The ActiveQuery API, introduced with Propel 1.5, is the way to go.
– Propel doesn’t support “some of the join syntax”. It supports the join syntax.
– Propel and Doctrine support calculated fields as well
– Dynamic behaviors are a bad idea as long as you modify the model, which is the case of the example you give
– Both Propel and Doctrine provide hooks / lifecycle callbacks
– Propel supports two kind of inheritance, including one unique to Propel
– Doctrine doesn’t rely on code generation

Please check your facts before comparing your library to others.

Romans
Posted April 27, 20119:08 pm

Hi Franciois

Thanks for your feedback and corrections. I was trying to combine all the available resources for my comparison. I updated the article with your suggestions. I tried to find more about calculated columns in propel but haven’t found anything. Could you point me where I can find out more about them?

I will look into ActiveQuery. Is Peer/Criteria gone for good?