Comparing Propel, Doctrine and sfPropelFinder
When it comes to ORMs, it's all a matter of preference. Is it, really? This post compares side-by-side the code required to perform some simple operations with three OO database requesting API. The purpose is to demonstrate that productivity, and not only style, can vary a lot depending on the ORM you choose.
There are not many robust Object Relational Mapping layers in PHP5. I'll consider two of them:
Propel is an ORM that "allows you to access your database using a set of objects, providing a simple API for storing and retrieving data. Propel allows you, the web application developer, to work with databases in the same way you work with other classes and objects in PHP."
Doctrine is an ORM that "sits on top of a powerful PHP DBAL (database abstraction layer). One of its key features is the ability to optionally write database queries in an OO (object oriented) SQL-dialect called DQL inspired by Hibernates HQL. This provides developers with a powerful alternative to SQL that maintains a maximum of flexibility without requiring needless code duplication."
I will also consider an additional component to Propel named sfPropelFinder. It "provides an easy API for finding Propel objects - that is, easier than the Peer methods and the Criteria stuff". sfPropelFinder is a symfony plugin, but it can be used with Propel alone.
For the examples, I'll use the classic Article/Comment model.
Disclaimer: Being the author of sfPropelFinder, you may think that I chose examples that make it look better. To avoid this bias, I wrote a lot of examples, including some where this plugin does not perform very well. Still, if the sfPropelFinder comparison with the two other ORMs is not objective, the comparison between Propel and Doctrine is quite so.
Scope
This comparison will only focus on the API - I voluntarily leave the performance benchmarks to whoever wants to do it. But I think the gross performance comparison probably looks like:
Slowest sfPropelFinder + Propel 1.2
| Propel 1.2
| Doctrine 0.11
| sfPropelFinder + Propel 1.3
Fastest Propel 1.3
As for the features, it is hard to give an objective comparison without getting too much in the details. If you wonder if a particular ORM does something that another can't do, post a comment about it and I'll try to give you an honest answer.
Bear in mind that sfPropelFinder is very young, that Doctrine is quite young, and that Propel has a longer history and is the most stable and mature of all three.
Retrieving an article by its primary key
$article = ArticlePeer::retrieveByPk(123);
// Doctrine
$article = Doctrine::getTable('Article')->find(123);
// sfPropelFinder
$article = sfPropelFinder::from('Article')->findPk(123);
Retrieving the comments related to an article
$comments = $article->getComments();
// Doctrine
$comments = $article->Comments;
// sfPropelFinder
$comments = $article->getComments(); // no change - use Propel
Retrieving an article from its title
$c = new Criteria();
$c->add(ArticlePeer::TITLE, 'FooBar');
$article = ArticlePeer::doSelectOne($c);
// Doctrine
$article = Doctrine_Query::create()->
from('Article a')->
where('a.title = ?', array('FooBar'))->
fetchOne();
// Doctrine (faster)
$article = Doctrine::getTable('Article')->
findOneByTitle('FooBar');
// sfPropelFinder
$article = sfPropelFinder::from('Article')->
where('Title', 'FooBar')->
findOne();
// sfPropelFinder (faster)
$article = sfPropelFinder::from('Article')->
findOneByTitle('FooBar');
Retrieving the latest 5 articles
$c = new Criteria();
$c->addDescendingOrderByColumn(ArticlePeer::PUBLISHED_AT);
$c->setLimit(5);
$articles = ArticlePeer::doSelect($c);
// Doctrine
$articles = Doctrine_Query::create()->
from('Article a')->
orderby('a.published_at DESC')->
limit(5)->
execute();
// sfPropelFinder
$articles = sfPropelFinder::from('Article')->
orderBy('PublishedAt', 'desc')->
find(5);
Retrieving the last 5 comments related to an article
$c = new Criteria();
$c->addDescendingOrderByColumn(CommentPeer::PUBLISHED_AT);
$c->setLimit(5);
$comments = $article->getComments($c);
// Doctrine
$comments = Doctrine_Query::create()->
from('Comment c')->
where('c.article_id = ?', array($article->getId()))->
orderby('c.published_at DESC')->
limit(5)->
execute();
// sfPropelFinder
$comments = sfPropelFinder::from('Comment')->
relatedTo($article)->
orderBy('PublishedAt', 'desc')->
find(5);
Retrieving the last comment related to an article
$c = new Criteria();
$c->addDescendingOrderByColumn(CommentPeer::PUBLISHED_AT);
$c->add(CommentPeer::ARTICLE_ID, $article->getId());
$comment = CommentPeer::doSelectOne($c);
// Doctrine
$comments = Doctrine_Query::create()->
from('Comment c')->
where('c.article_id = ?', array($article->getId()))->
orderby('c.published_at DESC')->
fetchOne();
// sfPropelFinder
$comments = sfPropelFinder::from('Comment')->
relatedTo($article)->
findLast();
Retrieving articles based on a word appearing in the title or the summary
$c = new Criteria();
$cton1 = $c->getNewCriterion(ArticlePeer::TITLE, '%FooBar%', Criteria::LIKE);
$cton2 = $c->getNewCriterion(ArticlePeer::SUMMARY, '%FooBar%', Criteria::LIKE);
$cton1->addOr($cton2);
$c->add($cton1);
$articles = ArticlePeer::doSelect($c);
// Doctrine
$article = Doctrine_Query::create()->
from('Article a')->
where('a.title like ? OR a.summary like ?', array('%FooBar%', '%FooBar%'))->
execute();
// sfPropelFinder
$article = sfPropelFinder::from('Article')->
where('Title', 'like', '%FooBar%')->
_or('Summary', 'like', '%FooBar%')->
find();
Retrieving articles based on a complex AND/OR clause
// Propel
$c = new Criteria();
$cton1 = $c->getNewCriterion(ArticlePeer::TITLE, '%FooBar%', Criteria::LIKE);
$cton1 = $c->getNewCriterion(ArticlePeer::SUMMARY, '%FooBar%', Criteria::LIKE);
$cton1->addOr($cton2);
$c->add($cton1);
$c->add(ArticlePeer::PUBLISHED_AT, $begin, Criteria::GREATER_THAN);
$c->addAnd(ArticlePeer::PUBLISHED_AT, $end, Criteria::LESS_THAN);
$article = ArticlePeer::doSelect($c);
// Doctrine
$article = Doctrine_Query::create()->
from('Article a')->
where('(a.title like ? OR a.summary like ?) and (article.published_at> ? and article.published_at> ?)', array('%FooBar%', '%FooBar%', $begin, $end))->
execute();
// sfPropelFinder
$article = sfPropelFinder::from('Article')->
where('Title', 'like', '%FooBar%', 'cond1')->
where('Summary', 'like', '%FooBar%', 'cond2')->
combine(array('cond1', 'cond2'), 'or', 'cond3')->
where('PublishedAt', '>', $begin, 'cond4')->
where('PublishedAt', '<', $end, 'cond5')->
combine(array('cond4', 'cond5'), 'and', 'cond6')->
combine(array('cond3', 'cond6'), 'and')->
find();
Retrieving articles authored by someone
$c = new Criteria();
$c->addJoin(ArticlePeer::AUTHOR_ID, AuthorPeer::ID);
$c->add(AuthorPeer::NAME, 'John Doe');
$articles = ArticlePeer::doSelect($c);
// Doctrine
$article = Doctrine_Query::create()->
from('Article a')->
leftJoin('a.Author b')->
where('b.name = ?', array('John Doe'))->
execute();
// sfPropelFinder
$article = sfPropelFinder::from('Article')->
where('Author.Name', 'John Doe')-> // Guesses the join from the schema
find();
Retrieving articles authored by people of a certain group
$c = new Criteria();
$c->addJoin(ArticlePeer::AUTHOR_ID, AuthorPeer::ID);
$c->addJoin(AuthorPeer::GROUP_ID, GroupPeer::ID);
$c->add(GroupPeer::NAME, 'The Foos');
$articles = ArticlePeer::doSelect($c);
// Doctrine
$article = Doctrine_Query::create()->
from('Article a')->
leftJoin('a.Author b')->
leftJoin('b.Group c')->
where('c.name = ?', array('The Foos'))->
execute();
// sfPropelFinder
$article = sfPropelFinder::from('Article')->
join('Author')->
where('Group.Name', 'The Foos')-> // Guesses the Group join from the schema
find();
Retrieving all articles and hydrating their category object in the same query
$c = new Criteria();
$articles = ArticlePeer::doSelectJoinCategory($c);
// Doctrine
$article = Doctrine_Query::create()->
from('Article a')->
leftJoin('a.Category c')->
execute();
// sfPropelFinder
$article = sfPropelFinder::from('Article')->
with('Category')->
find();
Retrieving an article and its category by the article primary key
$c = new Criteria();
$c->add(ArticlePeer::ID, 123);
$c->setLimit(1);
$articles = ArticlePeer::doSelectJoinCategory($c);
$article = isset($articles[0]) ? $articles[0] : null;
// Doctrine
$article = Doctrine_Query::create()->
from('Article a')->
leftJoin('a.Category c')->
where('a.id = ?', array(123))->
fetchOne();
// sfPropelFinder
$article = sfPropelFinder::from('Article')->
with('Category')->
findPk(123);
Retrieving articles and hydrating their author object and the author group
// Impossible do to it simply - need for a custom hydration method (approx 40 LOC)
// Doctrine
$article = Doctrine_Query::create()->
from('Article a')->
leftJoin('a.Author b')->
leftJoin('b.Group c')->
where('a.id = ?', array(123))->
fetchOne();
// sfPropelFinder
$article = sfPropelFinder::from('Article')->
with('Category', 'Group')->
findPk(123);
Conclusion
That's a lot of queries. And I didn't mention many-to-many relations, addition of columns, behaviors, update/delete queries, count queries, or pagers. But overall, my conclusion after writing these examples is:
- Propel is the most verbose ORM of all three
- sfPropelFinder is the most magic of all three
- sfPropelFinder and Doctrine are the fastest to write, depending on the cases
- Some limits of Propel are very frustrating (limited doSelectJoinXXX(), Criterions, custom hydration)
- Propel and sfPropelFinder will never beat DQL for complex queries
Finally, if you are wondering which ORM to choose for your next symfony project, make sure that you put the productivity in the balance.
Possibly related posts (automatically generated):
Hello François,
Thank you for this comparison of syntax productivity and speed performance of the php orms, it's pretty interesting.
Isn't Propel 1.3 without sfPropelFinder the fastest on execution ?
As you seem to know pretty well Propel and Doctrine, I've a big question for you. For me, more than the query writing, the most painfull part of Propel is it doesn't have some "alter table" feature. So once the database is built, if you want to modify it you've got to rebuild it -> drop tables and recreate them.
On symfony, as long as your website isn't released, it's not a big deal thanks to the "propel-build-all-load" cli command. But when you want to deploy your modifications on production, you just can't drop all your data. So, you've got to the every column of every table, check the differences and write your own "alter" queries by hand. Gosh...
Is there a simpler way to do it with Propel ? I've read that it was one the features of Doctrine (I haven't tried it yet), does it work well ? Beside the Doctrine youth, and the fact you know the orm by heart, is there some other reasons why you prefer Propel to Doctrine ? How do you see the evolution of the dual orm solution on symfony.
Ok, it was a cheat, there's pretty more than one question. Sorry. But the orm is like the heart of a website, the subject interests me a lot.
Best regards and thank you for your work.
PS : There's a small error on your 7th example, the $cton2 definition is missing : $cton1 = ... $cton1 = ... <- here it should be $cton2 $cton1->addOr($cton2);
@Eric: Thanks for your comments, I updated the speed comparison and the Criterion problem.
As for the Alter Table, my experience is that no ORM can do it for you. The thing is that you never have to only add, rename or remove a column, you often have to migrate some data accordingly, and the ORM cannot guess that. So any schema modification goes along with a model migration, that is often written in SQL files and PHP scripts. There is a plugin that can automate that for you called sfPropelMigrationsLightPlugin, but you will still need to alter table by hand.
As for my personal choice, I see the number of symfony plugins using Propel as a big factor in favor of Propel. Its maturity and speed (in 1.3) are also big pros, and I wrote sfPropelFinder so that the Criteria stuff doesn't get in the way of the developers...
And as for dual ORMs, I think there is no future without an abstraction layer between both, or the community will just repeat its efforts twice, and that's a shame. sfPropelFinder could be the beginning of this layer, being easy to rewrite in Doctrine...
This is a handy article. I haven't looked into Propel for a while but I should definitely be keeping an eye on it, especially with sfPropelFinder in the picture. Thanks!
I wanted to mention that there is an alternate syntax that I prefer for writing DQL where the whole query can be expressed as a single string. This can make a lot of queries much more compact!
$article = Doctrine_Query::create()->query(' FROM Article a, a.Author b, b.Group c WHERE a.id = ? ', array(123))->getFirst();or alternately
$article = Doctrine_Connection::query(' FROM Article a, a.Author b, b.Group c WHERE a.id = ? ', array(123))->getFirst();A very interesting article, you'll be pleased to know that I praised sfPropelFinder at a talk on doctrine I did last week
One thing that Doctrine can do, and the current implementation of sfPropelFinder can't, is join onto the same table multiple times in the same query. This is one of the reasons that doctrine makes you supply alias names for your tables during the query.
@Eric
Doctrine has a feature for database migrations. It gives you a programmatic interface to the database abstraction layer so that you can write migration classes which represent one version of the database. The migration class has an up() and a down(). The up() will make some changes, and down() will negate those changes. Here is a little article on how you can use migrations to migrate an author column of blog posts to an author table while maintaining all the author names and associate the blog posts with the correct new author record:
http://www.phpdoctrine.org/documentation/cookbook/0_11?chapter=symfony-1.1-and-doctrine-migrations
François, thanks for this very interesting article ! i always feel a little frustrated with orms when things are getting more complex...
i'll give a chance to try sfPropelFinder for further projects !
This is an interesting post thanks. I can really see the value in this ORM layers for simple queries, but it seems quite evident that as soon as you do something even vaguely complex all you've really got is an SQL replacement/abstraction layer. Whilst this is valuable if you're switching databases (i.e. mysql to ms sql or somethign) it really doesn't help seperation of concerns and as such I feel the solutions are extremely limited.
Steve
@DangerMouse: Keep in mind that if the query language looks like SQL, the result is a set of hydrated objects, sometimes with related objects already hydrated. This is much different from raw SQL queries that return tabular data that you must manipulate by hand. The results of an ORM query engine take advantage of class methods that allow you to handle your business logic in the application, not in the database.
I would to see a many-to-many example with the three, perhaps with a article-to-tags table.
@Eric
Regarding Propel migrations, take a look at this helpful plugin : http://trac.symfony-project.org/wiki/sfPropelMigrationsLightPlugin
It allows making migration scripts without breaking all your database each time you deploy.
I think it'd be nice to see some actual performance benchmarks of these operations with native mysqli as a control.
Maybe even intercept queries made by the ORM's when performing JOINS in queries and seeing how they handle it...
We're using symfony 1.0 with Propel 1.3 and it is always a pain in the ass to write complex queries or when we want to hydrate from a class but 20% of the fields from the second class to the results. sfPropelFinder seems very promising but I had a hard time understanding the sfPropelFinder code for "Retrieving articles based on a complex AND/OR clause". Can you please elaborate more on this? Thanks
Joe
@Joe: you should take a look at the plugin documentation, it explains the process more clearly.
http://trac.symfony-project.org/wiki/sfPropelFinderPlugin
First of all, thanks for keeping us informed with all this kind of stuff, François!! I have some remarks on the code: in the "Retrieving the latest 5 articles" example // Propel $c = new Criteria(); $c->addDescendingOrderByColumn(ArticlePeer::PUBLISHED_AT); $c->setLimit(5); $articles = ArticlePeer::doSelectOne($c); in the last line shouldn't the doSelectOne($c) be replaced by doSelect($c) in order to get the 5 articles? And the same question goes for the following example "Retrieving articles based on a complex AND/OR clause" I know these observations aren't so important, since the objective of this post is to compare ORMs and no to debug the code
@Luciano: Thanks for the remarks, I updated the examples to correct the mistakes.
Hey, Good article!
I've recently switched from Symfony/Propel to Django/DjangoOrm Sql Alchemy Sql alchemy has been really amazing. Huge, huge difference with Propel, far more capable.
On the example for "Retrieving articles based on a word appearing in the title or the summary" you really can do it with plain Propel this way (less lines and clearer code):
// Propel $c = new Criteria(); $c->add(ArticlePeer::TITLE, '%FooBar%', Criteria::LIKE); $c->addOr(ArticlePeer::SUMMARY, '%FooBar%', Criteria::LIKE); $articles = ArticlePeer::doSelect($c);
Anyways, it's true that Propel can't help you to make complex queries easier, but it's also true that if you define correctly your data structure you probably wont (you shouldn't) need to make any JOIN. Most of the times it's faster (and clearer) to avoid JOINs, in my opinion.
And i would also say that, in my opinion, the whole thing about ORMs is not to write SQL or SQL like code and use objects instead... don't you think so?
On the other hand, Propel can actually execute SQL queries, but the problem comes from hidrating objects with the results... It can be quite a pain, in my experience. That's a point for Doctrine
It is possible to select only few columns in doctrine
I don't see this in documentation
[...] of all a link to Francois comparison article is in order. It features a lot of simpler DQL [...]
[...] comparaison intéressante sur l’utilisation de Doctrine et de Propel est accessible à cette adresse Rendez vous sur le site de Symfony pour l’utilisation de propel ou doctrine avec le [...]