This has been on my mind for a long, long time. Having been spoiled with SQLAlchemy and Elixir, I am tortured with sub-par ORM libraries while at work. Granted I did get to use SA for an internal project, but the majority of the things I do involves PHP.
I’ve been stuck with it for years. It is the language of choice for people who run servers and hate web developers. So yeah, 5 years of PHP and MySQL …and using so many different libs for database abstraction has given me some room to shoot my mouth off.
Anyway, this is going to be a long post and not for the faint of heart. I’m going to start off at the beginning…
1. I was a mysql_* junkie
It’s true — I was. But hey, it was PHP4 man! Love was free! No one knew about SQL injection just yet. We didn’t need features…just some SQL knowledge and a while-loop. Oh, how innocent we all were.
I’m never using this again because: It’s an unbelievably monstrous pain-in-the-nuts to use these functions. They’re totally unsafe and take no advantage over any of the features in the versions of MySQL greater than 4.0. It has been removed from the default PHP5 install.
2. Then I discovered, PEAR::DB and my journey started
I can’t remember how I discovered PEAR or any of the packages in it. I do know that I wrote a TON of boilerplate code with the mysql_* functions, especially with insert and update statements. So I imagine I was searching for some way to cut back on this and also I had probably started getting wind of SQL injection.
Holy crap — I totally found what I was looking for. The autoPrepare and autoExecute functions! This was perfect. The prepare/execute methods also helped me a great deal. It was truly a great day to be me.
I’m never using this again because: IT’S SO SLOW! I’ve never, ever, ever before or since had a single, legitimate PHP script increase the load time on a project to 5 seconds. It was unbelievable. Never mind the fact that the entire package has been deprecated. I gave it up long before. Also, error handling created a large amount of repetitive code. I actually had keyboard shortcuts in Eclipse (yeah, I said Eclipse) to paste a snippet for a query with error handling.
3. Time to find something else… look deeper at PEAR… find PEAR::MDB2
From the PEAR::MDB2 documentation:
PEAR MDB2 is a merge of the PEAR DB and Metabase php database abstraction layers.
More on that briefly. In the meantime, HOLY DAMN THIS IS WAY FASTER! How did they manage to get this *that* much fast than DB? Anyway it doesn’t matter. The romance was short lived….
I’m never using this again because: While I could consider this *my* fault if present-day Matt did this, back-in-the-day Matt took “merge of the PEAR DB…” statement to mean, “compatible API.” Wrong. I had to change every single query I wrote with PEAR::DB. I should have seen this as an omen…
Read the changelog. I can’t remember when I started using it exactly, but I have a feeling it was around Beta-2 when I saw the statement,
The core of MDB2 is now fairly stable API-wise.
Now check later releases…go ahead…I’ll be here. Notice anything? They all start out with, “Warning: BC breaks.” That’s Backwards-Compatibility to those who don’t know. Do you know what’s more frustrating to a developer than relying on a library that breaks backwards compatibility with every release? Nothing. You could pour fire ants down my ass-crack and taunt me with an Epi-Pen as my throat closes from anaphalyctic shock and I wouldn’t be so annoyed.
At this point I stopped using MDB2, waited for the API to stabilize, upgraded and fixed that dozen or so sites that used it. Then I buried it inside the cemetery in my mind. Their release schedule probably made me start noticing how ridiculous the PHP community was when it came to Release Tags — but that’s a rant for another day.
4. MySQLi am the Walrus
I tried MySQLi.
I’m never using this again because: The API is worse than Hitler.
5. PDO plz
Around 2006, I started hearing some buzz about ORMs and I thought PHP Data Objects was one. It’s not. It’s a DB abstraction API. That said, it was clearly the best attempt so far to some kind of standard. There are lots of goodies like prepared statements and parameter escaping. It tends to be a bit verbose, like any non-ORM DB layer.
I’m never using this again if I can help it because: Upgrading PHP becomes a hassle. PDO is built in (more or less) to the PHP stdlib. The problem is, none of the PDO db-drivers are — they’re all extensions. So when I upgrade PHP, I have to rebuild the database driver. Yeah, I’m lazy.
6. ADOdb FTW!
Now we come to my latest choice of library. I knew about ADOdb long ago, but for some reason, I didn’t use it. It wins for a bunch of reasons. I love it now though.
- It’s fast. I’ve never felt a performance hit because of it.
- It’s mature. I don’t know if the API has *ever* changed.
- It supports PHP4. Yeah I know, not even PHP supports PHP4, but tell that to some of these hosting providers. Unfortunately, in the real world, we still have to deal with 4 from time to time. (I’m looking at you, Yahoo).
- It doesn’t require PEAR. I’ve worked with a few hosts *cough*hostmysite*cough* :) who insist that upgrading PEAR will break everything and the world will die, famine will spread, the dead will rise from the grave and they might have to stay 5 minutes late. Installing PEAR to a shared host is a pain and if I don’t have to do it — that’s a major selling point.
- There’s another reason it wins that I will get in to shortly…
7. Release me from this tainted ORM
Ok, so even though ADOdb is the clear cut winner for me in the Abstraction Layer department, writing boilerplate-queries is still bothersome. I may have tried every single ORM for PHP that there is and there is only one that I can stand. So here, in no particular order are the crappy ones:
Zend_Db_Table
I don’t know who thought including this was better than making an ActiveRecord clone, but they need to experience Deep Hurting. I was using ZF since it’s pre-release days and I stopped using ZF before it’s actual release. They pulled the same, “Oh we said it was ok for production use, so we’ll change the API and give you absolutely no graceful deprecation.”
I’ve since looked at the Zend_Db_Table documents and man, it’s pure evil. The way to handle relationships is unbelievably verbose and it actually sickens me to think I wanted to contribute to this project.
CakePHP’s ORM
I have a fair amount of respect for CakePHP. The ORM *isn’t* the worst on this list, but what is up with using arrays for EVERYTHING? I can almost excuse using arrays for function parameters (things get messy very quickly), but for result sets? Objects in PHP exist for a reason, USE THEM!
Doctrine
In fairness, I’ve never gotten Doctrine to work. Why? Because the documents are written by The Riddler. I’ve installed Doctrine a few times and have never produced working models with it. I also don’t like defining the schema with YAML or whatever and using a command line tool to generate the code. You can generate models from an existing Schema, but defining the relations is manual and tedious.
Propel
There are things that bother me about the interface for retrieving data. ::retrieveByPK()? What’s wrong with retrieve(), select(), load()…do we really have to state ByPK? Why not go the full 10 yards and make it retrieveByPrimaryKey()?
The other thing — do*() methods? Really? I mean, great.
XML Schemas… come on.
On top of that, it requires Phing, which didn’t want to be installed when I was testing Propel. See ya.
ezPDO
I was almost ready to attempt to use ezPDO once, but then I remember how dumb of an idea it is to put code in the docstrings. Yeah, I didn’t make that up. It used to bother me that PEAR standards stipulate that people put some kind of typing scheme in the docstrings for function parameters. *sigh* But ezPDO takes it to a sinister new level.
CodeIgniter
I haven’t used CodeIgniter’s ORM, but reading the documents has made me a little peeved that they call it an ActiveRecord implementation. ActiveRecord has methods attached to it, like save() etc. Without those, it’s just Record.
And the rest…
There are a bunch of other ones that I won’t bother downloading:
- dORM — Requires an XML schema and also uses getters and setters for data access.
- Rocks — Sucks :) No really though, their website kept dying while I was trying to read the documents. Max execution time exceeded. When I finally got to it, I had to register for most of it.
- Coughphp — More getters and setters. Java is like herpes. It just keeps spreading and you can’t get rid of it.
and on and on.
The clear cut winner, in my opinion, was right under my nose the whole time. That’s right, ADOdb_Active_Record. It has the simplest API — no schema definitions needed, no models needed to be created before hand, no getter-setter methods, no field name mangling, simple debugging…
It’s not without it’s problems though. No N:M relations, although 1:m and m:1 are breath-takingly simple. Development on it is a bit sluggish, but I’ve been using it successfully for a few projects now and I’ve had no problems.
As far as performance? I have no idea. It’s not PEAR::DB slow, so I don’t care.
8. In conclusion
I’m sticking with ADOdb for now, but I’ll always be on the look for something better. Maybe when PHP gets namespaces, SQLAlchemy can be ported?