rexhin rexhin - 5 months ago 34
SQL Question

Singleton alternative for PHP PDO

This is my class that I'm using to connect to my

MySQL
database.
As you can see I'm using the
Singleton Pattern
but almost every post says it is a very bad pattern. What is the best approach to create a database connection class? Is there a better pattern?

class DB extends PDO {

function __construct() {
try {
parent::__construct('mysql:host=' . 'localhost' . ';dbname=' . 'kida', 'root', 'root', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'");
parent::setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo $e->getMessage();
}
}

public static function get_instance() {
static $instance = false;
if(!$instance) $instance = new self;
return $instance; //returns pdo object.
}
}

Answer

Using the singleton-pattern (or antipattern) is considered bad practice because it makes testing your code very hard and the depencies very convoluted until the project becomes hard to manage at some point. You can only have one fixed instance of your object per php-process. When writing automated unit-tests for your code you need to be able to replace the object the code you want to test uses with a test-double that behaves in a prdictable manner. When the code you want to test uses a singleton, then you cannot replace that with a test double.

The best way (to my knowlege) to organize the interaction between objects (like your Database-Object and other objects using the database) would be to reverse the direction of the depencies. That means that your code is not requesting the object it needs from an external source (in most cases a global one like the static 'get_instance' method from your code) but instead gets its depency-object (the one it needs) served from outside before it needs it. Normally you would use a Depency-Injection Manager/Container like this one from the symfony project to compose your objects.

Objects that use the database-object would get it injected upon construction. It can be injected either by a setter method or in the constructor. In most cases (not all) is it better to inject the depency (your db-object) in the constructor because that way the object that uses the db-object will never be in an invalid state.

Example:

interface DatabaseInterface
{
    function query($statement, array $parameters = array());
}

interface UserLoaderInterface
{
    public function loadUser($userId);
}

class DB extends PDO implements DatabaseInterface
{
    function __construct(
        $dsn = 'mysql:host=localhost;dbname=kida',
        $username = 'root',
        $password = 'root',
    ) {
        try {
            parent::__construct($dsn, $username, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'");
            parent::setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            echo $e->getMessage();
        }
    }

    function query($statement, array $parameters = array())
    {
        # ...
    }
}

class SomeFileBasedDB implements DatabaseInterface
{
    function __construct($filepath)
    {
        # ...
    }

    function query($statement, array $parameters = array())
    {
        # ...
    }
}

class UserLoader implements UserLoaderInterface
{
    protected $db;

    public function __construct(DatabaseInterface $db)
    {
        $this->db = $db;
    }

    public function loadUser($userId)
    {
        $row = $this->db->query("SELECT name, email FROM users WHERE id=?", [$userId]);

        $user = new User();
        $user->setName($row[0]);
        $user->setEmail($row[1]);

        return $user;
    }
}

# the following would be replaced by whatever DI software you use,
# but a simple array can show the concept.


# load this from a config file
$parameters = array();
$parameters['dsn'] = "mysql:host=my_db_server.com;dbname=kida_production";
$parameters['db_user'] = "mydbuser";
$parameters['db_pass'] = "mydbpassword";
$parameters['file_db_path'] = "/some/path/to/file.db";


# this will be set up in a seperate file to define how the objects are composed
# (in symfony, these are called 'services' and this would be defined in a 'services.xml' file)
$container = array();
$container['db'] = new DB($parameters['dsn'], $parameters['db_user'], $parameters['db_pass']);
$container['fileDb'] = new SomeFileBasedDB($parameters['file_db_path']);

# the same class (UserLoader) can now load it's users from different sources without having to know about it.
$container['userLoader'] = new UserLoader($container['db']);
# or: $container['userLoader'] = new UserLoader($container['fileDb']);

# you can easily change the behaviour of your objects by wrapping them into proxy objects.
# (In symfony this is called 'decorator-pattern')
$container['userLoader'] = new SomeUserLoaderProxy($container['userLoader'], $container['db']);

# here you can choose which user-loader is used by the user-controller
$container['userController'] = new UserController($container['fileUserLoader'], $container['viewRenderer']);

Notice how the different classes no not know about each other. There are no direct depencies between them. This is done by not require the actual class in the constructor, but instead require the interface that provides the methods it needs.

That way you can always write replacements for your classes and just replace them in the depency-injection container. You do not have to check the whole codebase because the replacement just has to implement the same interface that is used by all other classes. You know that everything will continue to work because every component using the old class only knows about the interface and calls only methods known by the interface.

P.S.: Please excuse my constant references to the symfony project, it is just what i am most used to. Other project's like Drupal, Propel or Zend probably also have concepts like this.

Comments