Zend-db vs. doctrine


#1

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


#2

In our company, we do not use Doctrine because:

  • It is heavy, we did load performance tests between Zend DB and Doctrine in bigger platforms and the results for Doctrine are not so good - this is in our cases.
  • It is a little bit harder to learn or to get used to it by new devs who are joining projects

We are a company with PHP team of 10+ people and we outsource, so we need fast and easy to learn ORMs


#3

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:

  1. manually reason about foreign key constraints
  2. manually fetch dependencies of a previous fetch: no lazy-loading - not that lazy-loading is necessarily a good thing, but that makes everything much more complex
  3. reason about object state rather than object behavor: very few developers understand that objects should have behavior before fields, and 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:

  1. write business/domain logic with a high level abstraction such as doctrine/orm or prooph/event-sourcing, not with an SQL-based abstraction. SQL is a good language, but for data querying, not data manipulation.
  2. write read-intensive operations with thin SQL-based abstractions: I personally use 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.


#4

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


#5

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.


#6

I don’t think we’ll ever agree on this point, we’ve just argued on Twitter for 15 mins which is fun :smiley:.

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.


#7

Just so anyone who finds this thread can be complete, here is the twitter arguement:


#8

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


#9

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


#10

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 :slight_smile:


#11

thanks a lot guys, i learned a lot :slight_smile:


#12
$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 :wink:
Also I would argue about exposing the Zend\Db\Sql\Select as it might serve a good purpose depending on the case.


#13

There are a few things not being considered:

  1. the SQL snippet can be C&P’d into an analysis tool without the need to run anything
  2. the SQL can be introspected with modern tools that have language-aware code snippets, so you know if a column does not exist by just running static analysis over the codebase
  3. the query builder you return is totally opaque: there’s no way to know what can be joined, how much has been joined so far, and what can be done safely. I’ve seen multiple 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.

#14

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 :wink:


#15

Seen systems going down because of the same query builder abuse in ORM context: do not share query builders through public API.