Posted

May 27, 2014

Tags

xPDO, ORM, SQL

The Good, The Bad and The ORM

A view to on going `war` for using ORM or not to use ORM

I am an ORM user...

and to know how to use it. The ORM I use is known as xPDO but more generally know as MODX CMF which is written on top of xPDO, a PHP based ORM and it can do all what ORM's can do;

  • Fetch lazy objects and collections
  • Fetch aggregate and composite objects (1+N queries)
  • Create automatic joins between tables
  • Load collection of parent child objects with one command
  • Use pure SQL to load objects

So it can do everything you need except UNION. Though that can be done by writing SQL query and load it into objects. xPDO tries to be database agnostic so you can write class based application code to load rows from SQL Server, SQLite, MySQL and with unofficial patches you can query PostgreSQL and Oracle. Also all objects have DBMS specific object classes which can have DBMS specific functions and queries.

The Problem...

is the ORM. From SQL perspective all ORM's are harmful to query performance, this point has been lifted up in numerous blog posts and articles around the web. And have to confess that they all are true. Here is what I usually see within MODX plugins

  • They load full row's when it is unnecessary
  • Do 1+N queries in foreach
  • Rely on DMBS ordering

Oh if it would be just that. Many extra's use unindexed queries with LIKE clause "%term%" or store data as JSON or comma separated list which then is queried with already mentioned LIKE clause. So we have a problem which actually is not ORM based issue itself, ORM is just the easiest target to point your finger at.

So we have a problem...

which is indirectly related to ORM in use. Yes, indirectly. The problem is actually community wide issue, regardless of what ORM is in use. So what is the problem then? The problem actually is lot more complex than we could think at first. The problem starts from education to PHP applications

. I started my career by reading "Learn PHP5 and MySQL" and writing the examples that the book had. The book itself was terrific book and got my first job with the skills I learnt from it. But what it lacked was SQL theory. It gave instructions how to create table, control users and alter table. But had no single reference to indexes nor anti-patterns, except PRIMARY key with AUTO_INCREMENT. That builds into a problem with huge proportions and is the problem around net even today. Might sound harsh, but would guestimate that 90% of web developers go into category of "I know SQL", the number can be bigger really.

So we got tool to blame on...

which is reasonable. All articles I've read against ORM's are valid. ORM is no by means the perfect solution for SQL. Or more of there is no ORM that actually would be something SQL writers would love. But blaming ORM for the bottlenecks and errors is silly. Those readers who have played pool know that the problem is not the table, cue nor the balls in use. The problem is something you can see thru mirror. The problem can show in many forms, one that personally know well is "I know more than ORM can do". Yes, I can beat my ORM so that I don't use it all the time. But I try to use it and here's why.

Cleaner code with ORM

Using ORM made my code cleaner and easier to read for developers who continue my work. An example of typical application without ORM.

$sql = "SELECT a.something, a.nothing, b.another FROM a_table WHERE";
if ($_POST['something'] == 1) {
    $sql .= " something = 1";
}
if ($_POST['another'] == 'Yes' && $_POST['something'] == 1) {
    $sql .= " AND another = 'yes'";
}

Same with xPDO / ORM

$criteria = new Query();
if ($_POST['something'] == 1) {
    $criteria->where(array(
        'something' => 1
    ));
}
if ($_POST['another'] == 'Yes') {
    $criteria->where(array(
        'another' => 'Yes'
    ));
}

Ok, maybe not the best examples, but hopefully you get the point what I am thriving for. With object based ORM you can enter any clause anywhere you want before querying. How that is beneficial is quite easy to point out. Developer has freedom to structure the query in logical order without having glue bits like " AND term = 'meep'".

Ok, ORM's are still bad...

or then not. The whole topic is theoretical all the way. Another likes to write concatenated strings where next likes to write function based code. You choose which form is worse than another as I cannot do the choice. Would be too harsh not to understand piece of both worlds and use it as advantage

The perfect ORM...

does not exist to my knowledge. What would be the perfect ORM then? From my point of view I would like to see next functionalities

  • DBMS agnostic queries
  • DBMS agnostic queries Throw error for "SELECT *" queries. Eg. Lazy loading would be disallowed
  • Disallow 1+N queries * There are cases when 1+N query is required but those are rare as you have Joins
  • ORM should have partial objects
  • Allow group actions like "DELETE FROM table WHERE key=value" instead of per object removal
  • Easy sharding by groups/objects

These are just few what ideal ORM could have, and it builds to impossibility really. Everything you can build with ORM could be cheated at code level. ORM just cannot ensure the cleanliness of SQL code.

The End

Unfortunately after viewing the ORM debate around. I cannot say if you should use ORM or not. Why? Cause the whole topic relies on your skill level and how you can learn new ways or bring old ways to new world. I can use ORM as my advantage to make my queries more flexible from programming side. Can you?

Share your Thoughts

0 Responses

Your email address will not be published

Please enter your name.
Please enter valid email address.
Please enter your website address.
Please enter your message.
TwitterX Error: Could not load tweets as Twitter responded with the error: 'Invalid or expired token.'.
-->

Contact me with email or add my account to skype

the_dunnock@outlook.com

the_dunnock@outlook.com