How can I design a model layer without ORM, and when should I use it?

Question

I'm a PHP developer who started with CodeIgniter. In it, designing models was easy: it seemed like each method defined in models was the equivalent of static methods in a normal, objected-oriented environment.

I tried FuelPHP, which has an built-in active record style ORM, and PHP ActiveRecord to use with CodeIgniter. I found creating models was easy with ORM, and taught me best practices when designing models.

However, I didn't like that I was tied to a specific ORM implementation, and as I got better with SQL, I thought ORMs were a bit heavy when compared to how quickly I could retrieve stuff with SQL.

But when I try to design models from scratch without ORM, I'm lost: The ORM system of one class per table and queries with eager loading makes sense, and seems like worth the performance hit for using ORM.

In what situations should I avoid using ORM? How can I design models without using ORM, so that I can use SQL directly?

Answer 1

I didn't like the fact that i was tied to a specific ORM implementation...

Why not? Are you afraid you will pick the wrong one? That sounds like a case of "Not Invented Here." You are going to be dependent on the chosen language, and SQL, and the web framework, and a dozen other things. An ORM is just one more.

As long as the performance through an ORM is adequate, I would use it. If the ORM-generated queries are too slow for some pages, then create a SQL view or stored procedure to retrieve precisely the needed information, and map the results to a custom presentation class. Use the ORM for the mapping if it is convenient, otherwise you can map the SQL result set to an array of objects yourself.

Answer 2

In your OP you asked for an instance when using an ORM is "bad". I wouldn't go so far as to say ORM is bad, but it has consequences, not all of which are good.

1. ORM's generally follow the Active Record pattern* (because Data Mapper is a bit tricky)
2. this tends to impose design decisions on the database that favour application code

Here's a great quote from an article by Bill Karwin that sums up why this may not be such a great pattern (I couldn't find the thread on SO where it's referenced...): http://karwin.blogspot.com/2008/05/activerecord-does-not-suck.html

A single Model class may be backed by a database table, or multiple database tables, or perhaps even no database tables. Data persistence should be an internal implementation detail within a Model; the external API of the Model class should reflect its logical OO requirements, not the physical database structure.

With regards to "DAL", this is a deceptively simple acronym that encompasses a range of patterns. Personally I prefer using query generators like Zend_DB or NotORM, and writing custom methods to describe the relationships as and when I need to. It takes a bit more hand cranking, but you get intimate with the Domain Model (as opposed to the ORM Model...) and the database that supports it.

There are a couple of classic books on this subject that are well worth a read if you can track them down:

Data Access Patterns : Clifton Nock

Handbook of Relational Database Design

As an aside, it seems somewhat tragic to abandon RDBMS design and the SQL to interrogate it to the application layer! It may not be the new thing on the block, but it's a remarkable bit of technology expressing some very refined concepts that are arguably more robust than the short term design phase that will be applied to the immediate needs of a project. I've seen way too many apps that leave integrity constraints in the application layer, or fail to take into account the indexing/querying methods employed by the DB platform they are using. ORMs are undoubtedly a clever bit of kit, but so is SQL...

Here's another great quote on the subject that expresses this sentiment (with reference to the Table Module pattern:

In many ways this approach treats the relational database like a crazy aunt who's shut up in an attic and whom nobody wants to talk about http://martinfowler.com/eaaCatalog/tableModule.html

and here is another thread on SO that is very pertinent to this subject: Constraints in a relational databases - Why not remove them completely?