This is a nice pattern to use, I am sure you realise that it is a variant of the singleton object. *REACHES for DP*

Singleton Intent Ensure a class only has one instance and provide a global point of access to it.

A singleton is a powerful tool BUT I would not use it in this case. The key issue here is why do you need a singleton? Is it to preserve some design decision to force a single instance of an object? .. no there will be many database connections .. is it to create a global variable by stealth, and so to extend the scope of an object? .. in this case I would say yes it is. Wilst a singleton offers many advantages over global variables and the associated namespace cluttering, giving a variable global access is indicitive of an incorrect design decision. An object VERY rarely needs global scope, you need to define clearly the scope and life of an object then enforce those rules.

--

Zigster

I believe this is bad advice.

Having a variable available from anywhere through a function has two advantages over a global. First it gains some protection from being modified anywhere you want (though not enough in this case because anyone can close the connection). Secondly it allows you to put off the decision over whether to initialize the variable as long as possible. If you need it, it will be there, but if you never needed it you didn't want to do the work. The third win is that if the variable has a complex initialization then every place it is used you document where to look for its initialization. That can be nice in a complex system.

The second is the real win here.

Connecting to a database takes a lot of work. For many useful programs the database connection takes more time than the entire program does! This is not the kind of operation that you want to possibly wind up doing repeatedly in a loop.

Furthermore this is an operation that is common to want to do from many places for many reasons. Which is why the appropriate life of a database connection is often longer than the life of your script! In fact that is one of the main wins that make people go to mod_perl, perlex, and friends. That you can have database connections that be reused across multiple web pages.

Incidentally the above point is why I think it is important to know why things are good design decisions. Zigster's advice as abstract advice is along a line that is generally recognized as the right thing to do. Were it practically any other example I would (probably silently) be in agreement with him.

But the cost of this advice is that wind up creating and destroying things a lot. Usually the overhead of creation and destruction is justified from reduced conflicts and easier to follow code. But in this case the excess creations can quickly turn into an..interesting..stress test for your database...

I was talking about correctly scoping an object, this would involve the object being constructed whilst in scope and destroyed while out of scope. Now my point was that in this case as the scope is well defined so should be used. Now as database connections represent a scarce resource, keeping a connection open too long is not IMHO desirable.

If I were to solve a problem such as this I suppose I would take the middle ground. The point you make about complex initialisation is very valid, however I would disagree that this is something that the singleton pattern is best suited to resolve. I would suggest the factory pattern would be more appropriate here. I would wrap the database connection up in object that is scoped over the life of the query, and have this object constructed by the factory. I would probably make the query an object also, complete the query close the connection. If performance was really an issue and opening and closing connections was a concern I would NOT extend the scope of a connection DIRECTLY. I would implement connection pooling withing the database connection object via a faceted object (pattern not seen within DP but described in C++ for proffessional programmers, kinda like a cross between a factory and a singleton, an object that can doll out several of a suite of objects within it's control). In short my point was that the use of a singleton in this case covers up weaknesses in the design that could be resolved more elegently.

UPDATE I just want to say that I am not actually disagreing with tilly in this post. His comments are all valid (except the one where he disagrees with me *grins*) I think my views actually fit alongside his. This post is to expand upon my thinking not to create an argument.
--

Zigster