pgsql vs. mysqli vs. PDO vs. Zend_DB in PHP

If your PHP app needs to talk to a database, there's a lot of debate as to whether you should use PHP's native database extensions (e.g. mysqli and pgsql) or PHP's new PHP Data Objects. mysqli and pgsql talk directly to their respective databases, whereas PDO is a database abstraction layer; it provides a common interface to talk to several different databases.

Many developers say that you should use PDO, but I disagree. The two primary reasons for using PDO are:

• You can connect to any major database with the same interface, which makes your application portable across databases. Simply change the connection string, and voila, you're talking to SQL Server instead of MySQL!
• Because you're only using one interface, you don't have to remember different PHP commands for dealing with different databases. No need to worry that mysqli_connect() takes an entirely different set of arguments than pg_connect().

Those are both very valid points, and on the surface they sound great. But consider this:

• If you're building anything but the most trivial application, you're going to need to use the fancy features of your database (stored procedures, sequences, check constraints, etc.), and every database supports a different set of fancy features. The native functions built into PHP have much better support for the fancy features, whereas PDO (and other database abstraction layers) generally don't, as it would violate their principle of being database neutral.
• While being able to switch databases sounds great in theory, how often have you actually done it? While there may be business reasons for switching from Oracle to MySQL (e.g. saving $$$), the databases that PHP apps most commonly use are all free or open source. It's almost always easier to install a copy of whatever database you need than go change your code (and then retest everything!) to get it to work with a database from a different vendor.
• Same thing goes for you, as a developer: how many databases do you actually use on a day-to-day basis? In the entire time I've been working with PHP, I've only used two in any serious production capacity: MySQL and PostgreSQL. PDO might be more attractive if I were consantly switching between four or five different databases, but its not that hard to remember just the mysqli_* and pg_* commands.

I don't hate PDO, and have used it in the past for simple apps, but I generally think you're better off using a native extension.

As of this writing (May 2009), Zend has a new database interface module out, called Zend_DB. It's sort of like a database abstraction layer combined with a set of database helper functions. It has methods like insert() and update() which let you automatically insert/update data without having to write a lot of SQL, as well as all the standard methods (e.g. query()) that any good abstraction layer provides. It actually uses the PHP database engines under the hood (mysqli, pgsql, PDO, etc.), and even gives you direct access to them via the getConnection() method.

I've been using Zend_DB recently, mainly with the mysqli engine, and so far I like it. It provides ready-made versions of a lot of the helper functions that I usually write myself, like quote() and quoteIdentifier(). And since it provides direct access to the underlying connection, it doesn't limit me from using advanced functionality if I need it. Still, Zend_DB does introduce one more layer your data has to go through, so it may be overkill for some projects. But it's certainly worth a look.