Singleton DB connection: good or bad practice?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
steel_rose
Forum Newbie
Posts: 15
Joined: Thu May 13, 2010 9:17 am

Singleton DB connection: good or bad practice?

Post by steel_rose »

Hi all,

As I was studying the singleton pattern recently I found that most articles suggest database connections as a possible implementation, as usually you want only one connection open at any time and you want to be able to open it wherever you are.
I was therefore wondering if it's common practice to use the singleton pattern to open database connections in most cases...
Any thoughts?

Thank you,

SR
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Singleton DB connection: good or bad practice?

Post by Weirdan »

I wouldn't call that good practice (and yes, the application I work on usually use more than one connection at a time). Singletons are essentially glorified globals - so they should be used sparingly, by a programmer fully aware of their implications (increased coupling).
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Singleton DB connection: good or bad practice?

Post by Christopher »

Like any global, they seem great when you start ...
(#10850)
steel_rose
Forum Newbie
Posts: 15
Joined: Thu May 13, 2010 9:17 am

Re: Singleton DB connection: good or bad practice?

Post by steel_rose »

I see what you mean, globals should be avoided and when talking about a "simple" database connection it's not usually necessary to go through a singleton.
However, I'm now slightly confused.... when should I consider a singleton a good idea? What are practical real-life applications? I tend to find that most articles about singleton reference database connections as examples!

Many thanks,

SR
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Singleton DB connection: good or bad practice?

Post by Christopher »

For me, the only place that makes sense to use a Singleton is if you are going to have one and only one place from which to get some kinds of objects. So the Singleton is not used as a thing itself, but as a place to find other objects. So Factory, ActiveRecord, Registry, ServiceLocator are all possible candidates for Singleton. However I would prefer to use a actual objects for those because it makes testing easier and it removes any subtle design compromises that introducing a global always seems to induce (when I have used them). And it takes some time before I discover the error of my ways and even more to unwind the problem.
(#10850)
steel_rose
Forum Newbie
Posts: 15
Joined: Thu May 13, 2010 9:17 am

Re: Singleton DB connection: good or bad practice?

Post by steel_rose »

Thank you very much for the thoughts and advice!

SR
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Singleton DB connection: good or bad practice?

Post by Darhazer »

It's not good idea, connection pulling is always better, for example if you run unbuffered query and you want to run queries while you process the results from the unbuffered one, you will need second connection.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Singleton DB connection: good or bad practice?

Post by josh »

Christopher wrote: So Factory, ActiveRecord, Registry, ServiceLocator are all possible candidates for Singleton..
I don't see how those are any different than the DB connection. Having a singleton factory is just as insidious as having a singleton DB, for exactly the same reasons. The implementation doesn't change the fact its a pattern of coupling. I would say singeton should be used to enforce one instance.

I guess the part you're not getting (OP) is that the singleton is supposed to enforce one instance, but the part that you *should not* do is use singletons to "pull" objects out of space.

Code: Select all

function doWork( $DB )
{
 // No need to "pull" $DB out of global space here, it is passed to us and assume to be a good DB object.
}
With the above function, the $DB may in fact be enforced w/ a singleton from the bootstrap of the application, but after that it is treated like a regular object. If I want to get rid of the singleton, I don't have to change any code.

With this above function, a programmer could have omitted the $DB parameter so they "dont have to pass objects around" (which isn't true anyways), in that case is where you run into trouble with a singleton:

Code: Select all

function doWork()
{
 $DB = Global_Space::Create();
 // Now we are abusing globals.
}
Now if I want to get rid of my singleton, I have to change possible 1000s of "doWork" style methods.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Singleton DB connection: good or bad practice?

Post by Christopher »

josh wrote:I don't see how those are any different than the DB connection. Having a singleton factory is just as insidious as having a singleton DB, for exactly the same reasons. The implementation doesn't change the fact its a pattern of coupling. I would say singeton should be used to enforce one instance.

I guess the part you're not getting (OP) is that the singleton is supposed to enforce one instance, but the part that you *should not* do is use singletons to "pull" objects out of space.
I didn't say I liked or would implement any of those as Singletons. I was acknowledging that people smarter than me implement those patterns as Singletons -- so I assume that they are possibilities.

As for a DB Connection implemented as at Singleton, I have many apps that use multiple connections -- so I could not use Singleton by your one instance definition. But a Singleton Factory that returns DB Connection objects is a possible solution for me, even though I do not choose to implement it that way.
(#10850)
Post Reply