What's your experience with Singleton DB classes ...

dalecosp

I have, laying around in the guts of an application, a 'classic' Singleton getInstance-type database class.

        /*
        Get an instance of the Database
        @return Instance
        */
        public static function getInstance() {
                if( !self::$_instance ) { // If no instance then make one
                        $s = new self();
                        self::$_instance        = $s->_connection;
                }
                return self::$_instance;
        }

I thought I might take some of the other classes that have their own "personal" DB connection and let them use the Singleton. What's your experience with this? More connections, or more use of a single connection ... which is better for performance & speed?

NogDog

I've always assumed that, since PHP is (usually) not multi-threaded, a single connection would be at least somewhat more efficient -- but I've never measured it. Whether you want/need a singleton class for that is optional, vs. just having one PDO or whatever object created in your initial config or such, and then pass it in to each thing that needs it. But if you want to apply it to existing code, a Singleton might be a good way to do it with minimal impact on the code base, if it's already using some common DB class?

Weedpacket

I have occasionally needed multiple DB connections (database migration; having to build an app-specific database so that the main db wouldn't get "contaminated" by the app's lookup table requirements or vice versa; or simply having to live with the fact that I had to connect to two different databases from different providers).

But I'd only want a single connection to each. I'm not sure how one PHP instance would handle two connection requests with the same credentials - whether they'd result in two connections or return the first connection for the second request - but the DBMS itself would create two distinct connections and stuff going on in one connection won't be visible in the other until the transactions commit. Fixing a bug from that would be like looking at a "spot-the-difference" puzzle: cross your eyes so the two pictures are in register and then look for the bits where your focus tries to fight back.

Putting those together I'd be inclined to make the Singleton a Factory (while still being a Singleton). It keeps a collection of which database connections have been made, and responds with existing ones when additional requests for them come through.

Note that disconnections have to pass through the factory/singleton as well, and if you really want the disconnection to happen the factory will need to at least keep a reference count of the number of times the connection has been requested. With a single connection, no-one using it could have the authority to close it in ignorance of who else might still be using it.

Derokorian

I use singleton connections, and what that means is my class holds an array of named connections - this way I can still easily have an arbitrary number of DB connections (maybe separate read and write, and maybe another for session, and whatever)

dalecosp

NogDog;11061427 wrote:
I've always assumed that, since PHP is (usually) not multi-threaded, a single connection would be at least somewhat more efficient -- but I've never measured it. Whether you want/need a singleton class for that is optional, vs. just having one PDO or whatever object created in your initial config or such, and then pass it in to each thing that needs it. But if you want to apply it to existing code, a Singleton might be a good way to do it with minimal impact on the code base, if it's already using some common DB class?

Thanks for getting the meeting started. I'll sit in the corner sucking my thumb while we discuss this, and you'll see why in a moment.

Weedpacket;11061437 wrote:
I have occasionally needed multiple DB connections (database migration; having to build an app-specific database so that the main db wouldn't get "contaminated" by the app's lookup table requirements or vice versa; or simply having to live with the fact that I had to connect to two different databases from different providers).

But I'd only want a single connection to each. I'm not sure how one PHP instance would handle two connection requests with the same credentials - whether they'd result in two connections or return the first connection for the second request - but the DBMS itself would create two distinct connections and stuff going on in one connection won't be visible in the other until the transactions commit. Fixing a bug from that would be like looking at a "spot-the-difference" puzzle: cross your eyes so the two pictures are in register and then look for the bits where your focus tries to fight back.

Putting those together I'd be inclined to make the Singleton a Factory (while still being a Singleton). It keeps a collection of which database connections have been made, and responds with existing ones when additional requests for them come through.

Note that disconnections have to pass through the factory/singleton as well, and if you really want the disconnection to happen the factory will need to at least keep a reference count of the number of times the connection has been requested. With a single connection, no-one using it could have the authority to close it in ignorance of who else might still be using it.

I have one app that has multiple DB connections. I'm not looking at it right now (it's Sunday evening here, so one good question might be what the heck am I doing on PHPBuilder, anyway? 😉) IIRC though, it does have a Singleton DB class and two subclasses for the other two connections, so I can call "MainDB::getInstance()", "RemoteDB::getInstance()", etc.

Now, here's the thing I'm in the corner stimming over (is that what the shrinks call it?). I've had this class sitting there for a Long Time, and the notes say it didn't work with the classes that called it, causing errors of the "cannot connect to DB" type. I was running through the classes for this app the other day and happened to open this one and found a bug. Ran a quick test and it seemed OK, so I inserted it into a Very Important Class and ran the live page on the production site; no detectable errors, page looks fine. Happy Dale 😃

So, how were the other classes all doing their DB stuff? Each one had a getDB() method which wrapped a connection call. So, for a script that had a Page class, a User class, a Product class, a Seller class, and a Category class, one call to the app was using 5 DB connections, ostensibly. (But, as you point out .... maybe not?) What I'm really curious about is if having them all access the Singleton will reduce DB server load.

Derokorian;11061443 wrote:
I use singleton connections, and what that means is my class holds an array of named connections - this way I can still easily have an arbitrary number of DB connections (maybe separate read and write, and maybe another for session, and whatever)

That's interesting. As I mentioned above, I've had success with a master class and subclasses for the other DB's ... that might be another valid way to skin the cat? Or what you might be saying here is that you can give a particular script as few or as many connections as you want, which might be something worth looking into, although unless you're running threads (which I've done but not frequently), I'm not sure how that would benefit us?

Derokorian

dalecosp;11061457 wrote:
I have one app that has multiple DB connections. I'm not looking at it right now (it's Sunday evening here, so one good question might be what the heck am I doing on PHPBuilder, anyway? 😉) IIRC though, it does have a Singleton DB class and two subclasses for the other two connections, so I can call "MainDB::getInstance()", "RemoteDB::getInstance()", etc.

The downside being that you have to create new classes for each connection you want to add, instead of being able to just add configuration.

dalecosp;11061457 wrote:
So, how were the other classes all doing their DB stuff? Each one had a getDB() method which wrapped a connection call. So, for a script that had a Page class, a User class, a Product class, a Seller class, and a Category class, one call to the app was using 5 DB connections, ostensibly. (But, as you point out .... maybe not?) What I'm really curious about is if having them all access the Singleton will reduce DB server load.

there is almost no noticeable load to having open connections, unless you are using those connections. However, there is a configurable connection limit that may refuse connection to new sessions. Remember the singleton is still only per request anyway, so you're not sharing the connection to multiple requests.

dalecosp;11061457 wrote:
That's interesting. As I mentioned above, I've had success with a master class and subclasses for the other DB's ... that might be another valid way to skin the cat? Or what you might be saying here is that you can give a particular script as few or as many connections as you want, which might be something worth looking into, although unless you're running threads (which I've done but not frequently), I'm not sure how that would benefit us?

I prefer configuration based additional connections, that way I can do [noparse]StorageFactory::openStorage('log', 'read');[/noparse], this gives me greater control over how the connection gets loaded. I can have the factory look for log connection name, if not it will use 'default', or it can use config in the root database config. Additionally it will check if the config has separate read/write settings, and if so, it will use the read setting.

sneakyimp

My first thought is that your singleton looks a bit like a Service Locator Pattern to me. I.e., your code, at any point it might need the db, can just call getInstance() and get the one and only connection to the database. I've heard that one of the drawbacks to using the Service Locator Pattern is that it can make Unit Testing difficult because, depending on how you've written your code, you may not be able to inject a mock implementation of your db for testing purposes. I have some vague recollection that someone referred me to Martin Fowler once and I see a lot of people link this article on Dependency Injection vs Service Locator. I've been a Dependency Injection zealot lately and my code tends to obtain the database connection from my framework (usually in a controller) and then passes that db connection to my classes. If that class instantiates other classes that need the db, then my outer class will hand the same db object into the subclasses. I.e., any custom class of mine that needs the db typically receives it as a constructor parameter. Theoretically, this would make it easier to unit test my code but I'm a loser and don't really unit test unless forced to. Side note: In practice, my dependency injection zeal leads to pretty extensive parameter lists sometimes. Every now and then I'll I cave in and pass in a config array but then I have to write code to validate the array of parameters -- or trust that they are correct (which is insecure and makes debugging harder).

My second thought is a vague recollection of Laravel's Facades vs. Dependency Injection article I read recently. I still haven't really absorbed whatever lesson is in there. I'm too busy hating Laravel.

Summary of first two thoughts; consider grabbing db object at a high level in your code and injecting it all the way down? Or if you hate passing the params around and want Service Locator, acknowledge potential difficulty of unit testing and also possibly impact on code reusability. If your code takes the initiative to grab a particular database, it can't be re-used to connect to another db without modification.

As for the use of a Singleton, it seems like a good idea to force your code to only make one db connection which sounds like a good way to protect yourself from the problem you described. I really like that you have a separate class for MainDB and RemoteDB -- seems to me this would be handy in a good IDE with autocomplete features and your code would be very clear. I imagine that the base DB class might be complicated but the extending classes MainDB and RemoteDB could be extremely simple (kinda like classes that extend Exception). I would point out that if you grab your DB at the top level of your code and inject it wherever it's needed, you might accomplish the One Database To Rule Them All thing without the use of a singleton. It would not be enforced as it is with the singleton approach and if you slipped up somewhere you might end up with an additional connection, but that probably wouldn't happen if you were consistent.

I agree that it's generally desirable to have only one connection to any given database. The 'too many connections' problem with a database is almost always the first problem I have when any website gets too busy. Anecdote: I was having this problem on an Amazon-hosted site at one point thanks to some automated spamming and hack attacks and we ended up using an enormous Amazon RDS instance with like 60GB of RAM. I had to increase the max_connections var to 2000 or something to keep the site running. This code only made one db connection for any given request.

Finally, I would add that in the parallel PHP programs I've written one often MUST create a new connection to the db for the forked process. If you don't, then the forked process may experience crosstalk with its parent process. E.g., if the parent process closes the db connection, the child loses its connection. I've not experimented thoroughly enough with this sort of thing to know exactly how it behaves, but I have eliminated problems by having a child process create its own, separate connection to the database.

Derokorian

I happen to like the SLP but also DI does make unit testing much easier. As such I use a mixed pattern like this:

    public function __construct(DataInterface $DB = null)
    {
        if (!$DB instanceof DataInterface) {
            $DB = Factory::GetDataInterface('default');
        }
        $this->DB = $DB;
    }

See, when my application builds the object it doesn't HAVE to inject the dependency, the object can request the default instance. However, during testing I just pass in a mock object and it doesn't use the SLP feature of the constructor. Additionally, I can just configure the object to use DI if I need something other than the default interface.

My factory will take care to only instantiate an object once if it is supposed to be a singleton. As you know I'm not good with words, so here's some code haha

    public static function buildClass(string $class, array $args = null)
    {
        if (in_array($class, static::$singletons)
            && array_key_exists($class, static::$instances)) {
            return static::$instances[$class];
        }

    $instance = null;
    $method = 'build' . (new \ReflectionClass($class))->getShortName();
    if (method_exists(static::class, $method)) {
        $instance = static::$method($args);
    }
    elseif (class_exists($class)) {
        $instance = new $class(...($args ?? []));
    }

    if (in_array($class, static::$singletons)) {
        static::$instances[$class] = $instance;
    }

    return $instance;
}

As for parallel/forked programs, for me every child process is a brand new bootstrap. I don't share anything between child and master. This way I never have to worry about crosstalk - every variable is unique to each process whether child or parent.