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

I don't want to create a discussion about singleton better than static or better than global, etc. I read dozens of questions about similar subjects on SO, but I couldn't come up with an answer to this SPECIFIC question, so I hope someone could now illuminate me by answering this question with one (or more) real simple EXAMPLES, and not just theoretical discussions.

In my app I have the typical DB class to abstract the DB layer and to perform tasks on DB without having to write everywhere in code mysql_connect / mysql_select_db / mysql...

I could write the class either as a STATIC CLASS:

class DB
{
   private static $connection = FALSE; //connection to be opened

   //DB connection values
   private static $server = NULL; private static $usr = NULL; private static $psw = NULL; private static $name = NULL;

   public static function init($db_server, $db_usr, $db_psw, $db_name)
   {
      //simply stores connections values, without opening connection
   }

   public static function query($query_string)
   {
      //performs query over alerady opened connection, if not open, it opens connection 1st
   }

   ...
}

OR as a SINGLETON:

class DBSingleton
{
   private $inst = NULL;
   private $connection = FALSE; //connection to be opened

   //DB connection values
   private $server = NULL; private $usr = NULL; private $psw = NULL; private $name = NULL;

   public static function getInstance($db_server, $db_usr, $db_psw, $db_name)
   {
      //simply stores conn开发者_C百科ections values, without opening connection

      if($inst === NULL)
         $this->inst = new DBSingleton();
      return $this->inst;
   }
   private __construct()...

   public function query($query_string)
   {
      //performs query over already opened connection, if connection is not open, it opens connection 1st
   }

   ...
}

Then after in my app if I want to query the DB i could do

//Performing query using static DB object
DB:init(HOST, USR, PSW, DB_NAME);
DB::query("SELECT...");

//Performing query using DB singleton
$temp = DBSingleton::getInstance(HOST, USR, PSW, DB_NAME);
$temp->query("SELECT...");

To me Singleton has got the only advantage to avoid declaring as static each method of the class. I'm sure some of you could give me an EXAMPLE of real advantage of singleton in this specific case. Thanks in advance.

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

Making DB library static is certainly shorter and quicker, than doing:

$db = DBSingleton::blabla(); // everytime I need ya

But also, since it is global, tempting to use everywhere.

So, choose other methods if you want clean code... and choose static if you need quick code ;-)

/* Data base*/
 class Database
{
    /* Database field definition */
    private static $_instance; /instance
    private $_connection;
    private $DB_USER = "database_user_name_here";
    private $DB_PASS = "your_password_here";
    private $DB_NAME = "your_database_name_here";
    private $DB_SERVER = "localhost";

    /* Initiate the database connection */
    private function __construct()
    {
        $this->_connection = new mysqli($this->DB_SERVER ,
                                        $this->DB_USER ,
                                        $this->DB_PASS ,
                                        $this->DB_NAME);
        /* Test if connection succeeded */
        if (mysqli_connect_errno()) {
            die("Database connection failed: " .
                mysqli_connect_error() .
                " (" . mysqli_connect_errno() . ")"
            );
        }
    }

    /**
     * Instance of the database
     * @return Database
     *
     */
    public static function Instance()
    {
        if (!self::$_instance) { // If no instance then make one
            self::$_instance = new self();
        }

        return self::$_instance;
    }

    /**
     * Void duplicate connection
     */
    private function __clone() { }

    /* Return a connection */
    public function getConnection()
    {
        return $this->_connection;
    }

}

/** This is how you would use it in a different class.
  @var TYPE_NAME $connection */
$db = Database::Instance();
$connection = $db->getConnection();