PHP - a DB abstraction layer use static class vs singleton object?

What is wrong with the following (simplified) example:

class Database{    protected $_connection;    protected $_config;    public function __construct( array $config ) // or other means of passing config vars    {        $this->_config = $config;    }    public function query( $query )    {        // use lazy loading getter        return $this->_getConnection()->query( $query );    }    protected function _getConnection()    {        // lazy load connection        if( $this->_connection === null )        {            $dsn = /* create valid dsn string from $this->_config */;            try            {                $this->_connection = new PDO( $dsn, $this->_config[ 'username' ], $this->_config[ 'password' ] );            }            catch( PDOException $e )            {                /* handle failed connecting */            }        }        return $this->_connection;    }}$db1 = new Database( array(    'driver'   => 'mysql',    'host'     => 'localhost',    'dbname'   => 'test',    'username' => 'test_root',    'password' => '**********') );$db2 = new Database( array(    'driver'   => 'pgsql',    'host'     => '213.222.1.43',    'dbname'   => 'otherdb',    'username' => 'otherdb_root',    'password' => '**********') );$someModel       = new SomeModel( $db1 );$someOtherModel  = new SomeOtherModel( $db2 );$yetAnotherModel = new YetAnotherModel( $db2 );

This demonstrates how you can make use of lazy loading connections, and still have flexibility to use different database connections.

The database instances will only connect to their individual connection when an object that consumes one of the instances (in this case one of the models) decides to call a method of the instance.

In my most recent project, I actually went against the "good" design principles by making the database class entirely static. The reason behind this is that I used a lot of caching on PHP objects. Originally I had the database passed in through the constructor of each object as a dependency injection, however I wanted to make sure that the database didn't have to connect unless absolutely necessary. Thus, using a database as a member variable of that object would not have been practical because if you unserialized an object from the cache, you wouldn't want to connect to the database unless you actually performed an operation on it.

So in the end I had only two (public) static functions, Database::fetch() and Database::execute() which would check whether or not it had already connected, and if not, it would connect and perform the query. This way I wouldn't have to worry about deserialization and would connect as seldom as possible. It technically makes unit testing impossible though.

You don't always have to follow every single good practice. But I would still recommend against doing what I did since some would consider it premature optimization.

My advice: STOP using Singleton and static all together.

Why? Because you will insert dependencies that will render your code unusable in other projects, and will not allow to unit test it. Also forget about loose coupling if using singleton.

The alternatives? Dependency Injection. http://www.potstuck.com/2009/01/08/php-dependency-injection