1

The discussion of using a Singleton or not is out of scope for this question.

That said, I've found online multiple database connection classes using PDO and following the singleton pattern.

Here is one for example :

<?php
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_CHAR', 'utf8');

class DB
{
    protected static $instance = null;

    protected function __construct() {}
    protected function __clone() {}

    public static function instance()
    {
        if (self::$instance === null)
        {
            $opt  = array(
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => FALSE,
            );
            $dsn = 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHAR;
            self::$instance = new PDO($dsn, DB_USER, DB_PASS, $opt);
        }
        return self::$instance;
    }

    public static function __callStatic($method, $args)
    {
        return call_user_func_array(array(self::instance(), $method), $args);
    }

    public static function run($sql, $args = [])
    {
        if (!$args)
        {
             return self::instance()->query($sql);
        }
        $stmt = self::instance()->prepare($sql);
        $stmt->execute($args);
        return $stmt;
    }
}

However, according to this Stackoverflow answer Is it necessary to close PDO connections , some users suggest that it's a good practice to close all connections at the end of the script.

So: 1. Why does no one include a destroy/close method? 2. How would it be done right (destroying a PDO inside a singleton?)

My approach would have been:


class DB
{
    // ... the whole singleton

    public static function close()
    {
        self::$instance = null;
        return null;
    }

}

$pdo = DB::getInstance();
// do stuff
$pdo = DB::close($pdo);
Advena
  • 1,664
  • 2
  • 24
  • 45
  • 1
    I think the one point made in the thread you point to is that it can be difficult to know when to close a connection. The code gets extended and suddenly the code tries to use a connection which has been already closed. As PHP will close the connection anyway you can stop the guesswork of *is this connection still needed* – Nigel Ren Dec 02 '19 at 19:14
  • The "right" way to do it, would be in a `__destruct`. Then you get the benefit of not forgetting it. – colburton Dec 02 '19 at 19:16
  • 1
    _"Why does no one include a destroy/close method?"_ Because it's virtually never needed, and doing so is more likely to introduce a problem than prevent a problem that you're not actually having. – Alex Howansky Dec 02 '19 at 19:47
  • @NigelRen So, a DB singleton doesn't need any closing method? – Advena Dec 02 '19 at 20:12
  • @colburton __destruct, __construct and all other __ methods are generally speaking not part of a singleton pattern – Advena Dec 02 '19 at 20:15
  • @AlexHowansky so I should simply go along with that single db connection and not close it? – Advena Dec 02 '19 at 20:15
  • 3
    Explicitly closing is not required and not recommended, it doesn't matter if it's a singleton or not. If you haven't specifically encountered an issue that requires force closing (you likely never will) then don't. – Alex Howansky Dec 02 '19 at 20:25
  • @AlexHowansky okay thanks. Is this for PDO in general or only with MySQL? – Advena Dec 03 '19 at 12:57
  • 1
    The principle of automatically closing the PDO connection at the end of any PHP script (as stated in the chapter [Connections and Connection management](https://www.php.net/manual/en/pdo.connections.php), between _"Example #2"_ and _"Example #3"_, and also cited in the accepted [SO answer](https://stackoverflow.com/a/15444763/9455607) from the link provided by you) is applied to [all PDO_* drivers](https://www.php.net/manual/en/pdo.drivers.php) implementing the [PDO interface](https://www.php.net/manual/en/book.pdo.php). – PajuranCodes Dec 03 '19 at 13:45
  • 1
    Additionally, the [MySQLi driver](https://www.php.net/manual/en/book.mysqli.php) follows the same principle as any [PDO_* driver](https://www.php.net/manual/en/pdo.drivers.php) (see _"Connection pooling"_ in the chapter [Connections](https://www.php.net/manual/en/mysqli.quickstart.connections.php)): _"By default, every database connection opened by a script is either explicitly closed by the user during runtime or released automatically at the end of the script."_ – PajuranCodes Dec 03 '19 at 13:52

0 Answers0