Advantages of ORM

About 18 months ago I decided to develop my own software library. Although I know you have the likes of Laravel out there, I thought it would be a good learning exercise. On that front, I have learnt loads recently.

However, there always seems to be something new to implement. I am upgrading everything to use MVP and better routing. I am wondering though about ORM.

What are the advantages? Is it worth implementing on smaller projects like the ones I am involved in? It seems like overkill for the kind of work I do. I look at code samples and can’t help feeling that a MySQL query is just as easy to read.

Interesting nobody has answered yet.

Advantages of an ORM.

  1. you aren’t dependent on a single database, since it entails an abstraction layer. For instance, Doctrine supports 7 different data stores.

  2. You can think more in terms of objects and less in terms of database tables. There are tools that build the database schema for you, from the objects you’ve mapped.

  3. You can modularize common queries into repositories.

Here is Doctrine’s reason why one should use an ORM.

The benefit of Doctrine for the programmer is the ability to focus on the object-oriented business logic and worry about persistence only as a secondary problem. This doesn’t mean persistence is downplayed by Doctrine 2, however it is our belief that there are considerable benefits for object-oriented programming if persistence and entities are kept separated.

Unfortunately, they don’t go into detail about the benefits.

If you’d like to read more. http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/getting-started.html

Scott

1 Like

Thanks. In response to 1. I only use MySQL. Also when I look at code like ->select(‘name’)->from(‘table’)->where(‘age < 10’) in my mind I just read that as as query anyway (SELECT name FROM table WHERE age < 10). If you aren’t bothered about that do you think the other two reasons are strong enough to warrant using them?

How common is it to use ORM? Do most of the major frameworks and CMSs use them?

There are tools that build the database schema for you, from the objects you’ve mapped.

I was looking at Propel and it looks like you have to declare relations in a XML file. When you say it builds the schema, what do you mean?

Thanks.

One of the biggest advantages of an ORM is you generally have to write less code and not worry about escaping values when inserting/selecting. It will generally look like this:

$users = new Orm('users');

//Instead of a select
$user = $users->findById('123');

$user = new stdclass;
$user->name = 'Tom';
//Instead of UPDATE
$users->save($user);

Seem nice. I already have some CRUD functions but they don’t map to objects. I also have a class that traverses database tables. I guess there’s some overhead involved.

By the way, TomB, I recognise your photo. I’m sure I’ve read a blog post about PHP/MVC written by you in the past. :smile:

That’s something else I’m trying to implement, though MVP, not MVC.

P.S. Other than mapping to objects and searching/finding records, does ORM typically do anything else?

Not really. The op is stubborn enough to reinvent a wheel that has been built 1000 times over. Which leads me to believe they are just looking for ANY reasons NOT to use an ORM and/or other peoples code in general. Which is fine but I’m not feeding into that mentality. Do what you will and hopefully I never have to deal with it.

My thinking was the slight delay was more likely because it is a national long weekend summer holiday in the US.

Lots going on for those that have a RL

As for which syntax is easier, I’m more comfortable with typical query syntax, though I am familiar with ActiveRecord syntax a bit too. I guess a lot depends on what one is more used to working with.

As for “reinventing the wheel”, if it’s for a learning exercise, why not?

Not at all. If I decide to use ORM I’d use an existing library. I was genuinely asking about the advantages.

Thanks anyway.

I like using ORM because:

  1. It makes writing queries in a very readable and nice format and not having to worry about escaping values (this is what TomB said above)

  2. Database records are encapsulated within objects so I can extend their fields with additional virtual/dynamic fields. For example, if I have $product->price and $product->value I can add a $product->total_value field even though it doesn’t really exist in the database. I can also get the object to format the db values to something more flexible in PHP, for example, $user->date_registered can get me a timestamp or a DateTime object. And for saving data I can do the same, for example:

    $user->last_activity_date = time();
    without worrying about creating a specific db datetime storage format. Or do:

    $user->setNewPassword($password);

and have the object do the proper hashing.

However, I like ORM only for simple CRUD stuff. When I see examples of complicated criteria object building they make my head ache because pure SQL is much easier to read and create and much more powerful. I don’t feel like learning another ORM-query-like language that will mirror what SQL can do. Sure, if you want a total database independence then this is a good way to go but in most web applications I can’t imagine why I would want to change the database vendor because the specific database is often chosen to fulfil certain needs and not to be just a generic data store that can be swapped with another at will.

For example, now I’m building a web application that uses Postgresql and I’m making use of its special features like array datatypes, range datatypes, custom datatypes, indexes on functions and expressions, specialized comparison operators for ranges, geometric types and time intervals, spacial calculations provided by Postgres extensions, and so on and on - how can I reasonably expect any ORM library to get me support for all of these things and still provide me with database independence? Writing custom SQL to cover these cases defeats the purpose of ORM.

I’ve used Propel in the past and while it was nice for simple stuff it was making things complicated when non-standard queries were needed and it was also painfully slow. Also, changing database schema was a nightmare because an XML definition file had to be changed each time - yet another “language” to learn.

Therefore, I have mixed feelings regarding ORM. What I have settled for now is I’m using my own ORM that:

  • does only basic CRUD
  • easily accepts plain SQL (so I can retrieve any db records into the objects)
  • no criteria/query construction mechanisms (just write plain WHERE clause)
  • almost no configuration (only optional configuration of mapping table names to object names - in case auto-naming from table names is not sufficient)
  • all entity classes are auto-generated from the database so after changing structure it’s just one click to update the objects
  • fast

I believe good ORM is not possible (or is very complicated) because relations and objects are so different concepts that I prefer to accept this fact and not get away from SQL at all costs. I often find it important to structure SQL queries so that I can take advantage of unique database features. So it all comes down to requirements really.

2 Likes

Imagine you have a game entity with plenty of many-to-one relations as well as many-to-many relations with teams and officials. You want to hydrate a game object graph with everything nicely linked.

Here is an example of building such a query using Doctrine 2’s ORM.
Not saying it’s a good example but it is real and it works.

public function createQueryBuilderForGames()
{
$qb = $this->createQueryBuilder('game');

$qb->addSelect('game, game_location, game_age_group, game_region');
$qb->addSelect('project, project_official_positions');

$qb->addSelect('project_game_teams,project_game_team,  project_game_team_region');
$qb->addSelect('project_game_officials,project_game_official,project_game_official_region');

$qb->leftJoin('game.project', 'project');
$qb->leftJoin('project.offpositions','project_official_positions');

$qb->leftJoin('game.region',  'game_region');
$qb->leftJoin('game.location','game_location');
$qb->leftJoin('game.agegroup','game_age_group');

$qb->leftJoin('game.projectGameTeams',         'project_game_teams');
$qb->leftJoin('project_game_teams.projectTeam','project_game_team');
$qb->leftJoin('project_game_team.region',      'project_game_team_region');

$qb->leftJoin('game.projectGameOfficials',             'project_game_officials');
$qb->leftJoin('project_game_officials.projectOfficial','project_game_official');
$qb->leftJoin('project_game_official.region',          'project_game_official_region');

return $qb;
}

You can use the above query to load a single game:

public function find($id)
{
if (!$id) return null;

$qb = $this->createQueryBuilderForGames();
$qb->where('game.id = :id');
$qb->setParameter('id',$id);
return $qb->getQuery()->getSingleResult();
}

Or to load a bunch of games using a criteria object:

public function findGamesByCriteria(array $criteria)
{
$gameIds = $this->findGameIdsByCriteria($criteria);
if (count($gameIds) < 1) return [];

$qb = $this->createQueryBuilderForGames();

$qb->where('game.id IN(:game_ids)');
$qb->setParameter('game_ids',$gameIds);

$qb->addOrderBy('game.date','ASC');
$qb->addOrderBy('game.time','ASC');
$qb->addOrderBy('game.id',  'ASC');

return $qb->getQuery()->getResult();
}

Yes you do have to map your objects to database tables.
And sometimes the ORM loads more than you really need.
But using one can save quite a bit of development time.

The fact you can concentrate on the business logic in a much more object oriented way is the main goal. So you get the benefits of OOP. Less code writing, as Tom demonstrated, more reusability of your persistence logic, more modularity, better design and easier code maintenance. All good reasons, I would say.

Scott

Thanks everyone, for your replies, very, very useful.

Lemon_Juice, although I’m uninformed on the matter by gut feeling towards ORM matches yours exactly!

I’m going to have a go with Propel and Doctrine though and see how I get on with it. One thing that bothers me is performance; my library is very lightweight and lightening fast. If it causes slowdown I’ll probably leave it. I also don’t like the idea of declaring database schema outside of the main code.

This is a perfect example what I don’t like about ORM implementations because they only pretend to do object relational mapping. In my opinion ORM’s goal is to allow the developer to work with data represented as objects not relations. And what you are doing here is simply constructing an SQL query with query builder’s methods. It is conceptually equivalent to string concatenation with the added benefit of database independence - to some extent. With an ORM I am supposed to work with objects and here we are back to SQL construction and we have to write selects and joins?

Imagine now that we replace the underlying relational database with a no-SQL database or with a real Object Oriented Database - how will this query construction work then? Join becomes a foreign concept and everything falls apart. The ironic thing is that if an ORM interface were to be used with an OODBMS then, for the most part, the ORM should be transparent because OODBMS is queried with objects natively so there’s nothing really for the ORM to do. But what would happen here? The ORM would have to map these selects and joins back to objects and methods to be able to communicate with an OODBMS!

What I’m getting at is that real ORM system does not yet exist for PHP and if it did I image it would be much more complicated than Doctrine or whatever exists now. Of course, once the query results are hydrated into objects then we get the proper object oriented data to work with. But at this stage it’s impossible to get away from SQL even if we use ORM - unless we settle for the simplest CRUD.

1 Like

Then you couldn’t use an ORM at all. It is an Object RELATIONAL Mapper. If you changed the database to a document database like MongoDB, you’d need an ODM, like Doctrine ODM. Or if you had an object database, you wouldn’t need any mapping abstraction at all. I am not sure what your argument is here.

Scott

This is splitting hairs really. While technically correct, any ORM with a decent SoC should be able to use a different data source for its data and not rely on a database. Given my code above. $users could be reference a database table, a csv file, a web service. The point that Lemon_Juice was making was that this implementation detail is hidden from the application developer and makes for more easily reused code :slight_smile: Although not technically an “ORM”, “ORM” has frequently been used interchangeably with “data mapper” for convenience.

The point is if I changed to Doctrine ODM would the code above still work? Not at all because ODM’s API is different and will not handle constructing joins. And why is ODM’s API different from its ORM counterpart? If they were true object relational/document mappers then I wouldn’t need to use strictly relational API (for building SQL). I suppose ODM likewise has methods specific to document store. What I mean is that the mapping does not exist, we only use object methods to query the database still in a relational way - not in object-oriented way.

in which case the “object oriented” code should work out of the box. Would it? Not even close. You would need a convoluted DoctrineORM-ObjectDatabase mapping to get it to work and between the two end points (php language and the OODBMS) you would have these Doctrine relational methods that are completely unrelated to any of them!

My argument is that real ORM is impossible to achieve in PHP now and even large ORM libraries like Doctrine do it only partially. Therefore, it’s not really important to use all the query and criteria construction mechanisms because they don’t get you any closer to the objective paradigm - you are still working in the relational paradigm, in which case constructing plain SQL will not change the overall concept or pattern here. I’m not saying you should or shouldn’t (as there may be other reasons) but just be aware you get no object-relational mapping in these cases.

Just what Tom said - the implementation detail should (ideally) be hidden but in Doctrine it’s very visible in many places.

Just a shameless plug for my own Data Mapper: https://github.com/TomBZombie/Maphper that does hide the underlying implementation :wink:

@Lemon_Juice
Apologies for my misleading post. My intent was to contrast a Doctrine ORM query with a SQL query. It was not meant to imply that such queries were required.

Out of the box, Doctrine allow you to:

$game = $gameRepository->find(42);

Once you have the game object you can navigate the object graph.

$location = $game->getLocation();
$homeTeamCoachName = $game->getHomeTeam()->getCoach()->getName();

Behind the scenes, Doctrine uses lazy loading by default to load the various relations. It’s really quite powerful and easy to use.

And just to be perfectly clear, $game,$location,$team,$coach are all objects.

Of course, lazy loading implies additional sql queries which can eventually start to impact performance. My example was just to show how a complete graph could be loaded with one sql query.

To shift to a nosql persistence layer just requires plugging in a different repository implementation.

You would think so and the different Doctrine libraries all do use core parts of Doctrine. However, the idiosyncrasies of the different data stores require quite a bit of dedicated code/ logic and thus, they are their own projects and also end up with their own “way of doing mappings”. So, after working a bit with MongoDB ODM, I’d venture to say, the “hiding of implementation details” wasn’t successful at all.

But the building of SQL isn’t a “normal” ORM activity really. At least it shouldn’t be. It is just a tool at your disposal, when the normal ORM API doesn’t cover the right bases. I’d say 80-90% of objects and their relationships can be handled well with the mappings. The rest need specialized queries and thus you have access to DBAL through the entity manager and the query builder.

Scott

I’ve had long history with ORM. At first, it was just a hyped technology that promoted ‘database interoperability’. In my 15 years in IT, I have yet to see a project that changed the RDBMS database with another RDBMS. It just doesn’t happen. In the beginning, ORM was really slow if you’re doing complex relation queries (aka subqueries) but it’s pretty good to use now.

Anyways, ORM is good now since there’s so many auto-code generation from database schema. If one decides to go step further, you can even create ORM REST client for JavaScript and Server Side Programming Language. However, if one decides to use straight up SQL then it’s perfectly fine! SQL isn’t made for Rocket Scientist. At least with ORM Technology I use can also run ‘native queries’, I really don’t see a reason to use straight up SQL.