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?

And what is the contradiction? You can just set up the connections to be persistent and use a singleton. This model is used so that a single request won't connect twice to the database, but two different clients will run two different instances of your script so will use two different connections — mishu, Feb 17 '14 at 15:26
@mishu: Thank you for explaining it. Many of us did not think about it. — kta, Dec 14 '16 at 23:43

hek2mgl · Accepted Answer · 2019-10-27T20:06:38.707

15

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");
}

edited Oct 27 '19 at 20:06

answered Feb 17 '14 at 15:26

hek2mgl

152,036
28
249
266

thanks a lot. What would you suggest me otherwise? I read about [Dependency Injection](http://fabien.potencier.org/article/11/what-is-dependency-injection). Cheers! – LucasM Feb 17 '14 at 15:39
This is not related. I would just do `$db = new Db()` and then make sure that this instance is accessible from all levels of code which need them. Symfony2 is using their `Service` concept. IMO this makes sense and is a good design approach.. (Never thought that it will come the day when I'm saying this about symfony :) But I did, and think this is a nice implementation) ... Check this: http://symfony.com/doc/current/book/service_container.html – hek2mgl Feb 17 '14 at 15:43
15

Generally avoiding Singletons for the sake of avoiding singletons is considered bad practice as well. There is a correct time and place for every sort of pattern, just don't over or underuse them. – Gilles Lesire May 31 '17 at 07:31
@GillesLesire Indeed, DB singletons are one of the most applicable use cases for singletons in PHP. Followed by singletons used in WordPress plugins. – unity100 May 06 '19 at 14:38
@unity100 How do you make sure that multiple unit tests don't influence each other when implementing the `DB` class as a singleton? – hek2mgl May 06 '19 at 14:47
You mean like race conditions? Those can only occur in multithreaded environments. PHP is single threaded. Well at the script level. Multiple calls run in multiple threads. Of course those PHP thread all have their own DB Singleton. But those won't interfere with each other anyway. Setting up a singleton that spans across actual PHP sessions threads is impossible. But that's obviously not what the OP meant with a singleton. He simply wants to use the same 1 connection for 1 thread for db performance reasons. – Gilles Lesire May 07 '19 at 14:36
No, I'm not talking about race conditions. I'm asking how you make sure that multiple unit tests don't influence each other when they have to use the _same_ Singleton. – hek2mgl May 07 '19 at 14:46
@GillesLesire Probably let me explain a bit more. I'm talking about _state_. Something simple like a `$connected` instance var for example. Once you used the singleton in a test, subsequent tests have no chance to get a clean state any more because the Singleton pattern doesn't allow to create a fresh instance once one has been created. Tests are just an example. You'll find other examples. The strict singleton condition "Only a single instance can be created" isn't helpful to be implemented at class level. – hek2mgl May 07 '19 at 15:42
@hek2mgl You can use a singleton to get an instance of the DB connection to pass into functions/classes, while still easily passing in a mock DB connection object for testing. I think the issue you speak of only arises when someone uses a singleton's static `getInstance` method within a function or class method itself rather than injecting the instance. – Nathan Oct 27 '19 at 19:26
@Nathan When the code is written in a way that it takes the db object as a parameter, that's definitely a pro. But if you have that already, why not just pass something like `your_function($app->get_db_instance(reconnect=false))` instead of the singleton? This could return always the same instance over the (normal) lifetime of `$app`, but the return value (the db instance) is just not literally implemented as a Singleton. – hek2mgl Oct 27 '19 at 20:01

PHP singleton database connection pattern

1 Answers1

Linked