mysql - PHP singleton database connection pattern

Question

I've been working on a small project using PHP and MySQL. I've read a lot around about best practices on managing a connection and so on.

I've also implemented (following some posts found around) a singleton class to manage MySQL connections.

require_once 'config.inc.php';
class DbConn {

    private static $instance;
    private $dbConn;

    private function __construct() {}

    /**
     *
     * @return DbConn
     */
    private static function getInstance(){
        if (self::$instance == null){
            $className = __CLASS__;
            self::$instance = new $className;
        }

        return self::$instance;
    }

    /**
     *
     * @return DbConn
     */
    private static function initConnection(){
        $db = self::getInstance();
        $connConf = getConfigData();
        $db->dbConn = new mysqli($connConf['db_host'], $connConf['db_user'], $connConf['db_pwd'],$connConf['db_name']);
        $db->dbConn->set_charset('utf8');
        return $db;
    }

    /**
     * @return mysqli
     */
    public static function getDbConn() {
        try {
            $db = self::initConnection();
            return $db->dbConn;
        } catch (Exception $ex) {
            echo "I was unable to open a connection to the database. " . $ex->getMessage();
            return null;
        }
    }
}

But... If my website has like 10K visitors contemporaneously and I'm calling every time my singleton object, should I expect to have performance issues? I meant, shouldn't I have a kind of pool of connections instead of a singleton?

Answer 1

Using singletons in PHP is considered bad practice. From my experience the most problematic issue with them are unit tests. It is hard to ensure that two tests are independent when testing singletons.

I would delegate the responsibility for the constraint "only one instance should exists" to the code which creates the Db object.

Also for me it looks like there is a misunderstanding in how Singletons work in PHP in contrast to other languages: If you have 10.000 concurrent requests for example, then each request runs in a separate PHP process or thread, meaning they will all have their own instance of the "singleton", there is no sharing of this object for more than a single request (when running PHP in common web backend scenarios)

There is no "connection pooling" in PHP, but you can use mysqli persistent connections for mysql. It can be achieved by passing the p: in front of the hostname when creating mysqli. This might help here, but handle it with care (meaning read the documentation first)

---

However, just for theory, a singleton in PHP must be aware of the fact that someone could use clone. Meaning in your case it would be possible to do that:

$db = DB::getInstance();
$db2 = clone $db; 

To avoid that you can implement the __clone() method like this:

public function __clone() {
    throw new Exception("Can't clone a singleton");
}