php singleton database connection, is this code bad practice?

Question

I'm trying to create a simple to use singleton class to connect to mysql database and do queries, the code works fine and i haven't had any problems with it, but since I'm new to OOP I'm wondering whether this is bad practice or not.

Here's the class

class Database {
private $databaseName = 'dbname';
private $host = 'localhost';
private $user = 'user';
private $password = 'pass'; 
private static $instance; //store the single instance of the database

private function __construct(){
    //This will load only once regardless of how many times the class is called
    $connection = mysql_connect($this->host, $this->user, $this->password) or die (mysql_error());
    $db = mysql_select_db($this->databaseName, $connection) or die(mysql_error()); 
    echo 'DB initiated<br>';
}

//this function makes sure there's only 1 instance of the Database class
public static function getInstance(){
    if(!self::$instance){
        self::$instance = new Database();
    }
    return self::$instance;     
}

public function connect() { 
    //db connection
} 
public function query($query) {
    //queries   
    $sql = mysql_query($query) or die(mysql_error()); 
    return $sql;
}

public function numrows($query) {
    //count number of rows  
    $sql = $this->query($query);
    return mysql_num_rows($sql);
}


}

//Intantiate the class
$database = Database::getInstance();

and when i want to use the class I would do:

$query = "SELECT * FROM registrations";
echo $database->numrows($query);
$sql = $database->query($query);

the Singleton pattern is often implemented in Database Classes, Loggers, Front Controllers or Request and Response objects. — xkeshav, Feb 10 '12 at 12:05
There are loads of db classes out there you can look at. Even look at frameworks such as codeigniter to see how their db classes work. — Thomas Clayson, Feb 10 '12 at 12:06
possible duplicate of [Who needs singletons?](http://stackoverflow.com/questions/4595964/who-needs-singletons) — Gordon, Feb 10 '12 at 12:08
You may want to add an optional parameter on your `getInstance()` to capture instances where you need a specific connection normally referred as formal parameter `$link_identifier` — mdprotacio, Feb 10 '12 at 12:11
@Gordon : would you like to describe me more into chat: it would be helpful for me. :) — xkeshav, Feb 10 '12 at 12:15
@diEcho its really all explained in http://stackoverflow.com/a/4596323/208809 and given links therein. The ones you mention in particular can easily be dealt with by http://butunclebob.com/ArticleS.UncleBob.SingletonVsJustCreateOne — Gordon, Feb 10 '12 at 12:17
Thank you Gordon.Thank you. I want to ask one lil Issue. what are difference between the usage of `public static db` or `private static db`?? — xkeshav, Feb 10 '12 at 12:21
@diEcho Visibility is explained at http://php.net/manual/en/language.oop5.visibility.php — Gordon, Feb 10 '12 at 12:36
possible duplicate of [Global or Singleton for database connection?](http://stackoverflow.com/q/130878/) — outis, Feb 10 '12 at 18:03

score 14 · Accepted Answer · answered Feb 10 '12 at 12:21

Singletons are bad news.

They introduce global state into a program. Most programmers should be familiar with why global state is bad.
They introduce tight coupling between the singleton and any class that uses it. This means you can't reuse the classes in question without reusing the singleton too.
They make unit testing of classes that depend on the singleton problematic because you can't easily replace the singleton with a mock.
They encourage a coding style where classes attempt to resolve their own dependencies. This is bad because it can reduce clarity regarding what dependencies the class has.
PHP has a Share Nothing Architecture, meaning that PHP singletons aren't really singletons at all, there can be multiple instances alive at any one time (one per open request).
What happens if you suddenly discover at some later date that you actually need more than one of the resource that's being provided by the singleton? It's a more common scenario than you might think

You're better off looking at dependency-injection instead, as it resolves the above issues.

Can you provide some good articles on `dependency-injection` and some explanation why "Registry for database" is bad idea? (I know I can google it, but something with confirmed quality and from reliable sources) — Vyktor, Feb 10 '12 at 12:29
Registries are like Singletons in most respects (global state, encourage classes resolving dependencies, etc). As for DI, there's plenty of articles out there if you google, like you said. :) I believe the documentation for the Symfony framework includes a pretty good discussion of DI in a PHP context. — GordonM, Feb 10 '12 at 12:34
What about autoloader class, with (extension of my answer) `get($connName)` will initialize connection automatically with `configs/$connName.ini`? I understand that you should provide "Model" with connection from "Controller" and model shouldn't do anything like `DBs::get('export')` but what's wrong on having default connection specified? — Vyktor, Feb 10 '12 at 12:39

score 2 · Answer 2 · answered Feb 10 '12 at 12:14

I think a singleton can be OK for a connection manager, but not for a connexion itself.

You never know when you'll need to have an extra connection for a specific part of your development. Let say you suddently need to add a synchronisation with a remote database.

A connection manager (who can manage multiple connection) can be a singleton. A connection itself; no.

You connection manager should also be able to load "Drivers", so that you'll be able to instanciate a MySQL connection and the day you need msSQL, sqLite or anything else, you're able to add the needed drivers.

score 0 · Answer 3 · answered Feb 10 '12 at 12:10

0

This pattern will be fine because the singleton will only apply to the current user session. The decision really comes down to what your priority is. If you want faster performance for the user then you want to allow more database connections per user, but if you want to limit how hard your database gets hit then the singleton gives you a good middle of the road.

answered Feb 10 '12 at 12:10

davidethell

11,708
6
43
63

2

Singletons are bad news. They should be avoided. – GordonM Feb 10 '12 at 12:25
@GordonM, very true. Your counsel is the more prudent long term solution. – davidethell Feb 10 '12 at 16:32

score 0 · Answer 4 · answered Feb 10 '12 at 14:55

The only positive argument I've heard for the Singleton design pattern in PHP was from a developer who implemented a Singleton database connection in conjunction with a Memcached object. I didn't actually get a chance to look at the code and performance but he was able to put forward a coherent argument.

Personally I don't believe that the Singleton design pattern is very relevant to PHP, which is largely stateless anyway (as pointed out before each request will have a singleton).

php singleton database connection, is this code bad practice?

4 Answers4

Linked