Side by Side: Doctrine2 and Propel 2

by
Patrycja Dybka
Community Manager at Vertabelo

Posted: April 8, 2015

 
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 

When you start working with data in an application, you may need to use an object-relational mapper (ORM), a layer between the database and application.

For PHP the two most frequently used ORM’s are Doctrine and Propel. That’s why I decided to compare the main features of Doctrine in version 2.4.7 and Propel in version 2.0. At this point, I must strongly emphasize that this article is not to indicate which ORM is better and should be used. I wrote this to review both ORM’s, not to rate or promote any of them.

Besides looking through the main features, I have also investigated supported data types, databases, and model structure. Both ORM’s support all of the usual CRUD operations you would expect, from creating new records to updating old ones, inheritance, migrations, reverse engineering, etc. When talking about the current versions of ORM’s, the major difference relates to the ActiveRecord/DataMapper pattern implemented.

OK, let’s take a look at the basic features of Doctrine and Propel:


Doctrine2 Propel 2
Requirements:
PHP 5.4 or newer PHP 5.4 or newer
Installation:
via Composer
via Pear
via Composer
via Git
Using a Tarball or a Zipball
Model structure
The structure of data model can be described using:
  • annotations
  • XML
  • YML






To generate a database schema use the Doctrine command-line interface.
The structure of data model (tables, columns and relationships) is described in an XML file called the schema.
Propel generates ActiveRecord classes based on the schema definition of tables.

If you are using Vertabelo, you can convert your Vertabelo models into Propel’s schema using our script
Mapping types
Available Doctrine and Propel column types that represent native column types for your RDBMS
Bit types
  • boolean
  • boolean
Numeric types
  • smallint
  • integer
  • bigint
  • tinyint
  • smallint
  • integer
  • bigint
Decimal types
  • double
  • float
  • decimal
  • double
  • float
  • decimal
  • real
  • numeric
String types
  • string
  • text
  • guid
  • char
  • varchar
  • longvarchar
Binary string types
  • binary
  • blob
  • binary
  • varbinary
  • longvarbinary
  • blob
  • clob
Date and time types
  • date
  • datetime
  • datetimetz
  • time
  • date
  • time
  • timestamp
Array types
  • array
  • simple_array
  • json_array
  • array
Object types
  • object
  • object
Supported databases
  • MySQL
  • PostgreSQL
  • SQLite
  • Oracle
  • MS SQL Server
  • MySQL
  • PostgreSQL
  • SQLite
  • Oracle
  • MS SQL Server


The basic difference between the current versions of Propel and Doctrine is their pattern approach. Propel uses Active Record. In order to update the database you have to call the save() method on the objects. Doctrine2 is a Data Mapper. This pattern separates your domain from the persistence layer (models don't have knowledge about the database). Instead of using the save() method, you use a different service known as an Entity Manager.

I will show basic CRUD operations using an example table author.

Doctrine2 Propel 2
Example table definition
<entity name="author">
 <id name="id" type="integer">
 <generator strategy="AUTO"/>
</id>
 <field name="firstName" type="string" nullable="false"/>
 <field name="lastName" type="string" nullable="false"/>
</entity>
 						
<table name="author">
 <column name="id" type="integer" required="true" 
primaryKey="true" autoIncrement="true"/>
 <column name="first_name" type="varchar" size="128" 
required="true"/>
 <column name="last_name" type="varchar" 
size="128" required="true"/>
</table>
							

Basic C.R.U.D Operations

Doctrine2 Propel 2
CREATE

Doctrine’s public interface is the Entity Manager. To obtain the entity manager:

$entityManager = EntityManager::create($conn, $config);

where $config is the instantiation of the ORM Configuration object and $conn contains database configuration parameters.

To add new data to the database:
  • create an object
  • invoke persist() method on an entity
  • invoke flush() method (it will issue all necessary SQL statements to synchronize your objects with the database in single transaction).

To add new data to the database:

  • create a Propel object
  • to define a value for each column use setXXX() method
  • call save() method

$author = new Author;
$author->setFirstName('Jane');
$author->setLastName('Austen');
$entityManager->persist($author);
$entityManager->flush();
$author = new Author();
$author->setFirstName('Jane');
$author->setLastName('Austen');
$author->save();
READ
$firstAuthor = $entityManager->find('Author', 1);
echo $firstAuthor->getFirstName();
To read a row from the database, use the generated Query object and generated findPK() method.
$firstAuthor = AuthorQuery::create()->findPK(1);
echo $firstAuthor->getFirstName();
UPDATE
$author = $entityManager->find('Author', 1);
$author->setLastName('Green');
$entityManager->flush();

$author = AuthorQuery::create()->findPK(1);
$author->setLastName('Green');
$author->save();
DELETE
$author = $entityManager->find('Author', 1);
$entityManager->remove($author);
$entityManager->flush();
$author = AuthorQuery::create()->findPK(1);
$author->delete();

Queries

Doctrine2 Propel 2
One of its key features is the option to write database queries in a proprietary object oriented SQL dialect called Doctrine Query Language (DQL), inspired by Hibernates HQL.

DQL is parsed by a top down recursive descent parser that constructs an AST (abstract syntax tree). The AST is used to generate the SQL to execute for your DBMS.

Example DQL query
$q = $em->createQuery(
 'select u, g from User u' .
 'left join u.Groups g ' .
 'ORDER BY u.name ASC, g.name ASC');
$users = $q->execute();


Doctrine Query Builder
  • It's used to build instances of query
  • Query building and query execution are separated → you don’t execute a QueryBuilder, you get the built Query instance from QueryBuilder and execute it.


Example DQL query using the QueryBuilder API:

$qb = $em->createQueryBuilder()
       ->select('u,g')
       ->from('User', 'u')
       ->leftJoin('u.Groups','g')
       ->orderBy('u.name','ASC')
       ->addOrderBy('g.name','ASC');

$q = $qb->getQuery();
$users = $q->execute();
Propel proposes an object-oriented API for writing database queries: Propel Query Reference

Querying the underlying database
use Propel\Runtime\Propel;
$con = Propel::getWriteConnection(\Map\
BookTableMap::DATABASE_NAME);
$sql = "SELECT * FROM book WHERE id NOT IN "
        ."(SELECT book_review.book_id FROM book_review"
        ." INNER JOIN author ON 
          (book_review.author_id=author.ID)"
        ." WHERE author.last_name = :name)";
$stmt = $con->prepare($sql);
$stmt->execute(array(':name' => 'Austen'));

Propel query:
Generated Query object has a method create(), which creates a new instance of the query.
$authors = AuthorQuery::create()
  ->filterByFirstName('Jane')
  ->find();
Doctrine2 Propel 2
Doctrine 2 relies on Data Mapper pattern (Doctrine 1 used to use Active Record pattern) Relies on the Active Record pattern
Schema file structure
Doctrine2 uses one *.dcm.xml schema file for each entity. Propel uses one schema definition file for each package.
Entity
<entity name="author">
  <id name="id" type="integer">
    <generator strategy="AUTO"/>
  </id>
  <field name="firstName" type="string" nullable="false"/>
  <field name="lastName" type="string" nullable="false"/>
  <field name="birthDate" type="string" nullable="true"/>
</entity>
					
Table
<table name="author">
  <column name="id" type="integer" size="255" 
required="true" autoIncrement="true" primaryKey="true"/>
  <column name="firstName" type="Varchar" required="true"/>
  <column name="lastName" type="Varchar" required="true"/>
  <column name="birthDate" type="Varchar"/>
</table>
Primary key definition
<id name="id" type="integer"
length="255">
  <generator strategy="AUTO"/>
</id>
<column name="id" type="integer" 
size="255" required="true" autoIncrement="true" primaryKey="true"/>
<unique name="IX_UQ_itemRecord_id">
  <unique-column name="id"/>
</unique>
Field
<field name="name" type="string" 
length="255" nullable="true"/>
Column
<column name="name" type="Varchar" 
size="255" required="true"/>
Index
Non-unique index:
<entity name="author">
  <field name="firstName"/>
  <field name="lastName"/>
  <field name="birthDate"/>
  <indexes>
    <index name="ix_name_last" columns="lastName"/>
  </indexes>
</entity>

Unique index:
<entity name="author">
  <field name="firstName"/>
  <field name="lastName"/>
  <field name="birthDate"/>
  <unique-constraints>
    <unique-constraint
       name="ix_first_name_last_name_date"
       columns="firstName,lastName,birthDate"/>
  </unique-constraints>
</entity>
Non-unique index:
<table name="author">
  <column name="firstName"/>
  <column name="lastName"/>
  <index name="ix_name_last">
    <index-column name="lastName"/>
  </index>
</table>


Unique index:
<table name="author">
  <column name="firstName"/>
  <column name="lastName"/>
  <column name="birthDate"/>
  <unique name="ix_first_name_last_name_date">
    <unique-column name="firstName"/>
    <unique-column name="lastName"/>
    <unique-column name="birthDate"/>
  </unique>
</table>


Behaviors

Both ORMs support behaviors. Behaviors can be used to modify your classes during the build process.
They can add attributes and methods to model classes, modify the structure of a database by adding columns or tables.

Doctrine2 Propel 2
Timestampable
The timestampable behavior updates date fields on create, update or property change.
<entity name=''entity_name''>
  <field name=''created'' type=''datetime''>
     <gedmo:timestampable on=''create''/>
</field>
</entity>
on option specifies when field should be updated. It can be create, update, change.
The timestampable behavior allows you to keep track of the date of creation and last update of your model objects. Instead of adding columns by hand, you have to declare ‘timestampable’ behavior in a <behavior> tag in schema.xml
<table name=''table_name''>
    ...
   <behavior name=''timestampable''/>
</table>

After rebuilding your model, you will have two new columns ‘created_at’ and ‘updated_at’ that store a timestamp automatically updated on save.
Aggregate column
The aggregate column behavior keeps a column updated using an aggregate function executed on a related table. In the schema.xml add aggregate_column behavior in the <behavior> tag .

Provided below aggregate column keeps the number of users.
<behavior name=''aggregate_column''> 
<parameter name=''name'' value=''nb_users''/>
  <parameter name=''foreign_table'' value=''user'' />
  <parameter name=''expression'' value=''COUNT(id)''/>
</behavior>

After rebuilding the model, a new column ‘nb_users’ will be added of type integer by default. This column stores the effect of executed aggregated function. Each time a record from the foreign table ‘user’ is added, modified or removed, the aggregate column is updated.
Archivable
The archivable behavior gives model objects the ability to be copied to an archive table. In the schema.xml add the archivable behavior in a <behavior> tag for a table.
Example:
<table name=''user''> 
  <column name=''id'' required=''true'' primaryKey=''true'' 
type=''integer'' /> 
  <column name=''name''  type=''varchar''/>
<behavior  name=''archivable''/> 
After rebuilding the table the model will have one additional table user_archive, with the same columns as the original user table. Table user_archive stores the archived users together with their archive date (the newest copy of each archived objects). To archive an object, call the archive() method on particular object.
AutoAddPk
The auto_add_pk behavior adds primary key columns to the tables. Add the schema.xml in the table definition tag <behavior name="auto_add_pk"/>.
The behavior adds an id column of type integer and autoincremented.
ReferenceIntegrity Delegate
The ReferenceIntegrity behavior automates the reference integrity for referenced documents. For example, if you set ReferenceIntegrity to ‘nullify’ it would then automatically remove referenced association when removing an entity. The delegate behavior allows you to relate tables. For example, in the schema.xml add the delegate behavior in the <behavior>tag for a table. In the <parameter> tag specify the delegated table. Table a and b are then related by a ‘created by’ behavior foreign primary key in the b table.
<table name=''a''> 
  <column name=''id'' required=''true'' primaryKey=''true''
autoIncrement=''true'' />
 <behavior name=''delegate''> 
   <parameter name=''to'' value=''b'' />
</behavior>

<table name=''b''>
  <column name=''name'' type=''varchar'' />
</table>
							
Translatable i18n
The translatable behavior enables you to keep data with the related appropriate locale.
$article = new Entity\Article;
$article->setTitle('my title in en');
$article->setContent('my content in en');
$em->persist($article);
$em->flush();

$article = $em->find('Entity\Article', 1 /*article id*/);
$article->setTitle('my title in de');
$article->setContent('my content in de');
$article->setTranslatableLocale('de_de'); // change locale
$em->persist($article);
$em->flush();
The i18n behavior is used in applications that support several languages. It allows you to keep several translations of the text data for single objects. In schema.xml for a table definition add the tag <behavior name="i18n"> with a <parameter> tag that specifies the columns that need internationalization.
<table name=''a''>
  <column name=''name'' type=''varchar'' />
   <behavior name=''i18n''>
      <parameter name=''i18n_columns'' value=''name''/>
   <behavior>
</table>

The internalizated columns have been moved to newly created table a_i18n. The new table contains a locale column and shares a many-to-one relationship with the a table.
Tree Nested set
The tree nested behavior implements the standard Nested-Set behavior on the Entity. The nested_set behavior allows a model to become a tree structure. In the schema.xml in the table definition add tag <behavior name="nested_set" />. After rebuilding the model, it can now be inserted into a tree structure.
Query cache
The query_cache behavior gives a speed boost to Propel queries by caching the transformation of a PHP Query object into reusable SQL code.
In the schema.xml in the table definition add the tag <behavior name="query_cache" />. After rebuilding the model, all the queries on this object can now be cached.
Slugabble

The sluggable behavior builds the slug (URL friendly version of post title) of predefined fields.

Example of usage

$a1 = new A();
$a1->setTitle('Hello, World!');
$this->em->persist($a);
$this->em->flush();
echo $a1->getSlug(); // 'hello-world'

The sluggable behavior allows a model to offer a human readable identifier. In the schema.xml in the table definition add the tag <behavior name=''sluggable'' />. A unique slug is automatically composed for every object that you save. It can be used to provide user-friendly URLs.

Example of usage

$a1 = new A();
$a1->setTitle('Hello, World!');
$a1->save();
echo $a1->getSlug(); // 'hello-world'
Sortable
The sortable behavior maintains a position field for ordering entities. The model has the ability to become an ordered list. The sortable behavior allows a model to become an ordered list. In the schema.xml in the table definition add the tag <behavior name="sortable" />. Propel gives the new object the first available rank in the list.
Validate
The validate behavior provides validating capabilities to ActiveRecord objects. Using this behavior, you can perform validation of an ActiveRecord and its related objects, checking if properties meet certain conditions. In the schema.xml in the table definition add the tag <behavior name="validate" />. Then add validation rules in the <parameter> tag.
Loggable Versionable
The loggable behavior tracks your record changes and is able to manage versions. The versionable behavior provides versioning capabilities to any ActiveRecord object. Using this behavior, you can:
  • Revert an object to previous versions easily
  • Track and browse history of the modifications of an object
  • Keep track of the modifications in related objects
Blameable
The blameable behavior automates the update of username or user reference fields on the entities or documents. It works similar to timestampable behavior. It simply inserts the current user id into the fields created_by and updated_by. That way every time a model gets created or updated, you can see who did it (or who blame for that).
Softdeleteable
The softdeleteable behavior allows you to “soft delete” objects, filtering them at SELECT time by marking them as with a timestamp, but not explicitly removing them from the database.
Uploadable
The uploadable behavior provides the tools to manage the persistence of files with Doctrine 2, including automatic handling of moving, renaming and removal of files and other features, for example:
  • to generate sha1 filename for the file
  • set a maximum size for the file in bytes
IpTraceable
The IpTraceable behavior automates the update of IP trace on entities or documents. It works similar to timestampable behavior but sets a string if particular column marked as ‘ipTracable’ is updated on create, update or change.

SYMFONY FRAMEWORK

Symfony supports both ORMs. When you generate a new project with Symfony, you can set it up for Propel or Doctrine.

$ php symfony generate:project foo --orm=Doctrine

$ php symfony generate:project foo --orm=Propel
Which PHP ORM do you use and why? Feel free to express your opinion!
 
 
 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags