Guys,
I’m trying to study/test ZF3. I tested on the zf3 documentation the blog sample and checked olegkrivtsov zf3 using doctrine. It seems both zend_db and doctrine sample are almost on same logic on how you prepare the entity, repository, command etc. My question is which is much better to use zend-db or doctrine? Any pros and cons? What mostly used in real life companies?
Thanks in advance
In our company, we do not use Doctrine because:
We are a company with PHP team of 10+ people and we outsource, so we need fast and easy to learn ORMs
TL;DR: don’t use zendframework/zend-db.
Disclaimer: I’m a maintainer of both doctrine/orm and zendframework/zend-db.
I use both at work, and have been using Zend_Db (ZF1) and Zend\Db (ZF2+) since ages, and switched to Doctrine ORM around 2011 (doctrine/orm:2.0.0-alpha4 at the time).
I also consult for at least 10 companies every year, so I see a lot of projects.
Considering all that, I’ve yet to see a Zend\Db-based application to grow into something manageable: it’s always an unholy mess that leads to more accidental complexity down the line.
I’ve seen some terrible Zend\Db applications, some terrible Doctrine ORM applications, some good Doctrine ORM applications, but never a good Zend\Db application.
For the performance: yes, Doctrine ORM has some performance implications (you can read about it here), but they are mostly handled by separating read/writes (generally through something like CQRS).
Zend\Db has some good abstractions for the Table-Data-Gateway pattern, and that part is mostly fine. When applied to “traditional” applications with foreign keys and such, using Zend\Db still leads to “table-oriented-software-development”, because developers have to:
Zend\Db is designed around saving/loading fields manually via hydrators. I wish I never introduced the concept of hydrators in this community…Then there is Zend\Db\Sql, which is just a nightmare to work with, in my opinion. Why? Mostly because it easily allows assembling arbitrary non-cross-platform-compliant SQL. I’ve seen Zend\Db\Sql\Literal being abused multiple times in multiple completely different projects. Why bother using it in first place, if you end up with non-standard SQL anyway?
Repeat with me: “I don’t need an SQL builder when the SQL is not dynamic”.
Sadly, a lot of developers smash everything into the Zend\Db\Sql component, making things extremely hard to read, maintain and refactor. This is not Zend\Db's fault, but just developers that don’t understand that “just write an SQL string” is perfectly OK.
To give a direction of where to go and what to do, I’ll tell you what works for me these days:
doctrine/orm or prooph/event-sourcing, not with an SQL-based abstraction. SQL is a good language, but for data querying, not data manipulation.doctrine/dbal, SQL strings for the actual SQL to be run, and DTOs that represent resultsets. Here’s an example:<?php
final class SQLBasedUsersWaitingForApproval implements UsersWaitingForApproval
{
/** @var Connection */
private $db;
public function __construct(Connection $db) {
$this->db = $db;
}
public function __invoke() : iterable {
return array_map(
[User::class, 'fromRecord'],
$this->db->query(
<<<'SQL'
SELECT
a, b, c, d
FROM
potato
WHERE
somethingComplicatedHere()
SQL
)
);
}
}
This is a decent compromise, and it handles domain logic well (where Doctrine ORM is involved), as well as intensive queries (where raw SQL is involved.
Hope that helps you further.
To give a contrary opinion to Macro’s Zend\Db has it’s place. It’s an excellent tool for rapid development when you know that you have certain constraints (e.g. short lived projects). It’s an excellent intermediate step if you’re trying to move away from concatenated strings run directly against pdo (or even worse mysql_). It’s an excellent choice if you are writing a web database and all of your operations are crud-like.
I’ll leave other’s to argue if you should be designing crud-like systems in 2018, but I want to make sure that this thread’s final message isn’t “never use Zend\Db, always use Doctrine”. Like every tool it’s horses for courses, and I’ve personally used and scaled Zend\Db elegantly and in a maintainable way.
Be wary of anyone who makes sweeping statements like “Don’t use this well maintained and fully mature tested library, use this one instead”.
My message remains
“never use
Zend\Db, always use Doctrine”.
Mostly because this bit:
“Don’t use this well maintained and fully mature tested library, use this one instead”
Is countered by an existing, stable, maintained and tested library that does things better than zendframework/zend-db, and it’s called doctrine/dbal.
I don’t think we’ll ever agree on this point, we’ve just argued on Twitter for 15 mins which is fun 
.
I just want to assure anyone reading this and happily using Zend\Db that they aren’t doing it wrong. Of course, investigate Doctrine and if it helps you work smarter and/or faster then start using it, but statements like “don’t use $x” can scaremonger people into stopping using something that’s working for them.
I can agree with the argument that in a brand new project you might as well use Doctrine over Zend\Db, but that doesn’t make Zend\Db a steaming pile of garbage that you should immediately refactor out of your application. Any new tool has a cost, in the learning curve and in the refactoring to it after all.
Just so anyone who finds this thread can be complete, here is the twitter arguement:
zendframework/zend-db is a god and quality component. I would say right tool for the job.
Never let me down, in projects of various sizes fit very nicely.
I am using it mostly as query builder (instead of a raw queries) + Hydration.
// just an example of query builder usage:
public function getCustomer($id, $address = false)
{
$select = $this->getSql()->select()->where(['id' => $id]);
if ($address) {
$select->join('address', 'customer_address.customer_id = customer.id');
}
return $this->selectWith($select);
}
For the second - Hydration, I don’t use any complex SQL result to hydrate.
You can check in the real life app how we implemented it https://github.com/phpsrbija/unfinished
for eg. check under application/packages/Page/
@ocramius feel free to give your opinion on the implementation, it would be really valuable.
at the end zend-db give us a freedom to control the code, but that required more work.
On the other side, I don’t like a lot ORMs.
Too much magic for simple things, they often interact too much in business logic.
And of course there are many performance issues.
If you have a complex project you probably want to make your own layers, and to control everything.
Just for the record - Doctrine is one of the best PHP package with quality code (not only among ORMs).
And if you decide to use any ORM be sure that you know what you are doing
I’ll gladly counter that. Take https://github.com/phpsrbija/unfinished/blob/7f691bb332539f5e53b738474947af4a42cf0356/application/packages/Category/src/Mapper/CategoryMapper.php#L47-L63 for example (first query I found - they seem to be all in mappers):
public function getCategoryPostsSelect($categoryId = null, $limit = null)
{
$select = $this->getSql()->select()
->columns(['category_name' => 'name', 'category_slug' => 'slug'])
->join('articles', 'articles.category_uuid = category.category_uuid',
['article_id', 'slug', 'admin_user_uuid', 'published_at']
)->join('article_posts', 'article_posts.article_uuid = articles.article_uuid', ['*'], 'right')
->join('admin_users', 'admin_users.admin_user_uuid = articles.admin_user_uuid',
['admin_user_id', 'first_name', 'last_name', 'face_img']
)->where(['articles.status' => 1])
->order(['published_at' => 'desc']);
if ($categoryId) {
$select->where(['category_id' => $categoryId]);
}
if ($limit) {
$select->limit($limit);
}
return $select;
}
The first problem here is that you expose a Zend\Db\Sql\Select, which then flies across the system and can be arbitrarily modified. Yes, this used to be very common, but also problematic due to complex queries being programmatically built and then executed, and this leads to extremely complex queries.
Second, this thing can be written as:
SELECT
c.category_name AS name,
c.category_slug AS slug
FROM
category c
JOIN
articles a
WHERE a.category_uuid = c.category_uuid
RIGHT JOIN
article_posts p
WHERE p.article_uuid = a.article_uuid
JOIN
admin_users au
WHERE au.admin_user_uuid = a.admin_user_uuid
WHERE
a.status = 1 -- yay magic numbers?
AND (c.categoryId = :categoryId OR 1 = :noCategoryId)
ORDER BY
published_at DESC
In DQL (doctrine’s language), this would probably not work due to the right join (which confuses the heck out of me BTW). I don’t know the domain, so I don’t know how to translate it, but it would probably look like this IF done through the ORM:
SELECT
c
FROM
MyNamespace\Posts p
LEFT JOIN
p.article a
LEFT JOIN
a.category c
LEFT JOIN
MyNamespace\AdminUser au WITH au.userId = a.user
WHERE
a.status = 1
AND (c.categoryId = :categoryId OR 1 = :noCategoryId)
I think the SQL version beats both examples, and makes things clearer to the reader. Also, I can copy-paste it in and figure things out without having to run the application and figure out (later) what the heck will hit the DB.
And then the entities end up like this: https://github.com/phpsrbija/unfinished/blob/7f691bb332539f5e53b738474947af4a42cf0356/application/packages/Page/src/Entity/Page.php#L7-L267
/**
* Class Page.
*/
class Page
{
private $page_uuid;
private $page_id;
private $title;
private $body;
private $description;
private $main_img;
private $has_layout;
private $is_homepage;
private $is_active;
private $created_at;
private $slug;
private $is_wysiwyg_editor;
/**
* @return mixed
*/
public function getIsWysiwygEditor()
{
return $this->is_wysiwyg_editor;
}
/**
* @param mixed $is_wysiwyg_editor
*/
public function setIsWysiwygEditor($is_wysiwyg_editor)
{
$this->is_wysiwyg_editor = $is_wysiwyg_editor;
}
That’s just setter/getter mess in order to please the hydrator. Can do manual hydration or hydration via reflection, but you end up mostly with 1:1 what is in the DB record, which is also not really useful, since your objects should have behavior (see https://github.com/symfony/symfony-docs/issues/8893 for a fun discussion about that).
OK thanks.
First SQL is for pagination object, I can agree that SQL object shouldn’t flies across the system - this is the only case where we return SLQ object for the pagination(s) and it’s not modified anywhere after it is created.
For the Entity - I can’t agree with you more 
thanks a lot guys, i learned a lot
$select = $this->getSql()->select();
$select
->columns([
'category_name' => 'name',
'category_slug' => 'slug',
])
->join(
'articles',
'articles.category_uuid = category.category_uuid',
[
'article_id',
'slug',
'admin_user_uuid',
'published_at',
]
)
->join(
'article_posts',
'article_posts.article_uuid = articles.article_uuid',
[Select::SQL_STAR],
Select::JOIN_RIGHT
)
->join(
'admin_users',
'admin_users.admin_user_uuid = articles.admin_user_uuid',
[
'admin_user_id',
'first_name',
'last_name',
'face_img',
]
)
->order(['published_at' => 'desc'])
->where
->equalTo('articles.status', 1);
if ($categoryId) {
$select->where->equalTo('category_id', $categoryId);
}
if ($limit) {
$select->limit($limit);
}
return $select;
Nothing complex for me there, but then again that is just me 
Also I would argue about exposing the Zend\Db\Sql\Select as it might serve a good purpose depending on the case.
There are a few things not being considered:
zend-db systems go down because someone added SQL_CALC_FOUND_ROWS programmatically to generic Zend\Db\Sql\Select instances, leading to disastrous consequences: it is not a safe API, and it should only be passed around within private scope, never returned on public endpoints.That’s in theory.
In practice the returned sql instance doesn’t “flies” across the system and
developers should know what to expect.
I am not sure if we should count systems that went down due to ORMs
Seen systems going down because of the same query builder abuse in ORM context: do not share query builders through public API.
We use ZendDb/* with PHP Slim framework on our mission-critical APIs.
When I built these APIs the following was the most important criteria for me: Performance!!!, Zero ActiveRecord, scalability and low learning curve for other devs.
I evaluated many ORMs, DBALs etc, which included Doctrine… and I chose ZendDb, its been more than 2 years now and I still have no doubts that it was the better choice, our APIs are insanely fast, consistent and easy to scale.
Our finance staff also generates very complex reports on a daily basis on millions of records, and performance is still not a problem (taking into account that our MySql database id fully normalized and optimized) - we accomplish this via dynamic query builders using Zend Db SQL, its an absolute breeze and even junior devs can understand the code… dynamically dropping in a new Join, Where, Limit etc is transparent and predictable…
Doctrine in my opinion is a monolithic, bloated, over-engineered resource hog.
You could have simply gone with DBAL, which does the exact same, in an even simpler way.
Looking at the amount of Doctrine DBAL bugs/issues on Github, elsewhere and even “known issues” on their own docs - no thanks.
Our Zend Db has been rock solid for 2 years and fits our requirements perfectly… seeing no compelling reason to switch.
That’s just because more issue come in than what is possible to resolve: this is common for any OSS project that grows, and the only thing I can do is closing them as stale, although that would just be a lie
I come on it, late, but I think it’s still the subject.
I tried Doctrine recently while I’m dig in Symfony. After some weeks, I see Doctrine like a shiny mess, uncomfortable as dumb.
That’s say, and because I’m not an opinionated developper (wink), let me argument :
1/ Lack of manipulation methods
The available methods to manipulate entities are the minimum vital methods.
For all the rest, you remain on the query bulder, which is very verbose.
For example, there is no method to get a list of entities by their identifiers.
You first have to know the identifier of your entity class, which is stored and accessible only in the ClassMetadata Object, which is accesible only IN the Repository, or from the EntityManager.
// Work
$repository->get(1);
// Does not work
$repository->get([1, 2]);
// Does not work, getClassMetadata is protected (but why?)
$repository->findBy([$repository->getClassMetadata()->getIdentifier() => [1, 2]]);
// So let's extend the RepositoryEntity class -> DIY
Even in the ZF1 DB there was quickest shortcut (ZF1 is from 2006).
2/ Array’s hell
Most of the basic methods never return a collection: you have to deal with arrays. It sucks.
$repository->findBy(..) // array
$repository->findAll(..) // array
Even you can use minimum-featured collections with undocumented gymnastic, there is no Entity specific collection anyway (nor in laminas-db it seems…). Modern
3/ Dangerous foundations
Everything about the ORM rely on a messy 250 lines public function createEntity(), that deal with multiple dimensionnals arrays.
The logic of Doctrine ORM anyway is to maintain an in-memory registry of all your loaded entities (using spl_object_hash/spl_object_id for object loaded in memory, or by getting their primary key(s), or by verify their persistence in the DB itself). So, once you try to deal with mass amount of data, you will have to flush Doctrine (with $em->clear()), to make him release your memory (and so forget its own logic too).
Most of the few methods you can call do not specify what type of argument they wait for, and some accept arguments that interface shall not.
Forget about strict typing.
4/ Miss of clear hydration process
You cannot easily hydrate/populate an entity, because your entities classes extend nothing (from Doctrine). All the hydrators take only a PDO statement as argument. This is very unconfortable.
$data = [ ['name' => 'Any'], ['name' => 'Other'] ];
foreach($data as $row) {
$entity = new Entity();
// What to do ?
Because your entities extend nothing, the populate/hydrator feature use Reflexion to set the values… It’s sad but, let’s agree, it’s working. I would have preferred better non-tricky solution as the populate() method used by lamninas/db.
So, painful while you need to import data. Who doesn’t need ?
5/ Not maleable
All rely on PDO anyway. If tomorow you want to implement a relationnal database which is not suported by PDO, then you can just fork doctrine/orm and rewrite all (or, yes, fork PDO). This is not the case in laminas/db, and in most of the dbal I know.
6/ Poor care about performances
The auto-loaded relations are interesting, be very not flexibles. There is no performances concern at all (just look Doctrine make one by one request everytime you load a reference). I did not find any method to force loading all references of a loaded entity set (array), all in one request. DQL and its join trick seems not a solution and it will slow down everything once your tables grow.
7/ Founded and locked on a tiny stone base.
At the top of everything, you got the Persistence abstraction, which is like a 2nd abstraction level to define the minimum methods you can have to deal with a database, without more details, enjoy, this is all you got to manipulate a table.
Also because it’s locked, it’s a totaly useless things that delay any new feature release, and slow you down when you look at the availales methods in your IDE. And it’s completely illogical to have it while your are so dependant on things like PDO.
I think I’ve listed the most shocking points I’ve seen. There is dozen others I have after my little experience, they should be implemented but can be easily fixed without having to dig deeply (like you cannot set the identifier value you want on an auto-incremented primary field)
Beside of this, there are some good points : the annotations, the command line tools are very helpfull (schema:update feel like the light at the middle of the dark forest), the entity reference proxy.
But the good point are very small comparing to the daily hell you have with it, where you, everytime, constantly, hesitate between writing 10 lines of code that you will surely copy-paste later, or abstract/extend everything because, anyway, you will need it.
I searched, in vain, for “extensions” of Doctrine that include all the missing features that every others DBAL have (yes, really).
Doctrine 3 has been announced 2 years ago. I look at the diff between the master (v3) branch and the 2.7, and I realize there is no improvements for making it more confortable. This update will more look like a move of the classes, with few improvement, and degradation too (say good bye to entity merge feature).
From my point of view (let’s opinionated now), Doctrine is a reliable, very old, software, without being a Swiss Army knife, bogged down in its legacy. It’s a trap, and because I do not find solutions to all of this, I’m looking to run out from it.
I will really, honestly, appreciate any comment that goes to find solutions, without the need to rewrite all 
Also, may I mistake in some points ?