Best way to hide DB connection code in PHP5 for apps that only require one connection?

Question

Below I present three options for simplifying my database access when only a single connection is involved (this is often the case for the web apps I work on).

The general idea is to make the DB connection transparent, such that it connects the first time my script executes a query, and then it remains connected until the script terminates.

I'd like to know which one you think is the best and why. I don't know the names of any design patterns that these might fit so sorry for not using them. And if there's any better way of doing this with PHP5, please share.

To give a brief introduction: there is a DB_Connection class containing a query method. This is a third-party class which is out of my control and whose interface I've simplified for the purpose of this example. In each option I've also provided an example model for an imaginary DB "items" table to give some context.

Option 3 is the one that provides me with the interface I like most, but I don't think it's practical unfortunately.

I've described the pros and cons (that I can see) of each in the comment blocks below.

At the moment I lean towards Option 1 since the burden is put on my DB wrapper class instead of on the models.

All comments appreciated!

Note: For some reason the Stack Overflow preview is showing an encoded HTML entity instead of underscores. If the post comes through like that, please take this into account.

<?php

/**
 * This is the 3rd-party DB interface I'm trying to wrap.
 * I've simplified the interface to one method for this example.
 *
 * This class is used in each option below.
 */
class DB_Connection {
    public function &query($sql) { }
}

/**
 * OPTION 1
 *
 * Cons: Have to wrap every public DB_Connection method.
 * Pros: The model code is simple.
 */
class DB {
    private static $connection;
    private static function &getConnection() {
        if (!self::$connection) {
            self::$connection = new DB_Connection();
        }
        return self::$connection;
    }
    public static function &query($sql) {
        $dbh = self::getConnection();
        return $dbh->query($sql);
    }
}

class Item {
    public static function &getList() {
        return DB::query("SELECT * FROM items");
    }
}

/**
 * OPTION 2
 *
 * Pros: Don't have to wrap every DB_Connection function like in Option 1
 * Cons: Every function in the model is responsible for checking the connection
 */

class DB {
    protected static $connection = null;
    public function connect() {
        self::$connection = new DB_Connection();
    }
}

class Item extends DB {
    public static function &getList() {
        if (!self::$connection) $this->connect();
        return self::$connection->query("SELECT * FROM items");
    }
}

/**
 * OPTION 3
 *
 * Use magic methods
 *
 * Pros: Simple model code AND don't have to reimplement the DB_Connection interface
 * Cons: __callStatic requires PHP 5.3.0 and its args can't be passed-by-reference.
 */
class DB {
    private static $connection = null;

    public static function &getConnection() {
        if (!self::$connection) {
            self::$connection = new DB_Connection();
        }
        return self::$connection;
    }

    public static function __callStatic($name, $args) {
        if (in_array($name, get_class_methods('DB_Connection'))) {
            return call_user_func_array(
                array(self::getConnection(), $name), $args);
        }
    }
}

Answer 1

Based on your examples above, I'd say option 1 is the best - simplicity always wins, and you can handle a failed connection differently depending on the method (you might want to fail differently for a stored procedure call than a simple SELECT, for instance).

Answer 2

Semantically speaking I think option 1 makes the most sense, if you're treating DB as a resource then the DB_Connectioin is an object that it uses but not necessarily the object itself.

However, several things I caution you against. First, don't make your DB class have all static methods as it will strongly impact your ability to test your code. Consider instead a very simple inversion of control container like this:

   class DB {
    private $connection;
    public function &query($sql) {
        return $connection->query($sql);
    }
    public __construct(&$db_connection) {
        $this->connection = $db_connection;
    }
}

class Item {
    public function &getList() {
        return  ResourceManager::getDB()->query("SELECT * FROM items");
    }
}

class ResourceManager {
    private $db_connection;
    private function &getDbConnection() {
        if (!$this->connection) {
            $this->connection = new DB_Connection();
        }
        return $this->connection;
    }
    private $db;
    public static function getDB() {
        if(!$this->db) $this->db = new DB(getDbConnection());
    return $this->db;
}

There are significant benefits. If you don't want DB to be used as a singleton you just make one modification to ResourceManager. If you decide it should not be a singleton - you make the modification in one place. If you want to return a different instance of DB based on some context - again, the change is in only one place.

Now if you want to test Item in isolation of DB simply create a setDb($db) method in ResourceManager and use it to set a fake/mock database (simplemock will serve you well in that respect).

Second - and this is another modification that this design eases - you might not want to keep your database connection open the entire time, it can end up using far more resources than need be.

Finally, as you mention that DB_Connection has other methods not shown, it seems like the it might be being used for more than simply maintaining a connection. Since you say you have no control over it, might I recommend extracting an interface from it of the methods that you DO care about and making a MyDBConnection extends DB_Connection class that implements your interface. In my experience something like that will ultimately ease some pain as well.