PHP - static DB class vs DB singleton object

Question

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

In my app I have the typical DB class needed to perform tasks on DB without having to write everywhere in code mysql_connect/mysql_select_db/mysql... (moreover in future I might decide to use another type of DB engine in place of mySQL so obviously I need a class of abstration).

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, withour opening connection
   }

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

   ...
}

or as a Singletonm class:

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 connections values, withour 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 wanto 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...");

My simple brain sees 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.

Answer 1

Benefits of singletons over static classes

1. Lazy creation, which would ensure that you don't allocate memory for that object until you need it. If you include db.inc it won't mean you allocate memory for the DB object if you don't use it.
2. Usage information via getInstance(); I.e. find out who's using the singleton.
3. You can create multiple instances of the singleton for load balancing purposes.
4. Construct a singleton with (constructor) parameters if necessary. Cleaner than passing parameters to an arbitrary method.
5. You can pass the singleton class reference dynamically. A static must always be used in a static fashion.
6. You can extend/interface singletons, unlike static classes. I.e. if you're building a framework.

There's few reasons to use static methods since they are wonky and cumbersome and don't provide many benefits over their singleton counterparts.

Answer 2

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.